读写分离案例

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