MySQL 主從（MySQL replication），主要用于 MySQL 的實時備份或者讀寫分離。主從複制可以将 MySQL 主資料庫中的資料實時複制到一個或多個 MySQL 從資料庫中。
MySQL 複制首先将 MySQL 主資料庫(master) 的資料通過 binlog 日志的方式經過網絡發送到一台或多台 MySQL 從資料庫上(slave)，然後在 slave 上重放傳送過來的日志（relay log），以達到和 master 資料同步的目的。
首先確定 master 資料庫上開啟了二進制日志，這是複制的前提。
- 在 slave 準備開始複制時，首先要執行 change master to 語句設定連接配接到 master 伺服器的連接配接參數，在執行該語句的時候要提供一些資訊，包括如何連接配接和要從哪複制 binlog，這些資訊在連接配接的時候會記錄到 slave 的 datadir 下的 master.info 檔案中，以後再連接配接 master 的時候将不用再提供這新資訊而是直接讀取該檔案進行連接配接。（當然也可以基于 GTID 的方式，就不需要指定 binlog 檔案和 position 了，隻需要指定一個全局唯一的 GTID）。
- 在 slave 上有兩種線程，分别是 IO 線程和 SQL 線程。
-
- IO 線程用于連接配接 master，監控和讀取 master 的 binlog。當啟動 IO 線程成功連接配接 master 時，master 會同時啟動一個 dump 線程，該線程将 slave 請求要複制的 binlog 給 dump 出來，之後 IO 線程負責監控并接收 master 上 dump 出來的 binlog 日志，當 master 上 binlog 有變化的時候，IO 線程就将其複制過來并寫入到自己的中繼日志(relay log)檔案中。
- slave上 的另一個線程 SQL 線程用于監控、讀取并重放 relay log 中的日志，将資料寫入到自己的資料庫中。
站在 slave 的角度上看，過程如下：
本文将會介紹兩種同步方式：
- 1.基于 binlog + postion 的傳統的同步方式。
- 2.基于 GTID 的同步方式（推薦）。
在同步之前會先往主資料庫中插入資料，然後分别介紹在建立主從之前通過 mysqldump 和 xtrabackup 全量同步資料的兩種方式。當然你可以直接建立主從以後再開始寫入資料，這樣就可以省略全量同步這個步驟了。
傳統異步主從複制
機器規劃
| 主機名 | IP位址 | 端口号 | 角色 |
| mysql-master | 192.168.1.36 | 3306 | master（主庫） |
| mysql-slave | 192.168.1.37 | slave（從庫） |
準備工作
準備工作在主庫和從庫的伺服器上都要執行。
建立相關目錄
#建立使用者
userdel -r mysql
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
#建立目錄
# /mysql/app/ MySQL 資料庫軟體根目錄
# /mysql/data/3306/data/ MySQL 資料檔案目錄
# /mysql/log/3306/binlog MySQL 二進制日志目錄
# /mysql/log/3306/relaylog MySQL 中繼日志目錄
# /mysql/backup/3306/xtrabackup/target_dir MySQL xtrabackup實體備份目錄
# /mysql/backup/3306/mysqldump MySQL mysqldump邏輯備份目錄
# /mysql/script MySQL 常用腳本存放目錄
mkdir -p /mysql/app/
mkdir -p /mysql/data/3306/data/
mkdir -p /mysql/log/3306/binlog
mkdir -p /mysql/log/3306/relaylog
mkdir -p /mysql/backup/3306/xtrabackup/target_dir
mkdir -p /mysql/backup/3306/mysqldump
mkdir -p /mysql/script
#給目錄授權
chown -R mysql:mysql /mysql 下載下傳并解壓 MySQL 安裝包
MySQL 壓縮包下載下傳位址：
https://dev.mysql.com/downloads/mysql/5.7.html#解壓壓縮包
tar zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /mysql/app
mv /mysql/app/mysql-5.7.29-linux-glibc2.12-x86_64 /mysql/app/mysql
chown -R mysql:mysql /mysql 配置環境變量
##将MySQL目錄添加環境變量##
cat >> ~/.bash_profile <<-EOF
export PATH=$PATH:/mysql/app/mysql/bin
EOF
source ~/.bash_profile 初始化主庫
主庫配置檔案，主庫需要指定 log_bin 開啟 binlog 以及設定 server_id。
#主機名和端口号作為目錄名的一部分
HostName=`hostname`
MySql_Port=3306
#IP位址
Ip=192.168.1.36
#master server_id 要和 slave 不一樣
Server_Id=1
cat > /mysql/data/$MySql_Port/my.cnf <<-EOF
#------------------------------------
#用戶端設定
#------------------------------------
[client]
port=$MySql_Port
socket =/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8
#------------------------------------
#mysql連接配接工具設定
#------------------------------------
[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>" #登入時顯示登入的使用者名、伺服器位址、預設資料庫名、目前時間
auto-rehash #讀取表資訊和列資訊，可以在連上終端後開啟tab補齊功能。
default-character-set=utf8 #預設字元集
#------------------------------------
#基本設定
#------------------------------------
[mysqld]
bind_address=0.0.0.0 #監聽本地所有位址
port=$MySql_Port #端口号
user=mysql #使用者
basedir=/mysql/app/mysql #安裝路徑
datadir=/mysql/data/$MySql_Port/data #MySQL資料目錄
socket=/mysql/data/$MySql_Port/mysql.sock #用于本地連接配接的socket檔案目錄
pid-file=/mysql/data/$MySql_Port/mysql.pid #程序ID檔案的目錄。
character-set-server=utf8 #預設字元集
#------------------------------------
#log setting 日志設定
#------------------------------------
long_query_time=10 #慢查詢時間，超過 10 秒則認為是慢查詢
slow_query_log=ON #啟用慢查詢日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查詢日志目錄
log_queries_not_using_indexes=1 #記錄未使用索引的語句
log_slow_admin_statements=1 #慢查詢也記錄那些慢的optimize table，analyze table和alter table語句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log #錯誤日志目錄
#------------------------------------
#master modify parameter 主庫複制更改參數
#------------------------------------
server_id=$Server_Id #master和slave server_id 需要不同
#二進制日志參數配置
log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目錄
log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引檔案的位置
binlog_format=row #行模式複制，預設是 row
binlog_rows_query_log_events=on #在 row 模式下，開啟該參數,可以将把 sql 語句列印到 binlog 日志裡面，友善檢視
binlog_cache_size=1M #事務能夠使用的最大 binlog 緩存空間。
max_binlog_size=2048M #binlog 檔案最大空間，達到該大小時切分檔案
expire_logs_days=7 #設定自動删除 binlog 檔案的天數。
sync_binlog=1 #表示每次事務的 binlog 都會fsync持久化到磁盤，MySQL 5.7.7 之後預設為1，之前的版本預設為0
innodb_flush_log_at_trx_commit=1 #表示每次事務的 redo log 都直接持久化到磁盤，預設值為1
EOF 初始化主庫：
mysqld \
--defaults-file=/mysql/data/3306/my.cnf \
--initialize --user=mysql \
--basedir=/mysql/app/mysql \
--datadir=/mysql/data/3306/data 配置 MySQL 啟動腳本：
cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3306
ln -sf /etc/init.d/mysql_3306 /usr/lib/systemd/system/mysql_3306
#修改啟動腳本##
vi /etc/init.d/mysql_3306
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
mysqld_pid_file_path=/mysql/data/3306/mysql.pid
#在$bindir/mysqld_safe 後面添加，注意 --defaults-file 要放在第一個
--defaults-file="/mysql/data/3306/my.cnf"
systemctl daemon-reload 啟動 MySQL，修改密碼，運作遠端登入：
#啟動、MySQL服務
systemctl start mysql_3306
#擷取MySQL臨時密碼
Passwd=`cat /mysql/log/3306/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'`
echo $Passwd
#通過本地 socket 登入、修改密碼
mysql -uroot -p$Passwd -S /mysql/data/3306/mysql.sock
alter user 'root'@'localhost' identified by "123456";
#允許遠端登入
grant all privileges on *.* to root@'%' identified by '123456';
#重新整理權限
flush privileges; 主庫插入資料
使用存儲過程每 5 秒往主庫插入 10 條資料，模拟生産環境。
create database yzjtestdb;
drop table yzjtestdb.data01;
create table yzjtestdb.data01(
id int not null primary key auto_increment,
name varchar(60),
age int);
use yzjtestdb;
drop procedure sp_data01_2;
#臨時指定結束符為 //，因為存儲過程中語句以;結束
delimiter //
create procedure sp_data01_10()
begin
declare n int;
set n=0;
repeat
insert
into
yzjtestdb.data01(name,age)
values(concat('samkeji1',n),22);
commit;
set n=n+1;
until n>=10 end repeat;
end
//
delimiter ; #恢複結束符
#開啟事件排程器
set global event_scheduler =1;
#每5秒執行一次存儲過程，插入10條資料
create event if not exists e_data01_10
on schedule every 5 second
on completion preserve
do
call sp_data01_10();
#5小時清空表
create event if not exists e_data01_5_truncate
on schedule every 5 hour
on completion preserve
do
truncate table yzjtestdb.data01;
#當為on completion preserve 的時候,當event到期了,event會被disable,但是該event還是會存在
#當為on completion not preserve的時候,當event到期的時候,該event會被自動删除掉.
alter event e_data01_10 on completion preserve enable;
alter event e_data01_5_truncate on completion preserve enable;
#停止存儲過程
#alter event yzjtestdb.e_data01_10 ON COMPLETION PRESERVE DISABLE; 檢視插入的資料：
mysql> select count(*) from yzjtestdb.data01;
+----------+
| count(*) |
+----------+
| 44 |
+----------+
1 row in set (0.01 sec) 從庫配置
從庫配置檔案，主要是 ip 位址，server_id，relay_log 的配置和主庫不同，其餘配置和主庫相同。
#主機名和端口号作為目錄名的一部分
HostName=`hostname`
MySql_Port=3306
#IP位址
Ip=192.168.1.37
#master server_id 要和 slave 不一樣
Server_Id=2
cat > /mysql/data/$MySql_Port/my.cnf <<-EOF
#------------------------------------
#用戶端設定
#------------------------------------
[client]
port=$MySql_Port
socket =/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8
#------------------------------------
#mysql連接配接工具設定
#------------------------------------
[mysql]
prompt="\\u@\\h : \\d\\r:\\m:\\s>" #登入時顯示登入的使用者名、伺服器位址、預設資料庫名、目前時間
auto-rehash #讀取表資訊和列資訊，可以在連上終端後開啟tab補齊功能。
default-character-set=utf8 #預設字元集
#------------------------------------
#基本設定
#------------------------------------
[mysqld]
bind_address=0.0.0.0 #監聽本地所有位址
port=$MySql_Port #端口号
user=mysql #使用者
basedir=/mysql/app/mysql #安裝路徑
datadir=/mysql/data/$MySql_Port/data #MySQL資料目錄
socket=/mysql/data/$MySql_Port/mysql.sock #用于本地連接配接的socket檔案目錄
pid-file=/mysql/data/$MySql_Port/mysql.pid #程序ID檔案的目錄。
character-set-server=utf8 #預設字元集
#------------------------------------
#log setting 日志設定
#------------------------------------
long_query_time=10 #慢查詢時間，超過 10 秒則認為是慢查詢
slow_query_log=ON #啟用慢查詢日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查詢日志目錄
log_queries_not_using_indexes=1 #記錄未使用索引的語句
log_slow_admin_statements=1 #慢查詢也記錄那些慢的optimize table，analyze table和alter table語句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log #錯誤日志目錄
#------------------------------------
#master modify parameter 主庫複制更改參數
#------------------------------------
server_id=$Server_Id #master和slave server_id 需要不同
#二進制日志參數配置
log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目錄
log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引檔案的位置
binlog_format=row #行模式複制，預設是 row
binlog_rows_query_log_events=on #在 row 模式下，開啟該參數,可以将把 sql 語句列印到 binlog 日志裡面，友善檢視
binlog_cache_size=1M #事務能夠使用的最大 binlog 緩存空間。
max_binlog_size=2048M #binlog 檔案最大空間，達到該大小時切分檔案
expire_logs_days=7 #設定自動删除 binlog 檔案的天數。
sync_binlog=1 #表示每次事務的 binlog 都會fsync持久化到磁盤，MySQL 5.7.7 之後預設為1，之前的版本預設為0
innodb_flush_log_at_trx_commit=1 #表示每次事務的 redo log 都直接持久化到磁盤，預設值為1
#------------------------------------
#slave parameter 從庫參數
#------------------------------------
relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog #中繼日志目錄
relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index #中繼日志索引目錄
log_slave_updates=1 #從庫從主庫複制的資料會寫入從庫 binlog 日志檔案裡，預設是不寫入
read_only=1 #從庫隻讀
relay_log_purge=1 #自動清空不再需要中繼日志
# 并行複制參數
#主庫上面怎麼并行，從庫上面就怎麼回放，基于邏輯時鐘的概念
#binlog 會記錄組送出的資訊，從回放的時候就可以知道哪些事務是一組裡面的，
#一組裡面的就丢到不同線程去回放，不是一組裡的就等待，以此來提升并行度
slave-parallel-type=LOGICAL_CLOCK
#多線程複制
slave-parallel-workers=4
#slave 上commit 的順序保持一緻，否則可能會有間隙鎖産生
slave-preserve-commit_order=1
master_info_repository=TABLE #預設每接收到10000個事件，寫一次master-info，預設是寫在檔案中的
#修改 relay_log_info_repository 的好處
#1.relay.info 明文存儲不安全，把 relay.info 中的資訊記錄在 table 中相對安全。
#2.可以避免 relay.info 更新不及時，slave 重新開機後導緻的主從複制出錯。
relay_log_info_repository=TABLE #将回放資訊記錄在 slave_relay_log_info 表中，預設是記錄在 relay-info.log 檔案中
relay_log_recovery=1 #當slave重新開機時，将所有 relay log 删除，通過 sql 線程重放的位置點去重新拉日志
#------------------------------------
#Replication Filter 從庫複制過濾參數
#------------------------------------
#（過濾某個資料庫、資料庫.表）
#replicate_do_db=yzjtestdb
#replicate_wild_do_table=yzjtestdb.%
#replicate_do_table=yzjtestdb.yzjtest_yg
#replicate_wild_do_table=yzjtestdb.yzjtest_yg
EOF 初始化從庫：
mysqld \
--defaults-file=/mysql/data/3306/my.cnf \
--initialize --user=mysql \
--basedir=/mysql/app/mysql \
--datadir=/mysql/data/3306/data cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3306
ln -sf /etc/init.d/mysql_3306 /usr/lib/systemd/system/mysql_3306
#修改啟動腳本##
vi /etc/init.d/mysql_3306
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
mysqld_pid_file_path=/mysql/data/3306/mysql.pid
#在$bindir/mysqld_safe 後面添加，注意 --defaults-file 要放在第一個
--defaults-file="/mysql/data/3306/my.cnf"
systemctl daemon-reload #啟動、MySQL服務
systemctl start mysql_3306
#擷取MySQL臨時密碼
Passwd=`cat /mysql/log/3306/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'`
echo $Passwd
#通過本地 socket 登入、修改密碼
mysql -uroot -p$Passwd -S /mysql/data/3306/mysql.sock
alter user 'root'@'localhost' identified by "123456";
#允許遠端登入
grant all privileges on *.* to root@'%' identified by '123456';
#重新整理權限
flush privileges; 主庫建立複制使用者
在主庫上建立一個用于資料複制的使用者。
create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%'; 主庫通過 mysqldump 備份資料庫
mysqldump 參數說明：
- --single-transaction：選項設定事務的隔離級别為 REPEATABLE READ，在導出資料之前向伺服器發送一個 START TRANSACTION 語句。--single-transaction 和 --lock-tables 選項是互斥的， --lock-tables 會鎖表，--single-transaction 隻有在剛開始設定事務隔離級别的時候會短暫的鎖表。
- --master-data=2：該選項将 binlog 的位置和檔案名追加到輸出檔案中，友善我們建立主從的時候指定 binlog position。如果為 1，将會輸出 CHANGE MASTER 指令；如果為 2，輸出的 CHANGE MASTER 指令前添加注釋資訊，我們手動在 slave 上執行。
- --flush-logs：開始導出之前重新整理日志。
- --events：導出事件。
- --routines：導出存儲過程以及自定義函數。
- --all-databases：導出全部資料庫。
mysqldump -S /mysql/data/3306/mysql.sock -uroot -p123456 \
--single-transaction \
--master-data=2 \
--flush-logs \
--flush-privileges \
--events --routines \
--all-databases > /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql 檢視備份的檔案：
[root@mysql-master ~]# ls -l /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql
-rw-r--r--. 1 root root 968343 9月 4 22:20 /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql 将備份檔案拷貝到從庫上：
scp -r /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql 192.168.1.37:/mysql/backup/3306/mysqldump/ 從庫導入主庫的備份資料
source /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql 檢視從庫導入的資料：
mysql> select * from yzjtestdb.data01 limit 10;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | samkeji16 | 22 |
| 2 | samkeji17 | 22 |
| 3 | samkeji18 | 22 |
| 4 | samkeji19 | 22 |
| 5 | samkeji10 | 22 |
| 6 | samkeji11 | 22 |
| 7 | samkeji12 | 22 |
| 8 | samkeji13 | 22 |
| 9 | samkeji14 | 22 |
| 10 | samkeji15 | 22 |
+----+-----------+------+
10 rows in set (0.01 sec) 從庫建立主從關系
檢視 mysqldump 導出的檔案記錄的主庫 binlog 檔案 position 位置。
cat /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql|more 從庫應該以主庫 mysql-master-binlog.000004 這個 binlog 檔案的 position 154 開始同步。使用以下指令建立主從關系、開啟複制。
stop slave;
reset master;
change master to
master_host='192.168.1.36',
master_user='repuser',
master_password='repuser123',
master_log_file='mysql-master-binlog.000004',
master_log_pos=154;
start slave; 在 slave上 執行 show slave status 可以檢視 slave 的狀态資訊。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.36
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
#IO 線程正在讀取的 master binlog
Master_Log_File: mysql-master-binlog.000004
#IO 線程已經讀取到 master binlog 的哪個位置；
Read_Master_Log_Pos: 1050654
#SQL 線程正在讀取和執行的 relay log
Relay_Log_File: mysql-slave-relaylog.000002
#SQL 線程已經讀取和執行到 relay log 的哪個位置
Relay_Log_Pos: 1050830
#SQL 線程最近執行的操作對應的是哪個 master binlog
Relay_Master_Log_File: mysql-master-binlog.000004
Slave_IO_Running: Yes #IO 線程和 SQL 線程 YES 表同步正常
Slave_SQL_Running: Yes
Replicate_Do_DB: #顯式指定要複制的資料庫
Replicate_Ignore_DB: #顯式指定要忽略的資料庫
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
#SQL 線程最近執行的操作對應的是 master binlog 的哪個位置
Exec_Master_Log_Pos: 1050654
Relay_Log_Space: 1051042
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
#slave 比 master 少多少秒的資料
#這個參數的描述并不标準，在同步正常的情況下值應該為 0
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7729c524-0cd3-11ec-a7b9-0050568b1bca
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
# slave SQL線程的狀态
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec) 從庫檢視讀寫權限，普通使用者隻讀，特權使用者可以讀寫。
mysql> select @@read_only,@@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
| 1 | 0 |
+-------------+-------------------+
1 row in set (0.01 sec) 在 slave 上檢視線程資訊：
- Id 為 4 的線程是 IO 線程，從 master 讀取 binlog 資料。
- MySQL 5.7 增加了多線程複制的特性，Id 為 3 的 SQL 線程作為 Coordinator 線程，來排程 worker 線程。
- 我們前面在 slave 的配置檔案中設定了複制并發為 4 個線程，是以可以看到 Id 為 5，6，7，8 的 work 線程來接收 Coordinator 排程。
mysql> show processlist;
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| 3 | system user | | NULL | Connect | 1485 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL |
| 9 | jack | localhost | yzjtestdb | Query | 0 | starting | show processlist |
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
7 rows in set (0.00 sec) 驗證主從同步狀态
通過查詢表中記錄條數可以看到主從現在資料是正常同步的。
select count(*) from yzjtestdb.data01; 基于 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 檔案再重新開機 MySQL。
mysql> show variables like "%uuid%";
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | c6bca41f-0d8a-11ec-b8a1-0050568b71df |
+---------------+--------------------------------------+
1 row in set (0.00 sec) - trans_id 是事務ID，可以唯一标記某 MySQL 伺服器上執行的某個事務。事務号從 1 開始，每送出一個事務，事務号加 1。例如 gtid_executed 5ad9cb8e-2092-11e7-ac95-000c29bf823d:1-6，表示該 server_uuid 上執行了從 1 到 6 的事務。
隻有送出了的事務，gtid 和對應的事務操作才會記錄到 binlog 檔案中。記錄的格式是先記錄 gtid，緊跟着再記錄事務相關的操作。
| 3307 | |||
準備工作在主庫和從庫的伺服器上都要執行。基于 GTID 同步的方式依然使用前面的 master 和 slave 兩台機器，為了區分把 MySQL 的端口号換成 3307。
mkdir -p /mysql/data/3307/data/
mkdir -p /mysql/log/3307/binlog
mkdir -p /mysql/log/3307/relaylog
mkdir -p /mysql/backup/3307/xtrabackup/target_dir
mkdir -p /mysql/backup/3307/mysqldump
#給目錄授權
chown -R mysql:mysql /mysql 主庫配置檔案，主庫需要開啟 binlog 以及設定 server_id。針對 GTIP 有兩個參數必須設定：
- gtid_mode=on
- enforce_gtid_consistency=on
#主機名和端口号作為目錄名的一部分
HostName=`hostname`
MySql_Port=3307
#IP位址
Ip=192.168.1.36
#master server_id 要和 slave 不一樣
Server_Id=1
cat > /mysql/data/$MySql_Port/my.cnf <<-EOF
#------------------------------------
#用戶端設定
#------------------------------------
[client]
port=$MySql_Port
socket =/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8
#------------------------------------
#mysql連接配接工具設定
#------------------------------------
[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>" #登入時顯示登入的使用者名、伺服器位址、預設資料庫名、目前時間
auto-rehash #讀取表資訊和列資訊，可以在連上終端後開啟tab補齊功能。
default-character-set=utf8 #預設字元集
#------------------------------------
#基本設定
#------------------------------------
[mysqld]
bind_address=0.0.0.0 #監聽本地所有位址
port=$MySql_Port #端口号
user=mysql #使用者
basedir=/mysql/app/mysql #安裝路徑
datadir=/mysql/data/$MySql_Port/data #MySQL資料目錄
socket=/mysql/data/$MySql_Port/mysql.sock #用于本地連接配接的socket檔案目錄
pid-file=/mysql/data/$MySql_Port/mysql.pid #程序ID檔案的目錄。
character-set-server=utf8 #預設字元集
#------------------------------------
#log setting 日志設定
#------------------------------------
long_query_time=10 #慢查詢時間，超過 10 秒則認為是慢查詢
slow_query_log=ON #啟用慢查詢日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查詢日志目錄
log_queries_not_using_indexes=1 #記錄未使用索引的語句
log_slow_admin_statements=1 #慢查詢也記錄那些慢的optimize table，analyze table和alter table語句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log #錯誤日志目錄
#------------------------------------
#master modify parameter 主庫複制更改參數
#------------------------------------
server_id=$Server_Id #master和slave server_id 需要不同
#二進制日志參數配置
log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目錄
log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引檔案的位置
binlog_format=row #行模式複制，預設是 row
binlog_rows_query_log_events=on #在 row 模式下，開啟該參數,可以将把 sql 語句列印到 binlog 日志裡面，友善檢視
binlog_cache_size=1M #事務能夠使用的最大 binlog 緩存空間。
max_binlog_size=2048M #binlog 檔案最大空間，達到該大小時切分檔案
expire_logs_days=7 #設定自動删除 binlog 檔案的天數。
sync_binlog=1 #表示每次事務的 binlog 都會fsync持久化到磁盤，MySQL 5.7.7 之後預設為1，之前的版本預設為0
innodb_flush_log_at_trx_commit=1 #表示每次事務的 redo log 都直接持久化到磁盤，預設值為1
#------------------------------------
#GTID Settings GTID 同步複制設定
#------------------------------------
gtid_mode=on #開啟GTID同步
enforce_gtid_consistency=on #強制事務一緻，確定 GTID 的安全，在事務中就不能建立和删除臨時表
binlog_gtid_simple_recovery=1 #這個變量用于在 MySQL 重新開機或啟動的時候尋找 GTIDs 過程中，控制 binlog 如何周遊的算法
EOF mysqld \
--defaults-file=/mysql/data/3307/my.cnf \
--initialize --user=mysql \
--basedir=/mysql/app/mysql \
--datadir=/mysql/data/3307/data cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3307
ln -sf /etc/init.d/mysql_3307 /usr/lib/systemd/system/mysql_3307
#修改啟動腳本##
vi /etc/init.d/mysql_3307
basedir=/mysql/app/mysql
datadir=/mysql/data/3307/data
mysqld_pid_file_path=/mysql/data/3307/mysql.pid
#在$bindir/mysqld_safe 後面添加，注意 --defaults-file 要放在第一個
--defaults-file="/mysql/data/3307/my.cnf"
systemctl daemon-reload #啟動、MySQL服務
systemctl start mysql_3307
#擷取MySQL臨時密碼
Passwd=`cat /mysql/log/3307/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'`
echo $Passwd
#通過本地 socket 登入、修改密碼
mysql -uroot -p$Passwd -S /mysql/data/3307/mysql.sock
alter user 'root'@'localhost' identified by "123456";
#允許遠端登入
grant all privileges on *.* to root@'%' identified by '123456';
#重新整理權限
flush privileges; create database yzjtestdb;
drop table yzjtestdb.data01;
create table yzjtestdb.data01(
id int not null primary key auto_increment,
name varchar(60),
age int);
use yzjtestdb;
drop procedure sp_data01_2;
#臨時指定結束符為 //，因為存儲過程中語句以;結束
delimiter //
create procedure sp_data01_10()
begin
declare n int;
set n=0;
repeat
insert
into
yzjtestdb.data01(name,age)
values(concat('samkeji1',n),22);
commit;
set n=n+1;
until n>=10 end repeat;
end
//
delimiter ; #恢複結束符
#開啟事件排程器
set global event_scheduler =1;
#每5秒執行一次存儲過程，插入10條資料
create event if not exists e_data01_10
on schedule every 5 second
on completion preserve
do
call sp_data01_10();
#5小時清空表
create event if not exists e_data01_5_truncate
on schedule every 5 hour
on completion preserve
do
truncate table yzjtestdb.data01;
#當為on completion preserve 的時候,當event到期了,event會被disable,但是該event還是會存在
#當為on completion not preserve的時候,當event到期的時候,該event會被自動删除掉.
alter event e_data01_10 on completion preserve enable;
alter event e_data01_5_truncate on completion preserve enable;
#停止存儲過程
#alter event yzjtestdb.e_data01_10 ON COMPLETION PRESERVE DISABLE; mysql> select count(*) from yzjtestdb.data01;
+----------+
| count(*) |
+----------+
| 44 |
+----------+
1 row in set (0.01 sec) 從庫配置檔案，主要是 ip 位址，server_id，relay_log 的配置和主庫不同，其餘配置和主庫相同。針對 GTIP 也是有兩個參數必須設定：
#主機名和端口号作為目錄名的一部分
HostName=`hostname`
MySql_Port=3307
#IP位址
Ip=192.168.1.37
#master server_id 要和 slave 不一樣
Server_Id=2
cat > /mysql/data/$MySql_Port/my.cnf <<-EOF
#------------------------------------
#用戶端設定
#------------------------------------
[client]
port=$MySql_Port
socket =/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8
#------------------------------------
#mysql連接配接工具設定
#------------------------------------
[mysql]
prompt="\\u@\\h : \\d\\r:\\m:\\s>" #登入時顯示登入的使用者名、伺服器位址、預設資料庫名、目前時間
auto-rehash #讀取表資訊和列資訊，可以在連上終端後開啟tab補齊功能。
default-character-set=utf8 #預設字元集
#------------------------------------
#基本設定
#------------------------------------
[mysqld]
bind_address=0.0.0.0 #監聽本地所有位址
port=$MySql_Port #端口号
user=mysql #使用者
basedir=/mysql/app/mysql #安裝路徑
datadir=/mysql/data/$MySql_Port/data #MySQL資料目錄
socket=/mysql/data/$MySql_Port/mysql.sock #用于本地連接配接的socket檔案目錄
pid-file=/mysql/data/$MySql_Port/mysql.pid #程序ID檔案的目錄。
character-set-server=utf8 #預設字元集
#------------------------------------
#log setting 日志設定
#------------------------------------
long_query_time=10 #慢查詢時間，超過 10 秒則認為是慢查詢
slow_query_log=ON #啟用慢查詢日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查詢日志目錄
log_queries_not_using_indexes=1 #記錄未使用索引的語句
log_slow_admin_statements=1 #慢查詢也記錄那些慢的optimize table，analyze table和alter table語句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log #錯誤日志目錄
#------------------------------------
#master modify parameter 主庫複制更改參數
#------------------------------------
server_id=$Server_Id #master和slave server_id 需要不同
#二進制日志參數配置
log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目錄
log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引檔案的位置
binlog_format=row #行模式複制，預設是 row
binlog_rows_query_log_events=on #在 row 模式下，開啟該參數,可以将把 sql 語句列印到 binlog 日志裡面，友善檢視
binlog_cache_size=1M #事務能夠使用的最大 binlog 緩存空間。
max_binlog_size=2048M #binlog 檔案最大空間，達到該大小時切分檔案
expire_logs_days=7 #設定自動删除 binlog 檔案的天數。
sync_binlog=1 #表示每次事務的 binlog 都會fsync持久化到磁盤，MySQL 5.7.7 之後預設為1，之前的版本預設為0
innodb_flush_log_at_trx_commit=1 #表示每次事務的 redo log 都直接持久化到磁盤，預設值為1
#------------------------------------
#slave parameter 從庫參數
#------------------------------------
relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog #中繼日志目錄
relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index #中繼日志索引目錄
log_slave_updates=1 #從庫從主庫複制的資料會寫入從庫 binlog 日志檔案裡，預設是不寫入
read_only=1 #從庫隻讀
relay_log_purge=1 #自動清空不再需要中繼日志
# 并行複制參數
#主庫上面怎麼并行，從庫上面就怎麼回放，基于邏輯時鐘的概念
#binlog 會記錄組送出的資訊，從回放的時候就可以知道哪些事務是一組裡面的，
#一組裡面的就丢到不同線程去回放，不是一組裡的就等待，以此來提升并行度
slave-parallel-type=LOGICAL_CLOCK
#多線程複制
slave-parallel-workers=4
#slave 上commit 的順序保持一緻，否則可能會有間隙鎖産生
slave-preserve-commit_order=1
master_info_repository=TABLE #預設每接收到10000個事件，寫一次master-info，預設是寫在檔案中的
#修改 relay_log_info_repository 的好處
#1.relay.info 明文存儲不安全，把 relay.info 中的資訊記錄在 table 中相對安全。
#2.可以避免 relay.info 更新不及時，slave 重新開機後導緻的主從複制出錯。
relay_log_info_repository=TABLE #将回放資訊記錄在 slave_relay_log_info 表中，預設是記錄在 relay-info.log 檔案中
relay_log_recovery=1 #當slave重新開機時，将所有 relay log 删除，通過 sql 線程重放的位置點去重新拉日志
#------------------------------------
#Replication Filter 從庫複制過濾參數
#------------------------------------
#（過濾某個資料庫、資料庫.表）
#replicate_do_db=yzjtestdb
#replicate_wild_do_table=yzjtestdb.%
#replicate_do_table=yzjtestdb.yzjtest_yg
#replicate_wild_do_table=yzjtestdb.yzjtest_yg
#------------------------------------
#GTID Settings GTID 同步複制設定
#------------------------------------
gtid_mode=on #開啟GTID同步
enforce_gtid_consistency=on #強制事務一緻，確定 GTID 的安全，在事務中就不能建立和删除臨時表
binlog_gtid_simple_recovery=1 #這個變量用于在 MySQL 重新開機或啟動的時候尋找 GTIDs 過程中，控制 binlog 如何周遊的算法
EOF mysqld \
--defaults-file=/mysql/data/3307/my.cnf \
--initialize --user=mysql \
--basedir=/mysql/app/mysql \
--datadir=/mysql/data/3307/data cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3307
ln -sf /etc/init.d/mysql_3307 /usr/lib/systemd/system/mysql_3307
#修改啟動腳本##
vi /etc/init.d/mysql_3307
basedir=/mysql/app/mysql
datadir=/mysql/data/3307/data
mysqld_pid_file_path=/mysql/data/3307/mysql.pid
#在$bindir/mysqld_safe 後面添加，注意 --defaults-file 要放在第一個
--defaults-file="/mysql/data/3307/my.cnf"
systemctl daemon-reload #啟動、MySQL服務
systemctl start mysql_3307
#擷取MySQL臨時密碼
Passwd=`cat /mysql/log/3307/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'`
echo $Passwd
#通過本地 socket 登入、修改密碼
mysql -uroot -p$Passwd -S /mysql/data/3307/mysql.sock
alter user 'root'@'localhost' identified by "123456";
#允許遠端登入
grant all privileges on *.* to root@'%' identified by '123456';
#重新整理權限
flush privileges; create user 'repuser'@'%' identified by 'repuser123';
grant replication slave on *.* to 'repuser'@'%'; 主庫通過 xtrabackup 備份資料庫
xtrabackup 是 由 percona 開源的免費資料庫熱備份軟體，它能對 InnoDB 資料庫和 XtraDB 存儲引擎的資料庫非阻塞地備份（對于 MyISAM 的備份同樣需要加表鎖）。mysqldump 備份方式是采用的邏輯備份，其最大的缺陷是備份和恢複速度較慢，如果資料庫大于 50G，mysqldump 備份就不太适合。xtrabackup 是基于實體備份的，速度快，這次我們改為使用 xtrabackup 來做資料的同步。
安裝 xtrabackup：
#添加 yum 源
cat <<eof>>/etc/yum.repos.d/percona.repo
[percona]
name = Percona
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/\$releasever/RPMS/\$basearch
enabled = 1
gpgcheck = 0
[epel]
name=epelrepo
baseurl=https://mirrors.aliyun.com/epel/\$releasever/\$basearch
gpgcheck=0
enable=1
eof
#安裝
yum -y install percona-xtrabackup-24 使用 xtrabackup 在主庫上做一次全備：
xtrabackup \
--defaults-file=/mysql/data/3307/my.cnf \
--user=root \
--password=123456 \
--backup \
--socket=/mysql/data/3307/mysql.sock \
--parallel=2 \
--stream=tar \
--target-dir=/mysql/backup/3307/xtrabackup/target_dir|gzip > /mysql/backup/3307/xtrabackup/xtrabackup_full_`date +'%Y-%m-%d-%s'`.tar.gz 檢視主庫上備份的檔案：
[root@mysql-master ~]# ls -l /mysql/backup/3307/xtrabackup/
總用量 652
drwxr-xr-x. 2 mysql mysql 6 9月 4 23:58 target_dir
-rw-r--r--. 1 root root 665026 9月 5 00:26 xtrabackup_full_2021-09-05-1630772760.tar.gz scp -r /mysql/backup/3307/xtrabackup/xtrabackup_full_*.tar.gz 192.168.1.37:/mysql/backup/3307/xtrabackup/ 先停止從庫服務：
systemctl stop mysql_3307 清空 target_dir 目錄下的所有檔案（如果有）：
rm -rf /mysql/backup/3307/xtrabackup/target_dir/* 删除所有資料檔案（如果有）：
rm -rf /mysql/data/3307/data/* 從庫從備份檔案恢複資料：
#解壓
tar zxvf /mysql/backup/3307/xtrabackup/*.tar.gz -C /mysql/backup/3307/xtrabackup/target_dir
#準備過程
xtrabackup \
--defaults-file=/mysql/data/3307/my.cnf \
--prepare \
--target-dir=/mysql/backup/3307/xtrabackup/target_dir \
--parallel=2
#恢複過程
rsync -avrP /mysql/backup/3307/xtrabackup/target_dir/* --exclude='xtrabackup_*' /mysql/data/3307/data/
#修改目錄權限
chown -R mysql:mysql /mysql 重新啟動從庫：
systemctl start mysql_3307 mysql> select * from yzjtestdb.data01 limit 10;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | samkeji16 | 22 |
| 2 | samkeji17 | 22 |
| 3 | samkeji18 | 22 |
| 4 | samkeji19 | 22 |
| 5 | samkeji10 | 22 |
| 6 | samkeji11 | 22 |
| 7 | samkeji12 | 22 |
| 8 | samkeji13 | 22 |
| 9 | samkeji14 | 22 |
| 10 | samkeji15 | 22 |
+----+-----------+------+
10 rows in set (0.01 sec) 擷取 GTID 記錄：
cat /mysql/backup/3307/xtrabackup/target_dir/xtrabackup_info|grep "binlog_pos" GLOBAL.GTID_PURGED 參數指定跳過我們剛剛查到的 GITD 之前的資料，因為這些資料已經通過 xtrabackup 導入從庫中了。
stop slave;
reset master;
set @MYSQLDUMP_TEMP_LOG_BIN=@@SESSION.SQL_LOG_BIN; # 将目前會話 SQL_LOG_BIN 的參數的變量賦予 MYSQLDUMP_TEMP_LOG_BIN 記錄。
set @@SESSION.SQL_LOG_BIN=0; # 當還原的時候不記錄binlog日志
set @@GLOBAL.GTID_PURGED='9b835740-0d9a-11ec-a279-0050568b1bca:1-1583'; # GTID_PURGED 跳過這條GTID的事務進行操作，隻對下一條事務開始恢複
set @@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN; # 還原 SQL_LOG_BIN 的參數的變量。
change master to
master_host='192.168.1.36',
master_port=3307,
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
start slave; mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.36
Master_User: repuser
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-master-binlog.000002
Read_Master_Log_Pos: 1489366
Relay_Log_File: mysql-slave-relaylog.000002
Relay_Log_Pos: 886684
Relay_Master_Log_File: mysql-master-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1489366
Relay_Log_Space: 886896
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9b835740-0d9a-11ec-a279-0050568b1bca
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
#代表的是 slave 已經從 master 中複制了哪些事務過來
Retrieved_Gtid_Set: 9b835740-0d9a-11ec-a279-0050568b1bca:1584-3903
#slave 已經向自己的 binlog 中寫入了哪些 gtid
Executed_Gtid_Set: 9b835740-0d9a-11ec-a279-0050568b1bca:1-3903
#開啟 gtid 時是否自動擷取 binlog 坐标。1 表示開啟，這是 gtid 複制的預設值
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec) 在主庫或者從庫上使用 show master status 指令可以檢視同步的 gtid 和 binlog 點位。
mysql> show master status;
+---------------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-slave-binlog.000001 | 1159354 | | | 9b835740-0d9a-11ec-a279-0050568b1bca:1-4733 |
+---------------------------+----------+--------------+------------------+---------------------------------------------+ select count(*) from yzjtestdb.data01; 參考資料
- MySQL複制進階
- 深入MySQL複制(一)
- 深入MySQL複制(二)：基于GTID複制
- Mysqldump參數大全
- mysqldump備份時加single-transaction會不會加鎖
- MySQL運維之 binlog_gtid_simple_recovery(GTID)