基于proxysql实现的读写分离案例
读写分离案例
proxysql 192.168.94.141 rhel8
master 192.168.94.143 rhel8
slave 192.168.94.130 rhel8
mysql主从配置：https://www.cnblogs.com/fangxinxin/p/14228668.html
#通用配置
systemctl stop firewalld
setenforce 0
//proxysql端 安装proxysql mysql客户端
[root@proxysql ~]# yum -y install proxysql mariadb
[root@proxysql ~]# systemctl start proxysql.service
[root@proxysql ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:6032 0.0.0.0:*
LISTEN 0 128 0.0.0.0:6033 0.0.0.0:*
LISTEN 0 128 0.0.0.0:6033 0.0.0.0:*
LISTEN 0 128 0.0.0.0:6033 0.0.0.0:*
LISTEN 0 128 0.0.0.0:6033 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@proxysql ~]# chkconfig proxysql on
Note: Forwarding request to 'systemctl enable proxysql.service'.
Created symlink /etc/systemd/system/multi-user.target.wants/proxysql.service → /usr/lib/systemd/system/proxysql.service.
#master端
#授权proxysql和monitor账户
mysql> grant all on *.* to 'proxysql'@'192.168.94.141' identified by 'pwproxyssql';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.94.%' 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端配置
[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
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.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.000 sec)
#添加主机组
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.94.143',3306,1,'Write Group');
Query OK, 1 row affected (0.000 sec)
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.94.130',3306,1,'Read Group');
Query OK, 1 row affected (0.000 sec)
MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.003 sec)
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.012 sec)
MySQL [main]> select * from mysql_servers\G;
*************************** 1. row ***************************
hostgroup_id: 1
hostname: 192.168.94.143
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Write Group
*************************** 2. row ***************************
hostgroup_id: 2
hostname: 192.168.94.130
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Read Group
2 rows in set (0.000 sec)
#添加proxysql账户保存
MySQL [main]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','pwproxyssql',1,1);
MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)
MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.003 sec)
MySQL [main]> select * from mysql_users\G;
*************************** 1. row ***************************
username: proxysql
password: pwproxyssql
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
comment:
1 row in set (0.000 sec)
#设置监控账户
MySQL [main]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.000 sec)
MySQL [main]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.000 sec)
MySQL [main]> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)
MySQL [main]> save mysql variables to disk;
Query OK, 140 rows affected (0.003 sec)
#添加规则
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
Query OK, 1 row affected (0.000 sec)
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
Query OK, 1 row affected (0.000 sec)
MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 |
| 2 | 1 | ^SELECT | 2 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.000 sec)
MySQL [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)
MySQL [main]> load admin variables to runtime;
Query OK, 0 rows affected (0.000 sec)
MySQL [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.009 sec)
MySQL [main]> save admin variables to disk;
Query OK, 33 rows affected (0.002 sec)
#以proxysql用户登入
[root@proxysql ~]# mysql -uproxysql -ppwproxyssql -P6033 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.003 sec)
#创建新数据库（写）
MySQL [(none)]> create database write1;
Query OK, 1 row affected (0.002 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| write1 |
+--------------------+
6 rows in set (0.001 sec)
#查询表（读）
MySQL [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [school]> select * from student ;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | a | 11 |
| 3 | c | 16 |
| 4 | d | 17 |
| 5 | e | 23 |
| 6 | tom | 14 |
| 7 | eason | 28 |
+----+-------+-----+
6 rows in set (0.001 sec)
验证
proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执行
//以admin身份登入
[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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.
MySQL [(none)]> select * from stats_mysql_query_digest\G;
hostgroup: 1
schemaname: information_schema
username: proxysql
client_address:
digest: 0x4319029CA8B6F482
digest_text: create database write1
count_star: 1
first_seen: 1622738521
last_seen: 1622738521
sum_time: 1843
min_time: 1843
max_time: 1843
sum_rows_affected: 1
sum_rows_sent: 0
*************************** 9. row ***************************
hostgroup: 2
schemaname: information_schema
username: proxysql
client_address:
digest: 0x6A446897FCF01FCF
digest_text: select * from student
count_star: 1
first_seen: 1622738658
last_seen: 1622738658
sum_time: 2920
min_time: 2920
max_time: 2920
sum_rows_affected: 0
sum_rows_sent: 0
