读写分离案例
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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
