MySQL架构备份之M-S-S级联备份

M--S1--S2 级联复制

master—>slave1—>slave2

master需要开启二进制日志

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

打开log-slave-updates=1,让第一台传过来relay日志记录到自己的二进制日志。

思路:

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

环境准备及要求:

1、关闭防火墙和selinux

2、hosts文件中三台服务器主机名和ip地址一一对应起来

3、系统时间需要同步

4、master和slave的数据库版本保持一致(系统版本保持一致)

思路:

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

1、master和中间slave1必须开启二进制日志

2、slave2必须开启中继日志

3、master和slave1和slave2的server-id必须不一样  范围(2^23-1)

4、master和slave的初始数据一致

M--S1--S2级联架构操作步骤

环境:

master:192.168.1.166

slave1:192.168.1.114

slave2:192.168.1.180

1、环境准备

# master:
[root@master ~]# vim /etc/hosts
192.168.1.114 slave1 # slave1:
[root@slave1 ~]# vim /etc/hosts
192.168.1.166 master
192.168.1.180 slave2 # slave2:
[root@slave2 ~]# vim /etc/hosts
192.168.1.114 slave1

2、修改配置文件

# 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

3、数据初始化一致

情况1:master和slave都没有业务数据,直接初始化master和slave的数据库

情况2:master上面有业务数据

masteer:
1>停止数据库
[root@master ~]# /etc/init.d/mysqld stop 2>物理级别备份
[root@master ~]# cd /data/DB/ && ll
[root@master DB]# tar -cvzf /tmp/mysql.tar.gz *
[root@master DB]# scp /tmp/mysql.tar.gz 192.168.1.114:/tmp
[root@master DB]# scp /tmp/mysql.tar.gz 192.168.1.180:/tmp 3>slave1和slave2进行数据恢复
[root@slave1 ~]# cd /data/DB/ && ll
[root@slave1 DB]# tar -xf /tmp/mysql.tar.gz -C .
[root@slave1 DB]# /etc/init.d/mysqld start [root@slave2 ~]# cd /data/DB/ && ll
[root@slave2 DB]# tar -xf /tmp/mysql.tar.gz -C .
[root@slave2 DB]# /etc/init.d/mysqld start 4>查看slave和master上面的数据是否一样
[root@master DB]# echo "show databases;" | mysql -p123
[root@slave1 DB]# echo "show databases;" | mysql -p123
[root@slave2 DB]# echo "show databases;" | mysql -p123

示例master上面有业务数据

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

1>创建授权用户
mysql> grant replication slave on *.* to 'slave'@'192.168.1.114' identified by ''; 2>查看binglog信息,只有打开二进制日志,这句命令才有结果,表示当前数据库的二进制日志写到什么位置
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000013
Position: 331
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.03 sec)

5、在slave1上配置同步信息

slave1:
mysql> change master to master_host='192.168.1.166', master_user='slave', master_password='', master_port=3306, master_log_file='mysql-bin.000013', master_log_pos=331;
参数说明:
master_host: master ip
master_user: 同步用户
master_password: 密码
master_port:     端口
master_log_file: master上面查看到的二进制日志名
master_log_pos: master上面查看到的POS值 mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果查看状态这两个值都为Yes, 说明M--S就已经做好

6、在slave1上面对slave2授权

先停止slave
slave1:
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec) mysql> grant replication slave on *.* to 'slave'@'192.168.1.180' identified by '';
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000017
Position: 425
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

7、在slave2上配置同步信息

slave2:
mysql> change master to master_host='192.168.1.114', master_user='slave', master_password='', master_port=3306, master_log_file='mysql-bin.000017', master_log_pos=425;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

8、在回到slave1上面启动slave

slave1:
mysql> start slave;

9、测试验证

先查看原有的三个服务器的db01库中的table03表,
master:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 3 | lili |
| 4 | yoyo |
+------+------+
7 rows in set (0.10 sec) slave1:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 3 | lili |
| 4 | yoyo |
+------+------+
7 rows in set (0.17 sec) slave2:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 3 | lili |
| 4 | yoyo |
+------+------+
7 rows in set (0.44 sec) 在master上面删除一条数据
mysql> delete from db01.table03 where name="lili";
Query OK, 1 row affected (0.38 sec) 再次查看
master:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
+------+------+
6 rows in set (0.00 sec) slave1:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
+------+------+
6 rows in set (0.00 sec) slave2:
mysql> select * from db01.table03;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
| 1 | haha |
| 2 | wowo |
| 4 | yoyo |
+------+------+
6 rows in set (0.00 sec)

测试验证

总结:

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

上一篇:机器学习——交叉验证,GridSearchCV,岭回归


下一篇:CSS在线字体库,外部字体的引用方法@font-face