MySQL读写分离-----ProxySQL

ゝ一世哀愁。 2024-04-19 13:07 68阅读 0赞

# 一、实验环境 #

主机名			IP				角色				server-id		操作系统
    lxn1		172.25.5.1		master(node1)			3			RHEL7.3
    lxn3		172.25.5.3		slave (node2)			1			RHEL7.3
    lxn4		172.25.5.4		slave(node3)			2			RHEL7.3
    lxn2		172.25.5.2		proxysql中间件						CentOS7.3

*  两从库都开启 read\_only=on

所有主机关闭防火墙和SELinux

# 二、ProxySQL 读写分离配置 #

## 1、本实验架构介绍 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]

## 2、下载并安装 ##

**在proxysql端：**

下载地址：https://www.percona.com/downloads/proxysql/

安装：

rpm -ivh proxysql-1.4.9-1.1.el7.x86_64.rpm

*  如果遇到依赖问题就先装依赖包
 *  用户名和密码默认为admin

启动服务：

systemctl start proxysql

端口：

netstat -tnlp | grep proxysql
    #6032是 ProxySQL 的管理端口号
    #6033是对外服务的端口号

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]  
查看版本：  
proxysql -V

ProxySQL version 1.4.9-percona-1.1, codename Truls

## 3、登陆ProxySQL客户端 ##

**在proxysql端：**

安装mysql客户端命令

yum install mysql -y

管理员登陆

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]  
可以看到有五个库：

*  main:内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]  
    mysql\_servers：后端可以连接MySQL服务器的列表  
    mysql\_users：配置后端数据库的帐号和监控的帐号  
    mysql\_query\_rules：指定Query路游到后端不同服务器的规则列表
 *  disk：持久化磁盘的配置
 *  stats：统计信息的汇总
 *  monitor：一些监控的收集信息，包括数据库的健康状态

## 4、在master端创建ProxySQL相关帐号 ##

创建监控帐号和对外访问帐号

create user 'monitor'@'172.25.5.%' identified by 'monitor123';
    grant all privileges on *.* to 'monitor'@'172.25.5.%';
    create user 'lxn'@'172.25.5.%' identified by 'lxn123';
    grant all privileges on *.* to 'lxn'@'172.25.5.%';
    flush privileges;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]

## 5、ProxySQL添加主从服务器列表 ##

**ProxySQL 端：**

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.25.5.1',3306);
    insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.25.5.3',3306);
    insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.25.5.4',3306);
    load mysql servers to runtime;
    save mysql servers to disk;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 5]

*  登陆 ProxySQL 后，进行任何操作，都要运行 load 啥啥 to runtime ,从 memory 加载到 runtime ，再执行 save 啥啥 to disk 持久化到磁盘。

加载之后，三台服务器都是 ONLINE 状态  
![在这里插入图片描述][20190918205128133.png]

## 6、配置监控帐号 ##

**ProxySQL 端：**

set mysql-monitor_username='monitor';
    set mysql-monitor_password='monitor123';
    load mysql variables to runtime;
    save mysql variables to disk;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 6]  
查看监控连接是否正常：

select * from mysql_server_connect_log;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 7]  
查看监控心跳信息（对ping指标的监控）：

select * from mysql_server_ping_log order by time_start_us desc limit 6;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 8]

*  查看read\_only和replication\_lag的监控日志:  
    select \* from mysql\_server\_read\_only\_log;  
    select \* from mysql\_server\_replication\_lag\_log;

## 7、配置ProxySQL主从分组信息 ##

**在 proxysql 端：**

show create table mysql_replication_hostgroups \G

![在这里插入图片描述][20190918222457364.png]

*  writer\_hostgroup ：是写入组的编号
 *  reader\_hostgroup：是读取组的编号
 *  ProxySQL 根据 server 的 read\_only 的取值将服务器分组。  
    在本例中， read\_only = 1 的 server ，会被分到 20 组 ，read\_only = 0 的server会分到 10 组，从下面可以看到。

**给读写服务器分组**

insert into mysql_replication_hostgroups values (10,20,'proxy');
    load mysql servers to runtime;
    save mysql servers to disk;

![在这里插入图片描述][20190918225624946.png]

select * from mysql_replication_hostgroups ;

![在这里插入图片描述][20190918225804561.png]

select * from mysql_servers ;

![在这里插入图片描述][20190918230513229.png]  
**配置对外访问帐号**

#默认指定主库
    insert into mysql_users(username,password,default_hostgroup) values ('lxn','lxn123',10);
    #开启事务持久化保护，避免脏读、幻读
    update mysql_users set transaction_persistent=1 where username='lxn';
    load mysql users to runtime;
    save mysql users to disk;

**验证登陆的服务器：**

mysql -ulxn -plxn123 -h 172.25.5.2 -P 6033 -e "show slave hosts"
     mysql -ulxn -plxn123 -h 172.25.5.2 -P 6033 -e "select @@hostname"

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 9]

*  可以看到连接到的服务器是主库（172.25.5.1）
 *  6033 是 proxysql 的对外端口

## 8、配置读写分离策略 ##

**在 proxysql 端：**

#select for update 会产生一个写锁，分到 10 写组中去,其余默认到写组
    insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values (1,'^select.*for update$',10,1);
    #把所有以 select 开头的语句全部分配到 20 读组中
    insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values (1,'^select',20,1);
    load mysql query rules to runtime;
    save mysql query rules to disk;

*  match\_pattern ：设置的规则
 *  destination\_hostgroup ：默认指定的分组
 *  apply ：真正执行的应用规则

![在这里插入图片描述][20190919114123722.png]

# 三、测试读写分离 #

**在 ProxySQL 端：**

通过创建的对外账户 lxn 连接 ProxySQL 登陆到数据库：

mysql -ulxn -plxn123 -h 127.0.0.1 -P 6033

执行读写操作：

insert into lxn values ('proxysql','proxysql');
    select * from lxn;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 10]  
然后登陆管理端口：

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

查询 stats\_mysql\_query\_digest 这张表，监控查询状态

select * from stats_mysql_query_digest;

![在这里插入图片描述][20190919150015229.png]

*  可以看到 digest\_text 下的命令，相对应的 hostgroup 。从截图能看出读写分离成功！

# 四、调整权重 #

在读写分离实现后，调整权重、让某台机器承受更多的读操作，在实际生产环境中非常有必要！

示例：将 172.25.5.3 node2 节点的查询权重增大，让更多的读请求路由到这台机器上

update mysql_servers set weight=10 where hostname='172.25.5.3';
    load mysql servers to runtime;
    save mysql servers to disk;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 11]

推荐参考文章：https://mp.csdn.net/mdeditor/100988644

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f9da95713a88436b968f5eff8d704fa6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2efa725f01334e72b44fdbce5b903ae7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/0ac4d42df264499698039d7152e915d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f20b41165b0d4cf4a7d58513075baabb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7d4be7d1c44d46f2ba33211996fe2af2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ce36c758e5e54681a01c665265db7d5e.png
[20190918205128133.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/10f7fc33b8cb49f393d70ed57d141ced.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/dcc4290036f34e90b129c2d1732a1eee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/43db28d2ebde457eab9e17ef3a47451c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/bd544d9224a14cb180c15e2e1e1f14be.png
[20190918222457364.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/54adcd347afc4597b876d80a26a2278e.png
[20190918225624946.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/e48cf5ace46b4f0e837df37011e7d655.png
[20190918225804561.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/db4cc31db6eb42fcbd9005a2f960f580.png
[20190918230513229.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7e652d604163432f8feefe357882f723.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/07fa4efcd75b42faba3e5c0e2cc3b77b.png
[20190919114123722.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7a0c37a88ad5441badc1ccd101833f57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/719c2c8194234c61ae1d5df7c4812680.png
[20190919150015229.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/1846b250140a4d0e938b6870dd9aff65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/93dd7d8450ca497280015543cd8b9dce.png