使用Mysql主从复制技术实现数据库热备

た入场券 2022-01-14 14:43 193阅读 0赞

一、 Mysql Replication  
1、 什么是Mysql Replication  
 Replication可以实现将数据从一台数据库服务器（master）复制到一台或多台数据库服务器（slave）  
 默认情况下属于异步复制，无需维持长连接  
 通过配置，可以复制所有的库或者几个库，甚至库中的一些表  
 是MySQL内建的，本身自带的

2、 Mysql Replication的原理  
简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作  
![在这里插入图片描述][20190613215205729.png]  
DML:SQL操作语句，update, insert，delete  
Relay log :中继日志

3、 Replication的作用  
1）Fail Over 故障切换  
2）Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份  
3）High Performance 高性能，可以多台slave,实现读写分离  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70]  
4、 Replication工作原理  
整体上来说，复制有3个步骤：  
（1） master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）  
（2） slave将master的binary log events拷贝到它的中继日志(relay log)  
（3） slave重做中继日志中的事件，修改salve上的数据。

MySQL主从复制：  
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。  
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程—I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。  
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

5、 Replication常见方案  
1） One master and Muti salve 一主多备  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 1]  
一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中  
在Master上<多台同步影响IO>

2） M-S-S  
使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 2]  
Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。  
3） M-M 双主互备（互为主从）  
很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。  
![在这里插入图片描述][20190613215248997.png]

4） M-M-M  
监控三台机器互相作对方的master  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 3]  
天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重  
mysql在5.6已经自身可以实现fail over故障切换  
5、One slave Muti master 一从对多主  
好处：节省成本，将多个master数据自动化整合  
缺陷：对库和表数据的修改较多

二、 部署Mysql主从同步（M-S）  
1、 环境准备  
主机名 IP 系统 MySQL版本 角色  
xuegod120 192.168.2.120 CentOS7.5/5.7.24 Master  
xuegod130 192.168.2.130 CentOS7.5/5.7.24 Slave  
模式：C/S  
端口：3306  
主从服务器数据库版本需要一直，同步的数据库和表结构一直

2、 配置主数据库服务器xuegod120  
1） 创建需要同步的数据库  
mysql> create database HA;  
Query OK, 1 row affected (0.00 sec)

mysql> use HA;  
Database changed  
mysql> create table t1(id int,name varchar(20));  
Query OK, 0 rows affected (0.00 sec)

2） 修改my.cnf配置文件，添加如下内容  
log-bin=mysql-bin-master \#启动二进制日志，并命名  
server-id=1 \#本机数据库ID标示  
binlog-do-db=HA \#可以被从服务器同步的数据库，多个库，另起一行再写  
binlog-ignore-db=mysql \#不可以被从服务器同步的库，因为每台服务器的mysql库都不同

3） 重启mysql服务  
\[root@xuegod120 ~\]\# systemctl restart mysqld  
注释：如果这里重启服务出现卡死现象，kill掉mysql的进程，再次重启

4） 授权  
mysql> grant replication slave on *.* to [slave@192.168.2.130][slave_192.168.2.130] identified by “123456”;  
Query OK, 0 rows affected, 1 warning (0.17 sec)  
注释：如果提示设置的密码过于简单，需要执行如下两条命令  
mysql> set global validate\_password\_policy=0;  
mysql> set global validate\_password\_length=1;  
mysql> flush privileges;

5） 查看master的状态信息  
mysql> show master status; \#这里出现二进制文件的名称，表示主服务器配置成功  
±------------------------±---------±-------------±-----------------±------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
±------------------------±---------±-------------±-----------------±------------------+  
| mysql-bin-master.000001 | 450 | HA | mysql | |  
±------------------------±---------±-------------±-----------------±------------------+  
1 row in set (0.00 sec)  
查看二进制文件： \#二进制文件存放在mysql的目录下  
\[root@xuegod120 ~\]\# ll /var/lib/mysql/mysql-bin-master.000001  
\-rw-r----- 1 mysql mysql 450 Jun 13 18:07 /var/lib/mysql/mysql-bin-master.000001  
mysql> show binlog events\\G \#查看binlog日志文件的内容（数据库内部）

注意：因为做主从同步，两台服务器的数据库必须一致。所以这里我们将刚刚创建的数据库导出，然后在将数据库导入到从服务器，避免需要在单独创建一次。  
\[root@xuegod120 ~\]\# mysqldump -uroot -p123456 -B HA > HA.sql \#-B参数，可以导出库  
mysqldump: \[Warning\] Using a password on the command line interface can be insecure.  
\[root@xuegod120 ~\]\# scp HA.sql 192.168.2.130:/root/  
The authenticity of host ‘192.168.2.130 (192.168.2.130)’ can’t be established.  
ECDSA key fingerprint is SHA256:+mDCRrBeEUYL4pEZzVZ8nuJVaCa6/Uef8aEr4z6DkzQ.  
ECDSA key fingerprint is MD5:33:d2:5f:66:a9:2d:77:58:1f:b2:2c:5b:51:74:70:07.  
Are you sure you want to continue connecting (yes/no)? yes  
Warning: Permanently added ‘192.168.2.130’ (ECDSA) to the list of known hosts.  
[root@192.168.2.130][root_192.168.2.130]’s password: \#输入130的密码  
HA.sql 100% 1765 368.9KB/s 00:00

3、 配置从数据库服务器xuegod130  
1） 使用slave用户登录120服务器的数据库，测试slave用户是否可以登录  
\[root@xuegod130 ~\]\# mysql -uslave -p123456 -h 192.168.2.120  
mysql> show databases; \#只有复制权限，所以看不到其他库  
±-------------------+  
| Database |  
±-------------------+  
| information\_schema |  
±-------------------+  
1 row in set (0.00 sec)

3） 修改从服务器的配置文件，添加如下参数：  
server-id=2  
\#从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

4） 重启mysql的服务  
\[root@xuegod130 ~\]\# systemctl restart mysqld  
5） 停止slave，指定主服务器数据库是那一条，并指定用户和密码  
\[root@xuegod130 ~\]\# mysql -uroot -p123456 -A  
mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master\_host=‘192.168.2.120’,master\_user=‘slave’,master\_password=‘123456’;  
Query OK, 0 rows affected, 2 warnings (0.12 sec)

6） 启动slave，查看slave的状态信息  
mysql> start slave;  
Query OK, 0 rows affected (0.20 sec)

mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.2.120  
Master\_User: slave  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin-master.000002  
Read\_Master\_Log\_Pos: 154  
Relay\_Log\_File: xuegod130-relay-bin.000005  
Relay\_Log\_Pos: 327  
Relay\_Master\_Log\_File: mysql-bin-master.000002  
Slave\_IO\_Running: Yes \#这里能看到两个YES，表示主从成功  
Slave\_SQL\_Running: Yes

两个状态解释：  
Slave\_IO\_Running ：一个负责与主机的IO通信  
Slave\_SQL\_Running：负责自己的slave mysql进程

错误提示解决方法：  
a） Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.  
解决方法：  
这里提示数据库的UUID值和主服务器的UUID值一样，因为虚拟机我们是直接克隆的，所以相同  
修改UUID值后，重启mysql服务和slave，就可以

b）Slave\_IO\_Running: Connecting  
Slave\_SQL\_Running: Yes  
解决方法：  
这里的状态不是两个yes，主要问题出现三个方面，一一排查就可以。  
网络不通、用户名和密码不对、指定的主服务器IP地址不对  
7） 到主服务器，查看进程列表  
mysql> show processlist \\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Id: 3  
User: slave \#从服务器已经连接  
Host: 192.168.2.130:52884 \#从服务器的IP地址和端口号  
db: NULL \#数据库的是空的  
Command: Binlog Dump \#复制主的二进制日志文件到从的命令  
Time: 398  
State: Master has sent all binlog to slave; waiting for more updates \#等待复制binlog到slave  
Info: NULL

4、 在master服务器的数据库中插入数据，测试同步  
mysql> use HA;  
Database changed  
mysql> insert into t1 values (1,‘LB’);  
Query OK, 1 row affected (0.44 sec)

mysql> insert into t1 values (2,‘CC’);  
Query OK, 1 row affected (0.01 sec)

mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | LB |  
| 2 | CC |  
±-----±-----+  
2 rows in set (0.00 sec)

5、 到slave服务器的数据库，查看t1表的数据是否同步过来  
mysql> use HA;  
Database changed  
mysql> select \* from t1; \#同步成功  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | LB |  
| 2 | CC |  
±-----±-----+  
2 rows in set (0.00 sec)

6、 拍错  
如果遇到主从不同步，看一下主从bin-log的位置，然后再同步。  
mysql> show master status;  
±------------------------±---------±-------------±-----------------±------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
±------------------------±---------±-------------±-----------------±------------------+  
| mysql-bin-master.000002 | 664 | HA | mysql | |  
±------------------------±---------±-------------±-----------------±------------------+  
1 row in set (0.00 sec)  
在主服务器上看二进制日志事件列表  
mysql> show binlog events \\G；  
从服务器执行MySQL命令下：  
mysql> stop slave; \#先停止slave服务  
mysql> change master to master\_log\_file=‘mysql-bin-master.000001’,master\_log\_pos=1164;  
\#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果  
mysql> start slave; \#启动从服务器同步服务  
mysql> show slave status\\G; \#用show slave status\\G;看一下从服务器的同步情况  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
如果都是yes，那代表已经在同步

重启从服务器，再查看状态：  
stop slave; \#停止从服务器  
start slave; \#开启从服务器

排错思路：  
1）二进制日志没有开启  
2）IPTABLES 没有放开端口  
3）对应的主机 IP地址写错了

SQL线程出错  
主从服务器数据库结构不统一，出错后，数据少，可以手动解决创建插入，再更新slave状态。  
注：如果主上误删除了。那么从上也就误删除了。 \#因此主上要定期做mysqldump备份。

三、 部署Mysql主主双向主从复制（M-M）  
通过MySQL主主：进行MySQL双向同步数据库HA的配置  
MySQL主：服务端：xuegod120 IP：192.168.2.120  
MySQL主：服务端：xuegod130 IP：192.168.2.130  
如果没有恢复快照要先清空一下二进制日志  
mysql> reset master

1、 配置主服务器数据库xuegod120  
同样的先创建测试的数据库，然后导出数据库给到xuegod130使用  
1） 修改mysql.cnf文件，添加如下：  
server-id = 1  
log-bin=mysql-bin-master  
binlog-do-db=HA  
binlog-ignore-db=mysql  
保存退出，重启mysql服务  
systemctl restart mysqld

2） 查看master的状态，并授权  
mysql> show master status;  
±------------------------±---------±-------------±-----------------±------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
±------------------------±---------±-------------±-----------------±------------------+  
| mysql-bin-master.000002 | 664 | HA | mysql | |  
±------------------------±---------±-------------±-----------------±------------------+  
1 row in set (0.00 sec)  
授权  
mysql>grant replication slave on *.* to slave@‘192.168.2.%’ identified by ‘123456’;  
注意：这里的授权地址，可以写一个网段的地址，用%代替

注意：如果不符合密码要求修改如下（5.7版本）  
方法一：  
mysql> set global validate\_password\_policy=0; \#定义复杂度  
mysql> set global validate\_password\_length=1; \#定义长度 默认是8  
mysql> grant replication slave on *.* to slave@‘192.168.2.%’ identified by ‘123456’;  
mysql> flush privileges; \#记得刷新授权表

方法二：  
关闭密码强度审计，在my.cnf添加validate-password=off  
mysql> grant replication slave on *.* to slave@‘192.168.1.%’ identified by ‘123456’;  
mysql> flush privileges; \#记得刷新授权表

2、 配置主服务器数据库xuegod130  
先将测试的数据库导入到mysql，保证数据库一致  
因为我们这里没有恢复快照，所以先关闭之前主从实验的从服务器  
mysql> stop slave; \#这个在130服务器上操作  
Query OK, 0 rows affected (0.01 sec)  
1） 修改my.cnf配置文件，添加如下：  
server-id=2  
log-bin=mysql-bin-slave  
binlog-do-db=HA  
binlog-ignore-db=mysql  
保存退出，重启mysql服务 systemctl restart mysqld  
2） 测试能否使用slave用户登录到120数据库  
\[root@xuegod130 ~\]\# mysql -uslave -p123456 -h 192.168.2.120  
mysql> exit \#记得加退出命令，不然下面在授权的时候，会报错

3） 然后在xuegod130主服务器的数据库上，进行授权  
mysql> grant replication slave on *.* to slave@‘192.168.2.120’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

4） 停止slave，在主xuegod130上指定另外一个主的IP地址和用户名和密码  
mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  
mysql> change master to master\_host=‘192.168.2.120’,master\_user=‘slave’,master\_password=‘123456’;  
mysql> start slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.2.120  
Master\_User: slave  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin-master.000002  
Read\_Master\_Log\_Pos: 664  
Relay\_Log\_File: xuegod130-relay-bin.000007  
Relay\_Log\_Pos: 327  
Relay\_Master\_Log\_File: mysql-bin-master.000002  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

5） 配置主xuegod120服务器的slave  
先测试能否使用slave用户登录xuegod130  
\[root@xuegod120 ~\]\# mysql -uslave -p123456 -h 192.168.2.130  
mysql> exit;  
Bye

配置slave状态  
mysql> change master to master\_host=‘192.168.2.130’,master\_user=‘slave’,master\_password=‘123456’;  
Query OK, 0 rows affected, 2 warnings (0.18 sec)

启动slave  
mysql> start slave;  
Query OK, 0 rows affected (0.01 sec)  
6） 在主xuegod120上查看slave的状态  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.2.130 \#指到对方  
Master\_User: slave  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin-slave.000001  
Read\_Master\_Log\_Pos: 450  
Relay\_Log\_File: xuegod120-relay-bin.000002  
Relay\_Log\_Pos: 675  
Relay\_Master\_Log\_File: mysql-bin-slave.000001  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

3、 插入数据进行测试  
1） 现在xuegod120上插入数据，进行测试同步  
mysql> use HA;  
Database changed  
mysql> insert into t1 values (3,‘AA’);  
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (4,‘BB’);  
Query OK, 1 row affected (0.00 sec)

2） 到xuegod130上，查看是否同步  
mysql> use HA;  
Database changed  
mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | LB |  
| 2 | CC |  
| 3 | AA |  
| 4 | BB | \#同步成功  
±-----±-----+  
4 rows in set (0.00 sec)

3） 反过来，到xuegod130上插入数据  
mysql> insert into t1 values (5,‘DD’);  
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (6,‘EE’);  
Query OK, 1 row affected (0.00 sec)

4） 到xuegod120上查看数据是否同步  
mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | LB |  
| 2 | CC |  
| 3 | AA |  
| 4 | BB |  
| 5 | DD | \#同步成功  
| 6 | EE |  
±-----±-----+  
6 rows in set (0.00 sec)

四、 部署M-S-S模型  
环境：  
xuegod120 master mysql5.7.24 192.168.2.120  
Xuegod130 slave中继 mysql5.7.24 192.168. 2.130  
xuegod140 slave mysql5.7.24 192.168. 2.140  
注意：这里就需要恢复快照，再做这个实验

将数据库的导出文件，分别复制到三台机器上：  
\[root@xuegod140 ~\]\# scp HA.sql 192.168.2.120:/root/  
\[root@xuegod140 ~\]\# scp HA.sql 192.168.2.130:/root/

分别在三台机器上导入数据库  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 -A  
mysql> source /root/HA.sql  
\[root@xuegod130 ~\]\# mysql -uroot -p123456 -A  
mysql> source /root/HA.sql  
\[root@xuegod140 ~\]\# mysql -uroot -p123456 -A  
mysql> source /root/HA.sql

1、 xuegod120主服务器配置  
1） 修改my.cnf配置文件  
server-id=1 \#服务器ID,每台服务器的ID都是唯一  
binlog-do-db=HA \#需要同步的库名  
binlog-ignore-db=mysql \#不能同步的库名  
log-bin=mysql-bin-master \#二进制日志文件名称  
sync-binlog=1 \#将二进制文件同步都磁盘，1表示没修改一次，就进行同步动作  
binlog-format=row \#以行的方式格式化二进制文件  
保存并重启mysql服务 systemctl restart mysqld

2） 添加授权用户，给到slave使用，指定slave的IP地址（这里的slave指的是中继）  
mysql> grant replication slave on *.* to repl@‘192.168.2.130’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.25 sec)

2、 配置中继服务器xuegod130  
1） 配置my.cnf文件  
server-id=2  
log-bin=mysql-bin-slave1  
log-slave-updates=1 \#把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录到自己二进制文件，这样才能使第三台slave通过中继slave读取到相应数据变化  
binlog-format=row  
注释：这里中继服务器也需要开启日志文件，因为需要从主服务器同步二进制日志文件，并放到自己的二进制日志文件（包括自己本身的操作），然后让从服务器来读取  
保存并重启mysql服务  
\[root@xuegod130 ~\]\# systemctl restart mysqld

2） 授权slave来读取中继的二进制文件，并创建用户  
mysql> grant replication slave on *.* to ‘repl’@‘192.168.2.140’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.03 sec)

3） 配置中继服务器到主服务器读取文件的授权  
mysql> change master to master\_host=‘192.168.2.120’,master\_user=‘repl’,master\_password=‘123456’;  
Query OK, 0 rows affected, 2 warnings (0.12 sec)

启动slave  
mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)

4） 查看中继服务器的salve状态  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.2.120 \#指到主服务器  
Master\_User: repl  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin-master.000001  
Read\_Master\_Log\_Pos: 601  
Relay\_Log\_File: xuegod130-relay-bin.000002  
Relay\_Log\_Pos: 828  
Relay\_Master\_Log\_File: mysql-bin-master.000001  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

3、 配置slave服务器xuegod140（从服务器）  
1） 修改配置文件  
server-id=3  
log-bin=mysql-bin-slave2  
binlog-format=row  
保存，并重启服务  
\[root@xuegod140 ~\]\# systemctl restart mysqld

2） 指定从服务器到中继来获取二进制文件，并指定用户名和密码  
mysql> change master to master\_host=‘192.168.2.130’,master\_user=‘repl’,master\_password=‘123456’;  
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动slave  
mysql> start slave;  
Query OK, 0 rows affected (0.01 sec)

3） 查看slave的状态  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.2.130 \#指定到中继服务器  
Master\_User: repl  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin-slave1.000001  
Read\_Master\_Log\_Pos: 896  
Relay\_Log\_File: xuegod140-relay-bin.000002  
Relay\_Log\_Pos: 1123  
Relay\_Master\_Log\_File: mysql-bin-slave1.000001  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

4、 在master xuegod120上插入数据测试  
1） 在master中插入数据  
mysql> use HA;  
Database changed  
mysql> insert into t1 values (1,‘AA’);  
Query OK, 1 row affected (0.24 sec)

mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | AA |  
±-----±-----+  
1 row in set (0.00 sec)

2） 到中继服务器中查看数据是否同步  
mysql> use HA;  
Database changed  
mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | AA |  
±-----±-----+  
1 row in set (0.00 sec)

3） 到从服务器查看数据是否同步  
mysql> use HA;  
Database changed  
mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | AA |  
±-----±-----+  
1 row in set (0.14 sec)

5、 修改中继服务器的表存储引擎  
这里我们可以的看到中继服务器不只是同步二进制文件，同时也同步数据，我们可以通过修改表的存储引擎，不让它同步数据到本地  
这我们就需要使用一个叫做黑洞的存储引擎blackhole

先关闭日志记录，在进行修改，不然会把从服务器的表存储引擎也修改为黑洞引擎  
mysql> set sql\_log\_bin=off；  
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 ENGINE=blackhole;  
Query OK, 1 row affected (0.15 sec)  
Records: 1 Duplicates: 0 Warnings: 0

mysql> show create table t1\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: t1  
Create Table: CREATE TABLE `t1` (  
`id` int(11) DEFAULT NULL,  
`name` varchar(20) DEFAULT NULL  
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8  
1 row in set (0.00 sec)

开启日志记录  
mysql> set sql\_log\_bin=on;  
Query OK, 0 rows affected (0.00 sec)

6、 再到master xuegod120服务器中插入数据测试中继服务器是否会同步数据  
1） master服务器插入数据  
mysql> insert into t1 values (2,‘BB’);  
Query OK, 1 row affected (0.00 sec)

mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | AA |  
| 2 | BB |  
±-----±-----+  
2 rows in set (0.00 sec)

2） 到中继服务器查看  
mysql> select \* from t1;  
Empty set (0.00 sec) \#没有数据

3） 到从服务器查看  
mysql> select \* from t1;  
±-----±-----+  
| id | name |  
±-----±-----+  
| 1 | AA |  
| 2 | BB |  
±-----±-----+  
1 row in set (0.00 sec)

4、 排错方法  
模拟故障：  
1） 由于历史遗留问题，MySQL主从库的表结构不一致，主库的某个表tableA比从库表tableA少了一个字段  
当尝试在主库上更改表结构时，这行alter语句会随着binlog同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动slave上的主从同步线程。场景大概是下面这个样子：  
先在从库添加这个字段：  
mysql> alter table student add age int default 0 after name;  
再在主库添加这个字段：  
mysql> alter table student add age int default 0 after name;\#修改主库上的表结构，添加一个字段  
从库会同步主库的，但是从库已经存在了这个字段  
查看slave状态  
![在这里插入图片描述][20190613215338849.png]  
![在这里插入图片描述][20190613215344611.png]  
解决方法1：  
跳过错误的事务  
从库上执行：  
mysql> stop slave;  
mysql>set global sql\_slave\_skip\_counter=1;  
mysql> start slave;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 4]  
很多slave数据库的时候这样改太麻烦了。

解决方法2：  
slave比较少的时候还可以，但是当从库有几十台时，逐台去处理既费时又容易出错，怎样在主库这一侧一劳永逸地避免呢？  
那很简单，我们不要让主库将alter语句记录到binlog中就行  
我们直接这主库中关闭binlog记录  
mysql> set sql\_log\_bin=off;

然后我们再执行alter语句  
mysql> alter table student add age int default 0 after name;  
再开启bin-log  
mysql> set sql\_log\_bin=on;

2） 错误2  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 5]  
这种要求对齐binlog  
先到作为它的主上查看binlog  
![在这里插入图片描述][20190613215411981.png]  
Slave上不对应  
Slave上操作：  
mysql> stop slave;  
mysql> change master to master\_host=‘192.168.1.63’,master\_user=‘repl’,master\_password=‘123456’,master\_log\_file=‘mysql-bin-slave1.000002’,master\_log\_pos=415;  
mysql> start slave;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 6]  
Ok,恢复正常

[20190613215205729.png]: /images/20220114/b2493b774e7a4440bfd80ffb9a627132.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70]: /images/20220114/1c525e2ffcb847bba0187c10ae5349bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 1]: /images/20220114/c0e0ca5daa5440acaf9ce4d231ae3977.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 2]: /images/20220114/43ce6804c5184c279db99845f7dbe874.png
[20190613215248997.png]: /images/20220114/2cb30da9e11d4b1c9fb7c52656f85b46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 3]: /images/20220114/9a3bb2fbf024409ba34cf9beb75e6629.png
[slave_192.168.2.130]: mailto:slave@192.168.2.130
[root_192.168.2.130]: mailto:root@192.168.2.130
[20190613215338849.png]: /images/20220114/a4fbfd3bf09b42c59037a4fbf5e4a765.png
[20190613215344611.png]: /images/20220114/8714eb585e8947238f7cc8d3a0fd3f5f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 4]: /images/20220114/b217eb6edfc944dcadb95bc2d8c2996f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 5]: /images/20220114/5bf4e845d43541f7b8d98c3011e5c881.png
[20190613215411981.png]: /images/20220114/c0c36ee50bb64aeeb57cdffba9d08346.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 6]: /images/20220114/3a000d0f664c47c9860fb83023b6301b.png