mysqlbinlog恢复备份

常用参数

-d,--database=name  根据指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤）
-r,--result-file=name  指定解析binlog输出SQL语句的文件
-R,--read-from-remote-server  从MySQL服务器读取binlog日志，是下面参数的别名。read-from-remote-master=BINLOG-DUMP-NON-GTIDS
-j,--start-position=#  读取binlog的起始位置点，#号是具体的位置点
--stop-postion=# 读取binlog的停止位置点，#号是具体的位置点
--start-datetime=name  读取binlog的起始位置点，name是具体的时间，格式为：2004-12-25 11:25:56
--stop-datetime=name  读取binlog的停止位置点，name是具体的时间，格式为：2004-12-25 11:25:56
--base64-output=decode-rows 解析ROW级别binlog日志的方法，
例如：
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000016

切割binlog日志参数

[root@mysql ~]# mysqladmin flush-logs

查看binglog日志文件

[root@mysql ~]# mysqlbinlog /data/mysql/logs/mysql-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190614 14:40:46 server id 1  end_log_pos 120 CRC32 0xd7cdab86     Start: binlog v 4, server v 5.6.42-log created 190614 14:40:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
bkEDXQ8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYar
zdc=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

解析某个库的binlog

[root@mysql ~]# mysqlbinlog -d zhyr /data/mysql/logs/mysql-bin.000006 -r bin.sql
[root@mysql ~]# cat bin.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190614 14:40:46 server id 1  end_log_pos 120 CRC32 0xd7cdab86     Start: binlog v 4, server v 5.6.42-log created 190614 14:40:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
bkEDXQ8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYar
zdc=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过binlog恢复数据

在恢复数据的过程中，禁止数据有写的操作，以防止有脏数据产生。可以通过防火墙策略控制

查看数据

mysql> select * from zhyr;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
|  1 | aaaa |   1 | aa   |
|  2 | bbbb |   2 | bb   |
|  3 | cccc |   3 | cc   |
|  4 | dddd |   4 | dd   |
|  5 | eeee |   5 | ee   |
+----+------+-----+------+
5 rows in set (0.00 sec)

备份数据

[root@mysql ~]# mysqldump --databases --master-data=2 --single-transaction db_zhyr | gzip > /root/sql/zhyr$(date +%F).sql.gz

插入新数据

[root@mysql ~]# mysql -e "use db_zhyr;insert into zhyr values(6,'ffff',6,'ff')"
[root@mysql ~]# mysql -e "use db_zhyr;insert into zhyr values(7,'gggg',7,'gg')"
[root@mysql ~]# mysql -e "use db_zhyr;select * from zhyr;"
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
|  1 | aaaa |   1 | aa   |
|  2 | bbbb |   2 | bb   |
|  3 | cccc |   3 | cc   |
|  4 | dddd |   4 | dd   |
|  5 | eeee |   5 | ee   |
|  6 | ffff |   6 | ff   |
|  7 | gggg |   7 | gg   |
+----+------+-----+------+

删除库

[root@mysql ~]# mysql -e "drop database db_zhyr;show databases;"

查看binlog中的记录

[root@mysql ~]# mysqlbinlog /data/mysql/logs/mysql-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190614 14:40:46 server id 1  end_log_pos 120 CRC32 0xd7cdab86     Start: binlog v 4, server v 5.6.42-log created 190614 14:40:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
bkEDXQ8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYar
zdc=
'/*!*/;
# at 120
#190614 15:21:23 server id 1  end_log_pos 195 CRC32 0x87aa9ce5     Query   thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1560496883/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#190614 15:21:23 server id 1  end_log_pos 252 CRC32 0x5a04609e     Table_map: `db_zhyr`.`zhyr` mapped to number 71
# at 252
#190614 15:21:23 server id 1  end_log_pos 301 CRC32 0x9a715a35     Write_rows: table id 71 flags: STMT_END_F

BINLOG '
80oDXRMBAAAAOQAAAPwAAAAAAEcAAAAAAAEAB2RiX3poeXIABHpoeXIABAP+AQ8E/jwwAAieYARa
80oDXR4BAAAAMQAAAC0BAAAAAEcAAAAAAAEAAgAE//AGAAAABGZmZmYGAmZmNVpxmg==
'/*!*/;
# at 301
#190614 15:21:23 server id 1  end_log_pos 332 CRC32 0xca74dd02     Xid = 82
COMMIT/*!*/;
# at 332
#190614 15:21:34 server id 1  end_log_pos 407 CRC32 0x149a9361     Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1560496894/*!*/;
BEGIN
/*!*/;
# at 407
#190614 15:21:34 server id 1  end_log_pos 464 CRC32 0x922c5124     Table_map: `db_zhyr`.`zhyr` mapped to number 71
# at 464
#190614 15:21:34 server id 1  end_log_pos 513 CRC32 0xea190cfa     Write_rows: table id 71 flags: STMT_END_F

BINLOG '
/koDXRMBAAAAOQAAANABAAAAAEcAAAAAAAEAB2RiX3poeXIABHpoeXIABAP+AQ8E/jwwAAgkUSyS
/koDXR4BAAAAMQAAAAECAAAAAEcAAAAAAAEAAgAE//AHAAAABGdnZ2cHAmdn+gwZ6g==
'/*!*/;
# at 513
#190614 15:21:34 server id 1  end_log_pos 544 CRC32 0x54390722     Xid = 87
COMMIT/*!*/;
# at 544
#190614 15:28:37 server id 1  end_log_pos 645 CRC32 0xb08e5ec7     Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1560497317/*!*/;
drop database db_zhyr  ##记录了删除语句
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

解压全备份的数据

[root@mysql ~]# zcat /root/sql/zhyr2019-06-14.sql.gz >/root/sql/zhyr-bin.sql

查看binlog日志点

[root@mysql ~]# sed -n '22p' /root/sql/zhyr-bin.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120;

按日志点恢复binlog数据

[root@mysql ~]# mysqlbinlog -d db_zhyr --start-position=120 /data/mysql/logs/mysql-bin.000006 -r bin.sql

恢复前期备份的数据

[root@mysql ~]# mysql < /root/sql/zhyr-bin.sql
[root@mysql ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_zhyr            |
| logs               |
| mysql              |
| performance_schema |
+--------------------+
[root@mysql ~]# mysql -e "use db_zhyr;select * from zhyr;"
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
|  1 | aaaa |   1 | aa   |
|  2 | bbbb |   2 | bb   |
|  3 | cccc |   3 | cc   |
|  4 | dddd |   4 | dd   |
|  5 | eeee |   5 | ee   |
+----+------+-----+------+

删除binlog中删除数据库的字段

grep -i drop bin.sql
sed -i '/^drop.*/d' bin.sql

开始恢复增量–恢复binlog数据

[root@mysql ~]# mysql < bin.sql

[root@mysql ~]# mysql -e "use db_zhyr;select * from zhyr;"
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
|  1 | aaaa |   1 | aa   |
|  2 | bbbb |   2 | bb   |
|  3 | cccc |   3 | cc   |
|  4 | dddd |   4 | dd   |
|  5 | eeee |   5 | ee   |
|  6 | ffff |   6 | ff   |
|  7 | gggg |   7 | gg   |
+----+------+-----+------+

数据恢复成功！！！

小结：

• 适合认为sql语句造成的误操作或者没有主从复制等热备情况宕机时的修复。
• 恢复条件要全备和增量的所有数据
• 恢复时，建议对外停止更新数据库。
• 先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成sql文件，把文件中有问题的sql语句删除，再恢复到数据库。