MySQL 主從複製原理與實踐詳解

本文實例講述了MySQL 主從複製原理與實踐。分享給大家供大家參考，具體如下：

簡介

MySQL 的主從複製又叫 Replication、AB 複製。至少需要兩個 MySQL 服務（可以是同一臺機器，也可以是不同機器之間進行）。

比如A服務器做主服務器，B服務器做從服務器，在A服務器上進行數據的更新，通過 binlog 日誌記錄同步到B服務器上，並重新執行同步過來的 binlog 數據，從而達到兩臺服務器數據一致。

MySQL 數據庫的主從複製方案，與使用 scp/rsync 等命令進行的文件級別複製類似，都是數據的遠程傳輸。

只不過 MySQL 的主從複製是其自帶的功能，無需藉助第三方工具，而且MySQL的主從複製並不是數據庫磁盤上的文件直接拷貝，而是通過邏輯的 binlog 日誌複製到要同步的服務器本地，然後由本地的線程讀取日誌裡面的 SQL 語句，重新應用到 MySQL 數據庫中。

作用

1.  可以實時災備，用於故障切換；

2.  讀寫分離，提供查詢服務，實現負載均衡；

3.  數據熱備，避免影響業務。

原理

1、主服務器MySQL服務將所有的寫操作記錄在 binlog 日誌中，並生成 log dump 線程，將 binlog 日誌傳給從服務器MySQL服務的 I/O 線程。

2、從服務器MySQL服務生成兩個線程，一個是 I/O 線程，另一個是 SQL 線程。

3、從庫 I/O 線程去請求主庫的 binlog 日誌，並將 binlog 日誌中的文件寫入 relaylog（中繼日誌）中。

4、從庫的 SQL 線程會讀取 relaylog 中的內容，並解析成具體的操作，來實現主從的操作一致，達到最終兩個數據庫數據一致的目的。

 注意點： - 主從複製是異步的邏輯的 SQL 語句級的複製； - 複製時，主庫有一個 I/O 線程，從庫有兩個線程，及 I/O 和 SQL 線程； - 實現主從複製的必要條件是主庫要開啟記錄 binlog 的功能； - 作為複製的所有 MySQL 節點的 server-id 都不能相同； - binlog 文件只記錄對數據內容有更改的 SQL 語句，不記錄任何查詢語句。

形式

一主一從

主主複製

一主多從

多主一從（5.7後開始支持）

聯級複製

實踐

需求

實現一主一從複製模式，同一臺主機兩個MySQL實例

環境

 Mac：10.15.1 Docker：2.0.0.3//採取docker安裝MySQL主要是管理維護方便、獨立IP、啟動秒級 MySQL-master：5.7.29//主服務器 MySQL-master IP：172.17.0.3 MySQL-slave：5.7.29//從服務器 MySQL-slave IP：172.17.0.4

步驟

第一步：準備好兩臺MySQL服務器

mysql-master（主服務器）：

 1. 創建目錄結構： master/conf、master/data、master/logs 2. 啟動運行實例容器： docker run --name mysql-master > -p 3310:3306 > -v ~/docker/master/conf:/etc/mysql/conf.d > -v ~/docker/master/data:/var/lib/mysql > -v ~/docker/master/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進入容器 docker exec -it mysql-master bash 4. 登錄MySQL mysql -uroot -p

mysql-slave（從服務器）：

 1. 創建目錄結構： slave/conf、slave/data、slave/logs 2. 啟動運行實例容器： docker run --name mysql-slave > -p 3310:3306 > -v ~/docker/slave/conf:/etc/mysql/conf.d > -v ~/docker/slave/data:/var/lib/mysql > -v ~/docker/slave/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進入容器 docker exec -it mysql-slave bash 4. 登錄MySQL mysql -uroot -p

第二步：配置文件（my.cnf）修改

 主服務器： [mysqld] port = 3306 server-id = 1 #要同步的數據庫 binlog-do-db = school #要生成二進制日誌文件 主服務器一定要開啟 log-bin = mysql-bin 重啟MySQL: docker restart mysql-master

 從服務器： [mysqld] port = 3306 server-id = 2 #要同步的數據庫 binlog-do-db = school #要生成二進制日誌文件（從服務器可選） log-bin = mysql-bin 重啟MySQL: docker restart mysql-slave

第三步：創建主服務器複製用戶及相關權限

 create user 'slave'@'%' identified by '123456';//創建用戶 grant replication slave,replication client on *.* to 'slave'@'%';//設置用戶權限 flush privileges;//刷新權限 show grants for 'slave'@'%';//查看用戶權限

第四步：數據備份同步

 1. 登錄master，執行鎖表操作 mysql -uroot -p FLUSH TABLES WITH READ LOCK; 2. 將master中需要同步的db的數據dump出來 mysqldump -uroot -p school > school.dump 3. 將數據導入slave mysql -uroot -h172.17.0.4 -p school<school.dump 4. 解鎖master UNLOCK TABLES;

第五步：主服務器複製狀態

 1. 創建新數據表及增加數據 create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4; insert into user(name) values(222); 2. 主服務器 binlog 記錄狀態 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 769 | school | | | +------------------+----------+--------------+------------------+-------------------+

第六步：從服務器開始複製

 1. 連接主服務器及設置複製的起始節點 mysql> change master to master_host='172.17.0.3', -> master_port=3306, -> master_user='slave', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=769; 2. 開始複製 start slave; 3. 查看複製狀態 mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.0.3 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 961 Relay_Log_File: 87dc5224655d-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //表示I/O線程讀取成功 Slave_SQL_Running: Yes //表示SQL線程執行成功 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: 961 Relay_Log_Space: 892 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: 45540733-4e0c-11ea-b0ac-0242ac110003 Master_Info_File: /var/lib/mysql/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: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 4. 查看數據表數據 mysql> show create table userG *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | 222 | +----+------+

 複製的控制相關命令： stop salve //停止slave連路 reset slave //重置slave連路 start slave //開啟slave連路 stop master //停止master連路 reset master //重置master連路 start master //開啟master連路

第七步：主從服務器的進程查看

mysql-master：

mysql-slave：