Linux下配置MySQL主从自动备份

环境：
centos7，使用yum在线安装mysql5.7.31，主服务器为自行搭建的centos7.8，从服务器为虚拟机，Centos7.3。
主服务器IP：172.168.2.221
从服务器IP：172.168.1.174
主从设备之间要能够互相ping通，此处不作介绍，自行配置。

现在需要将主服务器中名为zabbix的数据库做主从同步，自动同步到从服务器上。
工具：MobaXterm/Xshell/CRT均可

一、复制数据库

1、首先要将主服务器的数据库复制到从服务器中。
用MobaXterm连接主服务器，导出数据库：

mysqldump -uroot -p img >/root/img.sql
---输入数据库密码---

然后ls看下，可以看到一个 img.sql的文件。

2、用MobaXterm连接从服务器，使用sftp命令远程到主服务器（有提示输入yes）：

sftp root@主服务器IP地址
---输入ssh密码---

然后ls看下，可以看到主服务器的目录，里面有个img.sql的文件，执行：

get img.sql

这样数据库文件就被复制到从服务器上了。 exit 退出sftp。

3、接下来在从服务器上建立一个名为img的数据库，排序规则设为uft8

mysql -uroot -p
---输入数据库密码---
create database img;
use img;
set names utf8;
source img.sql

这样就成功导入了，exit退出回到linux命令行。

二、主服务器配置

1、切回主服务器，用vim 编辑 /etc下的my.cnf，如图

这里主要配置的是server-id和log-bin。server-id用于区分不同主机上的数据库，log-bin是二进制文件的名字（可以随意命名）。同时，为了保证只同步需要的数据库，我们需要加上：

binlog-do-db=zabbix
binlog-ignore-db=mysql

然后 :wq 保存退出，重启mysql：

service mysqld restart

2、连接数据库进行如下操作：

mysql -uroot -p img
---输入数据库密码---
#以下为修改数据库连接权限
grant all privileges on *.* to 'root'@'%' identified by '自定义密码';
flush privileges;
#下面为显示bin-log文件名和位置
show master status;

执行完最后一条后我们可以看到：

记下红框处的文件名和数字。

三、从服务器配置

1、连接到从服务器，用vim修改/etc下的my.cnf文件，把server-id改为2。

重启mysql服务：

service mysqld restart

2、连接数据库，配置从数据库：

mysql -uroot -p
---输入数据库密码---
stop slave;
change master to
master_user='root',
master_password='你设置的自定义密码',
master_host='172.168.2.221'，#主服务器地址
master_log_file='mysql-bin.000002',  #记下的上图红框的文件名
master_log_pos=2971525;   #记下的上图红框的位置
start slave;
show slave status\G   #查看是否安装成功

然而：原本应该出现两个Yes的地方，却出现了一个No，代表我配置失败了。

四、排错

在网上找了很多方法，一一实验，结果都是一样。Slave_SQL_Running: No。

最后得出结论可能出现的原因如下：

1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：

（一）方法一：在从机上配置

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

此方法在我的设备中并不奏效，再次寻找原因。

（二）方法二：
首先停掉Slave服务：slave stop

到主服务器上查看主机状态：

记录File和Position对应的值

1、进入master：

手动同步需要停止master的写操作！
手动同步需要停止master的写操作！
手动同步需要停止master的写操作！

这是非常重要的一点，就是因为这个原因，才会导致我的从机配置失败，出现No的现象。

在MySQL数据库中，在进行数据迁移和从库只读状态设置时，都会涉及到只读状态和Master-slave的设置和关系。
经过实际测试，对于MySQL单实例数据库和master库，如果需要设置为只读状态，需要进行如下操作和设置：
将MySQL设置为只读状态的命令：
#mysql -uroot -p
mysql> show global variables like “%read_only%”;
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like “%read_only%”;

将MySQL从只读设置为读写状态的命令：
mysql> unlock tables;
mysql> set global read_only=0;

对于需要保证master-slave主从同步的salve库，如果要设置为只读状态，需要执行的命令为：
mysql> set global read_only=1;

将salve库从只读状态变为读写状态，需要执行的命令是：
mysql> set global read_only=0;

2、然后到slave服务器上执行手动同步：

mysql -uroot -p
---输入数据库密码---
stop slave;
change master to
master_user='root',
master_password='你设置的自定义密码',
master_host='172.168.2.221',#主服务器地址
master_log_file='mysql-bin.000002',   #记下的上图红框的文件名
master_log_pos=7050842;   #记下的上图红框的位置
start slave;
show slave status\G   #查看是否安装成功

配置成功！！出现两个Yes。

再说一遍：手动同步需要停止master的写操作！

参考资料：
https://www.cnblogs.com/zhaoying/p/11737464.html
https://blog.csdn.net/chiwodeng9612/article/details/100860205
https://blog.csdn.net/zhwxl_zyx/article/details/88416498
感谢各位大佬提供资源，帮推广。。。。