Mysql中间件Proxysql实现mysql主从架构读写分离

迈不过友情╰ 2021-09-20 01:58 413阅读 0赞

实验主机

*  Master: 192.168.0.17
 *  Slave: 192.168.0.20
 *  Proxysql:192.168.0.30  
    建议关闭防火墙  
    建立Master: 192.168.0.17与Slave: 192.168.0.20的MySQL主从复制架构

Proxysql:192.168.0.30主机上基于yum安装proxysql

[root@centos6 ~ 20:21:05]#cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
    > [proxysql_repo]
    > name= ProxySQL YUM repository
    > baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
    > gpgcheck=1
    > gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    > EOF

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql]

查看yum源配置是否成功  
![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 1]  
成功了可以开始安装proxysql  
yum install proxysql  
![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 2]  
用mysql工具进入proxy管理页面

[root@centos6 ~ 20:08:36]#mysql -uadmin -padmin -P6032 -h127.0.0.1

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 3]  
查看proxy的表  
![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 4]  
对表mysql\_servers中插入主从节点信息，加载到RUNTIME，并保存到disk

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.0.17',3306);
    MySQL[(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.0.20',3306);
    MySQL [(none)]> load mysql servers to runtime;
    MySQL [(none)]> save mysql servers to disk;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 5]  
添加监控后端节点的用户。ProxySQL通过每个节点的read\_only值来自动调整它们是属于读组还是写组

MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.0.%' identified by '123';
    MariaDB [(none)]> flush privileges;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 6]  
ProxySQL上配置监控用户，加载到RUNTIME，并保存到disk

MySQL [(none)]> set mysql-monitor_username='monitor';
    MySQL [(none)]> set mysql-monitor_password='123';
    MySQL [(none)]> load mysql variables to runtime;
    MySQL [(none)]> save mysql variables to disk;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 7]  
查看监控连接是否正常

MySQL> select * from mysql_server_connect_log;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 8]  
查看监控心跳信息 (对ping指标的监控)：

MySQL> select * from mysql_server_ping_log;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 9]  
查看read\_only和replication\_lag的监控日志

MySQL> select * from mysql_server_read_only_log;
    MySQL> select * from mysql_server_replication_lag_log;

设置分组信息：  
需要修改的是main库中的mysql\_replication\_hostgroups表，该表有3个字段：writer\_hostgroup，reader\_hostgroup，comment, 指定写组的id为10，读组的id为20

MySQL> insert into mysql_replication_hostgroups values(10,20,"test");

读组proxysql会自动根据my.cnf的read\_only来判断你是否是读组自动更改  
将mysql\_replication\_hostgroups表的修改加载到RUNTIME生效

MySQL> load mysql servers to runtime;
    MySQL> save mysql servers to disk;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 10]  
Monitor模块监控后端的read\_only值，按照read\_only的值将节点自动移动到读/写组

MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 11]  
在master节点上创建访问用户

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

MariaDB \[(none)\]> flush privileges;  
![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 12]  
在slave节点上创建访问用户

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.0.%' identified by '123';
    MariaDB [(none)]> flush privileges;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 13]  
在ProxySQL配置，将用户sqluser添加到mysql\_users表中， default\_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库

MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser','123',10);
    MySQL> load mysql users to runtime;
    MySQL> save mysql users to disk;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 14]  
使用sqluser用户测试是否能路由到默认的10写组实现读、写数据

mysql -usqluser –p123 -P6033 -h127.0.0.1 -e 'select @@server_id'
    mysql -usqluser –p123 -P6033 -h127.0.0.1 -e 'create database testdb'
    mysql -usqluser –p123 testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 15]  
在proxysql上配置路由规则，实现读写分离  
与规则有关的表：mysql\_query\_rules和mysql\_query\_rules\_fast\_routing，后者是前者的扩展表，1.4.7之后支持  
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组

MySQL> 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);
    MySQL> load mysql query rules to runtime;
    MySQL> save mysql query rules to disk;

注意：因ProxySQL根据rule\_id顺序进行规则匹配，select ... for update规则的rule\_id必须要小于普通的select规则的rule\_id  
![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 16]  
测试读操作是否路由给20的读组

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 17]  
测试写操作，以事务方式进行测试

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'

路由的信息：查询stats库中的stats\_mysql\_query\_digest表

MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

![Mysql中间件Proxysql实现mysql主从架构读写分离][Mysql_Proxysql_mysql 18]

转载于:https://blog.51cto.com/14233913/2391050

[Mysql_Proxysql_mysql]: https://s1.51cto.com/images/blog/201905/08/8f1a7b9f662c9b4f8ba13b8e56f8bdd3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 1]: https://s1.51cto.com/images/blog/201905/08/51064ad5611c8b50504ec7f3a2219e30.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 2]: https://s1.51cto.com/images/blog/201905/08/a5a82014dff1ca772070e052408e2335.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 3]: https://s1.51cto.com/images/blog/201905/08/c6bd0f6e94ae4cbeb30104c16638d852.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 4]: https://s1.51cto.com/images/blog/201905/08/ba157bf8ee4966ca0527857ca87325e7.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 5]: https://s1.51cto.com/images/blog/201905/08/5aec191af081cd7ac414da86b38941fe.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 6]: https://s1.51cto.com/images/blog/201905/08/403292c675933ebe03cbec23eb9ab658.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 7]: https://s1.51cto.com/images/blog/201905/08/970437f9590a43ae063eedbf63382a91.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 8]: https://s1.51cto.com/images/blog/201905/08/35c43684b9d708818203b1fa63022e24.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 9]: https://s1.51cto.com/images/blog/201905/08/61a7096e18e7672565d3fd1281c7ff47.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 10]: https://s1.51cto.com/images/blog/201905/08/555866c4fbd65a98d30a71b2c299f60d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 11]: https://s1.51cto.com/images/blog/201905/08/5295ed31bcd6dbee279cc2f96335ffdf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 12]: https://s1.51cto.com/images/blog/201905/08/2c2f827281de11d1c5a4d2b79d2abfe1.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 13]: https://s1.51cto.com/images/blog/201905/08/088fd0f6d909e0e25c8a83bf4b7627e5.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 14]: https://s1.51cto.com/images/blog/201905/08/ddc192afc23f172b4faaa9952cc9c7ab.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 15]: https://s1.51cto.com/images/blog/201905/08/363e0d48e82a8f2384a28ed7ba18812c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 16]: https://s1.51cto.com/images/blog/201905/08/35d16b91946fee970a7d42cc9cb68774.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 17]: https://s1.51cto.com/images/blog/201905/08/1d883151648f241980944573b62c979c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[Mysql_Proxysql_mysql 18]: https://s1.51cto.com/images/blog/201905/08/b82c73b495cfb51a476dd3e83e7ea3b6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=