ProxySQL+MySQL實現(xiàn)數(shù)據(jù)庫讀寫分離實戰(zhàn)

作者：民工哥 2020-03-24 14:16:18

數(shù)據(jù)庫 MySQL

今天給大家?guī)淼氖顷P于數(shù)據(jù)庫讀寫分離相關的實戰(zhàn)操作。一起來卡一下吧。

前面也寫過幾篇關于Mysql數(shù)據(jù)的文章：

MySQL集群高可用架構之MHA
MySQL 同步復制及高可用方案總結
官方工具｜MySQL Router 高可用原理與實戰(zhàn)

今天給大家?guī)淼氖顷P于數(shù)據(jù)庫讀寫分離相關的實戰(zhàn)操作。

ProxySQL介紹

ProxySQL是一個高性能的MySQL中間件，擁有強大的規(guī)則引擎。具有以下特性：http://www.proxysql.com/

1、連接池，而且是multiplexing

2、主機和用戶的最大連接數(shù)限制

3、自動下線后端DB

延遲超過閥值
ping 延遲超過閥值
網絡不通或宕機

4、強大的規(guī)則路由引擎

實現(xiàn)讀寫分離
查詢重寫
sql流量鏡像

5、支持prepared statement

6、支持Query Cache

7、支持負載均衡，與gelera結合自動failover

整體環(huán)境介紹

1、系統(tǒng)環(huán)境

三臺服務器系統(tǒng)環(huán)境一致如下

[root@db1 ~]# cat /etc/redhat-release   
CentOS Linux release 7.4.1708 (Core)   
[root@db1 ~]# uname -r  
3.10.0-693.el7.x86_64

2、IP地址與軟件版本

proxy 192.168.22.171
db1 192.168.22.173
db2 192.168.22.174
mysql 5.7.17
proxy sql 1.4.8

3、關閉防火墻、selinux

systemctl stop firewalld  #停止防火墻服務  
systemctl disable firewalld  #禁止開機自啟動  
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g'  /etc/selinux/conf  && reboot  
#用sed命令替換的試修改selinux的配置文件

4、mysql安裝與主從同步

安裝請參考以下文章

LAMP架構應用實戰(zhàn)——MySQL服務

主從同步請參以下文章

Linux系統(tǒng)MySQL數(shù)據(jù)庫主從同步實戰(zhàn)過程

安裝布署過程

1、數(shù)據(jù)庫主從同步

查看主從同步狀態(tài)

mysql> show slave status\G  
*************************** 1. row ***************************           
 Slave_IO_State: Waiting for master to send event  
Master_Host: 192.168.22.173                
Master_User: rep              
Master_Port: 3306                 
Connect_Retry: 60               
Master_Log_File: master-log.000001 
Read_Master_Log_Pos: 154                
Relay_Log_File: db2-relay-bin.000002  
Relay_Log_Pos: 321          
Relay_Master_Log_File: master-log.000001   
Slave_IO_Running: Yes        
Slave_SQL_Running: Yes             
Replicate_Do_DB:            
Replicate_Ignore_DB:             
Replicate_Do_Table:         
Replicate_Ignore_Table:        
Replicate_Wild_Do_Table:    
Replicate_Wild_Ignore_Table:                     
Last_Errno: 0  
Last_Error:  
Skip_Counter: 0  
Exec_Master_Log_Pos: 154  
Relay_Log_Space: 526  
Until_Condition: None  
Until_Log_File:  
Until_Log_Pos: 0  
Master_SSL_Allowed: No   
Master_SSL_CA_File:   
Master_SSL_CA_Path:   
Master_SSL_Cert:  
Master_SSL_Cipher:  
Master_SSL_Key:  
Seconds_Behind_Master: 0  
Master_SSL_Verify_Server_Cert: No   
Last_IO_Errno: 0   
Last_IO_Error:   
Last_SQL_Errno: 0   
Last_SQL_Error:  
Replicate_Ignore_Server_Ids:   
Master_Server_Id: 1   
Master_UUID: 70a61633-63ae-11e8-ab86-000c29fe99ea   
Master_Info_File: /mysqldata/master.info  
SQL_Delay: 0   
SQL_Remaining_Delay: NULL  
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates   
Master_Retry_Count: 86400   
Master_Bind:  
Last_IO_Error_Timestamp:  
Last_SQL_Error_Timestamp:  
Master_SSL_Crl:   
Master_SSL_Crlpath:   
Retrieved_Gtid_Set:   
Executed_Gtid_Set:   
Auto_Position: 0   
Replicate_Rewrite_DB:  
Channel_Name:   
Master_TLS_Version:  
1 row in set (0.00 sec)

檢測主從同步

[root@db1 ~]# mysql -uroot -p -e "create database testdb;  
"Enter password:   
[root@db1 ~]# mysql -uroot -p -e "show  databases;" |grep testdb  
Enter password:   
testdb  
#db2上查看是否同步  
mysql> show databases;  
+--------------------+  
| Database           |  
+--------------------+  
| information_schema |  
| mysql              |  
| performance_schema |  
| sys                |  
| testdb             |  
+--------------------+  
5 rows in set (0.01 sec)

2、準備proxySQL軟件

[root@proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm  
[root@proxy ~]# ll proxysql-1.4.8-1-centos7.x86_64.rpm   
-rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm

3、安裝配置

[root@proxy ~]# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm  
[root@proxy ~]# rpm -ql  proxysql  
/etc/init.d/proxysql    #啟動腳本  
/etc/proxysql.cnf       #配置文件，僅在第一次(/var/lib/proxysql/proxysql.db文件不存在)啟動時有效。啟#動后可以在proxysql管理端中通過修改數(shù)據(jù)庫的方式修改配置并生效(官方推薦方式。)  
/usr/bin/proxysql       #主程序文件  
/usr/share/proxysql/tools/proxysql_galera_checker.sh  
/usr/share/proxysql/tools/proxysql_galera_writer.pl

4、配置文件詳解

[root@proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf  
datadir="/var/lib/proxysql"     #數(shù)據(jù)目錄  
admin_variables=  
{      
admin_credentials="admin:admin"   #連接管理端的用戶名與密碼   
mysql_ifaces="0.0.0.0:6032"       #管理端口，用來連接proxysql的管理數(shù)據(jù)庫  
}  
mysql_variables=  
{      
threads=4      #指定轉發(fā)端口開啟的線程數(shù)量      
max_connections=2048      
default_query_delay=0  
default_query_timeout=36000000  
have_compress=true  
poll_timeout=2000  
interfaces="0.0.0.0:6033"        #指定轉發(fā)端口，用于連接后端mysql數(shù)據(jù)庫的，相當于代理作用  
default_schema="information_schema"  
stacksize=1048576  
server_version="5.5.30"          #指定后端mysql的版本  
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=  
(  
)  
#因此我們使用官方推薦的方式來配置proxy sql

5、啟動服務并查看

[root@proxy ~]# /etc/init.d/proxysql  
startStarting ProxySQL: DONE!  
[root@proxy ~]# ss -lntup|grep proxy  
tcp    LISTEN     0   128   *:6032      *:*    users:(("proxysql",pid=1199,fd=23))  
tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=22))  
tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=21))  
tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=20))  
tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=19))   
#可以看出轉發(fā)端口6033是啟動了四個線程

6、在mysql上配置賬號并授權

mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.22.%' IDENTIFIED BY '123456';  
Query OK, 0 rows affected, 1 warning (0.03 sec)  
mysql> flush privileges;  
Query OK, 0 rows affected (0.02 sec)

7、proxysql默認數(shù)據(jù)庫說明

[root@proxy ~]# yum install mysql -y  
[root@proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032  
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, 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;  
+-----+---------------+-------------------------------------+  
| 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：內存配置數(shù)據(jù)庫，表里存放后端db實例、用戶驗證、路由規(guī)則等信息。表名以 runtime_開頭的表示proxysql當前運行的配置內容，不能通過dml語句修改，只能修改對應的不以 runtime_ 開頭的（在內存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盤以供下次重啟加載。

disk：是持久化到硬盤的配置，sqlite數(shù)據(jù)文件。

stats：是proxysql運行抓取的統(tǒng)計信息，包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時間等等。

monitor：庫存儲 monitor 模塊收集的信息，主要是對后端db的健康/延遲檢查。

8、proxysql的配置系統(tǒng)

ProxySQL具有一個復雜但易于使用的配置系統(tǒng)，可以滿足以下需求：

1、允許輕松動態(tài)更新配置（這是為了讓ProxySQL用戶可以在需要零宕機時間配置的大型基礎架構中使用它）。與MySQL兼容的管理界面可用于此目的。

2、允許盡可能多的配置項目動態(tài)修改，而不需要重新啟動ProxySQL進程

3、可以毫不費力地回滾無效配置

4、這是通過多級配置系統(tǒng)實現(xiàn)的，其中設置從運行時移到內存，并根據(jù)需要持久保存到磁盤。

3級配置由以下幾層組成：

參考文章：https://github.com/sysown/pro...

9、配置proxysql管理用戶

proxysql默認的表信息如下

MySQL [main]> show tables;  
+--------------------------------------------+  
| tables                                     |  
+--------------------------------------------+  
| global_variables                           |  
| mysql_collations                           |  
| mysql_group_replication_hostgroups         |  
| mysql_query_rules                          |  
| mysql_query_rules_fast_routing             |  
| mysql_replication_hostgroups               |  
| mysql_servers                              |  
| mysql_users                                |  
| 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)  
#這里是使用insert into語句來動態(tài)配置，而可以不需要重啟  
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');  
Query OK, 1 row affected (0.01 sec)  
MySQL [(none)]> insert intomysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');  
Query OK, 1 row affected (0.00 sec)  
MySQL [(none)]> select * from mysql_servers;  
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |  
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
| 1            | db1      | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Write Group |  
| 2            | db2      | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group    
|+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
2 rows in set (0.00 sec)  
#接下來將剛剛在mysql客戶端創(chuàng)建的用戶寫入到proxy sql主機的mysql_users表中，它也是用于proxysql客戶端訪問數(shù)據(jù)庫，默認組是寫組，當讀寫分離規(guī)則出現(xiàn)問題時，它會直接訪問默認組的數(shù)據(jù)庫。  
MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);  
Query OK, 1 row affected (0.00 sec)  
MySQL [main]> select * from mysql_users;  
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | 
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 
| proxysql | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           | 
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1  
row in set (0.00 sec)

在mysql上添加監(jiān)控的用戶

mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%' 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主機端配置監(jiān)控用戶  
MySQL [main]> set mysql-monitor_username='monitor';  
Query OK, 1 row affected (0.00 sec)  
MySQL [main]> set mysql-monitor_password='monitor';  
Query OK, 1 row affected (0.00 sec)  
#參考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

10、配置proxysql的轉發(fā)規(guī)則

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.01 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.00 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.00 sec)  
#配置查詢select的請求轉發(fā)到hostgroup_id=2組上（讀組）#征對select * from table_name  for update這樣的修改語句，我們是需要將請求轉到寫組，也就是hostgroup_id=1#對于其它沒有被規(guī)則匹配的請求全部轉發(fā)到默認的組（mysql_users表中default_hostgroup）

11、更新配置到RUNTIME中

由上面的配置系統(tǒng)層級關系可以得知所有進來的請求首先是經過RUNTIME層

MySQL [main]> load mysql users to runtime;  
Query OK, 0 rows affected (0.00 sec)  
MySQL [main]> load mysql servers to runtime;  
Query OK, 0 rows affected (0.02 sec)  
MySQL [main]> load mysql query rules to runtime;  
Query OK, 0 rows affected (0.00 sec)  
MySQL [main]> load mysql variables to runtime; 
Query OK, 0 rows affected (0.00 sec)  
MySQL [main]> load admin variables to runtime;  
Query OK, 0 rows affected (0.00 sec)

12、將所有配置保存至磁盤上

所有配置數(shù)據(jù)保存到磁盤上，也就是永久寫入/var/lib/proxysql/proxysql.db這個文件中

MySQL [main]> save mysql users to disk;  
Query OK, 0 rows affected (0.03 sec)  
MySQL [main]> save mysql servers to disk;  
Query OK, 0 rows affected (0.04 sec)  
ySQL [main]> save mysql query rules to disk;  
Query OK, 0 rows affected (0.03 sec)  
MySQL [main]> save mysql variables to disk;  
Query OK, 94 rows affected (0.02 sec) 
MySQL [main]> save admin variables to disk;  
Query OK, 31 rows affected (0.02 sec)  
MySQL [main]> load mysql users to runtime;  
Query OK, 0 rows affected (0.00 sec)

13、測試讀寫分離

[root@proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033  
Welcome to the MariaDB monitor.Commands end with ; or \g.  
Your MySQL connection id is 2Server 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 |  
| sys                |  
| testdb             |  
+--------------------+  
5 rows in set (0.02 sec)#這才是我們真正的數(shù)據(jù)庫啊

創(chuàng)建數(shù)據(jù)與表，測試讀寫分離情況

MySQL [(none)]> create database test_proxysql;  
Query OK, 1 row affected (0.02 sec)  
MySQL [(none)]> use test_proxysql;  
Database changed  
MySQL [test_proxysql]> create table test_tables(name varchar(20),age int(4));  
Query OK, 0 rows affected (0.07 sec)  
MySQL [test_proxysql]> insert into test_tables values('zhao','30');  
Query OK, 1 row affected (0.09 sec)  
MySQL [test_proxysql]> select * from test_tables;  
+------+------+  
| name | age  |  
+------+------+  
| zhao |   30 |  
+------+------+  
1 row in set (0.02 sec)

在proxysql管理端查看讀寫分離

MySQL [main]> 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         | test_proxysql      | proxysql | 0x57CF7EC26C91DF9A | select * from test_tables                            |1          | 1527667635 | 1527667635 | 14253    | 14253    | 14253    | 
| 1         | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ?                     | 1          | 1527667214 | 1527667214 | 0        | 0        | 0        | 
| 1         | test_proxysql      | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values(?,?)                  | 1          | 1527667623 | 1527667623 | 89033    | 89033    | 89033    | 
| 1         | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql                        | 1          | 1527667316 | 1527667316 | 8470     | 8470     | 8470     | 
| 1         | information_schema | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667222 | 1527667222 | 19414    | 19414    | 19414    | 
| 1         | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql                        | 1          | 1527667332 | 1527667332 | 15814    | 15814    | 15814    | 
| 2         | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE()                                    | 1          | 1527667342 | 1527667342 | 23386    | 23386    | 23386    | 
| 1         | test_proxysql      | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667342 | 1527667342 | 2451     | 2451     | 2451     | 
| 1         | test_proxysql      | proxysql | 0x59F02DA280268525 | create table test_tables                             | 1          | 1527667360 | 1527667360 | 9187     | 9187     | 9187     | 
| 1         | test_proxysql      | proxysql | 0x99531AEFF718C501 | show tables                                          | 1          | 1527667342 | 1527667342 | 1001     | 1001     | 1001     | 
| 1         | test_proxysql      | proxysql | 0xC745E37AAF6095AF | create table test_tables(name varchar(?),age int(?)) | 1          | 1527667558 | 1527667558 | 68935    | 68935    | 68935    | 
+-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 
11 rows in set (0.01 sec)#從上述結果就可以看出讀寫分離配置是成功的，讀請求是轉發(fā)到2組，寫請求轉發(fā)到1組

整個讀寫分離的架構配置到此就完成了，但是此架構存在需要優(yōu)化的地方，那就是此架構存在單點問題。實際生產環(huán)境中可采用MHA+ProxySQL+Mysql這類架構解決此問題，請持續(xù)關注!

責任編輯：龐桂玉來源： segmentfault