ProxySQL读写分离

2018-06-17 23:55:13来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

我们首先看一下自己的环境:
MHA已经搭建:
master:172.16.16.35:3306
slave:172.16.16.35:3307
slave:172.16.16.34:3307

MHA manager在172.16.16.34,配置文件如下:

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""
ssh_user=root
user=root
[server1]
hostname=172.16.16.35
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=172.16.16.34
port=3306
[server3]
hostname=172.16.16.35
port=3307

 

下面我们基于这样一套MHA环境搭建读写分离。
1:安装ProxySQL软件,这个我们部署到172.16.16.34上
[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
[root@localhost bin]# yum install proxysql

 

最后有以下提示:
Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

 

也就是安装完成了。然后查一下具体的文件:
[root@localhost bin]# find / -name proxysql
/var/lib/proxysql
/var/run/proxysql
/etc/rc.d/init.d/proxysql
/usr/bin/proxysql

 

发现确实已经将ProxySQL安装成功了
2:启动配置ProxySQL
看一下配置文件:
[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"
export PROXYSQL_PASSWORD="admin"
export PROXYSQL_HOSTNAME="localhost"
export PROXYSQL_PORT="6032"
 
# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"
export CLUSTER_PASSWORD="admin"
export CLUSTER_HOSTNAME="localhost"
export CLUSTER_PORT="3306"
 
# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"
export MONITOR_PASSWORD="monit0r"
 
# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"
export CLUSTER_APP_PASSWORD="passw0rd"
 
# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"
export READ_HOSTGROUP_ID="11"
 
# ProxySQL read/write configuration mode.
export MODE="singlewrite"

 

启动:
[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

 

现在来说ProxySQL 的路由已经启动,提示我们要启动proxysql service
[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE!
[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

 

可以看到我们已经登录成功了,这里要说明的是 proxysql的默认配置文件是在:
[root@localhost bin]# find / -name proxysql.cnf
/etc/proxysql.cnf

 

接下来我们开始配置ProxySQL:
[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+
13 rows in set (0.00 sec)

 

下面加入主从信息:
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

 

然后添加读写映射(主要是MHA后端切换的时候保证ProxySQL也能够自动切换):
mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+
1 row in set (0.00 sec)

 

为ProxySQL添加监控账号:
mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

 

配置监控账号(在proxySQL当中进行配置):
mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 74 rows affected (0.02 sec)

 

PS:有时候runtime_mysql_servers的status不为ONLINE状态的话可以通过查看monitor.mysql_server_ping_log这个表来查看具体的报错信息。
mysql> select * from monitor.mysql_server_ping_log;
然后配置程序账号,简单点统一使用root:123456最高权限来配置:
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

 

配置完成以后开始重新加载和保存我们的配置:
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

 

接下来开始配置路由规则:
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

 

至此配置已经完成了
3:测试读写分离
在172.16.16.35上链接proxySQL端口6033,并且做简单的select操作:
[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from maxiangqian.test;
+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+
5 rows in set (0.04 sec)

 

然后在进行查询看一下:
mysql> select * from stats_mysql_query_digest;

可以看到已经完成了读写分离了。
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+
1 row in set (0.01 sec)

 

查看一下server ID已经路由到了172.16.16.35:3307这个从库上了。
测一下for update:
mysql> select * from maxiangqian.test for update;
+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+
5 rows in set (0.00 sec)

 

再查看
mysql> select * from stats_mysql_query_digest;

已经自动路由到了主库。
至此ProxySQL+MySQL MHA读写分离测试完成。。。未完待续
5:关于ProxySQL的思考以及简单的命令
ProxySQL通过以上方式是可以实现读写分离,但是这种方式真的就没有问题了吗,如果是一些比如查询订单状态的这种要求实时性非常高的SQL的话,似乎被路由到了从库就会出现BUG。我们可以选择在程序端控制这些参数,ProxySQL只作为一个负载均衡来使用,给ProxySQL创建多个账号,一个读写,一个只读。然后程序去实现读写分离。
ProxySQL是分三层来设计运行的,分别为RUNTIME ,MEMORY ,DISK :
RUNTIME 代表的是ProxySQL当前生效的配置,包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置,必须要从下一层load进来。
MEMORY 是平时在mysql命令行修改的 main 里头配置,可以认为是SQLite数据库在内存的镜像
DISK / CONFIG FILE 持久存储的那份配置,一般在$(DATADIR)/proxysql.db,在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到,完了后,如果要修改监听端口,还是需要在管理命令行里修改,再 save 到硬盘
常用命令:
LOAD MYSQL SERVERS TO RUNTIME -- 让修改的配置生效,也就是从MEMORY 把参数LOAD过来,等价于LOAD MYSQL USERS FROM MEMORY,这个语句的语法比较单间,FROM代表从上层LOAD过来,TO代表从本层到某一个层。比如前面我们设置了MySQL的监控账号,但是还是要执行LOAD和SAVE保存变量并且使变量生效。

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL--pt-osc工具学习

下一篇:数据库优化