使用Replication建立Mysql双机热备

数据库

总体目标：

现有两台Mysql服务器，IP分别为：
10.122.1.35 ，别名A
10.122.1.36 ，别名B
版本：Mysql 5.7 社区版

现使用A,B建立 Replication主主模式双机热备，需要同步的数据库为：ps3000；
主主模式即为两个主从模式叠加，具体操作如下：

1、建立A>B主从模式热备

1、 在主服务器 A 上建立备份用户，限制只能B访问

grant replication slave on *.* to 'repl_user'@'10.122.1.36' identified by 'yanfa@999';

2、开启主服务器A的 binarylog

vi /etc/my.cnf

[mysqld]下增加如下内容：

log-bin=mysql-bin
binlog_format=mixed
server-id=1
read-only=0
binlog-do-db=ps3000
expire_logs_days=3
slave-skip-errors=all
auto-increment-increment=2
auto-increment-offset = 1

expire_logs_days 自动清除二进制日志的时间，单位为天
slave-skip-errors=all 忽略所有同步时产生的错误

完成配置后重启Mysql

service mysqld restart

3、锁定A数据库，使其可读不可写

FLUSH TABLES WITH READ LOCK;

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。

4、同步数据

将A，B 两个Mysql 需要进行同步的数据库【ps3000】表结构与数据进行手工同步,使其具有相同的初始状态。

5、查看服务器A 的同步状态

show master status\G

结果如下：

mysql> show master status\G
*********************** 1. row ***********************
File: mysql-bin.000002
Position: 154
Binlog_Do_DB: ps3000
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

6、A服务器操作完成，解锁数据库

UNLOCK TABLES;

7、设置 服务器 B 需要复制的数据库

vi /etc/my.cnf

[mysqld]下增加如下内容：

log-bin=mysql-bin
binlog_format=mixed
server-id=2
replicate-do-db=ps3000
relay_log=mysqld-relay-bin
log-slave-updates=ON

保存， 重启mysql

8、服务器B开启同步

CHANGE MASTER TO
MASTER_HOST='10.122.1.35',
MASTER_USER='repl_user',
MASTER_PASSWORD='yanfa@999',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;

保存， 重启mysql

9：查看replication状态

show slave status\G

以上完成单向主从热备，如上图 两个 Yes代表主从备份目前状态正常。

如果是主主备份，则反过来设置一次即可，操作如下：

2、建立B>A主从模式热备

1、 在主服务器 B 上建立备份用户，限制只能 A 访问

grant replication slave on *.* to 'repl_user'@'10.122.1.35' identified by 'yanfa@999';

2、开启主服务器B的 binarylog

vi /etc/my.cnf

[mysqld]下增加如下内容：

read-only=0
binlog-do-db=ps3000
expire_logs_days=3
slave-skip-errors=all
auto-increment-increment=2
auto-increment-offset = 2

expire_logs_days 自动清除二进制日志的时间，单位为天
slave-skip-errors=all 忽略所有同步时产生的错误

完成配置后重启Mysql

service mysqld restart

3、记录B服务器数据库初态

show master status\G

mysql> show master status\G
*********************** 1. row ***********************
File: mysql-bin.000003
Position: 154
Binlog_Do_DB: ps3000
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

4、设置从服务器 A 需要复制的数据库

replicate-do-db=ps3000
relay_log=mysqld-relay-bin
log-slave-updates=ON

5、启动同步

CHANGE MASTER TO
MASTER_HOST='10.122.1.36',
MASTER_USER='repl_user',
MASTER_PASSWORD='yanfa@999',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;

3、如何暂停重设同步

如果同步遇到错误或者业务需要，重新设置A—>B的主从数据备份，过程如下：

1、停止A-B主从同步

stop slave;

2、锁定A服务器的数据库

FLUSH TABLES WITH READ LOCK;

3、将数据库进行手工同步A>B

4、查看A数据库Master状态

show master status\G

结果如下图：

5：重设B服务器的同步起点

CHANGE MASTER TO
MASTER_HOST='10.122.1.35',
MASTER_USER='repl_user',
MASTER_PASSWORD='yanfa@999',
MASTER_LOG_FILE='mysql-bin.000021',
MASTER_LOG_POS=28962043;

6、开启同步服务

start slave;

问题汇总

做主主热备时，第二台主机Salve启动状态一直未 No，如下图所示：

可以查看Mysql日志，mysql日志一般在：/usr/local/mysql/data 目录下，有一个 err后缀的文件，如下图：

目前遇到几种错误，调整后需要重新 设置 Slave，如下图

CHANGE MASTER TO
MASTER_HOST='10.122.1.36',
MASTER_USER='repl_user',
MASTER_PASSWORD='yanfa@999',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;

启动slave

start slave

查看Slave状态，显示状态正常。

1、Check that you do not already have another mysqld process using the same InnoDB data or log files

完整报错如下：

2017-09-19T06:12:39.655879Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

则可能其他程序占用了Mysql 的Data目录文件，查询到之后Kill即可

ps -ef|grep mysql*

结果如下：

Kill后，重新启动 Mysql。

2、Failed to open the relay log './localhost-relay-bin.000001'

2017-09-27T07:48:27.490613Z 0 [ERROR] Failed to open the relay log './localhost-relay-bin.000001' (relay_log_pos 4).
2017-09-27T07:48:27.490656Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './mysqld-relay-bin.index' during relay log initialization.
2017-09-27T07:48:27.496853Z 0 [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2017-09-27T07:48:27.496939Z 0 [ERROR] Failed to create or recover replication info repositories.
2017-09-27T07:48:27.496980Z 0 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
2017-09-27T07:48:27.496992Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository
2017-09-27T07:48:27.496999Z 0 [ERROR] Failed to start slave threads for channel ''

提示无法打开：localhost-relay-bin.000001，则可能在当前目录存在了这个文件，删除即可，如下图：

3、Slave failed to initialize relay log info structure from the repository, Error_code: 1872

完整报错如下：

2017-09-27T07:58:50.355368Z 0 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
2017-09-27T07:58:50.355408Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository
2017-09-27T07:58:50.355421Z 0 [ERROR] Failed to start slave threads for channel ''

检查my.cnf，原来没指定relay_log或者relay_log 指定的值与mysql默认的值相冲突，修改即可。