【MYSQL】在线恢复主从复制方案

一、恢复前提

因复杂情况，从库无法从binlog中恢复主从复制关系，需要从备份文件中恢复。恢复过程的几个关键点为：
1、从库现有数据的清理。本方案采用覆盖的方式，导出时添加add-drop参数即可。还有一个方案是手动删除数据文件，再初始化数据库。
2、主库导出之前一定要reset master清除日志文件，否则备库连上后会执行binlog的语句出现各种各样的问题。

二、恢复步骤

1、备份主库、备库文件系统/data
mkdir -p /mysqlrecovery
mount 109.0.14.58:/appm/aixinst/mysql_install/MaterSlaveRecovery /mysqlrecovery
cd /mysqlrecovery
tar cf mysql_master.tar /data/mysql_5.7_3306
tar cf mysql_slave.tar /data/mysql_5.7_3306

2、主库清理binlog
mysql -uroot -pxxxx -e “reset master;”

3、备库导出、获取备份文件
mysqldump -h109.6.45.35 -uroot -pxxxx --triggers --routines --events --single-transaction --add-drop-database --add-drop-table --add-drop-trigger --all-databases > /mysqlrecovery/mysql_data.sql 2>/mysqlrecovery/mysql_data.sql.exp.log

echo $?
cat /mysqlrecovery/mysql_data.sql.exp.log

4、备库上恢复数据。
mysql -uroot -pxxxx -e “stop slave;”
mysql -uroot -pxxxx -e “reset slave;”
mysql -uroot -pxxxx -e “reset master;”

mysql -uroot -pxxxx< /mysqlrecovery/mysql_data.sql > /mysqlrecovery/mysql_data.sql.imp.log 2>&1
cat /mysqlrecovery/mysql_data.sql.imp.log

5、停启数据库，确定没有问题
mysqladmin -uroot -pxxxx shutdown
cd $MYSQL_HOME
./bin/mysqld_safe &

tail -n 100 $MYSQL_DATADIR/mysql.err

6、开始主备同步：
mysql -uroot -pxxxx -e “change master to master_host=‘109.6.45.35’,master_port=3306,master_user=‘repl’,master_password=‘repl’,master_auto_position=1;”
mysql -uroot -pxxxx -e “start slave;”
mysql -uroot -pxxxx -e “show slave status\G;”

三、测试验证情况

1、插入模拟数据（建库、建用户、建存过）
– 创建test库及用户及表，插入1000条数据；

create database testdb;
CREATE USER test@'%' IDENTIFIED BY 'test';
GRANT ALL ON testdb.* TO test@'%';
FLUSH PRIVILEGES;
1
2
3
4

– 建表testtable

create table testtable(
id int auto_increment not null,
name varchar(30) ,
createtime varchar(30) ,
primary key(id)
)
1
2
3
4
5
6

– 插入测试数据

DELIMITER $$
USE `testdb`$$
DROP PROCEDURE IF EXISTS `addTestData`$$
CREATE DEFINER=`test`@`%` PROCEDURE `addTestData`(IN n INT)
BEGIN
		DECLARE i INT DEFAULT 1;
		WHILE (i <= n ) DO
			INSERT INTO `testtable`( `id`, `name`, `createtime`) VALUES (i, CONCAT('坤坤',i), NOW());
			SET i=i+1;
		END WHILE;
	END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12

– 调用存过，插入数据

CALL addTestData(1000)
1

2、主备库操作
主库执行步骤二的1、2，备库执行步骤二的1、3、4、5、6

3、主备恢复情况验证
1）主备同步状态都YES

[sysdba@localhost][(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 109.6.109.57
Master_User: admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 1830722
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 1830379
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2）备库数据已与主库相同