ProxySQL读写分离

原创

sixsixLLL 2019-05-10 15:49:41 ©著作权

©著作权归作者所有：来自51CTO博客作者sixsixLLL的原创作品，请联系作者获取转载授权，否则将追究法律责任

中间件ProxySQL读写分离试验

主机	IP	作用
Master	192.168.37.7	主服务器
Slave	192.168.37.17	从服务器
ProxySQL	192.168.37.27	中间件服务器
Clinet	192.168.37.37	客户主机

前期准备: Master、Slave(从服务器my.cnf文件中必须要加上read_only,因为ProxSQL通过此语句判断主从服务器)先完成主从复制。ProxySQL安装Mariadb,Clinet最少要安装MySQL客户端

ProxySQL设置

设置ProxySQL yum源并安装,实验时版本为1.4

[root@poxysql ~]#vim /etc/yum.repos.d/proxysql.repo
[proxysql]
name=ProxySQLyum
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
[root@poxysql ~]#yum install proxysql

ProxySQL文件

[root@poxysql ~]#rpm -ql proxysql 
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

启动ProxySQL

[root@poxysql ~]#service proxysql start
Starting ProxySQL: 2019-05-10 14:15:18 [INFO] Using config file /etc/proxysql.cnf
DONE!

登录服务,ProxySQL默认用户为admin,密码为admin,管理端口为6032，客户使用端口为6033

[root@poxysql ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>

设置读写分离的主机的地址
在mysql_servers表中添加记录

MySQL [(none)]>insert into mysql_servers(hostgrup_id,hostname,prot)values(10,'192.168.37.7',3306);
Query OK, 1 row affected (0.01sec)
MySQL [(none)]>insert into mysql_servers(hostgrup_id,hostname,prot)values(10,'192.168.37.17',3306);
Query OK, 1 row affected (0.01sec)
MySQL [(none)]>  load mysql servers to runtime;   #加载到内存，使设置生效
Query OK, 0 row affected (0.00 sec)
MySQL [(none)]>  save mysql servers to disk;      #保存到硬盘中
Query OK, 0 row affected (0.00 sec)

ProxySQL配置监控，添加一个访问Master和Slave的用户：monitor密码：centos
Masetr主机(因为已经设置主从复制，所有Slave不用手动设置用户，通过主从自动复制)

MariaDB [(none)]>grant replication client on *.* to monitor@'192.168.37.%' identified by 'centos';

ProxySQL主机

MySQL[(none)]>  set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> set mysql-monitor_password='centos';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql variables to runtime;   #加载到内存，使设置生效
Query OK, 0 row affected (0.00 sec)
MySQL [(none)]> save mysql variables to disk;      #保存到硬盘中
Query OK, 0 row affected (0.00 sec)

查看监控连接是否正常：如果connect_error的结果为NULL则表示正常

MySQL [(none)]> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.37.7  | 3306 | 1557470898315003 | 1898                    | NULL          |
| 192.168.37.17 | 3306 | 1557470899044242 | 1950                    | NULL          |
                .
                .
                .

| 192.168.37.7  | 3306 | 1557471439197688 | 4458                    | NULL          |
+---------------+------+------------------+-------------------------+---------------+
20 rows in set (0.01 sec)

查看监控心跳信息 (对ping指标的监控)：
MySQL> select * from mysql_server_ping_log;
查看read_only和replication_lag的监控日志
MySQL> select * from mysql_server_read_only_log;
MySQL> select * from mysql_server_replication_lag_log;

设置分组信息(读写分离的读主机和写主机)

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");  #在分组表mysql_replication_hostgroups中添10(写组)组和20(读组)组，test为描述信息  
Query 0K, 1 row affacted (0.00sec)
MySQL [(none)]>  load mysql servers to runtime;   #加载，使生效  
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]>  save mysql servers to disk;      #保存到硬盘中
Query OK, 0 rows affected (0.01 sec)

设置读写分离规则
Master主机上设置客户使用用户

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.37.%' identified by 'centos';

ProxySQL中添加用户

MySQL[(none)]> insert into mysql_users(username,password,default_hostgroup)
values('sqluser','centos',10);
MySQL[(none)]> load mysql users to runtime;   #加载，使设置生效
MySQL[(none)]> save mysql users to disk;      #保存到硬盘

插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组

MySQL [(none)]> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);  #路由规则，读语句到20组，其他到10组
MySQL [(none)]> load mysql query rules to runtime;  #加载，使设置生效
MySQL [(none)]> save mysql query rules to disk;     #保存到硬盘

测试

在client中执行命令查读和写的主机ID判断是否完成读写分离
在还没有设置规则时；所有的访问都发送到master机器上

[root@Centos7 ~]#mysql -usqluser -pmagedu -P6033 -h192.168.37.27
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |   #主服务器(Master)ID 
+-------------+
1 row in set (0.01 sec)

规设置成功后，select 语句被发送到slave上执行，其他语句发到master上执行

MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          17 |   #从服务器(slave)ID 
+-------------+
1 row in set (0.00 sec)

查询调度记录

MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text
    -> FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 20 | 32464    | 5          | select @@server_id               |
| 10 | 15435    | 12         | select @@server_id               |
| 10 | 9941     | 1          | show @@server_id                 |
| 10 | 0        | 1          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
4 rows in set (0.00 sec)

MySQL [(none)]>