MySQL主從復制之進階延時同步、GTID復制、半同步復制完整實驗流程

1.主從同步

1.1主從同步原理

是指將主庫的DDL和DML操作通過二進制日志(binlog)傳到從庫服務器，然后在從庫上對這些日志進行重新執行，從而使從庫和主庫數據保持一致

1.2環境設置

庫名	ip地址	操作系統	mysql版本
主庫msyql-master	192.168.31.228	rhel7.9	源碼安裝mysql8.0.40
從庫mysql-slave1	192.168.31.229	rhel7.9	源碼安裝mysql8.0.40

1.3定位主庫

#定位主庫#編輯配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=228     #每臺服務器必須要唯一標識自己的id,可以使用ip尾號方便記憶
log-bin=binlog    #開啟二進制日志#啟動msyql
[root@mysql-master ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!#進入mysql
[root@mysql-master ~]# mysql -uroot -p123
mysql> create user 'rep'@'%' identified by 'rep123';   #創建用于共享的賬戶并設置密碼為rep123
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'rep'@'%';     #賦予rep賬戶復制權限
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;      #鎖表設置只讀，注意生產環境要提前申請停機時間
Query OK, 0 rows affected (0.00 sec)
mysql> create database andy;      #測試，創建數據庫失敗，鎖表成功！
ERROR 1223 (HY000): Can't execute the query because you have a licting read lock
mysql> show master status\G;   #查看主庫狀態
*************************** 1. row ***************************File: binlog.000004       #二進制日志文件Position: 1053            #文件位置Binlog_Do_DB:  Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.4定位從庫

#定位從數據庫#編輯配置文件
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=229    #設置唯一id
log-bin=binlog#因為此臺從服務器是由主數據庫克隆而來的，因此兩臺服務器具有相同的uuid,后續會出現錯誤，因此先停止mysql,刪除/data/mysql目錄下的所有文件，初始化。重啟mysql,修改賬戶密碼即可；具體如下操作；但是如果你的兩臺機子是單獨創建的，可以不用進行如下操作，直接進入mysql即可
[root@mysql-slave1 ~]# /etc/init.d/mysqld stop   #停止mysqlERROR! MySQL server PID file could not be found!
[root@mysql-slave1 ~]# rm -rf /data/mysql/*    #刪除/data/mysql目錄下的所有文件
[root@mysql-slave1 ~]# mysqld --initialize --user=mysql   #初始化生成原始密碼
2025-09-04T08:24:26.715327Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.40) initializing of server in progress as process 2802
2025-09-04T08:24:26.731299Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-09-04T08:24:26.907524Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-09-04T08:24:27.443256Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ,<aO>w<GF1xi    
[root@mysql-slave1 ~]# /etc/init.d/mysqld start   #重啟mysql
Starting MySQL.Logging to '/data/mysql/mysql-slave1.err'.
. SUCCESS! 
[root@mysql-slave1 ~]# mysqladmin -uroot -p',<aO>w<GF1xi' password '123'  #修改密碼
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.#進入mysql
[root@mysql-slave1 ~]# mysql -uroot -p123
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 9
Server version: 8.0.40 Source distributionCopyright (c) 2000, 2024, Oracle and/or its affiliates.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> change replication source to      #設置從庫向主庫同步-> source_host='192.168.31.228',      #主庫ip-> source_user='rep',      #主庫設置用于共享的賬戶名-> source_password='rep123',      #賬戶密碼-> source_log_file='binlog.000004',     #二進制日志文件；MySQL主從復制是主庫通過二進制日志將數據傳送給從庫-> source_log_pos=1053,    #日志文件位置-> source_ssl=1;           
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start replica;     #啟動同步
Query OK, 0 rows affected (0.13 sec)mysql> show replica status\G;    #查看同步狀態
Replica_IO_Running: Yes  
Replica_SQL_Running: Yes    #I/O線程、SQL線程已打開

1.5測試

#測試#定位主庫：
mysql> create database andy;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| andy               |
+--------------------+
5 rows in set (0.00 sec)#定位從庫：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |   
| andy                 |    #出現andy數據庫，同步成功
+--------------------+
5 rows in set (0.00 sec)

2.延時同步

2.1延時同步原理

通過人為配置從庫和主庫延時N小時可以實現延時同步，延時同步可以解決數據庫故障出現的數據丟失問題。基于上述實驗進行！

2.2定位從庫-關閉同步、設置延遲時間、開啟同步

#定位從庫mysql> stop replica;    #停止同步
Query OK, 0 rows affected (0.01 sec)mysql> change replication source to source_delay=300;  #設置SQL線程延時300s后讀取relay log
Query OK, 0 rows affected (0.01 sec)mysql> start replica;     #開啟同步
Query OK, 0 rows affected (0.14 sec)mysql> show replica status\G;   #查看同步狀態
SQL_Delay: 300    #延遲時間300s
SQL_Remaining_Delay: NULL   #在主庫未進行操作，保留延遲為空

2.3定位主庫-創建庫、表、插入數據、誤刪庫

#定位主庫mysql> create database relay;   #創建relay賬戶
Query OK, 1 row affected (0.00 sec)#如果創建失敗，表名表還正在被鎖，無法創建，可以進行鎖表操作！
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)ysql> use relay;   #使用relay庫
Database changedmysql> create table t1(id int,name char(30));   #創建t1表
Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 value(001,'zhangsan');    #插入數據
Query OK, 1 row affected (0.03 sec)mysql> drop database relay;      #此時進行誤刪操作！誤刪relay數據庫
Query OK, 1 row affected (0.01 sec)

2.4定位從庫-停止SQL線程、后去延遲日志文件、文件位置，刪除數據位置、恢復relay log

#定位從庫mysql> show replica status\G;  #查看同步狀態
SQL_Delay: 300   #延遲時間300s
SQL_Remaining_Delay: 211    #保留延遲時間剩余211smysql> stop replica sql_thread;     #停止SQL線程
Query OK, 0 rows affected (0.01 sec)mysql> show replica status\G;   #查看同步狀態
Relay_Log_File: mysql-slave1-relay-bin.000002   #延遲日志文件
Relay_Log_Pos: 323     #延遲日志起始位置mysql> show relaylog events in 'mysql-slave1-relay-bin.000002';    #找到誤刪數據的位置1267
+-------------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------+
| Log_name                      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                            |
+-------------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------+
| mysql-slave1-relay-bin.000002 |    4 | Format_desc    |       229 |         126 | Server ver: 8.0.40, Binlog ver: 4                               |
| mysql-slave1-relay-bin.000002 |  126 | Previous_gtids |       229 |         157 |                                                                 |
| mysql-slave1-relay-bin.000002 |  157 | Rotate         |       228 |           0 | binlog.000004;pos=1053                                          |
| mysql-slave1-relay-bin.000002 |  201 | Format_desc    |       228 |           0 | Server ver: 8.0.40, Binlog ver: 4                               |
| mysql-slave1-relay-bin.000002 |  323 | Anonymous_Gtid |       228 |        1130 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
| mysql-slave1-relay-bin.000002 |  400 | Query          |       228 |        1238 | create database andy /* xid=35 */                               |
| mysql-slave1-relay-bin.000002 |  508 | Anonymous_Gtid |       228 |        1315 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
| mysql-slave1-relay-bin.000002 |  585 | Query          |       228 |        1426 | create database relay /* xid=36 */                              |
| mysql-slave1-relay-bin.000002 |  696 | Anonymous_Gtid |       228 |        1503 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
| mysql-slave1-relay-bin.000002 |  773 | Query          |       228 |        1630 | use `relay`; create table t1(id int,name char(30)) /* xid=41 */ |
| mysql-slave1-relay-bin.000002 |  900 | Anonymous_Gtid |       228 |        1709 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
| mysql-slave1-relay-bin.000002 |  979 | Query          |       228 |        1785 | BEGIN                                                           |
| mysql-slave1-relay-bin.000002 | 1055 | Table_map      |       228 |        1840 | table_id: 109 (relay.t1)                                        |
| mysql-slave1-relay-bin.000002 | 1110 | Write_rows     |       228 |        1889 | table_id: 109 flags: STMT_END_F                                 |
| mysql-slave1-relay-bin.000002 | 1159 | Xid            |       228 |        1920 | COMMIT /* xid=42 */                                             |
| mysql-slave1-relay-bin.000002 | 1190 | Anonymous_Gtid |       228 |        1997 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
| mysql-slave1-relay-bin.000002 | 1267 | Query          |       228 |        2104 | drop database relay /* xid=43 */                                |
+-------------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------+
17 rows in set (0.00 sec)[root@mysql-slave1 ~]# mysqlbinlog --start-position=323 --stop-position=1267 
/data/mysql/mysql-slave1-relay-bin.000002  > /tmp/relay.sql   #將relay log進行備份
[root@mysql-slave1 ~]# mysql -uroot -p -e 'source  /tmp/relay.sql'  # 恢復誤刪除的數據庫 
Enter password:      #輸入密碼
[root@mysql-slave1 ~]# mysql -uroot -p123     #進入msyqlmysql> select * from relay.t1;    #查看t1表數據
+------+----------+
| id   | name     |
+------+----------+
|   001| zhangsan |     #恢復成功！
+------+----------+
1 row in set (0.00 sec)#解除從庫身份
mysql> stop replica;
Query OK, 0 rows affected (0.01 sec)mysql> reset replica all;
Query OK, 0 rows affected (0.01 sec)

3.GTID復制

3.1GTID復制原理

GTID是全局事務ID（global transaction identifier），其保證為每一個提交的事務可以生成一個唯一的ID。當事務提交時，MySQL Server在寫binlog的時候，會先寫一個類型為GTID_Event的特殊Binlog Event，指定下一個事務的GTID，然后再寫事務的Binlog。主從同步時GTID_Event和事務的Binlog 都會傳遞到從庫，從庫在執行的時候也是用同樣的GTID寫binlog，這樣主從同步以后，就可通過GTID確定從庫同步到的位置了。也就是說，無論是級聯情況，還是一主多從情況，都可以通過GTID自動找同步位置，而無需像之前那樣通過log_file和log_pos找同步位置了。

有兩種方式，將二進制日志同步切換為gtid同步和直接進行gtid同步，以下在上述主從同步的基礎上進行二進制日志同步切換為gtid同步

3.2主庫、從庫同時開啟強一致性與gtid模式

mysql> set global enforce_gtid_consistency=on;   #開啟強一致性
Query OK, 0 rows affected (0.00 sec)mysql> set global gtid_mode=off_permissive; 
Query OK, 0 rows affected (0.00 sec)mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)mysql> set global gtid_mode=on;       #開啟gtid模式
Query OK, 0 rows affected (0.00 sec)

3.3定位從庫

#定位從庫mysql> stop replica;  #關閉同步mysql> CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;     #切換到gtid模式mysql> start replica;   #開啟模式mysql> show replica status \G；  #查看同步狀態
Replica_IO_Running: Yes
Replica_SQL_Running: Yes

3.4測試

#定位主庫mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| andy               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql> create database cheng;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| andy               |
| cheng              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)#定位從庫mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| andy               |
| cheng              |   #同步成功
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

4.半同步復制

4.1半同步復制原理

主庫在應答客戶端提交的事務前需要保證至少一個從庫接收并將事務寫到relay log中，即master將寫入binlog的事務傳遞到slave，主庫提交事務，master等待slave回復ACK確認已將數據寫入relay log，此時master才會將commit OK結果反饋給客戶端，如果出現異常沒有收到ACK，那么將自動降級為普通的復制，直到異常修復后又會變為半同步復制。

4.2定位主庫

#定位主庫mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';  #安裝插件mysql> show plugins;   #判斷插件是否安裝成功mysql> set global rpl_semi_sync_master_enabled=1;  #打開半同步開關，可寫入配置文件mysql> set global rpl_semi_sync_master_timeout=3000;#設置主庫等待從庫回復ack的超時時間為3s默認是10s,可寫入配置文件mysql> show variables like 'rpl_semi_sync_master%'; 
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+

4.3定位從庫

#定位從庫mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';mysql> show plugins;mysql> set global rpl_semi_sync_slave_enabled=1; #打開半同步開關，可寫入配置文件mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+mysql> stop replica IO_THREAD;mysql> start replica IO_THREAD;  #重啟IO線程

4.4測試

#測試#定位主庫
mysql> use andy;
mysql> create table t1(id int,name varchar(30));mysql> show status like 'Rpl_semi_sync_master_yes_tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_yes_tx | 1     |
+-----------------------------+-------+   #master成功接收到slave的回復的次數