前面的文章介紹了ProxySQL用法，這裡說下ProxySQL中介軟體針對Mysql組複製模式實現讀寫分離以及主節點故障時能夠自動切換到新的主節點，而應用對此過程無感知的功能。Mysql組複製(MGR)組複製能夠完成主節點故障後推選出來新的主節點，不過在應用層不可能通過修改新的主節點的IP來連線新的主節點，但是通過MGR+ProxySQL可以實際主節點故障時應用無感應自動切換到新的主節點。
根據上圖，描述下實現思路：三個節點使multi-primary的方式連線，應用通過連線ProxySQL中介軟體，根據sql的屬性（是否為select語句）來決定連線哪一個節點，一個可寫節點，兩個只讀節點（其實三個都是可寫節點，只不過通過proxysql進行了讀寫分離）。如果預設的可寫節點掛掉的話，proxysql通過定期執行的排程器會將另一個只讀節點的其中一臺設為可寫節點，實際主節點故障應用無感應的要求。上述的整個過程中，應用無需任何變動。應用從意識發生了故障，到連線重新指向新的主，正常提供服務，秒級別的間隔。
一、環境準備
172.16.60.211 MGR-node1 (master1) Centos7.5 172.16.60.212 MGR-node2 (master2) Centos7.5 172.16.60.213 MGR-node3 (master3) Centos7.5 172.16.60.214 ProxySQL-node Centos7.5 [root@MGR-node1 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) 為了方便實驗，關閉所有節點的防火牆 [root@MGR-node1 ~]# systemctl stop firewalld [root@MGR-node1 ~]# firewall-cmd --state not running [root@MGR-node1 ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled [root@MGR-node1 ~]# setenforce 0 setenforce: SELinux is disabled [root@MGR-node1 ~]# getenforce Disabled 特別要注意一個關鍵點: 必須設定好各個mysql節點的主機名，並且保證能通過主機名找到各成員！ 則必須要在每個節點的/etc/hosts裡面做主機名繫結，否則後續將節點加入group組會失敗！報錯RECOVERING！！ [root@MGR-node1 ~]# cat /etc/hosts ........ 172.16.60.211 MGR-node1 172.16.60.212 MGR-node2 172.16.60.213 MGR-node3
二、在三個節點上安裝Mysql5.7
在三個mysql節點機上使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html
安裝MySQL yum資源庫
[root@MGR-node1 ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安裝MySQL 5.7
[root@MGR-node1 ~]# yum install -y mysql-community-server
啟動MySQL伺服器和MySQL的自動啟動
[root@MGR-node1 ~]# systemctl start mysqld.service
[root@MGR-node1 ~]# systemctl enable mysqld.service
設定登入密碼
由於MySQL從5.7開始不允許首次安裝後使用空密碼進行登入！為了加強安全性，系統會隨機生成一個密碼以供管理員首次登入使用，
這個密碼記錄在/var/log/mysqld.log檔案中，使用下面的命令可以檢視此密碼：
[root@MGR-node1 ~]# 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@MGR-node1 ~]# 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@MGR-node1 ~]# 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;
三、MGR組複製環境部署 (多寫模式)
可以參考：https://www.cnblogs.com/kevingrace/p/10260685.html
由於之前做了其他測試，這裡需要將三個節點的mysql環境抹乾淨：
# systemctl stop mysqld
# rm -rf /var/lib/mysql
# systemctl start mysqld
然後重啟密碼
# cat /var/log/mysqld.log|grep 'A temporary password'
# mysql -p123456
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> set password=password("123456");
mysql> flush privileges;
=======================================================
1） MGR-node1節點操作
[root@MGR-node1 ~]# mysql -p123456
.........
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| ae09faae-34bb-11e9-9f91-005056ac6820 |
+--------------------------------------+
1 row in set (0.00 sec)
[root@MGR-node1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@MGR-node1 ~]# >/etc/my.cnf
[root@MGR-node1 ~]# 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
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.60.211:24901"
loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.16.60.0/24,127.0.0.1/8"
重啟mysql服務
[root@MGR-node1 ~]# systemctl restart mysqld
登入mysql進行相關設定操作
[root@MGR-node1 ~]# mysql -p123456
............
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.19 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.33 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
...............
...............
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.34 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
比如要保證上面的group_replication_applier的狀態為"ONLINE"才對！
建立一個測試庫
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 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.24 sec)
mysql> insert into kevin.haha values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui");
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
| 3 | yangyang |
| 4 | shikui |
+----+-----------+
4 rows in set (0.00 sec)
=====================================================================
2） MGR-node2節點操作
[root@MGR-node2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@MGR-node2 ~]# >/etc/my.cnf
[root@MGR-node2 ~]# 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
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.60.212:24901"
loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.16.60.0/24,127.0.0.1/8"
重啟mysql服務
[root@MGR-node2 ~]# systemctl restart mysqld
登入mysql進行相關設定操作
[root@MGR-node2 ~]# mysql -p123456
.........
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.17 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.21 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
.............
.............
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (6.25 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4281f7b7-34bb-11e9-8949-00505688047c | MGR-node2 | 3306 | ONLINE |
| group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
檢視下，發現已經將MGR-node1節點新增的資料同步過來了
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 | wangshibo |
| 2 | guohuihui |
| 3 | yangyang |
| 4 | shikui |
+----+-----------+
4 rows in set (0.00 sec)
=====================================================================
3） MGR-node3節點操作
[root@MGR-node3 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@MGR-node3 ~]# >/etc/my.cnf
[root@MGR-node3 ~]# 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 = 3
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.60.213:24901"
loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.16.60.0/24,127.0.0.1/8"
重啟mysql服務
[root@MGR-node3 ~]# systemctl restart mysqld
登入mysql進行相關設定操作
[root@MGR-node3 ~]# mysql -p123456
..........
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.10 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
.............
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.54 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4281f7b7-34bb-11e9-8949-00505688047c | MGR-node2 | 3306 | ONLINE |
| group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE |
| group_replication_applier | 456216bd-34bb-11e9-bbd1-005056880888 | MGR-node3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
檢視下，發現已經將在其他節點上新增的資料同步過來了
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 | wangshibo |
| 2 | guohuihui |
| 3 | yangyang |
| 4 | shikui |
+----+-----------+
4 rows in set (0.00 sec)
=====================================================================
4） 組複製資料同步測試
在任意一個節點上執行
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2658b203-1565-11e9-9f8b-005056880888 | MGR-node3 | 3306 | ONLINE |
| group_replication_applier | 2c1efc46-1565-11e9-ab8e-00505688047c | MGR-node2 | 3306 | ONLINE |
| group_replication_applier | 317e2aad-1565-11e9-9c2e-005056ac6820 | MGR-node1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
如上，說明已經在MGR-node1、MGR-node2、MGR-node3 三個節點上成功部署了基於GTID的組複製同步環境。
現在在三個節點中的任意一個上面更新資料，那麼其他兩個節點的資料庫都會將新資料同步過去的！
1）在MGR-node1節點資料庫更新資料
mysql> delete from kevin.haha where id>2;
Query OK, 2 rows affected (0.14 sec)
接著在MGR-node2、MGR-node3節點資料庫檢視，發現更新後資料已經同步過來了！
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
+----+-----------+
2 rows in set (0.00 sec)
2）在MGR-node2節點資料庫更新資料
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
接著在MGR-node1、MGR-node3節點資料庫檢視，發現更新後資料已經同步過來了！
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
| 11 | beijing |
| 12 | shanghai |
| 13 | anhui |
+----+-----------+
5 rows in set (0.00 sec)
3）在MGR-node3節點資料庫更新資料
mysql> update kevin.haha set id=100 where name="anhui";
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from kevin.haha where id=12;
Query OK, 1 row affected (0.22 sec)
接著在MGR-node1、MGR-node2節點資料庫檢視，發現更新後資料已經同步過來了！
mysql> select * from kevin.haha;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | wangshibo |
| 2 | guohuihui |
| 11 | beijing |
| 100 | anhui |
+-----+-----------+
4 rows in set (0.00 sec)
四、ProxySQL讀寫分離和主節點故障無感知切換
1) 安裝mysql客戶端，用於在本機連線到ProxySQL的管理介面
[root@ProxySQL-node ~]# 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@ProxySQL-node ~]# 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@ProxySQL-node ~]# rpm -qa|grep mariadb mariadb-libs-5.5.60-1.el7_5.x86_64 [root@ProxySQL-node ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps [root@ProxySQL-node ~]# yum install -y MariaDB-client
2) 安裝proxysql
proxysql的rpm包下載地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg
提取密碼: 5t1c
[root@ProxySQL-node ~]# yum install -y perl-DBI perl-DBD-MySQL
[root@ProxySQL-node ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force
啟動proxysql
[root@ProxySQL-node ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@ProxySQL-node ~]# ss -lntup|grep proxy
tcp LISTEN 0 128 *:6080 *:* users:(("proxysql",pid=29931,fd=11))
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=29931,fd=28))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=29931,fd=27))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=29931,fd=26))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=29931,fd=25))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=29931,fd=24))
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
............
............
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.000 sec)
接著初始化Proxysql，將之前的proxysql資料都刪除
MySQL [(none)]> delete from scheduler ;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_servers;
Query OK, 3 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_users;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> delete from mysql_query_rules;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_group_replication_hostgroups ;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
Query OK, 94 rows affected (0.175 sec)
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.140 sec)
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.050 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.096 sec)
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.156 sec)
MySQL [(none)]>
3）在資料庫端建立proxysql登入需要的帳號 （在三個MGR任意一個節點上操作，會自動同步到其他節點）
[root@MGR-node1 ~]# mysql -p123456 ......... mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql'; Query OK, 0 rows affected (0.07 sec) mysql> GRANT ALL ON * . * TO 'proxysql'@'%'; Query OK, 0 rows affected (0.06 sec) mysql> create user 'sbuser'@'%' IDENTIFIED BY 'sbpass'; Query OK, 0 rows affected (0.05 sec) mysql> GRANT ALL ON * . * TO 'sbuser'@'%'; Query OK, 0 rows affected (0.08 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.07 sec)
4) 建立檢查MGR節點狀態的函式和檢視 （在三個MGR任意一個節點上操作，會自動同步到其他節點）
在MGR-node1節點上，建立系統檢視sys.gr_member_routing_candidate_status，該檢視將為ProxySQL提供組複製相關的監控狀態指標。
下載addition_to_sys.sql指令碼，在MGR-node1節點執行如下語句匯入MySQL即可 (在mgr-node1節點的mysql執行後，會同步到其他兩個節點上)。
下載地址: https://pan.baidu.com/s/1bNYHtExy2fmqwvEyQS3sWg
提取密碼：wst7
[root@MGR-node1 ~]# vim /root/addition_to_sys.sql
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
SET result = result +
SUBSTR(gtid_set, next_dash_pos + 1,
LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
ELSE
SET result = result + 1;
END IF;
SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
DELIMITER ;
匯入addition_to_sys.sql檔案資料
[root@MGR-node1 ~]# mysql -p123456 < /root/addition_to_sys.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
在三個mysql節點上可以檢視該檢視：
[root@MGR-node1 ~]# mysql -p123456
............
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.01 sec)
5) 在proxysql中增加帳號
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
...........
MySQL [(none)]> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.001 sec)
MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.002 sec)
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.006 sec)
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.387 sec)
測試一下能否正常登入資料庫
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1 |
+------------+
===================================================================
如果上面測試登入時報錯:
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql'@'127.0.0.1' (using password: YES)
但是檢查發現，明明使用者名稱和密碼已經修改成proxysql:proxysql了
MySQL [(none)]> select * from global_variables;
..........
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.5.30 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_username | proxysql |
| mysql-monitor_password | proxysql |
解決辦法: 依次執行下面的命令
MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
MySQL [(none)]> SAVE MYSQL USERS TO DISK;
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
MySQL [(none)]> SAVE SCHEDULER TO DISK;
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;
=========================================================
如果測試登入再出現：
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 1 after 10000ms
這是因為後端三個mysql的MGR節點還沒有加入到proxysql中的原因，再進行完下面的步驟"配置proxysql"後就可以了
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1 |
+------------+
6) 配置proxysql
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............. MySQL [(none)]> delete from mysql_servers; Query OK, 3 rows affected (0.000 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.16.60.211',3306); Query OK, 1 row affected (0.001 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.16.60.212',3306); Query OK, 1 row affected (0.000 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'172.16.60.213',3306); Query OK, 1 row affected (0.000 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.16.60.211',3306); Query OK, 1 row affected (0.000 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.16.60.212',3306); Query OK, 1 row affected (0.000 sec) MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'172.16.60.213',3306); 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.001 sec) hostgroup_id = 1代表write group，針對我們提出的限制，這個地方只配置了一個節點； hostgroup_id = 2代表read group，包含了MGR的所有節點,目前只是Onlinle的，等配置過scheduler後，status就會有變化 。 對於上面的hostgroup配置，預設所有的寫操作會傳送到hostgroup_id為1的online節點，也就是傳送到寫節點上。 所有的讀操作，會傳送為hostgroup_id為2的online節點。 需要確認一下沒有使用proxysql的讀寫分離規則（因為之前測試中配置了這個地方，所以需要刪除，以免影響後面的測試）。 MySQL [(none)]> delete from mysql_query_rules; Query OK, 2 rows affected (0.000 sec) MySQL [(none)]> commit; Query OK, 0 rows affected (0.000 sec) 最後需要將global_variables，mysql_servers、mysql_users表的資訊載入到RUNTIME，更進一步載入到DISK: MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK; Query OK, 94 rows affected (0.080 sec) MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.463 sec) MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.134 sec) 再次驗證proxysql登入 [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname" +------------+ | @@hostname | +------------+ | MGR-node1 | +------------+
7）配置scheduler
首先，請在Github地址https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker下載相應的指令碼
這個地址有三個指令碼可供下載：
proxysql_groupreplication_checker.sh：用於multi-primary模式，可以實現讀寫分離，以及故障切換，同一時間點多個節點可以多寫；
gr_mw_mode_cheker.sh：用於multi-primary模式，可以實現讀寫分離，以及故障切換，不過在同一時間點只能有一個節點能寫；
gr_sw_mode_checker.sh：用於single-primary模式，可以實現讀寫分離，以及故障切換；
由於這裡實驗的環境是multi-primary模式，所以選擇proxysql_groupreplication_checker.sh指令碼。
三個指令碼我已打包放在了百度雲盤上，下載地址：https://pan.baidu.com/s/1lUzr58BSA_U7wmYwsRcvzQ
提取密碼：9rm7
將下載的指令碼proxysql_groupreplication_checker.sh放到目錄/var/lib/proxysql/下，並增加可以執行的許可權:
[root@ProxySQL-node ~]# chmod a+x /var/lib/proxysql/proxysql_groupreplication_checker.sh
[root@ProxySQL-node ~]# ll /var/lib/proxysql/proxysql_groupreplication_checker.sh
-rwxr-xr-x 1 root root 6081 Feb 20 14:25 /var/lib/proxysql/proxysql_groupreplication_checker.sh
最後，在proxysql的scheduler表裡面載入如下記錄，然後載入到RUNTIME使其生效，同時還可以持久化到磁碟:
執行語句"
INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5)
VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');"
如下：
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
..............
MySQL [(none)]> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> select * from scheduler;
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| 1 | 1 | 10000 | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1 | 2 | 1 | 0 | /var/lib/proxysql/proxysql_groupreplication_checker.log | |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
1 row in set (0.000 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.118 sec)
==============================================================================
scheduler各column的說明：
active : 1: enable scheduler to schedule the script we provide
interval_ms : invoke one by one in cycle (eg: 5000(ms) = 5s represent every 5s invoke the script)
filename: represent the script file path
arg1~arg5: represent the input parameters the script received
指令碼proxysql_groupreplication_checker.sh對應的引數說明如下:
arg1 is the hostgroup_id for write
arg2 is the hostgroup_id for read
arg3 is the number of writers we want active at the same time
arg4 represents if we want that the member acting for writes is also candidate for reads
arg5 is the log file
schedule資訊載入後，就會分析當前的環境，mysql_servers中顯示出當前只有172.16.60.211是可以寫的，
172.16.60.212以及172.16.60.213是用來讀的。
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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.000 sec)
因為schedule的arg4，我這裡設為了0，就表示可寫的節點不能用於讀。那我將arg4設定為1試一下：
MySQL [(none)]> update scheduler set arg4=1;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> select * from scheduler;
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
| 1 | 1 | 10000 | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1 | 2 | 1 | 1 | /var/lib/proxysql/proxysql_groupreplication_checker.log | |
+----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
1 row in set (0.000 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.286 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.000 sec)
arg4設定為1之後，172.16.60.211節點用來寫的同時，也可以被用來讀。
便於下面的測試還是將arg4設為0：
MySQL [(none)]> update scheduler set arg4=0;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.197 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.211 | 3306 | OFFLINE_SORT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.000 sec)
各個節點的gr_member_routing_candidate_status檢視也顯示了當前節點是否是正常狀態的，
proxysql就是讀取的這個檢視的資訊來決定此節點是否可用。
[root@MGR-node1 ~]# mysql -p123456
...........
mysql> select * from sys.gr_member_routing_candidate_status\G;
*************************** 1. row ***************************
viable_candidate: YES
read_only: NO
transactions_behind: 0
transactions_to_cert: 0
1 row in set (0.00 sec)
ERROR:
No query specified
8) 設定讀寫分離
MySQL [(none)]> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",2,1); Query OK, 1 row affected (0.001 sec) MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.264 sec) 解釋說明: match_pattern的規則是基於正規表示式的， active表示是否啟用這個sql路由項， match_pattern就是我們正則匹配項， destination_hostgroup表示我們要將該類sql轉發到哪些mysql上面去，這裡我們將select轉發到group 2，。 apply為1表示該正則匹配後，將不再接受其他匹配，直接轉發。 對於for update需要在gruop1上執行，可以加上規則： MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1); Query OK, 1 row affected (0.001 sec) 在proxysql本機或其他客戶機上檢查下，select 語句，一直連線的是172.16.60.212和172.16.60.213 [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node3 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node2 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node2 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node3 | +------------+
9) 驗證資料的讀寫分離效果
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" +------------+ | @@hostname | +------------+ | MGR-node2 | +------------+ [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select * from kevin.haha" +-----+-----------+ | id | name | +-----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 11 | beijing | | 100 | anhui | +-----+-----------+ [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "delete from kevin.haha where id=1;" [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "delete from kevin.haha where id=2;" [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select * from kevin.haha" +-----+---------+ | id | name | +-----+---------+ | 11 | beijing | | 100 | anhui | +-----+---------+ [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select * from kevin.haha" +-----+-----------+ | id | name | +-----+-----------+ | 11 | beijing | | 21 | zhongguo | | 22 | xianggang | | 23 | taiwan | | 100 | anhui | 最後在proxysql管理端檢視讀寫分離情況 [root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 .......... MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; +-----------+----------+------------------------------------------------------+------------+ | hostgroup | username | digest_text | count_star | +-----------+----------+------------------------------------------------------+------------+ | 1 | proxysql | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | | 1 | proxysql | insert into kevin.haha values(?,yangyang) | 1 | | 1 | proxysql | delete from kevin.haha where id=? | 2 | | 1 | proxysql | select @@version_comment limit ? | 120 | | 1 | proxysql | KILL ? | 8 | | 1 | proxysql | select @@hostname | 11 | | 1 | proxysql | KILL QUERY ? | 10 | | 2 | proxysql | select @@hostname, sleep(?) | 53 | | 1 | proxysql | insert into kevin.haha values(?,yangyang),(?,shikui) | 2 | | 1 | proxysql | show databases | 1 | | 2 | proxysql | select @@hostname | 31 | | 2 | proxysql | select * from kevin.haha | 4 | | 1 | proxysql | insert into kevin.haha values(?,wawa) | 3 | +-----------+----------+------------------------------------------------------+------------+ 13 rows in set (0.002 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.000 sec) 通過上面可以看到： 寫操作都分配到了group1組內，即寫操作分配到172.16.60.211節點上。 讀操作都分配到了group2組內，即讀操作分配到172.16.60.212、172.16.60.213節點上。
10）設定故障應用無感應
在上面的讀寫分離規則中，我設定了172.16.60.211為可寫節點，172.16.60.212,172.16.60.213為只讀節點 如果此時172.16.60.211變成只讀模式的話，應用能不能直接連到其它的節點進行寫操作？ 現手動將172.16.60.211變成只讀模式： [root@MGR-node1 ~]# mysql -p123456 ........ mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) 接著觀察一下mysql_servers的狀態，自動將group1的172.16.60.212改成了online，group2的172.16.60.211, 172.16.60.213變成online了，就表示將172.16.60.212變為可寫節點，其它兩個節點變為只讀節點了。 [root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 ........ 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 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.001 sec) 通過模擬的連線也可以看到select語句都連線到172.16.60.211和172.16.60.213進行了。 (模擬時可以稍微間隔一段時間，快速測試可能會連線同一個讀節點) [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node3 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node1 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node3 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node1 | +------------+ [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h172.16.60.214 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | MGR-node1 | +------------+ 然後再將將172.16.60.211變為可寫模式後，mysql_servers也恢復過來了。 [root@MGR-node1 ~]# mysql -p123456 ........ mysql> set global read_only=0; Query OK, 0 rows affected (0.00 sec) 接著觀察一下mysql_servers的狀態 [root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 ......... 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.000 sec) 經過測試將172.16.60.211節點停止組複製（stop group_replication）或者該節點當機(mysql服務掛掉)後，mysql_servers表的資訊也會正常的切換新的節點。 待172.16.60.211恢復再加入到組複製後，mysql_servers也會正常的將172.16.60.211改成online狀態。 ====================================================================================================== 可能出現的問題: mysql> select * from mysql_servers ; +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 172.16.60.211 | 3306 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) 也就是說，可能遇到上面所有節點都offline了的情況，檢視錯誤日誌如下： [root@ProxySQL-node ~]# tail -f /var/lib/proxysql/proxysql.log ........ [2019-02-18 16:23:52] read node [hostgroup_id: 2, hostname: 172.16.60.213, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT' ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'172.16.60.214' for table 'gr_member_routing_candidate_status' [2019-02-18 16:23:55] current write node [hostgroup_id: 2, hostname: 172.17.61.131, port: 3306, isOK: 0] is not OK, we need to do switch over ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'172.16.60.214' for table 'gr_member_routing_candidate_status' [2019-02-18 16:23:55] read node [hostgroup_id: 2, hostname: 172.17.61.132, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT' ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'172.16.60.214' for table 'gr_member_routing_candidate_status 從上面的錯誤日誌上看出是許可權的問題，proxysql使用者沒有足夠的許可權讀取資料。 解決辦法： [root@MGR-node1 ~]# mysql -p123456 ......... mysql> GRANT ALL ON * . * TO 'proxysql'@'%'; mysql> flush privileges; 再次看看，就有許可權了 [root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 ......... 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 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.212 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 172.16.60.213 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.211 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.000 sec)
到此，ProxySQL就簡單實現了MGR的讀寫分離和主節點故障無感知環境。