MySQL主從級(jí)聯(lián)復(fù)制詳解(一主一從一級(jí)聯(lián))

更新時(shí)間：2025年08月14日 11:00:38 作者：大新屋

本文介紹三臺(tái)MySQL服務(wù)器主從復(fù)制配置流程：主庫(kù)設(shè)置server_id、log_bin并創(chuàng)建復(fù)制用戶(hù),備份數(shù)據(jù)后同步至從庫(kù)slave01；slave01再作為主庫(kù)同步至級(jí)聯(lián)從庫(kù)slave02,均需鎖表確保數(shù)據(jù)一致性,最后通過(guò)測(cè)試驗(yàn)證同步狀態(tài)

主機(jī)規(guī)劃

主機(jī)類(lèi)型	主機(jī)名稱(chēng)	主機(jī)IP	操作系統(tǒng)版本	MySQL版本
MySQL主庫(kù)	master	192.168.91.61	CentOS 7.9	MySQL 8.0.28
MySQL從庫(kù)	slave01	192.168.91.62	CentOS 7.9	MySQL 8.0.28
MySQL級(jí)聯(lián)從庫(kù)	slave02	192.168.91.63	CentOS 7.9	MySQL 8.0.28

一、Master主庫(kù)配置

1、主庫(kù)配置MySQL數(shù)據(jù)庫(kù)配置文件，添加server_id和log_bin參數(shù)即可開(kāi)啟主從復(fù)制的主庫(kù)功能

[root@master ~]# vim /etc/my.cnf
[client]
socket=/usr/local/mysql/data/mysql.sock

[mysqld]
### 開(kāi)啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它從庫(kù)的server_id重復(fù)
server_id=10
log_bin=/usr/local/mysql/logs/mysql-bin
### 設(shè)置自動(dòng)清除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、重啟主庫(kù)MySQL數(shù)據(jù)庫(kù)

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

3、主庫(kù)創(chuàng)建主從復(fù)制驗(yàn)證用戶(hù)

mysql -uroot -p'123456'

create user 'sync'@'%' identified with 'mysql_native_password' by '123456';
grant replication slave on *.* to 'sync'@'%';
flush privileges;

4、主庫(kù)查看當(dāng)前二進(jìn)制日志文件名和位置

注意：查看主庫(kù)二進(jìn)制日志文件名和位置之前，需要把主庫(kù)MySQL數(shù)據(jù)庫(kù)進(jìn)行鎖表，防止有寫(xiě)入操作，以免影響二進(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、主庫(kù)備份數(shù)據(jù)庫(kù)并遠(yuǎn)程復(fù)制一份給salve01從庫(kù)

注意：如果MySQL數(shù)據(jù)庫(kù)過(guò)大，可以考慮使用物理備份XtraBackup工具。

mysqldump -uroot -p'123456' -A >/mnt/mysql_bak.sql

scp -rp /mnt/mysql_bak.sql root@192.168.91.62:/mnt/

二、Slave01從庫(kù)配置

1、slave01從庫(kù)配置MySQL數(shù)據(jù)庫(kù)配置文件，添加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]
### 開(kāi)啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它主庫(kù)或從庫(kù)的server_id重復(fù)
server_id=20
log_bin=/usr/local/mysql/logs/mysql-bin
### 設(shè)置自動(dòng)清除binlog二進(jìn)制日志，保留最近7天bin-log日志，默認(rèn)保留最近30天，單位秒
binlog_expire_logs_seconds=604800
### 開(kāi)啟從庫(kù)記錄中繼日志功能,命名方式(主機(jī)名稱(chēng)-relay-bin)，命名可自定義，默認(rèn)開(kāi)啟
#relay_log=/usr/local/mysql/logs/slave01-relay-bin
#relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index
### 啟動(dòng)副本更新,從庫(kù)從主庫(kù)接收到log-bin更新操作由從庫(kù)SQL線程執(zhí)行更新寫(xiě)入到從庫(kù)自己的log-bin日志中，默認(rèn)開(kāi)啟
#log_replica_updates=1
#### 設(shè)置只允許普通用戶(hù)寫(xiě)入，只針對(duì)普通用戶(hù)有效（擁有insert、update、delete、drop等權(quán)限），但對(duì)于超級(jí)用戶(hù)無(wú)效（管理員root或者all privileges所有權(quán)限用戶(hù)），默認(rèn)關(guān)閉
read_only=1
#### 設(shè)置是否禁止超級(jí)管理員寫(xiě)入，針對(duì)所有數(shù)據(jù)庫(kù)用戶(hù)，包括超級(jí)用戶(hù)和普通用戶(hù)，默認(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從庫(kù)導(dǎo)入master主庫(kù)的備份庫(kù)文件并重啟MySQL數(shù)據(jù)庫(kù)

mysql -uroot -p'123456'
mysql>source /mnt/mysql_bak.sql

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

3、開(kāi)啟slave01從庫(kù)同步master主庫(kù)

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從庫(kù)查看當(dāng)前二進(jìn)制日志文件名和位置

注意：查看slave01從庫(kù)二進(jìn)制日志文件名和位置之前，需要把slave01從庫(kù)MySQL數(shù)據(jù)庫(kù)鎖表，防止有寫(xiě)入操作，以免影響二進(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從庫(kù)備份數(shù)據(jù)庫(kù)并遠(yuǎn)程復(fù)制一份給salve02級(jí)聯(lián)從庫(kù)

注意：如果MySQL數(shù)據(jù)庫(kù)過(guò)大，可以考慮使用物理備份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級(jí)聯(lián)從庫(kù)配置

1、slave01從庫(kù)配置MySQL數(shù)據(jù)庫(kù)配置文件，添加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]
### 開(kāi)啟主從復(fù)制功能并指定二進(jìn)制日志存放路徑，指定的server_id不能與其它主庫(kù)或從庫(kù)的server_id重復(fù)
server_id=30
log_bin=/usr/local/mysql/logs/mysql-bin
### 設(shè)置自動(dòng)清除binlog二進(jìn)制日志，保留最近7天bin-log日志，默認(rèn)保留最近30天，單位秒
binlog_expire_logs_seconds=604800
### 開(kāi)啟從庫(kù)記錄中繼日志功能,命名方式(主機(jī)名稱(chēng)-relay-bin)，命名可自定義，默認(rèn)開(kāi)啟
#relay_log=/usr/local/mysql/logs/slave01-relay-bin
#relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index
### 啟動(dòng)副本更新,從庫(kù)從主庫(kù)接收到log-bin更新操作由從庫(kù)SQL線程執(zhí)行更新寫(xiě)入到從庫(kù)自己的log-bin日志中，默認(rèn)開(kāi)啟
#log_replica_updates=1
#### 設(shè)置只允許普通用戶(hù)寫(xiě)入，只針對(duì)普通用戶(hù)有效（擁有insert、update、delete、drop等權(quán)限），但對(duì)于超級(jí)用戶(hù)無(wú)效（管理員root或者all privileges所有權(quán)限用戶(hù)），默認(rèn)關(guān)閉
read_only=1
#### 設(shè)置是否禁止超級(jí)管理員寫(xiě)入，針對(duì)所有數(shù)據(jù)庫(kù)用戶(hù)，包括超級(jí)用戶(hù)和普通用戶(hù)，默認(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級(jí)聯(lián)從庫(kù)導(dǎo)入slave01從庫(kù)的備份庫(kù)文件并重啟MySQL數(shù)據(jù)庫(kù)

mysql -uroot -p'123456'
mysql> source /mnt/mysql_slave_bak.sql

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

3、開(kāi)啟slave01從庫(kù)同步master主庫(kù)

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;

四、測(cè)試

1、master主庫(kù)創(chuàng)建測(cè)試數(shù)據(jù)，查看slave01從庫(kù)同步狀態(tài)，查看slave02級(jí)聯(lián)從庫(kù)同步狀態(tài)

### master主庫(kù)寫(xiě)入測(cè)試數(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從庫(kù)查看主從同步狀態(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級(jí)聯(lián)從庫(kù)查看主從同步狀態(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主庫(kù)大批量寫(xiě)入測(cè)試數(shù)據(jù)，查看slave01從庫(kù)同步狀態(tài)，查看slave02級(jí)聯(lián)從庫(kù)同步狀態(tài)

### master主庫(kù)在操作系統(tǒng)使用shell批量寫(xiě)入測(cè)試數(shù)據(jù)
for i in {4..1000}; do mysql -uroot -p'123456' -e "insert into student.test(id,name) values('$i','AAA');";done

### slave01從庫(kù)查看主從同步狀態(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級(jí)聯(lián)從庫(kù)查看主從同步狀態(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