利用ProxySQL实现MySQL的读写分离 一、ProxySQL安装 二、ProxySQL架构示意图 三、ProxySQL搭建和配置
本文简单介绍ProxySQL的安装及如果实现后端MySQL主从结构的读写分离。
Proxy官方地址：https://proxysql.com/
proxysql-2.0.8-1-centos7.x86_64.rpm下载地址：https://github.com/sysown/proxysql/releases/tag/v2.0.8
安装方法：
1、上传安装包到服务器（CentOS7）
2、# yum install proxysql-2.0.8-1-centos7.x86_64.rpm
二、ProxySQL架构示意图
三、ProxySQL搭建和配置
1、配置MySQL的主从（点此链接）
注意："从库" 的配置文件/etc/my.cnf中必须包含 "read-only"参数
2、启动服务，登录ProxySQL
1、启动ProxySQL服务
# service proxysql start
2、安装mariadb客户端，ProxySQL需要通过mysql登录命令管理，CentOS7可以安装mariadb的客户端来实现
# yum install mariadb
3、登录ProxySQL
# mysql -uadmin -padmin -P6032 -h127.0.0.1
注意：1、默认的用户名密码admin
2、6032端口：ProxySQL的管理端口
3、host127.0.0.1，通过本机管理
3、ProxySQL添加主、从节点
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.100', 3306);
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.200', 3306);
MySQL > load mysql servers to runtime;
MySQL > save mysql servers to disk;
4、添加监控后端节点的用户
ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组
主库服务器上配置： 在master上执行，从节点会自动同步该账户。该账户用于proxysql识别哪个是read-only的数据库 MySQl > grant replication client on *.* to monitor@'192.168.1.%' identified by '123456'; ================================================= ProxySQL上配置： 在ProxySQL上配置监控 MySQL > set mysql-monitor_username='monitor'; MySQL > set mysql-monitor_password='123456';
加载到RUNTIME，并保存到disk MySQL > load mysql variables to runtime; MySQL > save mysql variables to disk;
查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)
MySQL> select * from mysql_server_connect_log;
查看监控心跳信息 (对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;
5、设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有4个字段：writer_hostgroup，reader_hostgroup，check_type，comment, 指定写组的id为10，读组的id为20
MySQL> insert into mysql_replication_hostgroups values(10,20,"read_only","test");
将mysql_replication_hostgroups表的修改加载到RUNTIME生效
MySQL> load mysql servers to runtime;
MySQL> save mysql servers to disk;
Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
查询后端状态
MySQL > select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.1.100 | 3306 | ONLINE | 1 |
| 20 | 192.168.1.200 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------
6、配置发送SQL语句的用户
在主库服务器上配置：
在master节点上创建访问用户，该账号给proxysql连接主从数据库用
MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by '123456';
=======================================================================
在ProxySQL服务器上配置：
在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库
MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser', 'magedu',10);
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk
7、在proxysql上配置路由规则
插入路由规则：将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;