MySQL proxysql一读写分离环境搭建

MySQL proxysql一读写分离环境搭建

1. 规划数据库磁盘路径，以下为例

主机	IP	路径	数据文件
proxysql	192.168.10.111
master	192.168.10.111	/home/mysql	/home/mysql/mysql_data
slave	192.168.10.113	/home/mysql	/home/mysql/mysql_data

2. 安装前准备

参考《MySQL Replication一主多从环境搭建》，完成mysql的一主多从的环境搭建

3. 安装proxysql

登录主机设置下载配置，$releasever为centos操作系统版本

cat

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=Index of /ProxySQL/proxysql-2.3.x/centos/$releasever

gpgcheck=1

gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

安装proxysql

yum install proxysql

4. 启动 ProxySQL库

启动

systemctl start proxysql.service

查看进程及端口

netstat -anlp | grep proxysql

6032 是 ProxySQL 的管理端口号，6033是对外服务的端口号

查看 ProxySQL 的版本

proxysql --version

停止

systemctl stop proxysql.service

5. 管理员登录 ProxySQL用户

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

这里的mysql就是安装在主机上的mysql命令

执行 show databases;

 

6. 主库创建用户

需要在my.cnf 加上这个用户认证方式，再来创建用户

[mysqld]

default_authentication_plugin=mysql_native_password

重启主库

create user 'monitor'@'%' identified by '123456';

grant all privileges on *.* to 'monitor'@'%' with grant option;

create user 'proxysql'@'%' identified by '123456';

grant all privileges on *.* to 'proxysql'@'%' with grant option;

flush privileges;

查看用户

select user,host,plugin from mysql.user;

用户认证的方式为 mysql_native_password

 

7. 创建组

writer_hostgroup 和reader_hostgroup 写组和读组都要大于0且不能相同，我的环境下，写组定义与1，读组定义为2

管理员登录 ProxySQL

mysql> use main

mysql> use maininsert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (1,2,'proxy');

提交生效

mysql> use mainload mysql servers to runtime;

mysql> use mainsave mysql servers to disk;

8. 添加 mysql 主机到 mysql_servers 表中

mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.10.111',3306,1,'Write Group');

mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.10.113',3306,1,'Read Group');

提交生效

mysql> load mysql servers to runtime;

mysql> save mysql servers to disk;

9. mysql_users 表中添加刚才在 master 上创建的账号 proxysql

mysql>insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

提交生效

mysql>load mysql users to runtime;

mysql>save mysql users to disk;

10. proxysql主机端修改变量设置健康检测的账号

mysql>set mysql-monitor_username='monitor';

mysql>set mysql-monitor_password='monitor';

提交生效

mysql>load mysql variables to runtime;

mysql>save mysql variables to disk;

查看变量

mysql> show variables like '%monitor%';

11. 配置proxysql组信息

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(1,2,'proxy');

mysql> load mysql servers to runtime;

mysql> save mysql servers to disk;

12. 添加读写分离的路由规则

mysql>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

mysql>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

提交生效

mysql>load mysql query rules to runtime;

mysql>load admin variables to runtime;

mysql>save mysql query rules to disk;

mysql>save admin variables to disk;

查看

mysql>select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

13. 用jdbc登录时需要设置mysql的版本

mysql>set mysql-server_version='8.0.26';

mysql>load mysql variables to runtime;

mysql>save mysql variables to disk;

查看变量

mysql> show variables like '%version%';

14. 验证读写分离

用proxysql用户登录

mysql -uproxysql -p123456 -h 192.168.10.111 -P6033

执行查询和插入操作

在proxysql主机端用admin登录查询

select * from stats_mysql_query_digest;

如图，读写分离成功

 

15. 配置相关

整套配置系统分为三层：顶层为 RUNTIME ,中间层为 MEMORY , 底层也就是持久层 DISK 和 CONFIG FILE 。

RUNTIME ： 代表 ProxySQL 当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层 “load” 进来。 MEMORY： MEMORY 层上面连接 RUNTIME 层，下面连接持久层。这层可以正常操作 ProxySQL 配置，随便修改，不会影响生产环境。修改一个配置一般都是现在 MEMORY 层完成的，确认正常之后在加载达到 RUNTIME 和 持久化的磁盘上。

DISK 和 CONFIG FILE：持久化配置信息，重启后内存中的配置信息会丢失，所需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。

为了将配置持久化到磁盘或者应用到 runtime，在管理接口下有一系列管理命令来实现它们。 

a) 要重新配置 MySQL 用户，可执行下面的其中一个命令：

1、LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME

 将内存数据库中的配置加载到 runtime 数据结构，反之亦然。

2、SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME

 将 MySQL 用户从 runtime 持久化到内存数据库。

3、LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK

 从磁盘数据库中加载 MySQL 用户到内存数据库中。

4、SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK

 将内存数据库中的 MySQL 用户持久化到磁盘数据库中。

5、LOAD MYSQL USERS FROM CONFIG /从配置文件中加载 MySQL 用户到内存数据库中。

b) 要处理 MySQL server：

1、LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME

 将 MySQL server 从内存数据库中加载到 runtime。

2、SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME

 将 MySQL server 从 runtime 持久化到内存数据库中。

3、LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK

 从磁盘数据库中加载 MySQL server 到内存数据库。

4、SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK

 从内存数据库中将 MySQL server 持久化到磁盘数据库中。

6、LOAD MYSQL SERVERS FROM CONFIG / 从配置文件中加载 MySQL server 到内存数据库中

c) 要处理 MySQL 的查询规则(mysql query rules)：

1、 LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME

 将 MySQL query rules 从内存数据库加载到 runtime 数据结构。

2、 SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME

 将 MySQL query rules 从 runtime 数据结构中持久化到内存数据库。

3、 LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK

 从磁盘数据库中加载 MySQL query rules 到内存数据库中。

4、 SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK

 将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。

5、 LOAD MYSQL QUERY RULES FROM CONFIG / 从配置文件中加载 MySQL query rules 到内存数据库中。

d) 要处理 MySQL 变量(MySQL variables)：

1、 LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME

 将 MySQL variables 从内存数据库加载到 runtime 数据结构。

2、 SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME

 将 MySQL variables 从 runtime 数据结构中持久化到内存数据中。

3、 LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK

 从磁盘数据库中加载 MySQL variables 到内存数据库中。

4、 SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK

 将 MySQL variables 从内存数据库中持久化到磁盘数据库中。

5、 LOAD MYSQL VARIABLES FROM CONFIG / 从配置文件中加载 MySQL variables 到内存数据库中。

e) 要处理管理变量(admin variables)：

1、 LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME

 将 admin variables 从内存数据库加载到 runtime 数据结构。

2、 SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME

 将 admin variables 从 runtime 持久化到内存数据库中。

3、 LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK

 从磁盘数据库中加载 admin variables 到内存数据库中。

4、 SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK

 将 admin variables 从内存数据库中持久化到磁盘数据库。

5、 LOAD ADMIN VARIABLES FROM CONFIG / 从配置文件中加载 admin variables 到内存数据库中。

 

 

f) 几个最常用的命令

如何让修改的配置生效(runtime)，以及如何持久化到磁盘上(disk)。记住，只要不是加载到 runtime，修改的配置就不会生效。

LOAD MYSQL USERS TO RUNTIME;  将内存数据库中的配置加载到 runtime 数据结构

SAVE MYSQL USERS TO DISK;   将内存数据库中的 MySQL 用户持久化到磁盘数据库中。

LOAD MYSQL SERVERS TO RUNTIME;  将 MySQL server 从内存数据库中加载到 runtime。

SAVE MYSQL SERVERS TO DISK;   从内存数据库中将 MySQL server 持久化到磁盘数据库中。

LOAD MYSQL QUERY RULES TO RUNTIME;  将 MySQL query rules 从内存数据库加载到 runtime 数据结构。

SAVE MYSQL QUERY RULES TO DISK;  将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。

LOAD MYSQL VARIABLES TO RUNTIME;  将 MySQL variables 从内存数据库加载到 runtime 数据结构。

SAVE MYSQL VARIABLES TO DISK;   将 MySQL variables 从内存数据库中持久化到磁盘数据库中。

LOAD ADMIN VARIABLES TO RUNTIME;   将 admin variables 从内存数据库加载到 runtime 数据结构。

SAVE ADMIN VARIABLES TO DISK;  将 admin variables 从内存数据库中持久化到磁盘数据库。

select * from mysql_servers;select * from stats_mysql_connection_pool;select * from disk.mysql_users;select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;select * from mysql_replication_hostgroups;