MySQL主從級聯(lián)復(fù)制詳解(一主一從一級聯(lián))
更新時(shí)間：2025年08月14日 11:00:38 作者：大新屋
本文介紹三臺MySQL服務(wù)器主從復(fù)制配置流程：主庫設(shè)置server_id、log_bin并創(chuàng)建復(fù)制用戶,備份數(shù)據(jù)后同步至從庫slave01；slave01再作為主庫同步至級聯(lián)從庫slave02,均需鎖表確保數(shù)據(jù)一致性,最后通過測試驗(yàn)證同步狀態(tài)
提示：三臺MySQL數(shù)據(jù)庫已安裝完成，本教程不講解MySQL數(shù)據(jù)庫安裝。
主機(jī)規(guī)劃
| 主機(jī)類型 | 主機(jī)名稱 | 主機(jī)IP | 操作系統(tǒng)版本 | MySQL版本 | 備注 |
|---|---|---|---|---|---|
| MySQL主庫 | master | 192.168.91.61 | CentOS 7.9 | MySQL 8.0.28 | |
| MySQL從庫 | slave01 | 192.168.91.62 | CentOS 7.9 | MySQL 8.0.28 | |
| MySQL級聯(lián)從庫 | slave02 | 192.168.91.63 | CentOS 7.9 | MySQL 8.0.28 |
一、Master主庫配置
1、主庫配置MySQL數(shù)據(jù)庫配置文件，添加server_id和log_bin參數(shù)即可開啟主從復(fù)制的主庫功能
[root@master ~]# vim /etc/my.cnf [client] socket=/usr/local/mysql/data/mysql.sock [mysqld] ### 開啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它從庫的server_id重復(fù) server_id=10 log_bin=/usr/local/mysql/logs/mysql-bin ### 設(shè)置自動清除binlog二進(jìn)制日志，保留最近7天bin-log日志，默認(rèn)保留最近30天，單位秒 binlog_expire_logs_seconds=604800 basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock mysqlx_socket=/usr/local/mysql/data/mysqlx.sock pid_file=/usr/local/mysql/data/mysqld.pid log_error=/usr/local/mysql/logs/error.log log-bin=/usr/local/mysql/logs/mysql.bin character-set-server=utf8mb4 max_connections=500 max_connect_errors=1000 port=3306 mysqlx_port=33060
2、重啟主庫MySQL數(shù)據(jù)庫
/etc/init.d/mysqld stop /etc/init.d/mysqld start
3、主庫創(chuàng)建主從復(fù)制驗(yàn)證用戶
mysql -uroot -p'123456' create user 'sync'@'%' identified with 'mysql_native_password' by '123456'; grant replication slave on *.* to 'sync'@'%'; flush privileges;
4、主庫查看當(dāng)前二進(jìn)制日志文件名和位置
注意：查看主庫二進(jìn)制日志文件名和位置之前，需要把主庫MySQL數(shù)據(jù)庫進(jìn)行鎖表，防止有寫入操作，以免影響二進(jìn)制日志文件名和位置的同步信息
mysql -uroot -p'123456'
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql.000001
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
5、主庫備份數(shù)據(jù)庫并遠(yuǎn)程復(fù)制一份給salve01從庫
注意：如果MySQL數(shù)據(jù)庫過大，可以考慮使用物理備份XtraBackup工具。
mysqldump -uroot -p'123456' -A >/mnt/mysql_bak.sql scp -rp /mnt/mysql_bak.sql root@192.168.91.62:/mnt/
二、Slave01從庫配置
1、slave01從庫配置MySQL數(shù)據(jù)庫配置文件，添加log_bin、relay_log、relay_log_index、log_replica_updates參數(shù)
[root@slave01 ~]# cat /etc/my.cnf [client] socket=/usr/local/mysql/data/mysql.sock [mysqld] ### 開啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它主庫或從庫的server_id重復(fù) server_id=20 log_bin=/usr/local/mysql/logs/mysql-bin ### 設(shè)置自動清除binlog二進(jìn)制日志，保留最近7天bin-log日志，默認(rèn)保留最近30天，單位秒 binlog_expire_logs_seconds=604800 ### 開啟從庫記錄中繼日志功能,命名方式(主機(jī)名稱-relay-bin)，命名可自定義，默認(rèn)開啟 #relay_log=/usr/local/mysql/logs/slave01-relay-bin #relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index ### 啟動副本更新,從庫從主庫接收到log-bin更新操作由從庫SQL線程執(zhí)行更新寫入到從庫自己的log-bin日志中，默認(rèn)開啟 #log_replica_updates=1 #### 設(shè)置只允許普通用戶寫入，只針對普通用戶有效（擁有insert、update、delete、drop等權(quán)限），但對于超級用戶無效（管理員root或者all privileges所有權(quán)限用戶），默認(rèn)關(guān)閉 read_only=1 #### 設(shè)置是否禁止超級管理員寫入，針對所有數(shù)據(jù)庫用戶，包括超級用戶和普通用戶，默認(rèn)關(guān)閉 super_read_only=1 basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock mysqlx_socket=/usr/local/mysql/data/mysqlx.sock pid_file=/usr/local/mysql/data/mysqld.pid log_error=/usr/local/mysql/logs/error.log log-bin=/usr/local/mysql/logs/mysql.bin character-set-server=utf8mb4 max_connections=500 max_connect_errors=1000 port=3306 mysqlx_port=33060
2、slave01從庫導(dǎo)入master主庫的備份庫文件并重啟MySQL數(shù)據(jù)庫
mysql -uroot -p'123456' mysql>source /mnt/mysql_bak.sql /etc/init.d/mysqld stop /etc/init.d/mysqld start
3、開啟slave01從庫同步master主庫
mysql -uroot -p'123456' CHANGE MASTER TO MASTER_HOST='192.168.91.61', MASTER_PORT=3306, MASTER_USER='sync', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=157; mysql> start slave; mysql> show slave status\G;
4、slave01從庫查看當(dāng)前二進(jìn)制日志文件名和位置
注意：查看slave01從庫二進(jìn)制日志文件名和位置之前，需要把slave01從庫MySQL數(shù)據(jù)庫鎖表，防止有寫入操作，以免影響二進(jìn)制日志文件名和位置的同步信息
mysql -uroot -p'123456'
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql.000002
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
5、slave01從庫備份數(shù)據(jù)庫并遠(yuǎn)程復(fù)制一份給salve02級聯(lián)從庫
注意：如果MySQL數(shù)據(jù)庫過大，可以考慮使用物理備份XtraBackup工具。
mysqldump -uroot -p'123456' -A >/mnt/mysql_slave_bak.sql scp -rp /mnt/mysql_slave_bak.sql root@192.168.91.63:/mnt/
三、Slave02級聯(lián)從庫配置
1、slave01從庫配置MySQL數(shù)據(jù)庫配置文件，添加log_bin、relay_log、relay_log_index、log_replica_updates參數(shù)
[root@slave01 ~]# cat /etc/my.cnf [client] socket=/usr/local/mysql/data/mysql.sock [mysqld] ### 開啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它主庫或從庫的server_id重復(fù) server_id=30 log_bin=/usr/local/mysql/logs/mysql-bin ### 設(shè)置自動清除binlog二進(jìn)制日志，保留最近7天bin-log日志，默認(rèn)保留最近30天，單位秒 binlog_expire_logs_seconds=604800 ### 開啟從庫記錄中繼日志功能,命名方式(主機(jī)名稱-relay-bin)，命名可自定義，默認(rèn)開啟 #relay_log=/usr/local/mysql/logs/slave01-relay-bin #relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index ### 啟動副本更新,從庫從主庫接收到log-bin更新操作由從庫SQL線程執(zhí)行更新寫入到從庫自己的log-bin日志中，默認(rèn)開啟 #log_replica_updates=1 #### 設(shè)置只允許普通用戶寫入，只針對普通用戶有效（擁有insert、update、delete、drop等權(quán)限），但對于超級用戶無效（管理員root或者all privileges所有權(quán)限用戶），默認(rèn)關(guān)閉 read_only=1 #### 設(shè)置是否禁止超級管理員寫入，針對所有數(shù)據(jù)庫用戶，包括超級用戶和普通用戶，默認(rèn)關(guān)閉 super_read_only=1 basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock mysqlx_socket=/usr/local/mysql/data/mysqlx.sock pid_file=/usr/local/mysql/data/mysqld.pid log_error=/usr/local/mysql/logs/error.log log-bin=/usr/local/mysql/logs/mysql.bin character-set-server=utf8mb4 max_connections=500 max_connect_errors=1000 port=3306 mysqlx_port=33060
2、slave02級聯(lián)從庫導(dǎo)入slave01從庫的備份庫文件并重啟MySQL數(shù)據(jù)庫
mysql -uroot -p'123456' mysql> source /mnt/mysql_slave_bak.sql /etc/init.d/mysqld stop /etc/init.d/mysqld start
3、開啟slave01從庫同步master主庫
mysql -uroot -p'123456' CHANGE MASTER TO MASTER_HOST='192.168.91.62', MASTER_PORT=3306, MASTER_USER='sync', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql.000002', MASTER_LOG_POS=157; mysql> start slave; mysql> show slave status\G;
四、測試
1、master主庫創(chuàng)建測試數(shù)據(jù)，查看slave01從庫同步狀態(tài)，查看slave02級聯(lián)從庫同步狀態(tài)
### master主庫寫入測試數(shù)據(jù) mysql -uroot -p'123456' create database student; create table student.test (id int(11),name varchar(25)); insert into student.test values(1,'xiaoming'); insert into student.test values(2,'zhangshang'); insert into student.test values(3,'lishi'); select * from student.test; ### slave01從庫查看主從同步狀態(tài) mysql -uroot -p'123456' select * from student.test; show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 ### slave02級聯(lián)從庫查看主從同步狀態(tài) mysql -uroot -p'123456' select * from student.test; show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
2、master主庫大批量寫入測試數(shù)據(jù)，查看slave01從庫同步狀態(tài)，查看slave02級聯(lián)從庫同步狀態(tài)
### master主庫在操作系統(tǒng)使用shell批量寫入測試數(shù)據(jù)
for i in {4..1000}; do mysql -uroot -p'123456' -e "insert into student.test(id,name) values('$i','AAA');";done
### slave01從庫查看主從同步狀態(tài)
mysql -uroot -p'123456'
mysql> select * from student.test;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
### slave02級聯(lián)從庫查看主從同步狀態(tài)
mysql -uroot -p'123456'
mysql> select * from student.test;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
mysql 連接出現(xiàn)Public Key Retrieval is n
在MySQL連接中出現(xiàn)“Public Key Retrieval is not allowed”錯(cuò)誤,通常是因?yàn)樵谑褂冒踩捉幼謱樱⊿SL）連接時(shí)遇到了問題,本文就來介紹一下解決方法,感興趣的可以了解一下2024-03-03
MySQL5.7中自增主鍵在重啟后會重置,而MySQL8.0中通過重做日志持久化自增變量,避免重啟后主鍵沖突,本文介紹MySQL?8.0自增變量的持久化問題小結(jié),感興趣的朋友一起看看吧2024-11-11
這篇文章主要給大家介紹了關(guān)于mysql如何利用覆蓋索引避免回表優(yōu)化查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場景盤點(diǎn)
這篇文章主要為大家介紹了MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場景盤點(diǎn),有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-11-11
MySQL如何運(yùn)行sql文件圖文詳解（以Navicat做演示）
Navicat是MySQL非常好用的可視化管理工具,功能非常強(qiáng)大,能滿足我們?nèi)粘?shù)據(jù)庫開發(fā)的所有需求,這篇文章主要給大家介紹了關(guān)于MySQL如何運(yùn)行sql文件（以Navicat做演示）的相關(guān)資料,需要的朋友可以參考下2024-08-08
MySQL數(shù)據(jù)類型與表操作全指南(?從基礎(chǔ)到高級實(shí)踐)
本文詳解MySQL數(shù)據(jù)類型分類（數(shù)值、日期/時(shí)間、字符串）及表操作（創(chuàng)建、修改、維護(hù)）,涵蓋優(yōu)化技巧如數(shù)據(jù)類型選擇、備份、分區(qū),強(qiáng)調(diào)規(guī)范設(shè)計(jì)與實(shí)際應(yīng)用結(jié)合,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友一起看看吧2025-08-08
MySQL中聚簇索引與非聚簇索引的實(shí)現(xiàn)
MySQL數(shù)據(jù)庫中,聚簇索引和非聚簇索引是提高查詢效率的關(guān)鍵,聚簇索引決定數(shù)據(jù)的物理存儲順序,通常由主鍵或UNIQUE索引構(gòu)成,非聚簇索引則通過指針定位數(shù)據(jù)行,適合訪問數(shù)據(jù)子集,下面就來具體介紹一下,感興趣的可以了解一下2024-09-09
這篇文章主要給大家介紹了關(guān)于MySQL磁盤碎片整理的相關(guān)資料,為什么數(shù)據(jù)庫會產(chǎn)生碎片,以及如何清理磁盤碎片，還有一些清理磁盤碎片的注意事項(xiàng),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了mysql更改引擎(InnoDB,MyISAM)的方法,實(shí)例講述了比較常見的幾種更改引擎的方法,非常具有實(shí)用價(jià)值,需要的朋友可以參考下2014-11-11
MySQL與PHP的基礎(chǔ)與應(yīng)用專題之自連接
MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，本系列將帶你掌握php與mysql的基礎(chǔ)應(yīng)用，本篇從自連接開始2022-02-02