ProxySQL實現MySQL讀寫分離

EdgarZz發表於2019-03-04
MySql

之前我們已經介紹了MySQL的高可用

這樣我們至少保證了我們的服務不會因為MySQL掛掉而不能使用，不過按照之前的配置我們訪問的時候依然訪問的是單個節點，接下來我們就要想辦法為節點分擔壓力了，本文介紹的方案是：使用ProxySQL實現MySQL讀寫分離

ProxySQL是一個讀寫分離的中介軟體，開源專案，優勢是強大的規則引擎、支援線上配置、支援負載均衡，詳情可以參考官方文件

ProxySQL還支援prepare、query cache、連線池，這些特性不在本文的介紹範圍內

這裡需要說明一下，讀寫分離方案是要看實際場景的。
如果對資料實時性要求極高，例如訂單系統，是不適合讀寫分離方案的，因為讀資料節點同步寫資料節點的資料是有一定時間差的。
當然也不是就不能用，只是針對這種場景需要單獨設定此時讀資料和寫資料使用同一節點

下面介紹一下ProxySQL的安裝和配置

說明一下伺服器情況：

1. 一共三臺伺服器，系統ubuntu16.04 64位
2. IP分別為：192.168.1.222、192.168.1.223、192.168.1.224，均安裝了MySQL5.7
3. 222為master節點，223和224都是slave節點
4. mha-manager裝在223上，三臺機器都裝了mha-node
複製程式碼

ProxySQL安裝

下載安裝包

wget https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql_1.4.9-dbg-ubuntu16_amd64.deb
複製程式碼

安裝

dpkg -i proxysql_1.4.9-dbg-ubuntu16_amd64.deb
複製程式碼

啟動服務

service proxysql start
複製程式碼

ProxySQL配置

ProxySQL的配置是支援兩種方式的：配置檔案，資料庫

資料庫的配置方式在第一次啟動服務的時候也是基於配置檔案的（/etc/proxysql.cnf），後續所有的配置都是在SQLLite中進行，並且不會更新proxysql.cnf配置檔案，配置是儲存在/var/lib/proxysql/proxysql.db中

前面提到的線上配置就是基於資料庫的，所以這裡我們就講解在資料庫中的配置方式

1. 建立服務賬號和monitor賬號

登入master資料庫

mysql mysql -u root -p
複製程式碼

新增賬號並授權

GRANT ALL ON *.* TO `proxysql`@`192.168.1.%` IDENTIFIED BY `proxysql`;
GRANT SELECT ON *.* TO `monitor`@`192.168.1.%` IDENTIFIED BY `monitor`;
複製程式碼

要先建立兩個資料庫賬號用於後續配置，其中proxysql用於運算元據庫，monitor用於監控

2. 登入到proxysql管理端

proxysql管理埠預設是6032，預設的使用者名稱密碼都是admin。

mysql -uadmin -padmin -h127.0.0.1 -P6032
複製程式碼

3. 檢視資料庫資訊

mysql> 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 |
| 6   | myhgm         |                                     |
+-----+---------------+-------------------------------------+
6 rows in set (0.00 sec)
複製程式碼
main：預設資料庫，存放使用者驗證、路由規則等資訊。我們要做的配置都是針對這個庫的
disk：持久化到硬碟的配置
stats：proxysql執行抓取的統計資訊，如各命令的執行次數、查詢執行時間等
monitor：monitor模組收集的資訊，db的健康情況、各種檢查等
複製程式碼

4. 檢視配置表

mysql> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| debug_levels                               |
| 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                                  |
+--------------------------------------------+
21 rows in set (0.00 sec)
複製程式碼
global_variables：各種變數，包括監聽的埠、管理賬號、是否禁用monitor等，詳情可以參考[官方文件](https://github.com/sysown/proxysql/wiki/Global-variables)
mysql_*：mysql開頭的表就是我們配置要操作的表，具體都是幹什麼的還是看官方文件吧，介紹的很細，後面我會針對讀寫分離的配置做介紹
runtime_*：runtime開頭的表是執行時讀的表，不能通過DML語句修改，我們針對mysql開頭的表做完配置修改之後，要執行**load mysql xxx to runtime**以將對應的配置載入到執行時環境
注意：當執行完load語句將配置載入到執行時環境後，還要執行**save mysql xxx to disk**將配置存到硬碟上，以便下次重啟時載入，如果忘記執行，當重啟時本次修改的配置會丟失
複製程式碼

5. 新增DB例項

我們一共有三個節點（一個master兩個slave），要進行讀寫分離，這裡我們將master設為寫節點，兩個slave設為讀節點

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,`192.168.1.222`,3306,1,1000,10,`write group`);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,`192.168.1.223`,3306,1,1000,10,`read group`);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,`192.168.1.224`,3306,1,1000,10,`read group`);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1000         | 192.168.1.222 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | write group |
| 2000         | 192.168.1.223 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | read group  |
| 2000         | 192.168.1.224 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | read group  |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
3 rows in set (0.00 sec)
複製程式碼
hostgroup_id：一個角色一個id，該表的主鍵是hostgroup_id+hostname+port
hostname：db例項IP
port：db例項埠
weight：權重，如果有多個相同角色的例項，會優先選擇權重高的
status：狀態
	-ONLINE 正常
	-SHUNNED 臨時被剔除
	-OFFLINE_SOFT 軟離線狀態，不再接受新的連線，已建立的連線會等待
	-OFFLINE_HARD 離線，不接收新連線， 已建立的連線也會強制斷開（當機或者網路不可用）
max_connections：最大連線數
max_replication_lag：允許的最大延遲
複製程式碼

6. 新增服務賬號

mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (`proxysql`,`proxysql`,1000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | proxysql | 1      | 0       | 1000              | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.01 sec)
複製程式碼

7. 設定監控賬號

mysql> set mysql-monitor_username=`monitor`;
Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password=`monitor`;
Query OK, 1 row affected (0.00 sec)
複製程式碼

8. 新增規則

讀寫分離規則

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,`^SELECT.*FOR UPDATE$`,1000,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,`^SELECT`,2000,1);
Query OK, 1 row affected (0.00 sec)

mysql> 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$ | 1000                  | 1     |
| 2       | 1      | ^SELECT              | 2000                  | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
複製程式碼

9. 完善配置

我們的mysql叢集是基於MHA的，master掛掉之後，slave會提升為新的master，這個時候我們希望proxysql的規則自動變更，在mysql_servers中增加一條記錄，將新的master的hostname和port新增到寫的hostgroup中

proxysql是支撐這種配置的，根據mysql_replication_hostgroups中的資料，proxysql通過檢測到各server的read_only值來自動為server設定hostgroup_id

mysql> insert into  mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values(1000,2000,`Reading and Writing Separation`);
Query OK, 1 row affected (0.00 sec)

mysql> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+--------------------------------+
| writer_hostgroup | reader_hostgroup | comment                        |
+------------------+------------------+--------------------------------+
| 1000             | 2000             | Reading and Writing Separation |
+------------------+------------------+--------------------------------+
1 row in set (0.01 sec)
複製程式碼

10. 將配置載入到執行時

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
複製程式碼

11. 將配置儲存到硬碟

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.15 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.33 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.36 sec)

mysql> save mysql variables to disk;
Query OK, 96 rows affected (0.09 sec)

mysql> save admin variables to disk;
Query OK, 32 rows affected (0.09 sec)
複製程式碼

到此讀寫分離的相關配置就大功告成了！之後我們的應用可以通過配置中配置proxysql使用者連線proxysql服務操作我們的mysql叢集

mysql -uproxysql -pproxysql -h 127.0.0.1 -P 6033
複製程式碼

相關文章