1. 开启bin-log
vim /etc/my.cnf
log_bin=/var/log/data/mysql-bin
binlog_format = row #日志格式
systemctl restart mysqld
常用命令：
# 是否启用binlog日志
show variables like 'log_bin';
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'mysql-bin.000003';
2. 准备试验数据
# 库名：test,表名：user
1、建表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
`password` varchar(128) NOT NULL DEFAULT '' COMMENT '密码',
`email` varchar(75) NOT NULL DEFAULT '' COMMENT '邮箱',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号码',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户基本信息表';
2. 插入数据
mysql> select * from user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 3 | three | 5543 | uuj | 69054 | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
+----+----------+----------+-------+----------+---------------------+---------------------+
3.模拟业务场景(误删数据)
# 删除数据
DELETE from user where username="three";
# 后续新的业务操作
insert into user(`username`,`password`,`email`,`mobile`)
values("four","wf55","ss7d","assef");
# 目前表记录
mysql> select * from user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 4 | four | wf55 | ss7d | assef | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
3. 恢复数据
3.1 更新日志文件,加只读锁
1. 查看最新的bin-log文件
[root@localhost data]# cd /var/log/data
[root@localhost data]# ls
mysql-bin.000001 mysql-bin.index
2.执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，mysql-bin.000001这个文件不会再有后续写入了，便于定位为题
mysql> flush logs;
Query OK, 0 rows affected (0.09 sec)
# 新增了mysql-bin.000002
[root@localhost data]# ls
mysql-bin.000001 mysql-bin.000002 mysql-bin.index
3.在数据恢复前阻断新的业务操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
#恢复后执行：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
3.2 分析日志文件
1. 通过之前删除的sql语句查找(过滤掉相关的位置信息，删除库里现有数据，进行全量恢复,当前我们只要恢复那条删除的数据，不适用)
[root@localhost data]# mysqlbinlog mysql-bin.000001 | grep -C 6 --color 'DELETE from user where username="three"'
SET TIMESTAMP=1610445036/*!*/;
BEGIN
/*!*/;
# at 6290
#210112 17:50:36 server id 1 end_log_pos 6403 CRC32 0xc9e6d67f Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1610445036/*!*/;
DELETE from user where username="three"
/*!*/;
# at 6403
#210112 17:50:36 server id 1 end_log_pos 6434 CRC32 0x71dab87a Xid = 213
COMMIT/*!*/;
# at 6434
#210112 17:50:45 server id 1 end_log_pos 6513 CRC32 0x1b225b82 Query thread_id=13 exec_time=0 error_code=0
2. 查找name=three相关的日志信息(找到了一条插入和删除记录)
[root@localhost data]# mysqlbinlog mysql-bin.000001 | grep -C 6 --color 'three'
# at 5971
# at 6003
#210112 15:57:20 server id 1 end_log_pos 6003 CRC32 0x50942aba Intvar
SET INSERT_ID=3/*!*/;
#210112 15:57:20 server id 1 end_log_pos 6180 CRC32 0xba7fdfb9 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1610438240/*!*/;
INSERT INTO `user` (`username`, `password`, `email`, `mobile`) VALUES ('three', '5543', 'uuj', '69054')
/*!*/;
# at 6180
#210112 15:57:20 server id 1 end_log_pos 6211 CRC32 0xb6a36777 Xid = 141
COMMIT/*!*/;
# at 6211
#210112 17:50:36 server id 1 end_log_pos 6290 CRC32 0x2931601b Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1610445036/*!*/;
BEGIN
/*!*/;
# at 6290
#210112 17:50:36 server id 1 end_log_pos 6403 CRC32 0xc9e6d67f Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1610445036/*!*/;
DELETE from user where username="three"
/*!*/;
# at 6403
#210112 17:50:36 server id 1 end_log_pos 6434 CRC32 0x71dab87a Xid = 213
COMMIT/*!*/;
# at 6434
#210112 17:50:45 server id 1 end_log_pos 6513 CRC32 0x1b225b82 Query thread_id=13 exec_time=0 error_code=0
3.binlog events查找
如下图：
插入username="three"
这条数据的日志区间是日志区间是Pos[6003] --> End_log_pos[6180];
按事务区间是：Pos[5892] --> End_log_pos[6211]；
3.3 mysqlbinlog还原
1. 通过插入记录的pos进行还原
注：单条数据恢复的话直接执行下sql语句就好(注意主键id)
命令：
[root@localhost data]# mysqlbinlog --start-position=5892 --stop-position=6211 --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test
查看：
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 3 | three | 5543 | uuj | 69054 | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
| 4 | four | wf55 | ss7d | assef | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)
3.4.删除user表后进行还原(通过pos点)
1. 清理数据
drop table test.user;
mysql> select * from test.user;
ERROR 1146 (42S02): Table 'test.user' doesn't exist
2.查看日志文件
[root@localhost data]# ls
mysql-bin.000001 mysql-bin.000002 mysql-bin.index
删除user表的日志存在了mysql-bin.000002
[root@localhost data]# mysqlbinlog mysql-bin.000002 | grep -C 6 --color 'DROP TABLE `user`'
#210112 15:57:20 server id 1 end_log_pos 959 CRC32 0xc38f7ca5 Xid = 307
COMMIT/*!*/;
# at 959
#210112 19:04:23 server id 1 end_log_pos 1076 CRC32 0x682f1347 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1610449463/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
DROP TABLE `user` /* generated by server */
/*!*/;
# at 1076
#210112 19:05:59 server id 1 end_log_pos 1198 CRC32 0xdf4c1492 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1610449559/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
DROP TABLE `yilou_log` /* generated by server */
# show binlog events in 'mysql-bin.000002';
3.还原数据
# 还原mysql-bin.000001所有数据
mysqlbinlog --start-position=4 --stop-position=6791 --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 4 | four | wf55 | ss7d | assef | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
# 还原mysql-bin.000002文件 pos点在959之前的所有数据
mysqlbinlog --start-position=4 --stop-position=959 --database=test mysql-bin.000002 | mysql -uroot -pqqcqqc -v test
最终结果：
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 3 | three | 5543 | uuj | 69054 | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
| 4 | four | wf55 | ss7d | assef | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)
3.5.删除user表后进行还原(通过时间点)
现有数据：
mysql> mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
| 3 | three | 5543 | uuj | 69054 | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
| 4 | four | wf55 | ss7d | assef | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)
# 只恢复2020年的数据
mysql> drop table test.user;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from test.user;
ERROR 1146 (42S02): Table 'test.user' doesn't exist
mysqlbinlog --start-datetime="2019-12-31 00-00-00" --stop-datetime="2020-12-31 00-00-00" --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test
# 查看结果
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile | create_time | update_time |
+----+----------+----------+-------+----------+---------------------+---------------------+
| 1 | one | 123456 | rty | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
| 2 | two | 67hff | efs | 239654 | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
+----+----------+----------+-------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
4. mysqlbinlog,mysqldump
还原数据mysqlbinlog：
--stop-position=1437 结束pos点
--start-datetime="2019-12-31 00-00-00" 起始时间点
--stop-datetime="2020-12-31 00-00-00" 结束时间点
--database=zyyshop 指定只恢复test数据库(一台主机上往往有多个数据库，只限本地log日志)
# 数据恢复本质上就是找到binlog中的sql日志，执行
备份数据mysqldump：
mysqldump -h 127.0.0.1 -uroot -pqqcqqc test > /tmp/test.sql
导入数据：
use test;
source /tmp/test.sql
参考：
https://dev.mysql.com/doc/refman/8.0/en/
https://www.cnblogs.com/martinzhang/p/3454358.html
https://blog.csdn.net/wwwdc1012/article/details/88373440
