MySQL全备+二进制日志恢复

在数据库使用过程中可能出现以下的问题，该怎么解决呢？

星期5的10:30做的全备，11点发生的误操作，删除了student表里的所有数据，如何快速恢复数据到删除之前的状态？

这时就可以使用MySQL全备+二进制日志恢复到数据被删除前的状态了。
下面我们来模拟这种情况：

前提配置：打开二进制日志

[root@mysql mysql]# vim /etc/my.cnf
[mysqld]  #在mysqld下配置logbin
...
#log bin  
log_bin
server_id = 1

1.新建一个库

root@(none) 10:15  mysql>create database sc;
Query OK, 1 row affected (0.00 sec)

创建表，插入数据:

root@(none) 10:15  mysql>use sc
Database changed
root@sc 10:16  mysql>create table student(id int primary key, name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)

root@sc 10:18  mysql>insert  into student values(1,'lisi'),(2,'wangwu'),(3,'zhangsan');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
root@sc 10:19  mysql>select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | wangwu   |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

2.全备份数据

使用mysqldump全备数据：

mysqldump -u【MySQL用户名】 -p【密码】 【数据库名】 【表名】 > 【备份文件名】

[root@mysql backup]# pwd
/backup
[root@mysql backup]# mysqldump -uroot -p"123456" sc  student > sc_student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

3.模拟数据发生变化

root@sc 10:27  mysql>insert  into student values(4,'xiaoming');
Query OK, 1 row affected (0.01 sec)

root@sc 10:28  mysql>update student set name='xiaohong' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@sc 10:29  mysql>select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaohong |
|  2 | wangwu   |
|  3 | zhangsan |
|  4 | xiaoming |
+----+----------+
4 rows in set (0.00 sec)

4.模拟操作失误

删除表里所有数据：

root@sc 10:57  mysql>delete from student;
Query OK, 4 rows affected (0.00 sec)

root@sc 10:58  mysql>select * from student;
Empty set (0.00 sec)

5.数据恢复

恢复全备：

[root@mysql backup]# mysql -uroot -p'Sanchuang123#' sc  < sc_student.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

发现只有全备的数据，没有修改后的数据：

root@sc 10:58  mysql>select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | wangwu   |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

二进制日志恢复：

bin-log是记录着mysql所有事件的操作，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复

查看正在使用的二进制日志：

root@sc 11:17  mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     3812 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

找到数据修改的时间点：

[root@mysql mysql]# mysqlbinlog -vv mysql-bin.000001 

恢复数据：
基于时间的恢复方式：

[root@mysql mysql]# mysqlbinlog --start-datetime="2023-07-17 10:27:51" --stop-datetime="2023-07-17 10:51:10" mysql-bin.000001 | mysql -u root -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.

基于位置：

[root@mysql mysql]# mysqlbinlog  --start-position=1985 --stop-position=2669  mysql-bin.000001 |mysql -uroot -p'123456'

全备恢复不是必须的，可以全部使用二进制日志，如果数据量不是特别大。

参考链接: mysql/mariadb知识点总结（25）：二进制日志