ProxySQL實作讀寫分離

環境說明：

IP	角色	應用	系統平台
192.168.92.131	讀寫分離解析主機	proxysql	rhel8
192.168.92.129	master	mysql
192.168.92.130	slave

準備工作：

關閉防火牆
關閉SELINUX
安裝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.129mysql: [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 7Server 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=3306pid-file=/opt/mydata/mysql.pid
user=mysql
skip-name-resolve

server-id = 10log-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=3306pid-file=/opt/mydata/mysql.pid
user=mysql
skip-name-resolve

server-id = 20relay-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.129ERROR 1045 (28000): Access denied for user 'proxysql'@'192.168.92.131' (using password: YES)
[root@proxysql ~]# mysql -uproxysql -pporxysql -h192.168.92.129Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9Server 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.130Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5Server 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.1Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2Server 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.

([email protected]:7777) [(none)]>

proxysql管理端添加後端連接配接mysql主從資料庫的配置

([email protected]: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)

([email protected]: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)

([email protected]: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([email protected]:7777) [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.006 sec)

([email protected]:7777) [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.071 sec)

在 proxysql 主機的 mysql_users 表中添加剛才在 master 上建立的賬号 proxysql，proxysql 用戶端需要使用這個賬号來通路資料庫

default_hostgroup 預設組設定為寫組，也就是100;

當讀寫分離的路由規則不符合時，會通路預設組的資料庫;

([email protected]: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)

([email protected]: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: 0transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000         最大連接配接數
            attributes: 
               comment: 
1 row in set (0.000 sec)//儲存到dixk([email protected]:7777) [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)

([email protected]: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主機端修改變量設定健康檢測的賬号

([email protected]:7777) [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.000 sec)

([email protected]:7777) [(none)]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.000 sec)

([email protected]:7777) [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.002 sec)

([email protected]: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)

([email protected]: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)

([email protected]: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)

([email protected]: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)

([email protected]:7777) [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.001 sec)

([email protected]: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 5Server 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.

([email protected]:6033) [(none)]> show databases;+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+5 rows in set (0.006 sec)

嘗試修改資料庫和查詢

建立2個資料庫并查詢一下表

([email protected]:6033) [(none)]> create database jinxinruntian;
Query OK, 1 row affected (0.007 sec)

([email protected]:6033) [(none)]> create database wangqing;
Query OK, 1 row affected (0.004 sec)

([email protected]: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)

驗證讀寫分離是否成功

([email protected]: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: 2953sum_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: 6590sum_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: 28595sum_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: 2763sum_rows_affected: 0
    sum_rows_sent: 1

ProxySQL實作讀寫分離

配置ProxySQL

mysql主庫添加proxysql可以增删改查的賬号

登入proxysql管理端

proxysql管理端添加後端連接配接mysql主從資料庫的配置

添加健康檢測的帳号

添加讀寫分離的路由規則

驗證讀寫分離

登入 proxysql 用戶端

嘗試修改資料庫和查詢

驗證讀寫分離是否成功

繼續閱讀

說梅止渴什麼意思？出處是哪裡？

帶有高字的成語

ProxySQL官檔翻譯__27_MySQL8.0ProxySQL支援MySQL 8.0，但有一些限制：

ProxySQL官檔翻譯__14_ProxySQL_Configuration14_ProxySQL_Configuration

ProxySQL新版本對MGR的原生支援

ProxySQL官檔翻譯__06_ProxySQL配置之密碼管理06_ProxySQL配置之密碼管理

ProxySQL官檔翻譯__03_ProxySQL配置之Global參數03_ProxySQL配置之Global參數

ProxySQL官檔翻譯__08_ProxySQL配置之查詢日志08_ProxySQL配置之查詢日志

proxysql-基礎

ProxySQL官檔翻譯__02_ProxySQL配置概述02_ProxySQL配置概述

ProxySQL官檔翻譯__07_ProxySQL配置之排程07_ProxySQL配置之排程

ProxySQL官檔翻譯__16_ProxySQL_Thread_02_Watchdog16_ProxySQL_Thread_02_Watchdog

ProxySQL官檔翻譯__22_Bug回報22_Bug回報

ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: NO)

記一次【Unknown thread id: XXX】的排查

開源的高性能資料庫代理工具ProxySQL安裝及配置