ProxySQL是一個高效能的MySQL中介軟體，擁有強大的規則引擎。ProxySQL是用C++語言開發的，也是percona推的一款中介軟體，雖然也是一個輕量級產品，但效能很好(據測試，能處理千億級的資料)，功能也足夠，能滿足中介軟體所需的絕大多數功能。
ProxySQL具備了很多優質特性，具體總結如下:
-> 連線池，而且是multiplexing
-> 主機和使用者的最大連線數限制
-> 自動下線後端DB
-> 延遲超過閥值
-> ping 延遲超過閥值
-> 網路不通或當機
-> 強大的規則路由引擎
-> 實現讀寫分離
-> 查詢重寫
-> sql流量映象
-> 支援prepared statement
-> 支援Query Cache
-> 支援負載均衡，與gelera結合自動failover
-> 可定製基於使用者、基於schema、基於語句的規則對SQL語句進行路由。換句話說，規則很靈活。基於schema和與語句級的規則，可以實現簡單的sharding。
-> 可快取查詢結果。雖然ProxySQL的快取策略比較簡陋，但實現了基本的快取功能，絕大多數時候也夠用了。此外，作者已經打算實現更豐富的快取策略。
-> 監控後端節點。ProxySQL可以監控後端節點的多個指標，包括：ProxySQL和後端的心跳資訊，後端節點的read-only/read-write，slave和master的資料同步延遲性(replication lag)。
ProxySQL是一個能實實在在用在生產環境的MySQL中介軟體，可以實現讀寫分離，支援 Query 路由功能，支援動態指定某個 SQL 進行 cache，支援動態載入配置、故障切換和一些 SQL的過濾功能。還有一些同類產品比如 DBproxy、MyCAT、OneProxy 等。但經過反覆對比和測試之後，還是覺得ProxySQL是一款效能不諳，靠譜穩定的MySQL 中介軟體產品 ！
ProxySQL+Mysql實現資料庫讀寫分離實戰記錄
1. 基礎環境準備
172.16.60.212 mysql-master 安裝Mysql5.7 172.16.60.213 mysql-slave 安裝Mysql5.7 172.16.60.214 mysql-proxy 安裝ProxySQL，Mysql-client 1) 三個節點各自設定主機名 [root@mysql-master ~]# hostnamectl --static set-hostname mysql-master [root@mysql-master ~]# hostname mysql-master [root@mysql-slave ~]# hostnamectl --static set-hostname mysql-slave [root@mysql-slave ~]# hostname mysql-slave [root@mysql-proxy ~]# hostnamectl --static set-hostname mysql-proxy [root@mysql-proxy ~]# hostname mysql-proxy 2) 三個節點都要繫結hosts [root@mysql-master ~]# cat /etc/hosts ......... 172.16.60.212 mysql-master 172.16.60.213 mysql-slave 172.16.60.214 mysql-proxy 3）三個節點都關閉selinux和iptables防火牆 (為了實驗方面，線上環境可以開啟，允許3306埠規則) [root@mysql-master ~]# setenforce 0 [root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled [root@mysql-master ~]# iptables -F [root@mysql-master ~]# systemctl disable firewalld [root@mysql-master ~]# systemctl stop firewalld [root@mysql-master ~]# firewall-cmd --state not running
2. 安裝Mysql 5.7 (在mysql-master 和 mysql-slave節點)
在兩個mysql節點機上使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html
安裝MySQL yum資源庫
[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安裝MySQL 5.7
[root@mysql-master ~]# yum install -y mysql-community-server
啟動MySQL伺服器和MySQL的自動啟動
[root@mysql-master ~]# systemctl start mysqld.service
[root@mysql-master ~]# systemctl enable mysqld.service
設定登入密碼
由於MySQL從5.7開始不允許首次安裝後使用空密碼進行登入！為了加強安全性，系統會隨機生成一個密碼以供管理員首次登入使用，
這個密碼記錄在/var/log/mysqld.log檔案中，使用下面的命令可以檢視此密碼：
[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
使用上面檢視的密碼TaN.k:*Qw2xs 登入mysql，並重置密碼為123456
[root@mysql-master ~]# mysql -p #輸入預設的密碼：TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
檢視mysql版本
[root@mysql-master ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
=====================================================================
溫馨提示
mysql5.7通過上面預設安裝後，執行語句可能會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
這個報錯與Mysql 密碼安全策略validate_password_policy的值有關，validate_password_policy可以取0、1、2三個值：
解決辦法：
set global validate_password_policy=0;
set global validate_password_length=1;
3. 配置Mysql基於GTID的主從同步 (在mysql-master 和 mysql-slave節點)
由於這兩臺節點機之前做過別的實驗，為了保證資料環境乾淨，可以刪除/var/lib/mysql，然後再次重啟mysqld即可!
# systemctl stop mysqld
# rm -rf /var/lib/mysql
# systemctl start mysqld
這樣操作後，mysql資料庫就等於重新初始化了一次，之前的資料環境全部被幹掉了，密碼也是初始化後的隨機密碼
如下檢視初始密碼
# cat /var/log/mysqld.log|grep 'A temporary password'
然後再進行密碼重置等操作，這裡就不贅述了，在前面安裝mysql的時候已經提到過了。
==================================================================
1) mysql-master 作為主資料庫節點，其my.cnf配置為:
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
配置完成之後，別忘了重啟Mysql
[root@mysql-master ~]# systemctl restart mysqld
檢視一下master狀態， 發現多了一項"Executed_Gtid_Set "
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000002 | 550 | | | 88d43a34-1ec8-11e9-b0b5-00505688047c:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 88d43a34-1ec8-11e9-b0b5-00505688047c |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 88d43a34-1ec8-11e9-b0b5-00505688047c:1-2 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
8 rows in set (0.00 sec)
主庫執行從庫複製授權
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for slave@'172.16.60.213';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.213 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.213' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在主資料庫機器上建立一個測試庫kevin（為了測試效果）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.27 sec)
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
==========================================================================
2) 從資料庫172.16.60.206的操作
my.cnf檔案裡GTID複製的配置內容如下：
與主伺服器配置大概一致，除了server_id不一致外，從伺服器還可以在配置檔案裡面新增："read_only＝on" ,
使從伺服器只能進行讀取操作，此引數對超級使用者無效，並且不會影響從伺服器的複製；
[root@mysql-slave ~]# > /etc/my.cnf
[root@mysql-slave ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
配置完成之後，別忘了重啟Mysql
[root@mysql-slave ~]# systemctl restart mysqld
接著登入mysql，做主從同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
在從資料庫裡，使用change master 配置主從複製
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.212',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.212
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1753
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 1968
Relay_Master_Log_File: master-bin.000002
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: 1753
Relay_Log_Space: 2181
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: 88d43a34-1ec8-11e9-b0b5-00505688047c
Master_Info_File: /var/lib/mysql/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: 88d43a34-1ec8-11e9-b0b5-00505688047c:1-7
Executed_Gtid_Set: 872832c0-1ec8-11e9-84a5-005056880888:1-2,
88d43a34-1ec8-11e9-b0b5-00505688047c:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
接著檢視從資料庫的資料，發現kevin庫已經同步過來了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
檢視從資料庫的gtid資訊
mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 872832c0-1ec8-11e9-84a5-005056880888:1-2,
88d43a34-1ec8-11e9-b0b5-00505688047c:1-11 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
3) 然後回到主資料庫中檢視master狀態
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 1 | 872832c0-1ec8-11e9-84a5-005056880888 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
通過上面一系列配置，則mysql基於GTID的主從複製環境就部署好了。
4) 下面開始驗證：
在172.16.60.212的主資料庫裡插入新資料
mysql> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu");
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
到172.16.60.213的從資料庫裡檢視，發現已經同步過來了
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
| 10 | heifei |
| 11 | huoqiu |
| 12 | chengxihu |
+----+-----------+
6 rows in set (0.00 sec)
4. 安裝配置ProxySQL，實現讀寫分離 (在mysql-proxy節點)
也可以參考另一篇博文https://www.cnblogs.com/kevingrace/p/5569652.html ，這篇文件裡已介紹了利用proxysql實現讀寫分離操作 (一主兩從，一寫兩讀模式)。
- 安裝mysql客戶端，用於在本機連線到ProxySQL的管理介面
[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.3.5/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 安裝mysql-clinet客戶端 [root@mysql-proxy ~]# yum install -y MariaDB-client -------------------------------------------------------------------------------------------------------------------------------------------------------- 如果遇到報錯： Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64 You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest 解決辦法： [root@mysql-proxy ~]# rpm -qa|grep mariadb* mariadb-libs-5.5.56-2.el7.x86_64 [root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps [root@mysql-proxy ~]# yum install -y MariaDB-client
- 安裝proxysql
proxysql的rpm包下載地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg
提取密碼: 5t1c
[root@mysql-proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
[root@mysql-proxy ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force
配置檔案詳解
[root@mysql-proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf
datadir="/var/lib/proxysql" #資料目錄
admin_variables=
{
admin_credentials="admin:admin" #連線管理端的使用者名稱與密碼
mysql_ifaces="0.0.0.0:6032" #管理埠，用來連線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" #指定轉發埠，用於連線後端mysql資料庫的，相當於代理作用
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
- 啟動服務並檢視
[root@mysql-proxy ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@mysql-proxy ~]# ss -lntup|grep proxy
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=2943,fd=24))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=22))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=21))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=20))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=19))
可以看出轉發埠6033是啟動了四個執行緒
- 在mysql-master主資料庫節點上執行：(只需master執行即可，會複製給slave從資料庫)
mysql> GRANT ALL ON *.* TO 'proxysql'@'172.16.60.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec)
- proxysql預設資料庫說明
[root@mysql-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, 2018, 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.001 sec) ProxySQL提供了幾個庫，每個庫都有各自的意義； -> main記憶體配置資料庫，表裡存放後端db例項、使用者驗證、路由規則等資訊。表名以 runtime_開頭的表示proxysql當前執行的配置內容， 不能通過dml語句修改，只能修改對應的不以 runtime_ 開頭的（在記憶體）裡的表，然後 LOAD 使其生效， SAVE 使其存到硬碟以供下次重啟載入。 -> disk 是持久化到硬碟的配置，sqlite資料檔案。 -> stats 是proxysql執行抓取的統計資訊，包括到後端各命令的執行次數、流量、processlist、查詢種類彙總/執行時間等等。 -> monitor 庫儲存 monitor 模組收集的資訊，主要是對後端db的健康/延遲檢查。
- proxysql的配置系統
ProxySQL具有一個複雜但易於使用的配置系統，可以滿足以下需求：
-> 允許輕鬆動態更新配置（這是為了讓ProxySQL使用者可以在需要零當機時間配置的大型基礎架構中使用它）。與MySQL相容的管理介面可用於此目的。
-> 允許儘可能多的配置專案動態修改，而不需要重新啟動ProxySQL程式
-> 可以毫不費力地回滾無效配置
-> 這是通過多級配置系統實現的，其中設定從執行時移到記憶體，並根據需要持久儲存到磁碟。
3級配置由以下幾層組成:
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+
可以參考官方文章：https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
- 配置proxysql管理使用者
MySQL [(none)]> 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.000 sec)
下面使用insert into語句來動態配置（插入語句裡使用主機名或ip都可以），而可以不需要重啟!!!
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'mysql-master','3306',1,'Write Group');
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'mysql-slave','3306',1,'Read Group');
Query OK, 1 row affected (0.000 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 | mysql-master | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |
| 2 | mysql-slave | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.000 sec)
接下來將剛剛在mysql客戶端建立的使用者寫入到proxy sql主機的mysql_users表中，它也是用於proxysql客戶端訪問資料庫，預設組是寫組，
當讀寫分離規則出現問題時，它會直接訪問預設組的資料庫。
MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> 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.000 sec)
- 在mysql-master主資料庫節點上新增監控的使用者：(只需master執行即可，會複製給slave從資料庫)
mysql> GRANT SELECT ON *.* TO 'monitor'@'172.16.60.%' IDENTIFIED BY 'monitor'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)
- 在mysql-proxy節點上配置監控使用者
MySQL [(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.001 sec) MySQL [(none)]> set mysql-monitor_password='monitor'; Query OK, 1 row affected (0.000 sec)
- 配置proxysql的轉發規則
MySQL [(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.000 sec) MySQL [(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.000 sec) MySQL [(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.000 sec) 以上配置後，可以得知： -> 配置查詢select的請求轉發到hostgroup_id=2組上（讀組）; -> 徵對select * from table_name for update這樣的修改語句，我們是需要將請求轉到寫組，也就是hostgroup_id=1; -> 對於其它沒有被規則匹配的請求全部轉發到預設的組（mysql_users表中default_hostgroup）;
- 更新配置到RUNTIME中
由上面的配置系統層級關係可以得知所有進來的請求首先是經過RUNTIME層
MySQL [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.004 sec) MySQL [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> load admin variables to runtime; Query OK, 0 rows affected (0.001 sec)
- 將所有配置儲存至磁碟上
所有配置資料儲存到磁碟上，也就是永久寫入/var/lib/proxysql/proxysql.db這個檔案中
MySQL [(none)]> save mysql users to disk; Query OK, 0 rows affected (0.103 sec) MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.273 sec) MySQL [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.320 sec) MySQL [(none)]> save mysql variables to disk; Query OK, 94 rows affected (0.084 sec) MySQL [(none)]> save admin variables to disk; Query OK, 31 rows affected (0.059 sec) MySQL [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.001 sec)
- 測試讀寫分離
通過轉發埠連線資料庫，這時連線到的才是我們真正需要的資料 （注意轉發埠是6033） [root@mysql-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 4 Server 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. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.002 sec) MySQL [(none)]> 這時發現的才是我們真正的資料庫 MySQL [(none)]> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 10 | heifei | | 11 | huoqiu | | 12 | chengxihu | +----+-----------+ 6 rows in set (0.001 sec) 下載開始建立資料與表，測試讀寫分離情況 MySQL [(none)]> delete from kevin.haha where id > 9; Query OK, 3 rows affected (0.218 sec) MySQL [(none)]> create database test_proxysql; Query OK, 1 row affected (0.027 sec) MySQL [(none)]> select * from kevin.haha; +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | +----+----------+ 3 rows in set (0.001 sec) MySQL [(none)]> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu"); Query OK, 3 rows affected (0.045 sec) Records: 3 Duplicates: 0 Warnings: 0 MySQL [(none)]> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 10 | heifei | | 11 | huoqiu | | 12 | chengxihu | +----+-----------+ 6 rows in set (0.001 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | | test_proxysql | +--------------------+ 6 rows in set (0.001 sec) [root@mysql-proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 -e "select @@server_id" +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ [root@mysql-proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 -e "select * from kevin.haha" +----+-----------+ | id | name | +----+-----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 10 | heifei | | 11 | huoqiu | | 12 | chengxihu | +----+-----------+ 在proxysql管理端檢視讀寫分離 （注意管理埠是6032） [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server 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. MySQL [(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 | 1 | 1548230792 | 1548230792 | 819 | 819 | 819 | | 1 | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql | 1 | 1548230738 | 1548230738 | 26859 | 26859 | 26859 | | 1 | information_schema | proxysql | 0x9316817E8C74BCB2 | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | 1548230755 | 1548230755 | 44572 | 44572 | 44572 | | 1 | information_schema | proxysql | 0xF6FA5DFBB674D5FF | delete from kevin.haha where id > ? | 1 | 1548230729 | 1548230729 | 218165 | 218165 | 218165 | | 2 | information_schema | proxysql | 0xD8AAAE77FA99AC44 | select * from kevin.haha | 4 | 1548230646 | 1548231045 | 3590 | 781 | 1149 | | 2 | information_schema | proxysql | 0xDA65260DF35B8D13 | select @@server_id | 2 | 1548231028 | 1548231030 | 1335 | 578 | 757 | | 2 | information_schema | proxysql | 0xF8F2DF2E08F87E71 | select * from mysql.kevin | 1 | 1548230640 | 1548230640 | 2004 | 2004 | 2004 | | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 2 | 1548230538 | 1548230771 | 2957 | 905 | 2052 | | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 4 | 1548229930 | 1548231045 | 0 | 0 | 0 | +-----------+--------------------+----------+--------------------+------------------------------------------------+------------+------------+------------+----------+----------+----------+ 9 rows in set (0.002 sec) 從上述結果就可以看出proxysql實現的讀寫分離配置是成功的，讀請求是轉發到2組，寫請求轉發到1組!!
整個讀寫分離的架構配置到此就完成了，但是此架構存在需要優化的地方，那就是此架構存在單點問題。實際生產環境中可採用MHA+ProxySQL+Mysql這類架構解來避免單點故障問題。