MySQL主从级联复制详解(一主一从一级联)

目录

• 主机规划
• 一、Master主库配置
• 二、Slave01从库配置
• 三、Slave02级联从库配置
• 四、测试
• 总结

提示：三台mysql数据库已安装完成，本教程不讲解MySQL数据库安装。

主机规划

一、Master主库配置

1、主库配置MySQL数据库配置文件，添加server_id和log_bin参数即可开启主从复制的主库功能

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

[mysqld]
###js 开启主从复制功能并指定二进制日志存放路径，指定的server_id不能与其它从库的server_id重复
server_id=10
log_bin=/usr/local/mysql/logs/mysql-bin
### 设置自动清除binlog二进制日志，保留最近7天bin-log日志，默认保留最近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数据库

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

3、主库创建主从复制验证用户

mysql -uroot -p'123456'

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

4、主库查看当前二进制日志文件名和位置

注意：查看主库二进制日志文件名和位置之前，需要把主库MySQL数据库进行锁表，防止有写入操作，以免影响二进制日志文件名和位置的同步信息

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、主库备份数据库并远程复制一份给salve01从库

注意：如果MySQL数据库过大，可以考虑使用物理备份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数据库配置文件，添加log_bin、relaandroidy_log、relay_log_index、log_replica_updates参数

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

[mysqld]
### 开启主从复制功能并指定二进制日志存放路径，指定的server_id不能与其它主库或从库的server_id重复
server_id=20
log_bin=/usr/local/mysql/logs/mysql-bin
### 设置自动清除binlog二进制日志，保留最近7天bin-log日志，默认保留最近30天，单位秒
binlog_expire_logs_seconds=604800
### 开启从库记录中继日志功能,命名方式(主机名称-relay-bin)，命名可自定义，默认开启
#relay_log=/usr/local/mysql/logs/slave01-relay-bin
#relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index
### 启动副本更新,从库从主库接收到log-bin更新操作由从库SQL线程执行更新写入到从库自己的log-bin日志中，默认开启
#log_replica_updates=1
#### 设置只允许普通用户写入，只针对普通用户有效（拥有insert、update、delete、drop等权限），但对于超级用户无效（管理员root或者all privileges所有权限用户），默认关闭
read_only=1
#### 设置是否禁止超级管理员写入，针对所有数据库用户，包括超级用户和普通用户，默认关闭
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从库导入master主库的备份库文件并重启MySQL数据库

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 MASTEpythonR 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 statuandroids\G;

4、slave01从库查看当前二进制日志文件名和位置

注意：查看slave01从库二进制日志文件名和位置之前，需要把slave01从库MySQL数据库锁表，防止有写入操作，以免影响二进制日志文件名和位置的同步信息

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从库备份数据库并远程复制一份给salve02级联从库

注意：如果MySQL数据库过大，可以考虑使用物理备份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级联从库配置

1、slave01从库配置MySQL数据库配置文件，添加log_bin、relay_log、relay_log_index、log_replica_updates参数

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

[mysqld]
### 开启主从复制功能并指定二进制日志存放路径，指定的server_id不能与其它主库或从库的server_id重复
server_id=30
log_bin=/usr/local/mysql/logs/mysql-bin
### 设置自动清除binlog二进制日志，保留最近7天bin-log日志，默认保留最近30天，单位秒
binlog_expire_logs_seconds=604800
### 开启从库记录中继日志功能,命名方式(主机名称-relay-bin)，命名可自定义，默认开启
#relay_log=/usr/local/mysql/logs/slave01-relay-bin
#relay_log_index=/usr/local/mysql/logs/slave01-relay-bin.index
### 启动副本更新,从库从主库接www.devze.com收到log-bin更新操作由从库SQL线程执行更新写入到从库自己的log-bin日志中，默认开启
#log_replica_updates=1
#### 设置只允许普通用户写入，只针对普通用户有效（拥有insert、update、delete、drop等权限），但对于超级用户无效（管理员root或者all privileges所有权限用户），默认关闭
read_only=1
#### 设置是否禁止超级管理员写入，针对所有数据库用户，包括超级用户和普通用户，默认关闭
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级联从库导入slave01从库的备份库文件并重启MySQL数据库

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主库创建测试数据，查看slave01从库同步状态，查看slave02级联从库同步状态

### master主库写入测试数据
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从库查看主从同步状态
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级联从库查看主从同步状态
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主库大批量写入测试数据，查看slave01从库同步状态，查看slave02级联从库同步状态

### master主库在操作系统使用shell批量写入测试数据
for i in {4..1000}; do mysql -uroot -p'123456' -e "insert into student.test(id,name) values('$i','AAA');";done

### slave01从库查看主从同步状态
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级联从库查看主从同步状态
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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。