背景
记录一次自身的误操作,将Mysql数据库中某系统数据库删库后的恢复步骤,以示警戒!
说明:20多天前本人将生产环境数据库导出备份过一次。数据库是8.0版本,只记录最近30天binlog日志。故本篇文章是以备份的数据+binlog一起恢复数据,备份数据是25天前数据,binlog是记录最近30天内的日志记录。综上已备份数据导入后,在采用binlog日志恢复上次导出时间到当前时间内的数据,完成数据恢复。下面内容本机会复现上述场景,实现数据恢复。
前置条件
查看数据库是否开启binlog
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-PNQ97MJ-bin |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-PNQ97MJ-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------------------------------------+
说明:
log_bin : ON表示当前数据库已开启binlog日志,OFF则表示未开启
log_bin_basename:这是binlog日志文件存放的目录和名称,可以从上面查看的结果中看出,这里的没有配置binlog日志文件的后缀名。因为binlog日志是一个一直滚动追加的日志文件,当超过一个指定的默认值(通常是1GB大小)之后,他会自动切分为一个新的binlog日志文件。文件的后缀名就是它滚动增长的标识符。它是一个有规律增长的序列,000001,000002,000003…999999
log_bin_index:这是binlog日志文件的索引文件目录和名称
查看数据库binlog日志存放时间
1.Mysql8.0以下版本,查看当前数据库日志binlog保存时效 以天为单位,默认0 永不过期,最多只能设置99天
mysql> show variables like 'expire_logs_days'; #查看binlog日志保存时间
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
2.Mysql8.0以上版本,通过全局参数binlog_expire_logs_seconds查看binlog保存时间 以秒为单位;默认2592000=30天 14400=4小时;86400=1天;259200=3天
mysql> show variables like '%binlog_expire_logs_seconds%'; #查看binlog日志保存时间
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
mysql> set global binlog_expire_logs_seconds=2592000; #可修改默认保存时间(单位:秒)
场景模拟
首先使用如下命令刷新binlog,后续的所有操作将使用新的binlog文件记录
mysql> show binary logs; #查看binlog文件列表
+----------------------------+------------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+------------+-----------+
| DESKTOP-PNQ97MJ-bin.000176 | 853166 | No |
| DESKTOP-PNQ97MJ-bin.000177 | 2938 | No |
| DESKTOP-PNQ97MJ-bin.000178 | 1290 | No |
| DESKTOP-PNQ97MJ-bin.000179 | 5293 | No |
+----------------------------+------------+-----------+
mysql> flush logs; #刷洗binlog,生成新文件记录最近的后续操作
Query OK, 0 rows affected (0.06 sec)
mysql> show binary logs; #查看最新binlog文件列表,发现已生成最新的.000180文件,那么后续的数据操作将会记录在此文件中
+----------------------------+------------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+------------+-----------+
| DESKTOP-PNQ97MJ-bin.000176 | 853166 | No |
| DESKTOP-PNQ97MJ-bin.000177 | 2938 | No |
| DESKTOP-PNQ97MJ-bin.000178 | 1290 | No |
| DESKTOP-PNQ97MJ-bin.000179 | 5293 | No |
| DESKTOP-PNQ97MJ-bin.000180 | 156 | No |
+----------------------------+------------+-----------+
建库建表
mysql> CREATE DATABASE IF NOT EXISTS db_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; #创建数据库
Query OK, 1 row affected (0.01 sec)
mysql> use db_test; #进入创建的数据库db_test内
Database changed
mysql> CREATE TABLE `blog_user` (
`userid` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
`userphone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
`userstate` int DEFAULT NULL COMMENT '状态',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`uptime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
`userimg` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户头像',
PRIMARY KEY (`userid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables; #查看到已创建的数据表
+-------------------+
| Tables_in_db_test |
+-------------------+
| blog_user |
+-------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (1, 'admin', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL); #添加第一条数据
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (2, 'zhangsan', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL); #添加第二条数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from blog_user; #查询发现数据已添加
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
2 rows in set (0.00 sec)
模拟备份数据导出
说明:当前环节模拟提前备份导出的数据,后续新追加的数据,不在导出,采用binlog恢复
使用mysqldump命令导出需要备份的数据库
mysqldump --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF -uroot -p123456 db_test --hex-blob > E:/mysql/db_test.sql
注:db_test就是本次备份的数据库
–default-character-set:设置字符编码,和数据库保持一致
–single-transaction:在备份前设置事务隔离级别为REPEATABLE READ并向server发送START TRANSACTION语句
–set-gtid-purged:确定是否在结果中添加SET @@GLOBAL.GTID_PURGED。若为ON但在server中没有开启GTID则发生错误。若OFF则什么都不做。若AUTO则server中开启GTID那么添加上述语句,反之不加。
-u:后面跟用户名
-p:后面跟密码
–hex-blob:将二进制列,如,BINARY,VARBINARY,BLOB,BIT备份为十六进制形式。
E:/mysql/db_test.sql:备份数据的文件地址
模拟多个binlog文件记录信息
说明:生产环境中会有大数据量的录入修改等操作,当一个binlog文件达到1GB(默认)大小后,会自动拆分生成新的binlog文件,此处为了模拟真实场景,使用命令手动进行拆分,生成新的binlog文件记录日志。拆分前,上述的建库建表插入数据的日志都记录在了.000180文件中。拆分后在模拟一些数据插入会记录在.000181的binlog文件中
mysql> flush logs; #刷洗binlog,生成新文件记录最近的后续操作
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs; #查看最新binlog文件列表,发现已生成最新的.000181文件,那么后续的数据操作将会记录在此文件中
+----------------------------+------------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+------------+-----------+
| DESKTOP-PNQ97MJ-bin.000176 | 853166 | No |
| DESKTOP-PNQ97MJ-bin.000177 | 2938 | No |
| DESKTOP-PNQ97MJ-bin.000178 | 1290 | No |
| DESKTOP-PNQ97MJ-bin.000179 | 5350 | No |
| DESKTOP-PNQ97MJ-bin.000180 | 3336 | No |
| DESKTOP-PNQ97MJ-bin.000181 | 156 | No |
+----------------------------+------------+-----------+
6 rows in set (0.00 sec)
#模拟继续插入第三,第四条数据,当前数据不再上述的备份sql中,后续采用binlog恢复,当前这些数据记录在.000181文件中
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (3, 'admin2', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (4, 'zhangsan2', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from blog_user;
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 3 | admin2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 4 | zhangsan2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------
mysql> flush logs; #刷洗binlog,生成新文件记录最近的后续操作
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs; #查看最新binlog文件列表,发现已生成最新的.000182文件,那么后续的数据操作将会记录在此文件中
+----------------------------+------------+-----------+
| Log_name | File_size | Encrypted |
+----------------------------+------------+-----------+
| DESKTOP-PNQ97MJ-bin.000176 | 853166 | No |
| DESKTOP-PNQ97MJ-bin.000177 | 2938 | No |
| DESKTOP-PNQ97MJ-bin.000178 | 1290 | No |
| DESKTOP-PNQ97MJ-bin.000179 | 5350 | No |
| DESKTOP-PNQ97MJ-bin.000180 | 3336 | No |
| DESKTOP-PNQ97MJ-bin.000181 | 932 | No |
| DESKTOP-PNQ97MJ-bin.000182 | 156 | No |
+----------------------------+------------+-----------+
7 rows in set (0.00 sec)
#模拟继续插入第五,第六条数据,当前数据不再上述的备份sql中,后续采用binlog恢复,当前这些数据记录在.000182文件中
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (5, 'admin5', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `db_test`.`blog_user`(`userid`, `username`, `password`, `userphone`, `userstate`, `createtime`, `uptime`, `remark`, `userimg`) VALUES (6, 'zhangsan6', '123456', '17371431054', 1, '2020-12-24 19:22:25', NULL, NULL, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from blog_user; 已
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 3 | admin2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 4 | zhangsan2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 5 | admin5 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 6 | zhangsan6 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------
模拟删除数据库db_test
mysql> drop database db_test; #删除数据库
Query OK, 1 row affected (0.03 sec)
mysql> show databases; #查看当前数据库列表
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| innovation_demonstration |
| mysql |
| performance_schema |
+--------------------------+
Query OK, 4 row affected (0.03 sec)
恢复实践
说明:
- 通过场景模拟,得到一个备份的数据库,备份数据记录中blog_user表只有id为1和2的数据记录,对应.000180的binlog文件
- 备份后新追加的id为3和4的数据记录,对应.000181的binlog文件
- 后续在追加的id为5和6得数据记录,以及删库的操作记录对应存放在.000182的binlog文件
通过备份数据库恢复部分数据
说明:当前环节也可通过binlog恢复id为1和2的数据,因为binlog文件都在,之所以使用备份数据库恢复是因为我们生产环境的binlog只保留30天的,之的前数据和操作已丢失。因此使用之前备份的数据库进行部分恢复,最近的没有丢失的binlog数据采用binlog文件恢复。
mysql> CREATE DATABASE IF NOT EXISTS db_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; #先手动创建一个相同名称的空数据库
Query OK, 1 row affected (0.00 sec)
mysql> source E:/mysql/db_test.sql; #使用source命令导入之前备份的数据先恢复部分数据
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show databases; #查看当前数据库列表
+--------------------------+
| Database |
+--------------------------+
| db_test |
| information_schema |
| innovation_demonstration |
| mysql |
| performance_schema |
+--------------------------+
Query OK, 5 row affected (0.03 sec)
mysql> use db_test; #进入db_test数据库
Database changed
mysql> select * from blog_user; #查看通过备份数据恢复的数据,当前已恢复id为1和2的数据,后续数据通过binlog恢复
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+----------+----------+-------------+-----------+---------------------+--------+--------+---------+
2 rows in set (0.00 sec)
通过binlog恢复数据
说明:首先需要进入到binlog文件存放的所在目录,通过log_bin_basename参数可以查看
mysql> show variables like '%log_bin_basename%';
+------------------+----------------------------------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------------------------------+
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-PNQ97MJ-bin |
+------------------+----------------------------------------------------------------+
注:C:\ProgramData\MySQL\MySQL Server 8.0\Data 下就是binlog的文件存放地址,已DESKTOP-PNQ97MJ-bin开头.00000X为后缀的都是binlog文件。DESKTOP-PNQ97MJ-bin每个机器的这个名称都不一致。
进入到C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下执行如下命令:
mysqlbinlog -vv --base64-output=decode-rows DESKTOP-PNQ97MJ-bin.000181 > E:/mysql/181.txt
#将.000181的二进制文件转换为可读性的文本文件
查看181.txt文件内容,找到新增id为3和4新增数据的记录。开始:321,结束:875
使用binlog命令进行数据恢复:
mysqlbinlog --start-position=321 --stop-position=875 --database=db_test DESKTOP-PNQ97MJ-bin.000181 | mysql -uroot -p123456
–start-position:开始行号,需要恢复数据的开始行号。不设置则从binlog文件开头恢复
–stop-position:结束行号,需要恢复数据的结束行号。不设置则恢复到binlog文件末尾处
–database:恢复指定的数据库,一个binlog文件中可能不只有一个数据的日志记录,因此需要指定。不设置则恢复所有数据库
DESKTOP-PNQ97MJ-bin.000181:需要恢复的指定binlog文件名
恢复后效果如下:
mysql> select * from blog_user;
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 3 | admin2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 4 | zhangsan2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
4 rows in set (0.00 sec)
进入到C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下执行如下命令:
mysqlbinlog -vv --base64-output=decode-rows DESKTOP-PNQ97MJ-bin.000182 > E:/mysql/182.txt
#将.000182的二进制文件转换为可读性的文本文件
查看182.txt文件内容,找到删除数据库前最近的commi的操作记录。结束行号:875
再次使用binlog命令进行数据恢复,恢复到删除数据库的操作前那时候的数据:
mysqlbinlog --stop-position=875 --database=db_test DESKTOP-PNQ97MJ-bin.000182 | mysql -uroot -p123456
恢复后效果如下:
mysql> select * from blog_user;
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| userid | username | password | userphone | userstate | createtime | uptime | remark | userimg |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
| 1 | admin | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 2 | zhangsan | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 3 | admin2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 4 | zhangsan2 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 5 | admin5 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
| 6 | zhangsan6 | 123456 | 17371431054 | 1 | 2020-12-24 19:22:25 | NULL | NULL | NULL |
+--------+-----------+----------+-------------+-----------+---------------------+--------+--------+---------+
6 rows in set (0.00 sec)
综上述发现,数据已恢复到删除数据库前的那个步骤的数据,已实现删库后的数据恢复。
意见建议
- 生产环境若每天数据量增量较大,建议每天定时备份一个全库
- 保留最近7天的全库备份记录即可
- 数据恢复时采用全库恢复,加上全库备份之后新的binlog日志结合实现数据恢复
- 综上是自身系统的一个恢复模拟案例,若有不足请指出