M--S1--S2 级联复制
master—>slave1—>slave2

master需要开启二进制日志
中间的slave1也需要打开二进制日志,但是它默认不把应用master的操作记录到自己的二进制日志。所以需要打开一个参数让它记录,才可以传给第三级的从;然后在中间从和第三级从之间再做一次AB复制就可以了。
打开log-slave-updates=1,让第一台传过来relay日志记录到自己的二进制日志。

先搭建好主从——>然后加入slave2

环境

master 172.16.0.9
slave1 172.16.0.5
slave2 172.16.0.11
关闭防火墙和selinux
hosts文件中三台服务器主机名和ip地址
系统时间需要同步
master和slave的数据库版本保持一致(系统版本保持一致)

思路

先搭建好主从——>然后加入slave2
master和中间slave1必须开启二进制日志
slave2必须开启中继日志
master和slave1和slave2的server-id必须不一样
master和slave的初始数据一致

环境配置

master:
[root@master /]# vim /etc/hosts
172.16.0.5 slave1

slave1:
[root@slave1 /]# vim /etc/hosts
172.16.0.9 master
172.16.0.11 slave2

slave2:
[root@slave2 /]# vim /etc/hosts
172.16.0.5 slave1

修改配置文件

master:
root@master /]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 1

slave1:
[root@slave1 /]# vim /etc/my.cnf
log-bin=mysql-bin
log-slave-updates=1
server-id = 2
relay-log=/data/DB/relay.log

slave2:
[root@slave2 /]# vim /etc/my.cnf
server-id = 3
relay-log=/data/DB/relay.log

数据初始化一致

情况1:master和slave都没有业务数据,直接初始化master和slave的数据库
情况2:master上面有业务数据
模拟情况2
停止数据库

[root@master ~]# systemctl stop mysqld

物理级别备份

[root@master /]# cd /data/DB/ && ll
[root@master DB]# tar -cvzf /tmp/mysql.tar.gz *
[root@master DB]# scp /tmp/mysql.tar.gz 172.16.0.5:/tmp
[root@master DB]# scp /tmp/mysql.tar.gz 172.16.0.11:/tmp

slave1和slave2进行数据恢复

[root@slave1 /]# cd /data/DB/ && ll
[root@slave1 DB]# tar -xf /tmp/mysql.tar.gz -C ./
[root@slave1 DB]# systemctl start mysqld

[root@slave2 /]# cd /data/DB/ && ll
[root@slave2 DB]# tar -xf /tmp/mysql.tar.gz -C ./
[root@slave2 DB]# systemctl start mysqld

查看slave和master上面的数据是否一样

[root@master DB]# echo "show databases;" | mysql -uroot -p123456
[root@slave1 DB]# echo "show databases;" | mysql -uroot -p123456
[root@slave2 DB]# echo "show databases;" | mysql -uroot -p123456

在master上面创建对slave1授权用户及查看binlog信息

创建授权用户

mysql> grant replication slave on *.* to 'slave'@'172.16.0.5' identified by '123456';

查看binglog信息,只有打开二进制日志,这句命令才有结果,表示当前数据库的二进制日志写到什么位置

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)

在slave1上配置同步信息

slave1:

mysql> change master to master_host='172.16.0.9', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=120;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果查看状态这两个值都为Yes, 说明M--S就已经做好'

参数说明:
master_host: master ip
master_user: 同步用户
master_password: 密码
master_port:     端口
master_log_file: master上面查看到的二进制日志名
master_log_pos: master上面查看到的POS值

在slave1上面对slave2授权

先停止slave
slave1:

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to 'slave'@'172.16.0.11' identified by '123456';
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

在slave2上配置同步信息

slave2:

mysql> change master to  master_host='172.16.0.5', master_user='slave', master_password='123', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos=120;
mysql> start slave;
mysql> show slave status \G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在回到slave1上面启动slave

slave1:

mysql> start slave;

测试验证

先查看原有的三个服务器的typecho库中的typecho_relationships表,
master:

mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
|   4 |   1 |
+-----+-----+
3 rows in set (0.00 sec)

slave1:
mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
|   4 |   1 |
+-----+-----+
3 rows in set (0.21 sec)

slave2:
mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
|   4 |   1 |
+-----+-----+
3 rows in set (0.17 sec)

在master删除一条数据

mysql> delete from typecho.typecho_relationships where cid="4";
Query OK, 1 row affected (0.00 sec)

再次查看

master:
mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
+-----+-----+
2 rows in set (0.00 sec)

slave1:
mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
+-----+-----+
2 rows in set (0.00 sec)

slave2:
mysql> select * from typecho_relationships;
+-----+-----+
| cid | mid |
+-----+-----+
|   1 |   1 |
|   3 |   1 |
+-----+-----+
2 rows in set (0.00 sec)

在master上面操作,会同步到slave1上面,相对slave2来说,也等于在slave1上面进行了相同的操作,所有slave2上面也会发生相应的改变

Last modification:August 20th, 2020 at 01:41 am
如果觉得我的文章对你有用,请随意赞赏