实现ProxySQL反向代理Mysql读写分离

简介

ProxySQL相当于小型的数据库，在磁盘上有存放数据库的目录；ProxySQL用法和mysql相似
启动ProxySQL后会有两个监听端口；
6032：ProxySQL的管理端口
6033：ProxySQL对外提供服务的端口
注意：ProxySQL区分主从节点，是通过主从服务器配置文件中的read_only来区分的；哪个服务器配置文件中有read_only，哪台服务器就是从节点，所以从节点必须加read_only选项

ProxySQL下载地址

实现ProxySQL代理最少准备4台机器

https://github.com/sysown/proxysql/releases/tag/v2.0.8

主节点配置：

[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=17
log-bin
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql -e 'grant replication slave on *.* to repluser@"192.168.38.%" identified by "centos";'     #授权并且创建用于主从同步的账号  
[root@master ~]# mysql -e 'grant replication client on *.* to monitor@"192.168.38.%" identified by "centos";'        #授权并创建用于ProxySQL监控节点服务器上的read_only值的账号
[root@master ~]# mysql -e 'grant all on *.* to sqluser@"192.168.38.%" identified by "centos";'       #创建一个mysql客户端连接ProxySQL代理服务器的账号；客户端连接代理服务器，代理服务器反向代理到后端的mysql数据库；ProxySQL上是没有这个账号的

授权创建的这些账号主从节点都需要设置，主节点和从节点都配置完，这些账号会自动同步到从节点上

从节点配置

[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=27
read-only           #从节点必须配置
[root@slave ~]# systemctl restart mariadb
[root@slave ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.38.37',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;

ProxySQL服务器配置

[root@proxysql ~]# yum install proxysql-2.0.8-1-centos67.x86_64.rpm -y     #提前下载好ProxySQL包
[root@proxysql ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@proxysql ~]# vim /etc/proxysql.cnf    #可以修改对外提供服务的端口号为3306
[root@proxysql ~]# service proxysql start
[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1       #连接proxysql的管理端口；mysql客户端连接服务器端，在同一台机器默认走的是mysql客户端的socket文件，所以这里指定代表本机的IP
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.38.37',3306);    #往mysql_servers表中插入主从节点的信息，默认都放在10组里
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.38.47',3306);
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.38.37 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.38.47 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
MySQL [(none)]> load mysql servers to runtime;     #加载到runtime中，生效
MySQL [(none)]> save mysql servers to disk;    #保存到磁盘中
MySQL [(none)]> set mysql-monitor_username='monitor';     #设置监控账号的用户名
MySQL [(none)]> set mysql-monitor_password='centos';    #密码
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
MySQL [(none)]> select * from mysql_server_connect_log;       #可以查到监控信息了，没指定连接数据库的监控账号之前查询，会显示error
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.38.37 | 3306 | 1574775423158525 | 873                     | NULL          |
| 192.168.38.47 | 3306 | 1574775423859699 | 701                     | NULL          |
+---------------+------+------------------+-------------------------+---------------+
MySQL [(none)]> select * from sqlite_master where name='mysql_replication_hostgroups'\G    #查询一下mysql_replication_hostgroups这个表的结构
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,'read_only',"test");    #把10组添加到写组，把20组添加到读组下，针对read_only进行区分；有read_only的放到20组，没有read_only的放到10组
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
MySQL [(none)]> MySQL [(none)]> select * from mysql_servers;     #47这台主机放到了20组下,区分开47这台机器为从节点（负责读），37为主节点（负责写）
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.38.37 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.38.47 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | test    |
+------------------+------------------+------------+---------+
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup)values('sqluser','centos',10);     #把mysql客户端连接proxysql，proxysql真正代理到mysql服务器的账号指定用户名密码，默认操作都指定为10组的服务器
MySQL [(none)]> load mysql users to runtime;
MySQL [(none)]> save mysql servers to disk;
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);      #往mysql_query_rules表中添加查询规则；select for update操作会申请写锁，所以默认放到10组（写）里面；其余select开头的操作放到20组（读）里面；其余所有默认操作都放到10组里面
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql servers to disk;

Mysql客户端操作

[root@centos6 ~]# mysql -usqluser -pcentos -P6033 -h192.168.38.7 -e 'begin;select @@server_id;commit;'     #指定连接proxysql代理服务器的用户名密码、指定proxysql提供服务的端口、IP；不进入mysql界面执行mysql里面的操作；开启事务操作属于写操作，所以显示的是主节点的server-id
+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+
[root@centos6 ~]# mysql -usqluser -pcentos -P6033 -h192.168.38.7 -e 'select @@server_id;'      #select属于读操作，所以显示的是从节点的server-id
+-------------+
| @@server_id |
+-------------+
|          27 |
+-------------+