mysqldump+binlog备份恢复笔记
模拟增量恢复
前提条件:
1.具备全量备份（mysqldump）。
2.除全量备份以外，还有全量备份之后产生的的所有binlog增量日志。
环境准备:
CREATE DATABASE dump;
USE `dump`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1,'hehe'),(2,'haha'),(3,'lala'),(4,'lili'),(5,'kaka');
commit;
模拟环境：
mkdir /data/backup -p
date -s "2020/7/20"
2020/7/20这天0晨创建一个全备：
mysqldump -uroot -p123456 \
-A --master-data=2 --single-transaction \
-R -E --triggers --set-gtid-purged=OFF \
--max-allowed-packet=256M gzip> /data/backup/full_$(date +%F).sql.gz
2020/7/20数据增加：
date -s "2020/7/20"
use dump;insert into test values(6,'lanlan');
commit;
模拟误删数据：
date -s "2020/7/20 11:40"
drop database dump;show databases;
#出现问题10分钟后,发现问题,删除了数据库了.
恢复数据准备
查看最后一次全备binlog pos点:
sed -n '22p' full_2020-07-20.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=655;
查看删除操作的binlog pos点:
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2755 |
| mysql-bin.000002 | 217 |
| mysql-bin.000003 | 1077 |
+------------------+-----------+
3306 [(none)]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 194 | f4c2d64d-c2c8-11ea-b04c-000c297a2fa5:1-11 |
| mysql-bin.000003 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '280d97e6-cb76-11ea-8b1f-000c29d7eddb:1' |
| mysql-bin.000003 | 259 | Query | 6 | 353 | CREATE DATABASE dump |
| mysql-bin.000003 | 353 | Gtid | 6 | 418 | SET @@SESSION.GTID_NEXT= '280d97e6-cb76-11ea-8b1f-000c29d7eddb:2' |
| mysql-bin.000003 | 418 | Query | 6 | 655 | use `dump`; CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
| mysql-bin.000003 | 655 | Gtid | 6 | 720 | SET @@SESSION.GTID_NEXT= '280d97e6-cb76-11ea-8b1f-000c29d7eddb:3' |
| mysql-bin.000003 | 720 | Query | 6 | 792 | BEGIN |
| mysql-bin.000003 | 792 | Table_map | 6 | 842 | table_id: 110 (dump.test) |
| mysql-bin.000003 | 842 | Write_rows | 6 | 889 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000003 | 889 | Xid | 6 | 920 | COMMIT /* xid=486 */ |
| mysql-bin.000003 | 920 | Gtid | 6 | 985 | SET @@SESSION.GTID_NEXT= '280d97e6-cb76-11ea-8b1f-000c29d7eddb:4' |
| mysql-bin.000003 | 985 | Query | 6 | 1077 | drop database dump |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
截取日志：
zcat full_2020-07-20.sql.gz >full_2020-07-20.sql
mysqlbinlog --start-position=655 --stop-position=920 /binlog/3306/mysql-bin.000003 -r bin.sql
需要恢复的日志:
3306 [(none)]>source full_2020-07-20.sql;
3306 [mysql]>source bin.sql;
