mysql8.x通过备份文件及binlog日志恢复数据

问题简述

记一次mysql数据库被误删（是整个库被删了）后的还原

前提条件

• 数据库版本为mysql8.x以上
• 具有库被删除前的完整备份
• 数据库开启binlog
• 最近备份时间不能超过日志删除时间

#查看数据库是否开启binlog
show variables like 'log_bin';
#默认binlog存储位置
/var/lib/mysql
#查看所有binlog文件-【没啥用】
SHOW BINARY LOGS
#查看binlog日志默认保留时间-【0表示不自动删除日志文件，日志删除时间小于上一次备份时间则无法还原，比如7月6删除日志，但完整备份是7月1的】
SHOW VARIABLES LIKE 'expire_logs_days';

还原步骤

第一步：通过完整备份还原被删的库

注意事项： 还原后切勿让其他用户连接，操作数据库。待使用binlog日志恢复数据后再对库进行操作，否则可能会丢失数据

第二步：查找日志位于哪个文件

进入binlog日志所在目录，根据最近修改时间，大致判断需要还原的位置处于哪个日志文件，因为我们在每日凌晨1点有做全量备份，所以对应找到00:25到当前时间所在的那份日志mysql-bin.000335

第三步：查看数据被破坏时间点

通过第二步找到的binlog日志文件，我们可以将其转为SQL文件查看（PS：直接用notepad打开会乱码），或者使用专有工具打开，此处将其转为SQL文件

/usr/bin/mysqlbinlog  /var/lib/mysql/mysql-bin.000335 > /var/lib/mysql/mysql-bin.000335.sql

通过搜索drop关键字查看破坏数据的时间点在2022-09-29 10:21:24，我们选择将数据还原到2022-09-29 10:20:59

第四步：还原数据

还原前还需要大致确定我们需要还原的时间范围，此处我们选择还原2022-09-29 08:00:00到2022-09-29 10:20:59之间的数据，可以使用以下命令

#还原指定数据库
/usr/bin/mysqlbinlog  --database=istrong_portal --start-datetime="2022-09-29 08:00:00" --stop-datetime="2022-09-29 10:20:59" /var/lib/mysql/mysql-bin.000335 | mysql -h127.0.0.1 -uroot -pPWDxxxxx

#还原所有数据库
/usr/bin/mysqlbinlog  --start-datetime="2022-09-29 08:00:00" --stop-datetime="2022-09-29 10:20:59" /var/lib/mysql/mysql-bin.000335 | mysql -h127.0.0.1 -uroot -pPWDxxxxx

注意事项

1、建议还原的时候停止系统，或者切断系统与数据库的连接，否则可能会出现系统日志实时写入库中，还原时报记录不一致错误
2、还原时需要找准对应的时间起止点，否则还原会出现数据不一致，还原的结果不是想要的情况
3、binlog还可以通过位置的起止点进行还原