之前我們已經介紹了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
複製程式碼