Centos7下ProxySQL+Mysql实现数据库读写分离

 

一、读写分离原理

用户在发送请求时，请求经过中间件proxysql，中间件将请求中的读和写操作分辨出来,将读请求发送给后端的从服务器，将写请求发送给后端的主服务器，再由主服务器通过主从复制将数据复制给其它从服务器。

二、实验环境：4台Centos7.6 

7.80  安装mysql5.7（主数据库）

7.81  安装mysql5.7（从数据库1）

7.82  安装mysql5.7（从数据库2）

7.83  安装proxysql+mysql5.7（用于配置）

三、7.80/81/82做好数据库主从复制

参考文章《Centos7下采用haproxy+keepalived搭建mysql高可用负载均衡》

注意：7.81/82从数据库配置文件/etc/my.cnf增加：read-only  

保存后，重启mysql服务

sudo systemctl restart mysqld

四、中间件服务器7.83

1、安装Mysql5.7

将4个安装包上传到/root/，运行安装

rpm -ivh mysql-community-common-5.7.38-1.el7.x86_64.rpm --force --nodeps

rpm -ivh mysql-community-libs-5.7.38-1.el7.x86_64.rpm --force --nodeps

rpm -ivh mysql-community-client-5.7.38-1.el7.x86_64.rpm --nodeps

rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm --force --nodeps

启动mysql 服务

sudo systemctl start mysqld

查看MySQL的启动状态

sudo systemctl status mysqld

开机启动

sudo systemctl enable mysqld

2、安装proxysql

将安装包proxysql-2.5.3-1-centos7.x86_64.rpm放至/root/，运行安装

yum -y install proxysql-2.5.3-1-centos7.x86_64.rpm

启动服务

service proxysql start

3、ProxySQL所使用的端口

6032:用来配置ProxySQL，是个管理接口

6033:用来被远程用户连接的端口

4、登录配置接口

mysql -uadmin -padmin -P6032 -h127.0.0.1

5、将MySQL主从服务器的信息添加到mysql_servers表

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.7.80',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.7.81',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.7.82',3306);

查看表

SELECT * FROM mysql_servers;

生效存盘

load mysql servers to runtime;

save mysql servers to disk;

五、7.80主服务器

登录mysql

Mysql -uroot -p

添加monitor账号并授权,用于监控后端MySQL节点

mysql> GRANT ALL ON *.* TO 'monitor'@'192.168.7.%' IDENTIFIED BY 'Lr12345!';

添加ProxySQL账号并授权,用来查看MySQL节点是主还是从，以及客户端连接

mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.7.%' IDENTIFIED BY 'Lr12345!';

刷新权限

flush privileges;

提醒：CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'monitor';（上面授权语句报错的话，用这个语句创建账号monitor和ProxySQL再授权）

六、中间件服务器7.83

1、在Proxysql上配置监控账号

mysql> SET mysql-monitor_username='monitor';

mysql> SET mysql-monitor_password='Lr12345!';

生效存盘

mysql> LOAD MYSQL VARIABLES TO RUNTIME;

mysql> SAVE MYSQL VARIABLES TO DISK;

查看连接状态

mysql> select * from mysql_server_connect_log;

2、设置读写分组

mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(10,20);

生效存盘

mysql> load mysql servers to runtime;

mysql> save mysql servers to disk;

查看表

mysql> SELECT * FROM mysql_replication_hostgroups;

此时mysql_server表已经将服务器分组

mysql> SELECT * FROM mysql_servers;

3、在ProxySQL服务器上，将proxysql用户添加至mysql_users表中

mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','Lr12345!',10);

查看表信息：

mysql> SELECT * FROM mysql_users;

生效存盘：

mysql> load mysql users to runtime;

mysql> save mysql users to disk;

目前未设置路由规则，所有请求发往主节点7.80

测试：任意一台数据库服务器，多次执行下列命令

[root@localhost ~]# mysql -uproxysql -pLr12345! -h192.168.7.83 -P6033 -e "SELECT @@server_id;"

此时只会显示主服务器的server_id

4、在ProxySQL上定义调度规则

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> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

mysql> SELECT * FROM mysql_query_rules\G;

生效存盘

mysql> LOAD MYSQL QUERY RULES TO RUNTIME;

mysql> SAVE MYSQL QUERY RULES TO DISK;

七、测试

1、任意一台数据库服务器，多次执行查询serverID

mysql -uproxysql -pLr12345! -h192.168.7.83 -P6033 -e "SELECT @@server_id;"

此时正常会切换显示两台从服务器的server_id，但这里也显示了主服务器的server_id。

查看语句路由情况，是没有问题，即查询语句都指向读组，没有写组。

mysql> select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

问题：读取时读到7.80(主数据库)，写正常。

原因是设置读写分组之前，必须先在7.80（主数据库）建立monitor用户及在中间服务器（proxysql）设置monitor用户，并保存。

2、navicate客户端连接192.168.7.83:6033

用navicate去连接192.168.7.83的6033端口，用户名proxysql,密码Lr12345!

在表中添加删除行，会写入到主服务器，并同步到从服务器。

问题：查询时会报错如下

解决：

登录ProxySql管理监视器，即7.83的管理接口

mysql -uadmin -padmin -P6032 -h127.0.0.1

mysql> set mysql-set_query_lock_on_hostgroup=0;

生效存盘

mysql> load mysql variables to runtime;

mysql> save mysql variables to disk;

再次查询正常。

3、读取权重

读写分离设置成功后，还可调权重，如让某台机器承受更多的读操作，默认权重是1 

update mysql_servers set weight=10 hostname='192.168.7.81';

update mysql_servers set weight=20 hostname='192.168.7.82';

查询权重

SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;

生效存盘

load mysql servers to runtime;

save mysql servers to disk;