Centos7.5部署MySQL5.7基於GTID主從複製+並行複製+半同步複製+讀寫分離(ProxySQL) 環境- 運維筆記 (完整版)

散盡浮華發表於2016-06-08

 

之前已經詳細介紹了Mysql基於GTID主從複製的概念,原理和配置,下面整體記錄下MySQL5.7基於GTID主從複製+並行複製+增強半同步複製+讀寫分離環境的實現過程,以便加深對mysql新特性GTID複製的理解和實際業務場景中部署應用。

一、實驗環境

[root@mysql-master ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

為了方便實驗,關閉所有節點的防火牆
[root@mysql-master ~]# systemctl stop firewalld
[root@mysql-master ~]# firewall-cmd --state
not running

[root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
SELINUX=disabled
[root@mysql-master ~]# setenforce 0               
setenforce: SELinux is disabled
[root@mysql-master ~]# getenforce                 
Disabled

二、安裝Mysql5.7

在三個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;

三、配置基於GTID的主從複製 

傳統的基於binlog position複製的方式有個嚴重的缺點:如果slave連線master時指定的binlog檔案錯誤或者position錯誤,會造成遺漏或者重複,
很多時候前後資料是有依賴性的,這樣就會出錯而導致資料不一致。

從MYSQL5.6開始,mysql開始支援GTID複製。GTID的全稱是global transaction id,表示的是全域性事務ID。GTID的分配方式為uuid:trans_id,其中:uuid是每個mysql伺服器都唯一的,記錄在$datadir/auto.cnf中。如果複製結構中,任意兩臺伺服器uuid重複的話(比如直接冷備份時,auto.conf中的內容是一致的),在啟動複製功能的時候會報錯。這時可以刪除auto.conf檔案再重啟mysqld。

基於GTID主從複製的優點大致有:
-  保證同一個事務在某slave上絕對只執行一次,沒有執行過的gtid事務總是會被執行。
-  不用像傳統複製那樣保證binlog的座標準確,因為根本不需要binlog以及座標。
-  故障轉移到新的master的時候很方便,簡化了很多工。
-  很容易判斷master和slave的資料是否一致。只要master上提交的事務在slave上也提交了,那麼一定是一致的。
-  當然,MySQL提供了選項可以控制跳過某些gtid事務,防止slave第一次啟動複製時執行master上的所有事務而導致耗時過久。
-  雖然對於row-based和statement-based的格式都能進行gtid複製,但建議採用row-based格式。 

                                                              基於GTID主從複製環境部署記錄                                                             

1)mysql-master主資料庫上的操作
 
在my.cnf檔案中配置GTID主從複製
[root@mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# cat /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 = mysql-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
 
登入mysql,並檢視master狀態, 發現多了一項"Executed_Gtid_Set "
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 317e2aad-1565-11e9-9c2e-005056ac6820 |
+---------------+--------------------------------------+
1 row 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                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)
 
檢視確認binlog日誌功能開啟
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
授權slave複製使用者,並重新整理許可權
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
 
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212                                                |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
再次檢視master狀態
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      622 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
這裡需要注意一下:
啟動配置之前,同樣需要對從伺服器進行初始化。對從伺服器初始化的方法基本和基於日誌點是相同的,只不過在啟動了GTID模式後,
在備份中所記錄的就不是備份時的二進位制日誌檔名和偏移量了,而是記錄的是備份時最後的GTID值。
   
需要先在主資料庫機器上把目標庫備份一下,假設這裡目標庫是kevin(為了測試效果,下面手動建立)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 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.06 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)
 
把kevin庫備份出來
[root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases kevin -uroot -p123456 > /root/kevin.sql
 
這裡稍微注意下:
mysql5.6使用mysqldump備份時,指定備份的具體庫,使用--database
mysql5.7使用mysqldump備份時,指定備份的具體庫,使用--databases
 
[root@mysql-master ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-master ~]# cat /root/kevin.sql
-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost    Database: kevin
-- ------------------------------------------------------
-- Server version       5.7.24-log
.............
.............
--
-- GTID state at the beginning of the backup
--
 
SET @@GLOBAL.GTID_PURGED='317e2aad-1565-11e9-9c2e-005056ac6820:1-5';
 
然後把備份的/root/kevin.sql檔案拷貝到mysql-slave1從資料庫伺服器上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/kevin.sql root@172.16.60.212:/root/
 
==============================================================
2)mysql-slave1從資料庫上的操作
 
在my.cnf檔案中配置GTID主從複製
與主伺服器配置大概一致,除了server_id不一致外,從伺服器還可以在配置檔案裡面新增:"read_only=on" ,
使從伺服器只能進行讀取操作,此引數對超級使用者無效,並且不會影響從伺服器的複製;
[root@mysql-slave1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# 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 = mysql-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-slave1 ~]# systemctl restart mysqld
 
接著將主資料庫目標庫的備份資料kevin.sql匯入到從資料庫裡
[root@mysql-slave1 ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-slave1 ~]# mysql -p123456
.........
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> source /root/kevin.sql;
 
mysql> select * from kevin.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | congcong |
|  2 | huihui   |
|  3 | grace    |
+----+----------+
3 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.211',master_user='slave',master_password='slave@123',master_auto_position=1; 
Query OK, 0 rows affected, 2 warnings (0.26 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.60.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1357
               Relay_Log_File: mysql-slave1-relay-bin.000002
                Relay_Log_Pos: 417
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
................
................
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5
                Auto_Position: 1
 
由上面可知,mysql-slave1節點已經和mysql-master節點配置了主從同步關係
 
3) mysql-master主資料庫上進行狀態檢視和測試測試插入
 
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1357 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
 
mysql> insert into kevin.haha values(4,"beijing"),(5,"hefei"),(10,"xihu");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> delete from kevin.haha where id<4;
Query OK, 3 rows affected (0.10 sec)
 
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
 
4)mysql-slave1從資料庫上檢視
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
 
發現mysql-slave1從資料庫已經將新插入的資料同步過來了,由此,基於GTID的主從同步複製關係已經正常部署完成了!

四、並行複製 (解決主從複製延遲問題)

一般Mysql主從複製有三個執行緒參與,都是單執行緒:Binlog Dump(主) -> IO Thread (從) -> SQL Thread(從)。複製出現延遲一般出在兩個地方:
SQL執行緒忙不過來 (可能需要應用資料量較大,可能和從庫本身的一些操作有鎖和資源的衝突;主庫可以併發寫,SQL執行緒不可以;主要原因)
網路抖動導致IO執行緒複製延遲(次要原因)。

MySQL主從複製延遲的解決辦法:MySQL從5.6開始有了SQL Thread多個的概念,可以併發還原資料,即並行複製技術。並行複製的機制,是MySQL的一個非常重要的特性,可以很好的解決MySQL主從延遲問題!

在MySQL 5.6中,設定引數slave_parallel_workers = 4(>1),即可有4個SQL Thread(coordinator執行緒)來進行並行複製,其狀態為:Waiting for an evant from Coordinator。但是其並行只是基於Schema的,也就是基於庫的。如果資料庫例項中存在多個Schema,這樣設定對於Slave複製的速度可以有比較大的提升。通常情況下單庫多表是更常見的一種情形,那基於庫的併發就沒有卵用。其核心思想是:不同schema下的表併發提交時的資料不會相互影響,即slave節點可以用對relay log中不同的schema各分配一個類似SQL功能的執行緒,來重放relay log中主庫已經提交的事務,保持資料與主庫一致。

MySQL 5.6版本支援所謂的並行複製,但是其並行只是基於schema的,也就是基於庫的。如果使用者的MySQL資料庫例項中存在多個schema,對於從機複製的速度的確可以有比較大的幫助。但是基於schema的並行複製存在兩個問題:
1) crash safe功能不好做,因為可能之後執行的事務由於並行複製的關係先完成執行,那麼當發生crash的時候,這部分的處理邏輯是比較複雜的。
2) 最為關鍵的問題是這樣設計的並行複製效果並不高,如果使用者例項僅有一個庫,那麼就無法實現並行回放,甚至效能會比原來的單執行緒更差。而 單庫多表是比多庫多表更為常見的一種情形 。

注意:mysql 5.6的MTS是基於庫級別的並行,當有多個資料庫時,可以將slave_parallel_workers設定為資料庫的數量,為了避免新建庫後來回修改,也可以將該引數設定的大一些。設定為庫級別的事務時,不允許這樣做,會報錯。

在MySQL 5.7中,引入了基於組提交的並行複製(官方稱為Enhanced Multi-threaded Slaves,即MTS),設定引數slave_parallel_workers>0並且global.slave_parallel_type=‘LOGICAL_CLOCK’,即可支援一個schema下,slave_parallel_workers個的worker執行緒併發執行relay log中主庫提交的事務。其核心思想:一個組提交的事務都是可以並行回放(配合binary log group commit);slave機器的relay log中 last_committed相同的事務(sequence_num不同)可以併發執行。其中,變數slave-parallel-type可以有兩個值:1)DATABASE 預設值,基於庫的並行複製方式;2)LOGICAL_CLOCK,基於組提交的並行複製方式;

MySQL 5.7開啟Enhanced Multi-Threaded Slave很簡單,只需要在Slave從資料庫的my.cnf檔案中如下配置即可:

# slave
 slave-parallel-type=LOGICAL_CLOCK
 slave-parallel-workers=8        #一般建議設定4-8,太多的執行緒會增加執行緒之間的同步開銷
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 relay_log_recovery=ON

MySQL 5.7是基於組提交的並行複製,並且是支援"真正"的並行複製功能,這其中最為主要的原因:就是slave伺服器的回放與主機是一致的, 即master伺服器上是怎麼並行執行的slave上就怎樣進行並行回放。不再有庫的並行複製限制,對於二進位制日誌格式也無特殊的要求(基於庫的並行複製也沒有要求)。

MySQL5.7的並行複製,期望最大化還原主庫的並行度,實現方式是在binlog event中增加必要的資訊,以便slave節點根據這些資訊實現並行複製。MySQL5.7的並行複製建立在group commit的基礎上,所有在主庫上能夠完成prepared的語句表示沒有資料衝突,就可以在slave節點並行複製。

所以在並行複製環境中,除了在Slace從資料庫中配置之外,還需要在Master主資料庫上的my.cnf檔案中新增binlog_group_commit配置,否則從庫無法做到基於事物的並行複製:

binlog_group_commit_sync_delay = 100                
binlog_group_commit_sync_no_delay_count = 10

binlog_group_commit_sync_delay,這個引數控制著日誌在刷盤前日誌提交要等待的時間,預設是0也就是說提交後立即刷盤,但是並不代表是關閉了組提交,當設定為0以上的時候,就允許多個事物的日誌同時間一起提交刷盤,也就是我們說的組提交。組提交是並行複製的基礎,我們設定這個值的大於0就代表開啟了組提交的延遲功能,而組提交是預設開啟的。最大值只能設定為1000000微妙。
binlog_group_commit_sync_no_delay_count ,這個參數列示我們在binlog_group_commit_sync_delay等待時間內,如果事物數達到這個引數的設定值,就會觸動一次組提交,如果這個值設為0的話就不會有任何的影響。如果到達時間但是事物數並沒有達到的話,也是會進行一次組提交操作的。

MySQL 5.7並行複製的思想簡單易懂,一言以蔽之: 一個組提交的事務都是可以並行回放 ,因為這些事務都已進入到事務的prepare階段,則說明事務之間沒有任何衝突(否則就不可能提交)。為了相容MySQL 5.6基於庫的並行複製,5.7引入了新的變數slave-parallel-type,其可以配置的值有:
-  DATABASE:預設值,基於庫的並行複製方式
-  LOGICAL_CLOCK:基於組提交的並行複製方式

支援並行複製的GTID
如何知道事務是否在一組中,又是一個問題,因為原版的MySQL並沒有提供這樣的資訊。在MySQL 5.7版本中,其設計方式是將組提交的資訊存放在GTID中。那麼如果使用者沒有開啟GTID功能,即將引數gtid_mode設定為OFF呢?故MySQL 5.7又引入了稱之為Anonymous_Gtid的二進位制日誌event型別,如:

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000003 | 4   | Format_desc    | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |
| mysql-bin.000003 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 259 | Query          | 88 | 330 | BEGIN |
| mysql-bin.000003 | 330 | Table_map      | 88 | 373 | table_id: 108 (aaa.t) |
| mysql-bin.000003 | 373 | Write_rows     | 88 | 413 | table_id: 108 flags: STMT_END_F |
......

這意味著在 MySQL 5.7版本中即使不開啟GTID,每個事務開始前也是會存在一個Anonymous_Gtid ,而這GTID中就存在著組提交的資訊。

LOGICAL_CLOCK
然而,通過上述的SHOW BINLOG EVENTS,並沒有發現有關組提交的任何資訊。但是通過mysqlbinlog工具,使用者就能發現組提交的內部資訊:

如下檢視一個binlog日誌

root@localhost:~# mysqlbinlog mysql-bin.0000002 | grep last_committed
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=0        sequence_number=1       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=1        sequence_number=2       rbr_only=yes
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=2        sequence_number=3       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=3        sequence_number=4       rbr_only=yes
可以發現較之原來的二進位制日誌內容多了last_committed和sequence_number,last_committed表示事務提交的時候,上次事務提交的編號,如果事務具有相同的last_committed,表示這些事務都在一組內,可以進行並行的回放 (一般是當執行的sql語句併發數大的情況下會進行組提交)。上面這個binlog日誌裡沒有組提交資訊(last_committed數值都不相等),下一個事物的last_committed永遠都和上一個事物的sequence_number是相等的,這是因為事物是順序提交的!這麼理解起來並不奇怪。
下面看一下組提交模式的事物:
root@localhost:~# mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259  CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8
#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9
#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10
#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11
#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12
#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13
...

例如上述last_committed為0的事務有6個,這意味什麼呢?意味著這6個事物是作為一個組提交的,6個事物在perpare截斷獲取相同的last_committed而且相互不影響,最終是會作為一個組進行提交?,這就是所謂的組提交!上面表示組提交時提交了6個事務,而這6個事務在從機是可以進行並行回放的。

總之:MySQL 5.7推出的Enhanced Multi-Threaded Slave解決了困擾MySQL長達數十年的複製延遲問題,再次提醒一些無知的PostgreSQL使用者,不要再停留在之前對於MySQL的印象,物理複製也不一定肯定比邏輯複製有優勢,而MySQL 5.7的MTS已經完全可以解決延遲問題。總之, 5.7版本後,主從複製延遲問題將不存在!

                                                       基於GTID的並行複製環境部署記錄                                             

1)在mysql-slave1從資料庫的my.cnf 檔案中新增下面MTS並行複製的配置
[root@mysql-slave1 ~]# 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 = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
read_only = on
   
#MTS
slave-parallel-type = LOGICAL_CLOCK          #開啟邏輯時鐘的複製
slave-parallel-workers = 4                   #這裡設定執行緒數為4 (最大執行緒數不能超過16,即最大執行緒為16)
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
   
配置完成後,記得重啟mysql服務
[root@mysql-slave1 ~]# systemctl restart mysqld
   
2)在mysql-master主資料庫的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 = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
   
#不配置binlog_group_commit從庫無法做到基於事物的並行複製
binlog_group_commit_sync_delay = 100               
binlog_group_commit_sync_no_delay_count = 10
   
#為了資料安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1 
#這個引數控制binlog寫入 磁碟的方式。設定為1時,表示每次commit;都寫入磁碟。這個重新整理的是redo log 即ib_logfile0,而不是binlog
   
配置完成後,記得重啟mysql服務
[root@mysql-master ~]# systemctl restart mysqld
   
登入mysql,檢視binlog_group_commit 
mysql> show variables like 'binlog_group_commit_%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 100   |
| binlog_group_commit_sync_no_delay_count | 10    |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
   
設定binlog_group_commit的上面兩個引數,否則從庫無法做到基於事物的並行複製! 這兩個引數共同決定了是否觸發組提交操作!
第二個參數列示該事務組提交之前總共等待累積到多少個事務(如上要累計到10個事務);
第一個引數則表示該事務組總共等待多長時間後進行提交(如上要總共等待100毫秒的時間),任何一個條件滿足則進行後續操作。
因為有這個等待,可以讓更多事務的binlog通過一次寫binlog檔案磁碟來完成提交,從而獲得更高的吞吐量。
 
3)登入mysql-slave1從資料庫
 
上面在配置並行複製後,主從資料庫的mysqld服務都重啟了,此時需要重啟從資料庫上的slave,這樣才能恢復正常的主從同步狀態!
記住:只要主資料庫的mysqld服務重啟,那麼從資料庫上就要重啟slave,以恢復主從同步狀態!!!
 
mysql> show slave status \G;        
...........
...........
             Slave_IO_Running: No
            Slave_SQL_Running: No
   
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
   
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
   
mysql> show slave status \G;
..........
..........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..........
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-11
                Auto_Position: 1
   
這樣,mysql-slave1從資料庫就恢復了與mysql-master主資料庫的同步關係了
 
檢視優化項
mysql> use mysql;                                 #切入到mysql庫
Database changed
mysql> select * from slave_worker_info;                 #檢視slave_worker_info表
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name                  | Relay_log_pos | Master_log_name   | Master_log_pos | Checkpoint_relay_log_name       | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 | ./mysql-slave1-relay-bin.000005 |           656 | mysql-bin.000008 |            481 | ./mysql-slave1-relay-bin.000005 |                      369 | mysql-bin.000008          |                       194 |                0 |                    64 |                                                                 |              |
|  2 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)
   
以上可以檢視到,mysql庫下的slave_worker_info表下檢視到4個執行緒
  
也可以使用下面命令檢視執行緒數,這個跟在my.cnf檔案裡配置的是一樣的!
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.00 sec)
 
4)同步複製測試
在mysql-master主資料庫插入新資料
mysql> insert into kevin.haha values(21,"caiyi"),(22,"manman"),(23,"titi");
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0
   
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)
   
然後在mysql-slave1從資料庫檢視,發現新資料已經同步過來了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)

最後在mysql-master主資料庫上檢視組提交資訊
[root@mysql-master ~]# cd /var/lib/mysql
[root@mysql-master mysql]# ll mysql-bin*
-rw-r----- 1 mysql mysql  751 Jan 12 18:08 mysql-bin.000001
-rw-r----- 1 mysql mysql 1365 Jan 12 19:28 mysql-bin.000002
-rw-r----- 1 mysql mysql 1326 Jan 12 19:42 mysql-bin.000003
-rw-r----- 1 mysql mysql   57 Jan 12 19:28 mysql-bin.index

[root@mysql-master mysql]# mysqlbinlog mysql-bin.000003 |grep last_committed
#190112 19:37:06 server id 1  end_log_pos 259 CRC32 0x893c0ae8  GTID    last_committed=0       sequence_number=1       rbr_only=yes
#190112 19:37:12 server id 1  end_log_pos 542 CRC32 0xc36def61  GTID    last_committed=1       sequence_number=2       rbr_only=yes
#190112 19:42:42 server id 1  end_log_pos 825 CRC32 0x361701a2  GTID    last_committed=2       sequence_number=3       rbr_only=yes
#190112 19:42:48 server id 1  end_log_pos 1108 CRC32 0x8ba858c7 GTID    last_committed=3       sequence_number=4       rbr_only=yes

如上,通過最新的binlog日誌看到,last_committed數值都不一樣,即沒有事務是在同一個組內提交的,也就是說這個日誌裡沒有組提交資訊。
這是因為沒有滿足binlog_grup_commit那兩個引數的條件,所以沒有進行組提交!

五、半同步複製

半同步複製: 預設情況下MySQL的複製是非同步的,master將新生成的binlog傳送給各slave後,無需等待slave的ack回覆(slave將接收到的binlog寫進relay log後才會回覆ack),直接就認為這次DDL/DML成功了, 半同步複製(semi-synchronous replication)是指master將新生成的binlog傳送給各slave時, 只需等待一個(預設)slave返回的ack資訊就返回成功。

MySQL 5.7對半同步複製作了大改進,新增了一個master執行緒。在MySQL 5.7以前,master上的binlog dump執行緒負責兩件事:dump日誌給slave的io_thread;接收來自slave的ack訊息。它們是序列方式工作的。在MySQL 5.7中,新增了一個專門負責接受ack訊息的執行緒ack collector thread。這樣master上有兩個執行緒獨立工作,可以同時傳送binlog到slave和接收slave的ack。還新增了幾個變數,其中最重要的是 rpl_semi_sync_master_wait_point ,它使得MySQL半同步複製有兩種工作模型。

半同步複製的兩種型別
從MySQL 5.7.2開始,MySQL支援兩種型別的半同步複製。這兩種型別由變數 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前沒有該變數)控制,它有兩種值:AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之後,預設值為AFTER_SYNC,在此版本之前,等價的型別為AFTER_COMMIT。這個變數控制的是master何時提交、何時接收ack以及何時回覆成功資訊給客戶端的時間點。
AFTER_SYNC模式:master將新事務寫進binlog(buffer)後傳送給slave,再sync到自己的binlog file(disk), 之後才允許接收slave的ack回覆,接收到ack之後才會提交事務,並返回成功資訊給客戶端。
AFTER_COMMIT模式:master將新事務寫進binlog(buffer)後傳送給slave,再sync到自己的binlog file(disk),然後直接提交事務。之後才允許接收slave的ack回覆,然後再返回成功資訊給客戶端。

如下作圖,理解起來就容易了。(前提: 已經設定了sync_binlog=1,否則binlog刷盤時間由作業系統決定)

根據上面的圖解,接下來分析下這兩種模式的優缺點。

AFTER_SYNC
-  對於所有客戶端來說,它們看到的資料是一樣的,因為它們看到的資料都是在接收到slave的ack後提交後的資料。
-  這種模式下,如果master突然故障,不會丟失資料,因為所有成功的事務都已經寫進slave的relay log中了,slave的資料是最新的。

AFTER_COMMIT
-  不同客戶端看到的資料可能是不一樣的。對於發起事務請求的那個客戶端,它只有在master提交事務且收到slave的ack後才能看到提交的資料。但對於那些非本次事務的請求客戶端,它們在master提交後就能看到提交後的資料,這時候master可能還沒收到slave的ack。
-  如果master收到ack回覆前,slave和master都故障了,那麼將丟失這個事務中的資料。

在MySQL 5.7.2之前,等價的模式是 AFTER_COMMIT ,在此版本之後,預設的模式為 AFTER_SYNC ,該模式能最大程度地保證資料安全性,且效能上並不比 AFTER_COMMIT 差。

                                                               基於GTID的半同步複製環境部署記錄                                                                    

Mysql 半同步複製配置可以參考: https://www.cnblogs.com/kevingrace/p/10228694.html

開啟半同步複製的方法有兩種: mysql命令列啟動; my.cnf檔案裡新增啟動配置;
推薦在my.cnf檔案裡新增半同步啟動配置方式

1) 配置mysql-master主資料庫上 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 = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
     
#relay log
skip_slave_start = 1
    
#不配置binlog_group_commit從庫無法做到基於事物的並行複製
binlog_group_commit_sync_delay = 100              
binlog_group_commit_sync_no_delay_count = 10
    
#為了資料安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#這個引數控制binlog寫入 磁碟的方式。設定為1時,表示每次commit;都寫入磁碟。這個重新整理的是redo log 即ib_logfile0,而不是binlog

#開啟半同步複製 (超時時間為1s)
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000 

配置完成後,記得重啟mysql服務
[root@mysql-master ~]# systemctl restart mysqld

2) 配置mysql-slave從資料庫上 my.cnf檔案,新增啟動半同步複製的配置
[root@mysql-slave1 ~]# 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 = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
   
#relay log
skip_slave_start = 1
read_only = on
  
#MTS
slave-parallel-type = LOGICAL_CLOCK          #開啟邏輯時鐘的複製
slave-parallel-workers = 4                               #最大執行緒16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

# 開啟半同步複製
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

配置完成後,記得重啟mysql服務
[root@mysql-slave1 ~]# systemctl restart mysqld

3)在mysql-master主資料庫上載入 (前提是/usr/lib64/mysql/plugin/semisync_master.so 檔案存在。 一般mysql安裝後就預設產生)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.25 sec)

檢視外掛是否載入成功
mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

檢視半同步是否在執行
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

4)在mysql-slave1從資料庫上載入 (前提是/usr/lib64/mysql/plugin/semisync_slave.so 檔案存在。 一般mysql安裝後就預設產生)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

檢視外掛是否載入成功
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

這個時候,檢視mysql-slave1的半同步是否執行
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

發現還是OFF,明明已經在my.cnf檔案裡開啟了!
這是因為此時還沒有生效,必須從資料庫上的IO執行緒才能生產!!!
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)

然後再檢視mysql-slave1的半同步狀態,發現就已經開啟了!
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

檢視此時的主從同步狀態
mysql> show slave status \G; 
...........
...........
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

發現主從同步異常,這個時候再重啟下slave即可!
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status \G;
............
............
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5) 在mysql-master主資料庫上檢視
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

從上面資訊,發現Rpl_semi_sync_master_clients的數值為1,說明此時mysql-master主資料庫已經有一個半同步複製的從機,即mysql-slave1節點。
Rpl_semi_sync_master_yes_tx的數值為0, 說明此時還沒有半同步複製的sql語句被執行。

接著在mysql-master主資料庫上插入和更新資料,測試半同步複製
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.01 sec)

mysql> insert into kevin.haha values(30,"shikui"),(31,"shibo"),(32,"shijuan");
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update kevin.haha set id=8 where name="beijing";
Query OK, 1 row affected (0.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0

接著再去mysql-slave1從資料庫上檢視,發現新資料已經同步過來了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)

接著再去mysql-master主資料庫上檢視,如下:
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 1      |
| Rpl_semi_sync_master_net_avg_wait_time     | 0      |
| Rpl_semi_sync_master_net_wait_time         | 0      |
| Rpl_semi_sync_master_net_waits             | 2      |
| Rpl_semi_sync_master_no_times              | 0      |
| Rpl_semi_sync_master_no_tx                 | 0      |
| Rpl_semi_sync_master_status                | ON     |
| Rpl_semi_sync_master_timefunc_failures     | 0      |
| Rpl_semi_sync_master_tx_avg_wait_time      | 133362 |
| Rpl_semi_sync_master_tx_wait_time          | 266725 |
| Rpl_semi_sync_master_tx_waits              | 2      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 2      |
+--------------------------------------------+--------+
14 rows in set (0.01 sec)

從上面資訊可發現Rpl_semi_sync_master_yes_tx的數值為2,即發生了兩條半同步複製的sql語句,就是上面執行的那兩條(insert 和update) sql語句。

 以上都是在mysql-master主資料庫和mysql-slave1從資料庫之間實現的基於GTID的主從複製、並行複製、半同步複製,即"一主一從"架構。 現在再把mysql-slave2的從節點新增進去,調整為"一主兩從"的同步架構。新增操作記錄如下:

1)在mysql-master主資料庫上操作
先新增同步許可權
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.14 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)

備份主庫
[root@mysql-master ~]# mysqldump -u root -p'123456' --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > 213_slave.sql

將備份檔案拷貝到mysql-slave2從資料庫上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/213_slave.sql root@172.16.60.213:/root/

記錄當前的gtid
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-16 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

2)在mysql-slave2從資料庫上操作
首先在my.cnf檔案新增相關同步配置 (跟mysql-slave1從節點的my.cnf配置相比,除了將server_id修改為3之外,其他配置內容都一樣!)
[root@mysql-slave2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave2 ~]# >/etc/my.cnf
[root@mysql-slave2 ~]# 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
     
#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
read_only = on
   
#MTS
slave-parallel-type = LOGICAL_CLOCK          #開啟邏輯時鐘的複製
slave-parallel-workers = 4                               #最大執行緒16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
 
# 開啟半同步複製
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

配置完成後,不要忘記重啟mysql服務
[root@mysql-slave2 ~]# systemctl restart mysqld

恢復備份庫
[root@mysql-slave2 ~]# ll /root/213_slave.sql 
-rw-r--r-- 1 root root 805694 Jan 13 00:26 /root/213_slave.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> source /root/213_slave.sql;

mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)

檢查一下mysql-slave2從資料庫上當前的gtid 
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.01 sec)

由於是從master-master主庫備份恢復過來的,所以此時mysql-slave2從資料庫的gtid和主庫的gtid是一樣的!

接著進行主從同步
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;  
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> start slave;
Query OK, 0 rows affected (0.17 sec)

=================================================================
如果遇到報錯:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解決辦法:
stop slave;
reset slave;
start slave;
=================================================================

檢視,發現主從同步正常
mysql> show slave status \G;
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

並行複製檢視
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.01 sec)

mysql> select * from mysql.slave_worker_info;  
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Checkpoint_relay_log_name | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  2 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)

接著在mysql-slave2從資料庫上開啟半同步
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';      
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

3) 現在回到mysql-master主資料庫檢視
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         1 | 2658b203-1565-11e9-9f8b-005056880888 |
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

如上資訊可知mysql-master主資料庫現在有兩個從資料庫,分別為mysql-slave1 和 mysql-slave2

mysql> show status like '%Rpl_semi%';                                                                      
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 2      |
| Rpl_semi_sync_master_net_avg_wait_time     | 0      |
| Rpl_semi_sync_master_net_wait_time         | 0      |
| Rpl_semi_sync_master_net_waits             | 4      |
| Rpl_semi_sync_master_no_times              | 0      |
| Rpl_semi_sync_master_no_tx                 | 0      |
| Rpl_semi_sync_master_status                | ON     |
| Rpl_semi_sync_master_timefunc_failures     | 0      |
| Rpl_semi_sync_master_tx_avg_wait_time      | 98077  |
| Rpl_semi_sync_master_tx_wait_time          | 392310 |
| Rpl_semi_sync_master_tx_waits              | 4      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 4      |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)

如上資訊可知,mysql-master主資料庫現在有兩個半同步複製的從庫,即mysql-slave1 和mysql-slave2

4)現在測試下同步效果
在mysql-master主資料庫上更新資料
mysql> delete from kevin.haha where id>10;
Query OK, 6 rows affected (0.20 sec)

mysql> insert into kevin.haha values(11,"changping"),(12,"wangjing");
Query OK, 2 rows affected (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 0

在mysql-slave1從資料庫檢視,發現已經同步過來了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)

在mysql-slave2從資料庫檢視,發現已經同步過來了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)

到此,已經完成了Mysql5.7"一主兩從"架構上基於GTID主從複製+並行複製+增強半同步複製環境,下面再說下讀寫分離環境

六、讀寫分離 (MySQL ProxySQL)

ProxySQL是用C++語言開發的,也是percona推的一款中介軟體,雖然也是一個輕量級產品,但效能很好(據測試,能處理千億級的資料),功能也足夠,能滿足中介軟體所需的絕大多數功能,包括:
最基本的讀寫分離,且安裝起來也十分方便。
可定製基於使用者、基於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-proxy代理層節點上完成的(需要注意: 兩個從庫都要開啟 read_only=on)
1)先在本機安裝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 

2)安裝ProxySQL (中文安裝手冊: https://github.com/malongshuai/proxysql/wiki

[root@mysql-proxy ~]# vim /etc/yum.repos.d/proxysql.repo 
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

執行安裝
[root@mysql-proxy ~]# yum -y install proxysql

[root@mysql-proxy ~]# proxysql --version
ProxySQL version 1.4.13-15-g69d4207, codename Truls

啟動ProxySQL
[root@mysql-proxy ~]# chkconfig proxysql on
[root@mysql-proxy ~]# systemctl start proxysql        
[root@mysql-proxy ~]# systemctl status proxysql 

啟動後會監聽兩個埠,
預設為6032和6033。6032埠是ProxySQL的管理埠,6033是ProxySQL對外提供服務的埠 (即連線到轉發後端的真正資料庫的轉發埠)。
[root@mysql-proxy ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      23940/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      23940/proxysql 

3)向ProxySQL中新增MySQL節點

ProxySQL也是有管理介面和客戶端介面,通過配置檔案/etc/proxysql.cnf可以看到管理和客戶端介面的資訊.
[root@mysql-proxy ~]# cat /etc/proxysql.cnf           
.........
admin_variables=
{
        admin_credentials="admin:admin"
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}

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;/tmp/proxysql.sock"
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
..........

通過上面,可以看到管理介面的埠是6032,賬號密碼是admin(可以動態修改),允許客戶端連線, 客戶端介面的埠是6033,賬號密碼通過管理介面去設定。
在mysql-proxy本機使用mysql客戶端連線到ProxySQL的管理介面(admin interface), 該介面的預設管理員使用者和密碼都是admin.
下面是通過管理埠6032去連線的(下面連線命令中後面的--prompt 'admin'欄位可以不加,也是可以登入進去的)
[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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.

admin> 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的健康/延遲檢查。

主要注意main和monitor資料庫中的表
admin> show tables from main;
+--------------------------------------------+
| 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.001 sec)

幾個重要欄位含義:
global_variables       設定變數,包括監聽的埠、管理賬號等。
mysql_collations       相關字符集和校驗規則。
mysql_query_rules   定義查詢路由規則。

admin> show tables from monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+
5 rows in set (0.000 sec)

runtime_開頭的是執行時的配置,這些是不能修改的。要修改ProxySQL的配置,需要修改了非runtime_表,修改後必須執行LOAD ... TO RUNTIME
才能載入到RUNTIME生效,執行save ... to disk才能將配置持久化儲存到磁碟。

使用insert語句新增主機到mysql_servers表中,其中:hostgroup_id 10 表示寫組,20表示讀組。
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306); 
Query OK, 1 row affected (0.001 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306);
Query OK, 1 row affected (0.000 sec)

檢視這3個節點是否插入成功,以及它們的狀態。
admin> select * from mysql_servers\G;
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.211
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.212
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.213
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
3 rows in set (0.000 sec)

ERROR: No query specified

admin> 

如上修改後,載入到RUNTIME,並儲存到disk
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

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

4)監控後端MySQL節點

如上新增Mysql節點之後,還需要監控這些後端節點。對於後端是主從複製的環境來說,這是必須的,因為ProxySQL需要通過每個節點的read_only值來自動調整
它們是屬於讀組還是寫組。

首先在後端master主資料節點上建立一個用於監控的使用者名稱(只需在master上建立即可,因為會複製到slave上),這個使用者名稱只需具有USAGE許可權即可。如果還需
要監控複製結構中slave是否嚴重延遲於master(這個俗語叫做"拖後腿",術語叫做"replication lag"),則還需具備replication client許可權。

即在mysql-master主資料庫節點行執行:
mysql> create user monitor@'172.16.60.%' identified by 'P@ssword1!'; 
Query OK, 0 rows affected (0.08 sec)

mysql> grant replication client on *.* to monitor@'172.16.60.%';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)

然後回到mysql-proxy代理層節點上配置監控
admin> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.001 sec)

admin> set mysql-monitor_password='P@ssword1!';
Query OK, 1 row affected (0.000 sec)

修改後,載入到RUNTIME,並儲存到disk
admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)

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

驗證監控結果:ProxySQL監控模組的指標都儲存在monitor庫的log表中。

以下是連線是否正常的監控(對connect指標的監控):(在前面可能會有很多connect_error,這是因為沒有配置監控資訊時的錯誤,
配置後如果connect_error的結果為NULL則表示正常)
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 172.16.60.213 | 3306 | 1547436747566683 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436748167350 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436748767837 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436807566915 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436808112262 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436808657603 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547436867566829 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436868151063 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436868735236 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436927567060 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436928247532 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436928927772 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436987567058 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436988049160 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436988531003 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.212 | 3306 | 1547437047567194 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437048132480 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437048697765 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547437107567258 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437108310411 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437109053480 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547437167567303 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437168158076 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437168748763 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437227567525 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437228288921 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437229010153 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547437287567412 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437288343609 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437289119851 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547437295397678 | 1388                    | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437295826682 | 1377                    | NULL                                                                   |
| 172.16.60.212 | 3306 | 1547437296255659 | 8072                    | NULL                                                                   |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
33 rows in set (0.001 sec)

以下是對心跳資訊的監控(對ping指標的監控)
admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 172.16.60.213 | 3306 | 1547436798804839 | 0                    | Access denied for user 'monitor'@'172.16.60.213' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436798875555 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436798946212 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436808804803 | 0                    | Access denied for user 'monitor'@'172.16.60.213' (using password: YES)  |
| 172.16.60.212 | 3306 | 1547436808931415 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436809058082 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
....................
....................
| 172.16.60.213 | 3306 | 1547437295030407 | 335                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437295159540 | 218                  | NULL                                                                   |
| 172.16.60.212 | 3306 | 1547437295288638 | 172                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437365241682 | 337                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437375031403 | 435                  | NULL                                                                   |
..................
..................
| 172.16.60.213 | 3306 | 1547437395160777 | 469                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437395289919 | 377                  | NULL                                                                   |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
183 rows in set (0.001 sec)

但是,read_only和replication_lag的監控日誌都為空。
admin> select * from mysql_server_read_only_log;
Empty set (0.000 sec)

admin> select * from mysql_server_replication_lag_log;
Empty set (0.000 sec)

例如,指定寫組的id為10,讀組的id為20。
admin> insert into mysql_replication_hostgroups values(10,20,1);
Query OK, 1 row affected (0.000 sec)

在該配置載入到RUNTIME生效之前,先檢視下各mysql server所在的組。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 172.16.60.211 | 3306 | ONLINE | 1      |
| 10           | 172.16.60.212 | 3306 | ONLINE | 1      |
| 10           | 172.16.60.213 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
3 rows in set (0.000 sec)

3個節點都在hostgroup_id=10的組中。
現在,將剛才mysql_replication_hostgroups表的修改載入到RUNTIME生效。
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

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

一載入,Monitor模組就會開始監控後端的read_only值,當監控到read_only值後,就會按照read_only的值將某些節點自動移動到讀/寫組。
例如,此處所有節點都在id=10的寫組,slave1和slave2都是slave,它們的read_only=1,這兩個節點將會移動到id=20的組。
如果一開始這3節點都在id=20的讀組,那麼移動的將是Master節點,會移動到id=10的寫組。

現在看結果
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 172.16.60.211 | 3306 | ONLINE | 1      |
| 20           | 172.16.60.212 | 3306 | ONLINE | 1      |
| 20           | 172.16.60.213 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
3 rows in set (0.000 sec)

admin> select * from mysql_server_read_only_log;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname      | port | time_start_us    | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 172.16.60.212 | 3306 | 1547438982938183 | 694             | 1         | NULL  |
| 172.16.60.211 | 3306 | 1547438982950903 | 741             | 0         | NULL  |
..............
..............
| 172.16.60.211 | 3306 | 1547439054961006 | 517             | 0         | NULL  |
| 172.16.60.213 | 3306 | 1547439054977658 | 479             | 1         | NULL  |
+---------------+------+------------------+-----------------+-----------+-------+
147 rows in set (0.001 sec)

5)配置mysql_users

上面的所有配置都是關於後端MySQL節點的,現在可以配置關於SQL語句的,包括:傳送SQL語句的使用者、SQL語句的路由規則、SQL查詢的快取、
SQL語句的重寫等等。本小節是SQL請求所使用的使用者配置,例如root使用者。這要求我們需要先在後端MySQL節點新增好相關使用者。這裡以root和
sqlsender兩個使用者名稱為例。

首先,在mysql-master主資料庫節點上執行:(只需master執行即可,會複製給兩個slave)
mysql> grant all on *.* to root@'172.16.60.%' identified by 'passwd';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> grant all on *.* to sqlsender@'172.16.60.%' identified by 'P@ssword1!';
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

然後回到mysql-proxy代理層節點,配置mysql_users表,將剛才的兩個使用者新增到該表中。
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
Query OK, 1 row affected (0.001 sec)

admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
Query OK, 1 row affected (0.000 sec)

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

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

mysql_users表有不少欄位,最主要的三個欄位為username、password和default_hostgroup:
-  username:前端連線ProxySQL,以及ProxySQL將SQL語句路由給MySQL所使用的使用者名稱。
-  password:使用者名稱對應的密碼。可以是明文密碼,也可以是hash密碼。如果想使用hash密碼,可以先在某個MySQL節點上執行
   select password(PASSWORD),然後將加密結果複製到該欄位。
-  default_hostgroup:該使用者名稱預設的路由目標。例如,指定root使用者的該欄位值為10時,則使用root使用者傳送的SQL語句預設
   情況下將路由到hostgroup_id=10組中的某個節點。

admin> select * from mysql_users\G
*************************** 1. row ***************************
              username: root
              password: passwd
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
*************************** 2. row ***************************
              username: sqlsender
              password: P@ssword1!
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
2 rows in set (0.000 sec)

雖然這裡沒有詳細介紹mysql_users表,但上面標註了"注意本行"的兩個欄位必須要引起注意。只有active=1的使用者才是有效的使用者。
至於transaction_persistent欄位,當它的值為1時,表示事務持久化:當某連線使用該使用者開啟了一個事務後,那麼在事務提交/回滾之前,
所有的語句都路由到同一個組中,避免語句分散到不同組。在以前的版本中,預設值為0,不知道從哪個版本開始,它的預設值為1。
我們期望的值為1,所以在繼續下面的步驟之前,先檢視下這個值,如果為0,則執行下面的語句修改為1。

admin> update mysql_users set transaction_persistent=1 where username='root';
Query OK, 1 row affected (0.000 sec)

admin> update mysql_users set transaction_persistent=1 where username='sqlsender';
Query OK, 1 row affected (0.000 sec)

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

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

然後,另開一個終端,分別使用root使用者和sqlsender使用者測試下它們是否能路由到預設的hostgroup_id=10(它是一個寫組)讀、寫資料。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"            #這是通過轉發埠6033連線的,連線的是轉發到後端真正的資料庫!
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| proxy_test         |
| sys                |
+--------------------+

[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);' 
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t                    |
+----------------------+

6)讀寫分離:配置路由規則

ProxySQL的路由規則非常靈活,可以基於使用者、基於schema以及基於每個語句實現路由規則的定製。本案例作為一個入門配置,實現一個最簡單的語句級路由規則,從而實現讀寫分離。
必須注意,這只是實驗,實際的路由規則絕不應該僅根據所謂的讀、寫操作進行分離,而是從各項指標中找出壓力大、執行頻繁的語句單獨寫規則、做快取等等。和查詢規則有關的表有兩個:
mysql_query_rules和mysql_query_rules_fast_routing,後者是前者的擴充套件表,1.4.7之後才支援該快速路由表。本案例只介紹第一個表。插入兩個規則,目的是將select語句分離到
hostgroup_id=20的讀組,但由於select語句中有一個特殊語句SELECT...FOR UPDATE它會申請寫鎖,所以應該路由到hostgroup_id=10的寫組。

admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.001 sec)
  
admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)
  
admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.179 sec)
  
需要注意: select ... for update規則的rule_id必須要小於普通的select規則的rule_id,因為ProxySQL是根據rule_id的順序進行規則匹配的。
  
再來測試下,讀操作是否路由給了hostgroup_id=20的讀組。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
  
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
  
讀操作已經路由給讀組,再看看寫操作。這裡以事務持久化進行測試。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;'
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
  
顯然,一切都按照預期進行。最後,如果想檢視路由的資訊,可查詢stats庫中的stats_mysql_query_digest表。
以下是該表的一個輸出格式示例(和本案例無關)。
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 10 | 236771   | 1          | create table t(id int)           |
| 10 | 53767    | 1          | create database proxy_test       |
| 20 | 10344    | 1          | select @@server_id               |
| 20 | 3350     | 3          | select @@server_id               |
| 10 | 2469     | 2          | SELECT DATABASE()                |
| 10 | 1678     | 2          | select @@server_id               |
| 10 | 1193     | 1          | show databases                   |
| 10 | 1191     | 1          | start transaction                |
| 10 | 815      | 1          | show tables from proxy_test      |
| 10 | 289      | 1          | reate table t(id int)            |
| 10 | 162      | 1          | commit                           |
| 10 | 0        | 6          | select @@version_comment limit ? |
| 10 | 0        | 4          | select @@version_comment limit ? |
| 10 | 0        | 1          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
14 rows in set (0.002 sec)

7)最後測試通過proxysql外掛進行讀寫分離

mysql-master主資料庫檢視
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)
  
mysql-slave1和mysql-slave2兩個從資料庫檢視
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)
  
在mysql-proxy代理層節點,通過proxysql進行資料寫入,並檢視
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");'
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id=10' 
 
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;'                                          
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
 
在mysql-master主資料庫節點檢視
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)
 
在mysql-slave1和mysql-slave2從資料庫節點檢視
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)
 
發現在客戶端通過proxysql外掛更新的資料,已經寫到mysql-master主資料庫上,並同步到mysql-slave1和mysql-slave2兩個從資料庫上了!

==========================需要注意=======================
上面測試的客戶機是在mysql-proxy本機,所以用127.0.0.1.
如果是在別的客戶機上,那麼連線命令中就用proxysql的地址,即"mysql -uroot -ppasswd -P6033 -h172.16.60.214"

比如在mysql-slave2機器上連線proxysql外掛:
[root@mysql-slave2 ~]# mysql -uroot -ppasswd -P6033 -h172.16.60.214
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| proxy_test         |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

刪除上面建立的proxy_test測試庫
mysql> drop database proxy_test;
Query OK, 1 row affected (0.16 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      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)

至此,MySQL5.7基於GTID主從複製+並行複製+半同步複製+讀寫分離的環境部署工作已完成。 

相關文章