基于binlog恢复之使用MySQL Binlog Server做恢复

我们知道，从MySQL 5.6版本开始，mysqlbinlog命令支持把远程线上库的binlog日志备份到本地目录，做到binlog日志的安全备份，保证数据的安全性；当远程库发生故障无法恢复数据时，还可以利用全备和binlog日志做恢复，当然我们都不希望这一天的发生。
假设binlog数据量不大，可以使用全量备份+binlog增量做恢复；当binlog量很大的时候，这样恢复是比较慢的，这时可以使用全量备份+binlog Server做恢复。
下面介绍MySQL5.7的Binlog Server环境搭建及如何用全量备份+Binlog Server做恢复

1. 三台服务器分别部署MySQL 5.7
mkdir -p /data/mysql/mysql_3306/{bin_logs,data,error_logs,general_logs,relay_logs,slow_logs,tmp,undo_logs}
vim /data/mysql/mysql_3306/my.cnf
chown mysql.mysql /data/mysql/ -R
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my.cnf &
(unknown)@localhost:mysql_3306.sock [(none)]> alter user current_user() identified by '0wkH&vSgIh7J';
Query OK, 0 rows affected (0.00 sec)

2. vdb_172_16_123_101和vbk_172_16_123_102创建复制账号
root@localhost:mysql_3306.sock [(none)]> create user repl@'172.16.123.%' identified by 'repl4slave';
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql_3306.sock [(none)]> grant replication slave on . to repl@'172.16.123.%';
Query OK, 0 rows affected (0.00 sec)

3. vdb_172_16_123_101创建测试库db_3306及t1表
root@localhost:mysql_3306.sock [(none)]> create database db_3306;
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql_3306.sock [(none)]> use db_3306;
Database changed
root@localhost:mysql_3306.sock [db_3306]> create table t1(id int unsigned not null auto_increment, name varchar(10), primary key(id));
Query OK, 0 rows affected (0.32 sec)

root@localhost:mysql_3306.sock [db_3306]> insert into t1(name) values('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@localhost:mysql_3306.sock [db_3306]> update t1 set name='mysql' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

4. vdb_172_16_123_101用mysqldump做全备并保存到vbk_172_16_123_102的/data/backup目录
（先在vbk_172_16_123_102上执行mkdir /data/backup）
mysqldump -S /tmp/mysql_3306.sock -p --single-transaction --master-data=2 -A > db_3306-20170403-1921.sql
scp -P 20755 db_3306-20170403-1921.sql 172.16.123.102:/data/backup/

5. vbk_172_16_123_102，把mysqld停掉，做成binlog server
mysql> reset master;
Query OK, 0 rows affected (0.23 sec)

mysqladmin -S /tmp/mysql_3306.sock -p shutdown
rm -fr /data/mysql/mysql_3306/bin_logs/*

/usr/local/mysql/bin/mysqlbinlog -R --raw --host='172.16.123.101' --port=3306 --user='repl' --password='repl4slave' --stop-never --stop-never-slave-server-id=3306102 mysql-bin.000001 --result-file=/data/mysql/mysql_3306/bin_logs/ &
参数解读:
-R, --read-from-remote-server #从远程机器上读取binlog
--raw #以binlog存储
--host #远程库的主机IP
--port #远程库的端口号
--user #远程库上用于复制的账号
--password #用远程库上复制账号的密码
--stop-never #一直连接到远程server上读取binlog日志，直到远程server关闭后退出，或被pkill掉
--stop-never-slave-server-id #如果需要启动多个binlog server, 需要给binlog server指定server-id, 最好按照部署规范指定server-id
--result-file #指定存储到本地的目录，注意后缀加上/，否则mysqlbinlog会认为是保存文件的前缀

同时编辑/data/mysql/mysql_3306/data/auto.cnf, 与vdb_172_16_123_101一样

5. 模拟线上实例故障
vdb_172_16_123_101上写入新数据，然后把实例停掉
root@localhost:mysql_3306.sock [db_3306]> insert into t1(name) values('d'),('e'),('f');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@localhost:mysql_3306.sock [db_3306]> update t1 set name='python' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

root@localhost:mysql_3306.sock [db_3306]> delete from t1 where name='d';
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql_3306.sock [db_3306]> select * from .t1;
+----+--------+
| id | name |
+----+--------+
| 1 | mysql |
| 2 | b |
| 3 | c |
| 5 | python |
| 6 | f |
+----+--------+

mysqladmin -S /tmp/mysql_3306.sock -p shutdown

6. vdb_172_16_123_101上把mysqld运行起来
ll /data/mysql/mysql_3306/bin_logs/
total 8
-rw-r----- 1 root root 177 Apr 3 19:04 mysql-bin.000001
-rw-r----- 1 root root 2654 Apr 3 19:31 mysql-bin.000002
find /data/mysql/mysql_3306/bin_logs/mysql-bin.00000* > /data/mysql/mysql_3306/bin_logs/mysql-bin.index
chown mysql.mysql /data/mysql/mysql_3306/bin_logs/mysql-bin.*

7. vdb_172_16_123_103上做恢复
首先把全备恢复出来
7.1 把全备同步过来scp -P20755 172.16.123.102:/data/backup/db* .
7.2 执行reset master;
7.3 导入全备
mysql --defaults-file=/data/mysql/mysql_3306/my.cnf -S /tmp/mysql_3306.sock -p < db_3306-20170403-1921.sql
7.4 change到binlog server上
root@localhost:mysql_3306.sock [db_3306]> change master to master_host='172.16.123.102', master_port=3306, master_user='repl', master_password='repl4slave', master_auto_position=1;
7.5 检查复制是否建立
root@localhost:mysql_3306.sock [db_3306]> show slave status\G
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 194
Retrieved_Gtid_Set: db2551e8-185a-11e7-9e57-00155d01670d:8-10
Executed_Gtid_Set: db2551e8-185a-11e7-9e57-00155d01670d:1-10
7.6 确实binlog同步完成
vbk_172_16_123_102和vdb_172_16_123_103执行show master status，对比Executed_Gtid_Set，当值一样的时候说明binlog同步完成了
7.7 检查数据
root@localhost:mysql_3306.sock [db_3306]> select * from t1;
+----+--------+
| id | name |
+----+--------+
| 1 | mysql |
| 2 | b |
| 3 | c |
| 5 | python |
| 6 | f |
+----+--------+
5 rows in set (0.00 sec)
可以看到和vdb_172_16_123_101关闭前的数据是一致的，也就是这个方法是可行的

总结:
(1)做好MySQL全量备份和binlog备份，可以提高数据安全性，与此同时，使用GTID可以更加简单实现数据恢复。
(2)注意binlog server的show master status输出，当在binlog server上执行过CRUD语句又未执行reset master时，用于恢复的数据库会读取这个事务，会造成数据不一致

备注: 学习资源来源为知数堂，MySQL Binlog Server参考资料为http://wubx.net/mysql-binlog-server/，在此感觉知数堂的吴炳锡老师

发送评论 编辑评论

发送评论编辑评论