MySQL數據庫復制

文章目錄

MySQL數據庫復制
- 一、復制的原理
- 二、復制的搭建
- - 1.編輯配置文件
  - 2.在主庫上創建復制的用戶
  - 3.獲取主庫的備份
  - 4.基于從庫的恢復
  - 5.建立主從復制
  - 6.開啟主從復制
  - 7.查看主從復制狀態

MySQL數據庫復制

MySQL作為非常流行的數據庫，支撐它如此出彩的因素主要有兩個，InnoDB存儲引擎和復制。InnoDB存儲引擎支持事務、行級別鎖、MVCC。基于此，MySQL能實現高并發。而復制可以將主庫的數據同步到另一個實例，基于此我們可以實現讀寫分離和數據容災。

一、復制的原理

MySQL主從復制的核心原理是通過二進制日志（binlog）實現的，熟悉Oracle的同學會問：MySQL不是有redo log嗎？為什么不直接基于redo log來實現呢？
實際上redo log是InnoDB存儲引擎獨有的，作為一個支持可插撥存儲引擎的數據庫，MySQL不僅支持InnoDB存儲引擎，還支持MyISAM、CSV、Memory等存儲引擎，對于這些存儲引擎的操作同樣需要持久化到binlog中。

在這里插入圖片描述

MySQL的復制主要涉及以下3個線程：

主庫 binlog dump 線程
從庫 I/O 線程
從庫 SQL 線程

MySQL的復制其運作流程大致如下：

從庫執行完START SLAVE命令后，會創建兩個線程：I/O線程和SQL線程。
I/O線程會建立一個到主庫的連接，相應地，主庫會創建一個binlog dump線程來響應這個連
接的請求。此時，對于主庫來說，從庫的1/O線程就是一個普通的客戶端。
I/O線程首先告訴主庫應該從何處開始發送二進制日志事件。
主庫的binlog dump線程開始從指定位置點讀取二進制日志事件，并發送給I/O線程。
I/O線程接收到二進制日志事件后，會將其寫入relay log。
SQL線程讀取relay log中的二進制日志事件，然后進行重放。

二、復制的搭建

實驗版本為 MySQL 8.0.41，部署MySQL異步復制，機器環境如下, 需要分別提前安裝數據庫軟件以及初始化好數據庫實例，可以參考我之前的《MySQL數據庫安裝》的文章

角色	IP
主庫	192.168.50.121
從庫	192.168.50.122

1.編輯配置文件

主庫編輯/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 1
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

從庫/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 2
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

這里給出的只是搭建復制最簡單的參數:

log-bin 開啟 binlog , 這里的mysql-bin是binlog文件的前綴(8.0之后的版本可以不用配置該數,但會習慣性的進行設置)
server-id 是服務端ID, 在一個復制組內必需全局唯一,有效值為 1~2^32^-1

注意事項:

server-id 支持在線調整,但開啟binlog 需要重啟實例
在MySQL8.0之前,binlog默認是關閉的,不顯示設置log-bin參數,則不開啟binlog。而在MySQL8.0中,binlog默認是開啟的,如果要關才binlog,需要顯示設置skip_log_bin 或 disable_log_bin。

2.在主庫上創建復制的用戶

在MySQL 8.0 之前可以直接使用第二第命令會隱式創建用戶,但官方不推薦這種方式。

create user 'repl'@'192.168.50.122' identified by 'test123';grant replication slave on *.* to 'repl'@'192.168.50.122';

創建用戶后在從庫環境驗證帳號是否能正常登陸

mysql -urepl -ptest123 -h 192.168.50.121

3.獲取主庫的備份

這里使用mysqldump工具進行備份,備份集通過scp命令拷貝到從庫

mysqldump -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock --single-transaction --source-data=2 -E -R --triggers -A > full_backup.sqlscp  full_backup.sql 192.168.50.122:/data

從MySQL 8.0.26開始,開始使用 --source-data 代替之前版本的 --master-data 否則備份文件中會寫入以下信息，影響到后續的導入。

WARNING:–master-data is deprecated and will be removed in a future version.use --source-data instead.

4.基于從庫的恢復

在從庫對備份文件進行導入

mysql -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock < /data/full_backup.sql

5.建立主從復制

在從庫執行 CHANGE MASTER TO命令創建主從關系

change master to
master_host='192.168.50.121',
master_port=3306,
master_user='repl',
master_password='test123',
master_log_file='mysql-bing.000001',
master_log_pos=717;

參數具體含義：

參數	作用
master_host	主庫的主機信息，可以為主機名或IP
master_port	主庫端口，若不指定則默認為3306
master_use	復制的用戶名
master_password	復制用戶的密碼
master_log_file	從庫I/O線程啟動時，應該從主庫的哪個binlog由這個參數決定
master_log_pos	I/O線程確定binlog后,從哪個位置開始讀取二進制日志事件.

我們在備份的時候通過 mysqldump 指定的 --source-data=2 (舊版本用--master-data=2) 他會將備份時的binlog位置信息記錄到備份文件中,內容如下

--
-- Position to start replication or point-in-time recovery from
---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bing.000001', MASTER_LOG_POS=717;

當從庫執行完CHANGE MASTER TO命令后，復制的相關信息會保存在兩張表中：mysqL.slave_master.info和mysql.slave_relay_.log_info

mysql.slave_master_.info主要記錄了兩類信息：

主庫的連接信息，包括主庫的IP、端口、復制用戶和密碼;
I/O線程讀取的主庫binlog的位置點信息。

注意，這個位置點不是實時更新的，它的更新頻率與sync_master_.info參數有關。該參數默認為10000,即I/O線程每寫入10000個事務會更新mysql.slave_.master_info一次。

mysql.slave_relay_log_info主要記錄了以下信息：

SQL線程重放relay log的位置點信息。

事務每次提交時都會更新mysqL.slave._relay._log_info

這兩張表是MySQL5.6引入的。在此之前復制的相關信息保存在master.info(對應mysql.slave_master.info表)和relay-log.info(對應mysql.slave_.relay_.log_info表)文件中，這兩個文件默認位于從庫的數據目錄下。復制的相關信息是保存在文件還是系統表中由master_info_repository參數決定，其中，FILE代表文件，TABLE代表系統表。從MySQL8.0.2開始，該參數的默認值由FILE調整為TABLE。

6.開啟主從復制

在從庫上執行如下以下命令

mysql> start slave;

7.查看主從復制狀態

重點關注 Slave_IO_Running和 Slave_SQL_Running 的結果均為YES代表主從復制搭建成功

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.50.121Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bing.000001Read_Master_Log_Pos: 717Relay_Log_File: mysql02-relay-bin.000003Relay_Log_Pos: 327Relay_Master_Log_File: mysql-bing.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 717Relay_Log_Space: 696Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: cdc9afd5-fcd8-11ef-9d33-000c29ea0d0aMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:1 row in set, 1 warning (0.00 sec)

在上面使用 SHOW SLAVE STATUS 命令時,提示了warning通過 show warnings 可以看到,SHOW SLAVE STATUS 已被棄用, 代用SHOW REPLICA STATUS替代。

實際上在MySQL 8.0.22 開始 start slave 、stop slave 、show slave staus 、show slave hosts 、reset slave 命令都補棄用了,取而代之的是 start replica、stop rplica 、show replica status、show replicas、reset replica。
從MySQL8.O.23開始，CHANGE MASTER TO命令被棄用，取而代之的是CHANGE REPLICATION SOURCE TO
從MySQL8.0.26開始，標識符（如系統參數、狀態變量）中的MASTER、SLAVE和MTS(multithreadedslave的縮寫)將分別被SOURCE、REPLICA和MTA(multithreaded applier的縮寫)替換，這一點需要注意。

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在主庫上執行 show processlist 命令，可以看到 ID為23 連接對應的即是 binlog dump線程。

mysql> show processlist\G;*************************** 3. row ***************************Id: 23User: replHost: 192.168.50.122:52016db: NULL
Command: Binlog DumpTime: 152State: Source has sent all binlog to replica; waiting for more updatesInfo: NULL

在從庫是執行 show processlist 命令, 5和6 的連接分別為 I/O線程和SQL線程

mysql>  show processlist\G;
*************************** 1. row ***************************Id: 5User: system userHost: connecting hostdb: NULL
Command: ConnectTime: 226State: Waiting for source to send eventInfo: NULL
*************************** 2. row ***************************Id: 6User: system userHost:db: NULL
Command: QueryTime: 226State: Replica has read all relay log; waiting for more updatesInfo: NULL

至此一個簡單的異步復制環境搭建完畢。