使用ProxySql实现Mysql的读写分离 详细安装步骤 亲测可行

主机ip	说明
192.168.168.109	ProxySql
192.168.168.77	mysql master(主）
192.168.168.78	mysql slave（从）

?

1.下载ProxySql安装包

在192.168.168.109机器上操作

https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql-2.5.5-1-centos7.x86_64.rpm

2.安装ProxySql

2.1安装

在192.168.168.109机器上操作

rpm -ivh proxysql-2.5.5-1-centos7.x86_64.rpm

报错

[root@mini109 opt]# rpm -ivh proxysql-2.5.5-1-centos7.x86_64.rpm 
警告：proxysql-2.5.5-1-centos7.x86_64.rpm: 头V4 RSA/SHA512 Signature, 密钥 ID 8217c97e: NOKEY
错误：依赖检测失败：
	perl(DBD::mysql) 被 proxysql-2.5.5-1.x86_64 需要
	perl(DBI) 被 proxysql-2.5.5-1.x86_64 需要

2.2解决报错

在192.168.168.109机器上操作

 yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL

2.3再次安装

在192.168.168.109机器上操作

rpm -ivh proxysql-2.5.5-1-centos7.x86_64.rpm

3.启动

在192.168.168.109机器上操作

systemctl start proxysql.service

4.登陆ProxySQL

在192.168.168.109机器上操作

管理员登录 ProxySQL

前提也需要能进入MySQL命令行，所以安装一个mariadb

yum -y install mariadb

登陆ProxySQL

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

5.查看ProxySQL数据库相关信息

在192.168.168.109机器上操作

5.1查看数据库

show databases;

?可见有五个库： main、disk、stats 、monitor 和 stats_history
main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息。

?main 库中有如下信息：

5.2查看表

show tables;

库下的主要表：
mysql_servers: 后端可以连接 MySQL 服务器的列表
mysql_users: 配置后端数据库的账号和监控的账号。
mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。

注： 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。

只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。
disk ：持久化的磁盘的配置
stats： 统计信息的汇总
monitor：一些监控的收集信息，比如数据库的健康状态等
stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标

6.配置 ProxySQL 所需账户

在192.168.168.77数据库操作

在主MySQL（192.168.168.77）上创建 ProxySQL 的监控账户和对外访问账户

#proxysql 的监控账户
grant all privileges on *.* to 'monitor'@'192.168.168.%' identified by 'Ww12345@king';
grant all privileges on *.* to 'monitor'@'%' identified by 'Ww12345@king';
 
#proxysql 的对外访问账户
grant all privileges on *.* to 'proxysql'@'192.168.168.%' identified by 'Ww12345@king';
grant all privileges on *.* to 'proxysql'@'%' identified by 'Ww12345@king';

#刷新权限 
flush privileges;

7.设置proxy SQL监控账户
在192.168.168.109机器上操作

在MySQL主节点已经创建过monitor账号
在proxy SQL端进行配置一下

?

7.1登陆

mysql -uadmin -padmin -P6032 -h127.0.0.1

7.2管理端配置这个monitor监控用户，配置方式是修改全局变量

set mysql-monitor_username='monitor';
set mysql-monitor_password='Ww12345@king';

7.3执行语句加入内存和磁盘

load mysql variables to runtime;
save mysql variables to disk;

8.管理端配置读写分离

在192.168.168.109机器上操作

8.1将MySQL主从服务器信息添加入mysql_servers表中。

先将主从服务器存放在同一组内，等指定好读写规则后，系统会根据配置文件中的read-only值自动将其分别添加至读组和写组,命令如下：

我的不行直接分好组

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1,'192.168.168.77',3306,1,1000,10,'vip'),(2,'192.168.168.78',3306,1,1000,10,'slave');

?

8.2执行以下命令即时生效：

load mysql servers to runtime;
save mysql servers to disk;

8.3设置读写分组

#我安装的版本这个表多了个字段check_type
#博主proxymysql版本2.0 我的2.5

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'read_only|innodb_read_only|','one-master-2-slave');

8.4执行以下命令即时生效：

load mysql servers to runtime;
save mysql servers to disk;

9.测试读写分离

在192.168.168.109机器上操作

本地使用MySQL命令行或者navicat都可以连接proxy SQL：6033端口

9.1写入数据

[root@localhost ~]# mysql -uproxysql -p123456 -P6033 -h127.0.0.1
MySQL [(none)]> create database A;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> create database B;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> use A;
Database changed

MySQL [A]> create table  test(id char(10),name char(10));
Query OK, 0 rows affected (0.03 sec)

MySQL [A]> insert into test(id,name) values(1,'hy');
Query OK, 1 row affected (0.00 sec)

MySQL [A]> select * from test;
+------+------+
| id   | name |
+------+------+
| 1    | hy   |
+------+------+
1 row in set (0.00 sec)

9.2查看

MySQL [(none)]> select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;
+-----------+--------------------+----------+---------------------------------------------+------------+
| hostgroup | schemaname         | username | digest_text                                 | count_star |
+-----------+--------------------+----------+---------------------------------------------+------------+
| 1         | A                  | proxysql | insert into test(id,name) values(?,?)       | 1          |
| 1         | A                  | proxysql | show tables                                 | 1          |
| 1         | A                  | proxysql | create table test(id char(?),name char(?))  | 1          |
| 2         | information_schema | proxysql | SELECT DATABASE()                           | 1          |
| 1         | information_schema | proxysql | create database A                           | 1          |
| 2         | A                  | proxysql | select * from test                          | 1          |
| 1         | information_schema | proxysql | select @@version_comment limit ?            | 2          |
| 1         | information_schema | proxysql | show databases                              | 1          |
| 1         | information_schema | proxysql | create database B                           | 1          |
| 1         | A                  | proxysql | create tables test(id char(?),name char(?)) | 1          |
| 1         | A                  | proxysql | show databases                              | 1          |
+-----------+--------------------+----------+---------------------------------------------+------------+

参考文档：proxy SQL实现MySQL主从读写分离-CSDN博客

参考文档：使用ProxySql实现Mysql的读写分离?