Centos7部署Mysql双机热备份（图文教学，简单易学，Mysql5.7.28）

1.双机热备简介

双机热备是应用于服务器的一种解决方案，其构造思想是主机和从机通过TCP/IP网络连接，正常情况下主机处于工作状态，从机处于监视状态，一旦从机发现主机异常，从机将会在很短的时间之内代替主机，完全实现主机的功能。

2.准备工作

两台服务器使用相同系统及相同Mysql数据库版本最好
本次教程所使用的环境

• 主数据库：
  • Centos7
  • Mysql 5.7.28
  • IP 192.168.11.99
• 副数据库：
  • Centos7
  • Mysql 5.7.28
  • IP 192.168.11.177

1. 确定主副服务器能够ping通
2. 备份前保证两个数据库的数据一致
3. 在部署期间两个数据库中数据不要有变化

3.配置

3.1 配置主数据库

1. 进入Mysql命令行，为副数据库创建连接用户

   grant file,select,replication slave on  *.* to backup@192.168.11.99 identified by '123456';
   

   注意修改，语句中 backup 为用户名，192.168.11.177 为副数据库ip地址，123456 为用户密码

2. 验证副数据库能否连接上主数据库，在副服务器上输入一下命令

   mysql -h192.168.11.99 -ubackup -p
   

   输入密码能连接上即可
   

3. 修改配置文件my.cnf，一般路径为 /etc/my.cnf
   将下面的命令添加在 [mysqld] 下 ，保存

   server-id = 1 //可能已经存在，找出来修改
   binlog_do_db  = fleet //记录日志的数据库，有多个库可以继续添加，直接再次设置该值，而不是使用‘，’分隔
   binlog_ignore_db = mysql   //不记录日志的数据库
   

   binlog_do_db = 你要备份的数据库名

   注意：其中可能有一些字段已经存在，多个相同字段可能会导致后面mysql重启失败

4. 重启Mysql服务，service mysqld restart（或是service mysql restart）

5. 备份数据库
   锁定数据库表

mysql> FLUSH TABLES WITH READ LOCK;

mysqldump -uroot -ppassword -RBE 数据库名称 > /home/backup/mysql/test.sql（保存的位置）

参数介绍：

• -B 导出多个数据库的结构以及数据
• -R 导出存储过程和函数
• -E 导出事件

加-B参数的好处：
加上-B参数后，导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复。
6. 查看主服务器状态

show master status\G

图中 File 和 Position 两个字段的值在后文需要用到

7. 解锁数据库

mysql> UNLOCK TABLES;

3.2 配置副数据库

1. 将主机上的备份sql复制到从机上，执行sql将主从数据库数据同步

mysql -uroot -p'123456' < /data/test.sql

2. 修改配置文件my.cnf，一般路径为 /etc/my.cnf
   将下面的命令添加在 [mysqld] 下 ，保存

   server-id = 2 //可能已经存在，找出来修改
   replicate-do-db=fleet //需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
   replicate-ignore-db = mysql,information_schema,performance_schema
   

   replicate-do-db=你要备份的数据库名

   注意：其中可能有一些字段已经存在，多个相同字段可能会导致后面mysql重启失败

3. 重启Mysql服务，service mysqld restart（或是service mysql restart）

4. 指定同步位置
   进入Mysql命令行，执行以下命令

   stop slave; 
   change master to master_host='192.168.11.99',master_user='backup',master_password='123456',master_log_file='mysql-bin.000012',master_log_pos=196641;
   start slave; 
   

   字段解释
   master_host —— 主数据库IP
   master_user —— 连接用户名（上文中3.1.1中）
   master_password —— 连接用户密码（上文中3.1.1中）
   master_log_file —— 日志文件名（上文中3.1.5中的File）
   master_log_pos—— 日志文件的位置（上文中3.1.5中的Position ）

   运行成功结果为

   Query OK, 0 rows affected, 2 warnings (0.04 sec)
   

5. 查看副服务器状态
   在mysql命令行中输入以下命令

   show slave status\G
   

   效果如下图
   

   注意看图中红色框中的两个字段为Yes即为成功，如果有一个为No或者两个为No可以看最后的常见问题

6. 现在可以修改一下主数据库，看看副数据库中数据是否刷新成功！

4. 取消主从

如果设置了错误的主从或主从失效时，想要取消主从关系

stop slave;
reset slave all;

5. 常见问题

5.1 Slave_SQL_Running：no

在副数据库中依次执行一下命令

stop slave;                                                      
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;            
start slave;                                                      
show slave status\G 

5.2 Slave_IO_Running：no

一般是在3.2.3步中指定日志文件名出现了错误

show master status\G

仔细对比副数据库中指定的数据日志文件是否跟主数据库中的相同
副：

主：

修改方法，在副数据库的命令行中执行以下命令

stop slave;                 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=0;  
start slave;                               
show slave status\G 

以上命令需要稍作修改
MASTER_LOG_FILE = ‘主数据库中的日志文件’

5.3 从机mysql同步自动出错断线

1. 查看问题所在

show slave status\G

主键重复错误1062
关于这种错误还有以下多种情况

第一种：在master上删除一条记录，而slave上找不到。

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254

第二种：主键重复。在slave已经有该记录，又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1;
Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

第三种：在master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263

2. 解决
   修改/etc/my.ini ，让mysql 遇见错误跳过

# 第二个参数会覆盖第一个参数。所以一定要写到同一行，并用逗号分隔。
slave-skip-errors=1062

重启mysql

3. 原因
   一般是断电等极端情况