MySQL使用ProxySQL達成讀寫分離

2024-07-24 12:35:47

字體：大中小

來源：轉載

供稿：網友

MySQL使用ProxySQL實現讀寫分離：

　　1 ProxySQL簡介：
　　ProxySQL是一個高性能的MySQL中間件，擁有強大的規則引擎。

　　2 環境：
　　系統：CentOS7.5
　　ProxySQL版本：proxysql-1.4.8-1-centos7.x86_64.rpm
　　Mysql版本：MySQL 5.7.22
　　ProxySQL主機IP：192.168.1.101
　　Mysql主庫IP：192.168.1.102
　　Mysql從庫IP：192.168.1.103
　　3 前提條件：
　　防火墻和selinux已關閉；
　　Mysql主從同步已經配置完成；
　　4 安裝ProxySQL：
　　4.1 安裝
　　# 配ProxySQL源
　　[root@ProxySQL ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
　　[proxysql_repo]
　　name= ProxySQL
　　baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos//$releasever
　　gpgcheck=1
　　gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
　　EOF

　　# 安裝
　　[root@ProxySQL ~]# yum install proxysql -y
　　# 記一次安裝依賴：
　　     perl-Compress-Raw-Bzip2
　　     perl-Compress-Raw-Zlib
　　     perl-DBD-MySQL
　　     perl-DBI
　　     perl-IO-Compress
　　     perl-Net-Daemon
　　     perl-PlRPC

　　#安裝生成的文件：
　　[root@ProxySQL ~]# rpm -ql proxysql
　　/etc/init.d/proxysql    # 啟動腳本

　　/etc/proxysql.cnf       # 配置文件，僅在第一次(/var/lib/proxysql/proxysql.db文件不存在)啟動時有效
　　                        # 啟動后可以在proxysql管理端中通過修改數據庫的方式修改配置并生效(官方推薦方式。)
　　/usr/bin/proxysql      #主程序文件
　　/usr/share/proxysql/tools/proxysql_galera_checker.sh
　　/usr/share/proxysql/tools/proxysql_galera_writer.pl
　　或者直接下載rpm包或源碼包:
　　github
　　官網

　　4.2 配置文件說明
　　[root@ProxySQL ~]# egrep -v "^#|^$" /etc/proxysql.cnf
　　datadir="/var/lib/proxysql"
　　admin_variables=
　　{
　　    admin_credentials="admin:admin"     # 定義連接管理端口的用戶名和密碼
　　    mysql_ifaces="0.0.0.0:6032"       # 定義管理端口6032；用來連接proxysql的管理數據庫，修改proxysql服務的設置以及路由策略
　　}
　　mysql_variables=
　　{
　　    threads=4                       # 定義每個轉發端口開啟多少個線程
　　    max_connections=2048
　　    default_query_delay=0
　　    default_query_timeout=36000000
　　    have_compress=true
　　    poll_timeout=2000
　　    interfaces="0.0.0.0:6033"               # 定義轉發端口6033；用來連接后端的mysql實例，起到代理轉發的作用；
　　    default_schema="information_schema"
　　    stacksize=1048576
　　    server_version="5.7.22"                # 設置后端mysql實例的版本號，僅起到comment的作用
　　    connect_timeout_server=3000
　　    monitor_username="monitor"
　　    monitor_password="monitor"
　　    monitor_history=600000
　　    monitor_connect_interval=60000
　　    monitor_ping_interval=10000
　　    monitor_read_only_interval=1500
　　    monitor_read_only_timeout=500
　　    ping_interval_server_msec=120000
　　    ping_timeout_server=500
　　    commands_stats=true
　　    sessions_sort=true
　　    connect_retries_on_failure=10
　　}
　　mysql_servers =
　　(
　　)
　　mysql_users:
　　(
　　)
　　mysql_query_rules:
　　(
　　)
　　scheduler=
　　(
　　)
　　mysql_replication_hostgroups=
　　(
　　)
　　[root@ProxySQL ~]# sed -i 's#5.5.30#5.7.22#g' /etc/proxysql.cnf      # 把5.5.30改為自己的版本
　　4.3 啟動proxysql：
　　4.3.1 添加到開機自啟動
　　[root@ProxySQL ~]# chkconfig proxysql on          # 添加到開機自啟動，默認已添加
　　[root@ProxySQL ~]# chkconfig --list |grep proxysql     # 查看是否開機自啟動

　　Note: This output shows SysV services only and does not include native
　　      systemd services. SysV configuration data might be overridden by native
　　      systemd configuration.

　　      If you want to list systemd services use 'systemctl list-unit-files'.
　　      To see services enabled on particular target use
　　      'systemctl list-dependencies [target]'.

　　proxysql        0:off   1:off   2:on    3:on    4:on    5:on    6:off
　　4.3.2 啟動
　　默認情況下，rpm安裝的ProxySQL只提供了SysV風格的服務腳本/etc/init.d/proxysql。
　　所以，可通過該腳本管理ProxySQL的啟動、停止等功能。

　　[root@ProxySQL ~]# /etc/init.d/proxysql --help
　　Usage: ProxySQL {start|stop|status|reload|restart|initial}

　　# 啟動
　　[root@ProxySQL ~]# service proxysql start
　　Starting ProxySQL: DONE!

　　# 查看
　　[root@tcloud-113 ~]# service proxysql status
　　ProxySQL is running (30422).

　　# 啟動后會監聽兩個端口，默認為6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL對外提供服務的端口。
　　[root@ProxySQL ~]# ss -lntup |grep proxysql
　　tcp    LISTEN     0      128                    *:6032                  *:*      users:(("proxysql",1322,20))
　　tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,19))
　　tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,18))
　　tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,17))
　　tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,16))
　　# 可以看到轉發端口的6033開啟了4個線程，線程數由全局變量"threads"控制，受cpu物理核心數的影響（每個端口下的線程數<=cpu物理核心數）
　　如果想要通過systemd管理ProxySQL，可在/usr/lib/systemd/system/proxysql.service中寫入如下內容：

　　[root@ProxySQL ~]# vim /usr/lib/systemd/system/proxysql.service
　　[Unit]
　　Description=High Performance Advanced Proxy for MySQL
　　After=network.target

　　[Service]
　　Type=simple
　　User=mysql
　　Group=mysql
　　PermissionsStartOnly=true
　　LimitNOFILE=102400
　　LimitCORE=1073741824
　　ExecStartPre=/bin/mkdir -p /var/lib/proxysql
　　ExecStartPre=/bin/chown mysql:mysql -R /var/lib/proxysql /etc/proxysql.cnf
　　ExecStart=/usr/bin/proxysql -f
　　Restart=always

　　[root@ProxySQL ~]#
　　一般來說，ProxySQL很少停止或重啟，因為絕大多數配置都可以在線修改。

　　5 配置proxysql
　　5.1 添加后端連接mysql主從數據庫的配置
　　5.1.1 mysql主庫添加proxysql可以增刪改查的賬號
　　例如：
　　user：proxysql；
　　password：pwproxysql

　　mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.1.%' IDENTIFIED BY 'pwproxysql';
　　5.1.2 登陸proxysql管理端
　　[root@ProxySQL ~]# yum install mysql -y         # 安裝mysql客戶端命令；依賴：mysql-libs
　　[root@ProxySQL ~]# export MYSQL_PS1="(/u@/h:/p) [/d]> "
　　[root@ProxySQL ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032     # 默認的用戶名密碼都是 admin。
　　Welcome to the MySQL monitor. Commands end with ; or /g.
　　Your MySQL connection id is 1
　　Server version: 5.5.30 (ProxySQL Admin Module)

　　Copyright (c) 2000, 2013, 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.

　　(admin@127.0.0.1:6032) [(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.00 sec)
　　庫說明：

　　main 內存配置數據庫，表里存放后端db實例、用戶驗證、路由規則等信息。表名以 runtime開頭的表示proxysql當前運行的配置內容，不能通過dml語句修改，只能修改對應的不以 runtime 開頭的（在內存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盤以供下次重啟加載。
　　disk 是持久化到硬盤的配置，sqlite數據文件。
　　stats 是proxysql運行抓取的統計信息，包括到后端各命令的執行次數、流量、processlist、查詢種類匯總/執行時間等等。
　　monitor 庫存儲 monitor 模塊收集的信息，主要是對后端db的健康/延遲檢查。
　　stats_history 統計信息歷史庫
　　5.1.3 Proxysql管理端添加后端連接mysql主從數據庫的配置
　　(admin@127.0.0.1:6032) [(none)]> show tables from main;
　　+--------------------------------------------+
　　| tables                                     |
　　+--------------------------------------------+
　　| global_variables                           |    # ProxySQL的基本配置參數，類似與MySQL
　　| mysql_collations                           |    # 配置對MySQL字符集的支持
　　| mysql_group_replication_hostgroups         |    # MGR相關的表，用于實例的讀寫組自動分配
　　| mysql_query_rules                          |    # 路由表
　　| mysql_query_rules_fast_routing             |    # 主從復制相關的表，用于實例的讀寫組自動分配
　　| mysql_replication_hostgroups               |    # 存儲MySQL實例的信息
　　| mysql_servers                              |    # 現階段存儲MySQL用戶，當然以后有前后端賬號分離的設想
　　| mysql_users                                |    # 存儲ProxySQL的信息，用于ProxySQL Cluster同步
　　| proxysql_servers                           |    # 運行環境的存儲校驗值
　　| runtime_checksums_values                   |    #
　　| runtime_global_variables                   |    #
　　| runtime_mysql_group_replication_hostgroups |    #
　　| runtime_mysql_query_rules                  |    #
　　| runtime_mysql_query_rules_fast_routing     |    #
　　| runtime_mysql_replication_hostgroups       |    # 與上面對應，但是運行環境正在使用的配置
　　| runtime_mysql_servers                      |    #
　　| runtime_mysql_users                        |    #
　　| runtime_proxysql_servers                   |    #
　　| runtime_scheduler                          |    #
　　| scheduler                                  |    # 定時任務表
　　+--------------------------------------------+
　　20 rows in set (0.00 sec)
　　runtime_開頭的是運行時的配置，這些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必須執行LOAD ... TO RUNTIME才能加載到RUNTIME生效，執行save ... to disk才能將配置持久化保存到磁盤。

　　下面語句中沒有先切換到main庫也執行成功了，因為ProxySQL內部使用的SQLite3數據庫引擎，和MySQL的解析方式是不一樣的。即使執行了USE main語句也是無任何效果的，但不會報錯。

　　使用insert語句添加mysql主機到mysql_servers表中，其中：hostgroup_id 1 表示寫組，2表示讀組。

　　(admin@127.0.0.1:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.1.102',3306,1,'Write Group');
　　Query OK, 1 row affected (0.00 sec)

　　(admin@127.0.0.1:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.1.103',3306,1,'Read Group');
　　Query OK, 1 row affected (0.00 sec)

　　(admin@127.0.0.1:6032) [(none)]> select * from mysql_servers;
　　+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
　　| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
　　+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
　　| 1            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Write Group |
　　| 2            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group |
　　+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
　　2 rows in set (0.00 sec)
　　修改后，加載到RUNTIME，并保存到disk。

　　(admin@127.0.0.1:6032) [(none)]> load mysql servers to runtime;
　　(admin@127.0.0.1:6032) [(none)]> save mysql servers to disk;
　　在proxysql主機的mysql_users表中添加剛才創建的賬號，proxysql客戶端需要使用這個賬號來訪問數據庫。
　　default_hostgroup默認組設置為寫組，也就是1;
　　當讀寫分離的路由規則不符合時，會訪問默認組的數據庫;

　　(admin@127.0.0.1:6032) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','pwproxysql',1,1);
　　Query OK, 1 row affected (0.00 sec)

　　(admin@127.0.0.1:6032) [(none)]> select * from mysql_users /G
　　*************************** 1. row ***************************
　　              username: proxysql        # 后端mysql實例的用戶名
　　             password: pwproxysql       # 后端mysql實例的密碼
　　                 active: 1              # active=1表示用戶生效，0表示不生效
　　                use_ssl: 0
　　       default_hostgroup: 1             # 用戶默認登錄到哪個hostgroup_id下的實例
　　          default_schema: NULL          # 用戶默認登錄后端mysql實例時連接的數據庫，這個地方為NULL的話，則由全局變量mysql-default_schema決定，默認是information_schema
　　           schema_locked: 0
　　 transaction_persistent: 1              # 如果設置為1，連接上ProxySQL的會話后，如果在一個hostgroup上開啟了事務，那么后續的sql都繼續維持在這個hostgroup上，不倫是否會匹配上其它路由規則，直到事務結束。雖然默認是0
　　               fast_forward: 0              # 忽略查詢重寫/緩存層，直接把這個用戶的請求透傳到后端DB。相當于只用它的連接池功能，一般不用，路由規則 .* 就行了
　　                     backend: 1
　　                     frontend: 1
　　          max_connections: 10000            # #該用戶允許的最大連接數
　　1 row in set (0.00 sec)
　　修改后，加載到RUNTIME，并保存到disk。

　　(admin@127.0.0.1:6032) [(none)]> load mysql users to runtime;
　　(admin@127.0.0.1:6032) [(none)]> save mysql users to disk;
　　5.2 添加健康監測的賬號
　　5.2.1 mysql端添加proxysql只能查的賬號
　　首先在后端master節點上創建一個用于監控的用戶名(只需在master上創建即可，因為會復制到slave上)，這個用戶名只需具有USAGE權限即可。如果還需要監控復制結構中slave是否嚴重延遲于master(先混個眼熟：這個俗語叫做"拖后腿"，術語叫做"replication lag")，則還需具備replication client權限。這里直接賦予這個權限。

　　mysql> GRANT replication client ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'monitor';
　　5.2.2 proxysql端修改變量設置健康檢測的賬號
　　    (admin@127.0.0.1:6032) [(none)]> set mysql-monitor_username='monitor';
　　    Query OK, 1 row affected (0.00 sec)

　　    (admin@127.0.0.1:6032) [(none)]> set mysql-monitor_password='monitor';
　　    Query OK, 1 row affected (0.00 sec)
　　以上設置實際上是在修改global_variables表，它和下面兩個語句是等價的：

　　    (admin@127.0.0.1:6032) [(none)]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
　　    Query OK, 1 row affected (0.00 sec)

　　    (admin@127.0.0.1:6032) [(none)]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
　　    Query OK, 1 row affected (0.00 sec)
　　修改后，加載到RUNTIME，并保存到disk。

　　(admin@127.0.0.1:6032) [(none)]> load mysql variables to runtime;
　　(admin@127.0.0.1:6032) [(none)]> save mysql variables to disk;
　　5.3 添加讀寫分離的路由規則：
　　將select語句全部路由至hostgroup_id=2的組(也就是讀組)
　　但是select * from tb for update這樣的語句是修改數據的，所以需要單獨定義，將它路由至hostgroup_id=1的組(也就是寫組)
　　其他沒有被規則匹配到的組將會被路由至用戶默認的組(mysql_users表中的default_hostgroup)
　　(admin@127.0.0.1:6032) [(none)]> 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.00 sec)

　　(admin@127.0.0.1:6032) [(none)]> 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.00 sec)

　　(admin@127.0.0.1:6032) [(none)]> 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.00 sec)
　　5.4 將剛才我們修改的數據加載至RUNTIME中(參考ProxySQL的多層配置結構)：
　　5.4.1 load進runtime，使配置生效
　　(admin@127.0.0.1:6032) [(none)]> load mysql query rules to runtime;
　　(admin@127.0.0.1:6032) [(none)]> load admin variables to runtime;
　　5.4.2 save到磁盤(/var/lib/proxysql/proxysql.db)中，永久保存配置
　　(admin@127.0.0.1:6032) [(none)]> save mysql query rules to disk;
　　(admin@127.0.0.1:6032) [(none)]> save admin variables to disk;
　　6 測試讀寫分離
　　6.1 連接proxysql客戶端:
　　登錄用戶是剛才我們在mysql_user表中創建的用戶，端口為6033

　　[root@centos7 ~]#mysql -uproxysql -ppwproxysql -h227.0.0.1 -P6033
　　Welcome to the MariaDB monitor. Commands end with ; or /g.
　　Your MySQL connection id is 4
　　Server version: 5.5.30 (ProxySQL)

　　Copyright (c) 2000, 2017, 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 |
　　| test               |
　　+--------------------+
　　4 rows in set (0.00 sec)

　　MySQL [(none)]>
　　6.2 嘗試修改數據庫和查詢：
　　創建兩個數據庫和查個表。

　　MySQL [(none)]> create database bigboss;
　　Query OK, 1 row affected (0.01 sec)

　　MySQL [(none)]> create database weijinyun;
　　Query OK, 1 row affected (0.00 sec)

　　MySQL [(none)]> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| bigboss            |
　　| mysql              |
　　| performance_schema |
　　| test               |
　　| weijinyun          |
　　+--------------------+
　　6 rows in set (0.01 sec)

　　MySQL [(none)]> select user,host from mysql.user;
　　+-------------+---------------+
　　| user        | host          |
　　+-------------+---------------+
　　| root        | 127.0.0.1     |
　　| monitor     | 192.168.1.% |
　　| proxysql    | 192.168.1.% |
　　| repliaction | 192.168.1.% |
　　| root        | ::1           |
　　|             | centos7       |
　　| root        | centos7       |
　　|             | localhost     |
　　| root        | localhost     |
　　+-------------+---------------+
　　9 rows in set (0.01 sec)
　　6.3 驗證讀寫分離是否成功：
　　proxysql有個類似審計的功能，可以查看各類SQL的執行情況。在proxysql管理端執行：
　　從下面的hostgroup和digest_text值來看，所有的寫操作都被路由至1組，讀操作都被路由至2組，
　　其中1組為寫組，2組為讀組！
　　(admin@127.0.0.1:6032) [(none)]> select * from stats_mysql_query_digest;
　　+-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+
　　| hostgroup | schemaname         | username | digest             | digest_text                            | count_star | first_seen | last_seen | sum_time | min_time| max_time |
　　+-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+
　　| 2         | information_schema | proxysql | 0x3EA85877510AC608 | select * from stats_mysql_query_digest | 2          | 1527233735 | 1527233782 | 4092     | 792| 3300     |
　　| 1         | information_schema | proxysql | 0x594F2C744B698066 | select USER()                          | 1          | 1527233378 | 1527233378 | 0        | 0| 0        |
　　| 1         | information_schema | proxysql | 0x02033E45904D3DF0 | show databases                         | 2          | 1527233202 | 1527233495 | 5950     | 1974| 3976     |
　　| 1         | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ?       | 2          | 1527233196 | 1527233378 | 0        | 0| 0        |
　　+-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+
　　4 rows in set (0.00 sec)

　　(admin@127.0.0.1:6032) [(none)]>
　　讀寫分離成功！！！

（編輯：武林網）

上一篇：MySQL使用ProxySQL達成讀寫分離

下一篇：mysql 自帶工具細說