实现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 [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.38.37',3306); #插入主从节点的信息，默认都放在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 | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+ | 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; #可以查到监控信息了，没指定连接数据库的监控账号之前，会显示拒绝 +---------------+------+------------------+-------------------------+---------------+ | 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进行区分； MySQL [(none)]> load mysql servers to runtime; MySQL [(none)]> save mysql servers to disk; MySQL [(none)]> select * from mysql_replication_hostgroups; +------------------+------------------+------------+---------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+---------+ | 10 | 20 | read_only | test | +------------------+------------------+------------+---------+ 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 | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+ | 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); #指定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); #添加查询规则；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;' #开启事务操作属于写操作，所以显示的是主节点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 | +-------------+