ProxySQL实现读写分离
环境说明：
| IP | 角色 | 应用 | 系统平台 |
|---|---|---|---|
| 192.168.92.131 | 读写分离解析主机 | proxysql | rhel8 |
| 192.168.92.129 | master | mysql | rhel8 |
| 192.168.92.130 | slave | mysql | rhel8 |
准备工作：
- 关闭防火墙
- 关闭SELINUX
- 安装mysql并配置主从
安装mysql并配置主从
//授权（master）
[root@master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'repl'@'192.168.92.130' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
//连接测试（slave）
[root@slave ~]# mysql -urepl -p123456 -h192.168.92.129
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
//更改配置文件（master）
[root@master ~]# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/mydata
socket=/tmp/mysql.sock
port=3306
pid-file=/opt/mydata/mysql.pid
user=mysql
skip-name-resolve
server-id = 10
log-bin = mysql_bin
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
//更改配置文件（slave）
[root@slave ~]# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/mydata
socket=/tmp/mysql.sock
port=3306
pid-file=/opt/mydata/mysql.pid
user=mysql
skip-name-resolve
server-id = 20
relay-log = myrelay
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
//设置改变属主（slave）
mysql> change master to
-> master_host='192.168.92.129',
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
。。。。。。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
。。。。。。
配置ProxySQL
mysql主库添加proxysql可以增删改查的账号
//授权（master） mysql> grant all on *.* to 'proxysql'@'192.168.92.131' ideentified by 'porxysql'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) //连接测试（proxy SQL） [root@proxysql ~]# mysql -uproxysql -pproxysql -h192.168.92.129 ERROR 1045 (28000): Access denied for user 'proxysql'@'192.168.92.131' (using password: YES) [root@proxysql ~]# mysql -uproxysql -pporxysql -h192.168.92.129 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> exit Bye [root@proxysql ~]# mysql -uproxysql -pporxysql -h192.168.92.130 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
登录proxysql管理端
[root@proxysql ~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "
[root@proxysql ~]# mysql -uadmin -padmin -P7777 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(admin@127.0.0.1:7777) [(none)]>
proxysql管理端添加后端连接mysql主从数据库的配置
(admin@127.0.0.1:7777) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(100,'192.168.92.129',3306,1,'Write Group');
Query OK, 1 row affected (0.001 sec)
(admin@127.0.0.1:7777) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(200,'192.168.92.130',3306,1,'Read Group');
Query OK, 1 row affected (0.000 sec)
(admin@127.0.0.1:7777) [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 100 | 192.168.92.129 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |
| 200 | 192.168.92.130 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.000 sec)
//修改后，需要加载到RUNTIME，并保存到disk
(admin@127.0.0.1:7777) [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.006 sec)
(admin@127.0.0.1:7777) [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.071 sec)
在 proxysql 主机的 mysql_users 表中添加刚才在 master 上创建的账号 proxysql，proxysql 客户端需要使用这个账号来访问数据库
default_hostgroup 默认组设置为写组，也就是100;
当读写分离的路由规则不符合时，会访问默认组的数据库;
(admin@127.0.0.1:7777) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','proxysql',100,1); Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> select * from mysql_users \G *************************** 1. row *************************** username: proxysql 用户名 password: proxysql 密码 active: 1 是否启用账户 use_ssl: 0 default_hostgroup: 100 默认主 default_schema: NULL schema_locked: 0 transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000 最大连接数 attributes: comment: 1 row in set (0.000 sec) //保存到dixk (admin@127.0.0.1:7777) [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.001 sec) (admin@127.0.0.1:7777) [(none)]> save mysql users to disk; Query OK, 0 rows affected (0.010 sec)
添加健康检测的帐号
在mysql的 master 端添加属于proxysql的只读账号
mysql> grant select on *.* to 'monitor'@'192.168.92.%' identified by 'monitor';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在proxysql主机端修改变量设置健康检测的账号
(admin@127.0.0.1:7777) [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.000 sec)
(admin@127.0.0.1:7777) [(none)]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.000 sec)
(admin@127.0.0.1:7777) [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.002 sec)
(admin@127.0.0.1:7777) [(none)]> save mysql variables to disk;
Query OK, 140 rows affected (0.005 sec)
添加读写分离的路由规则
需求：
- 将 select 查询语句全部路由至 hostgroup_id=2 的组(也就是读组)
- 但是 select * from tb for update 这样的语句是会修改数据的，所以需要单独定义，将它路由至 hostgroup_id=1 的组(也就是写组)
- 其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup)
(admin@127.0.0.1:7777) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(100,1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.001 sec)
(admin@127.0.0.1:7777) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(200,1,'^SELECT',200,1);
Query OK, 1 row affected (0.000 sec)
(admin@127.0.0.1:7777) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 100 | 1 | ^SELECT.*FOR UPDATE$ | 100 | 1 |
| 200 | 1 | ^SELECT | 200 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.000 sec)
(admin@127.0.0.1:7777) [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.001 sec)
(admin@127.0.0.1:7777) [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.034 sec)
验证读写分离
登录 proxysql 客户端
登录用户是刚才我们在 mysql_user 表中创建的用户，端口为6033
[root@proxysql ~]# mysql -uproxysql -pproxysql -h127.0.0.1 -P6033
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(proxysql@127.0.0.1:6033) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.006 sec)
尝试修改数据库和查询
创建2个数据库并查询一下表
(proxysql@127.0.0.1:6033) [(none)]> create database jinxinruntian;
Query OK, 1 row affected (0.007 sec)
(proxysql@127.0.0.1:6033) [(none)]> create database wangqing;
Query OK, 1 row affected (0.004 sec)
(proxysql@127.0.0.1:6033) [(none)]> select user,host from mysql.user;
+---------------+----------------+
| user | host |
+---------------+----------------+
| monitor | 192.168.92.% |
| repl | 192.168.92.130 |
| proxysql | 192.168.92.131 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+----------------+
6 rows in set (0.003 sec)
验证读写分离是否成功
proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执行
(admin@127.0.0.1:7777) [(none)]> select * from stats_mysql_query_digest\G
*************************** 1. row ***************************
hostgroup: 200
schemaname: information_schema
username: proxysql
client_address:
digest: 0x0F02B330C823D739
digest_text: select user,host from mysql.user
count_star: 1
first_seen: 1623022307
last_seen: 1623022307
sum_time: 2953
min_time: 2953
max_time: 2953
sum_rows_affected: 0
sum_rows_sent: 6
*************************** 2. row ***************************
hostgroup: 100
schemaname: information_schema
username: proxysql
client_address:
digest: 0xCE6BB7764BA98E04
digest_text: create database jinxinruntian
count_star: 1
first_seen: 1623022295
last_seen: 1623022295
sum_time: 6590
min_time: 6590
max_time: 6590
sum_rows_affected: 1
sum_rows_sent: 0
*************************** 3. row ***************************
hostgroup: 100
schemaname: none
username: proxysql
client_address:
digest: 0x99531AEFF718C501
digest_text: show tables
count_star: 1
first_seen: 1623022281
last_seen: 1623022281
sum_time: 28595
min_time: 28595
max_time: 28595
sum_rows_affected: 0
sum_rows_sent: 0
*************************** 4. row ***************************
hostgroup: 200
schemaname: school
username: proxysql
client_address:
digest: 0x620B328FE9D6D71A
digest_text: SELECT DATABASE()
count_star: 1
first_seen: 1623022281
last_seen: 1623022281
sum_time: 2763
min_time: 2763
max_time: 2763
sum_rows_affected: 0
sum_rows_sent: 1
从上面的 hostgroup 和 digest_text 值来看，所有的写操作都被路由至100组，读操作都被路由至200组，其中100组为写组，200组为读组！
