mysql配置数据恢复_MySQL 数据恢复

前言

前两天因为没注意的误操作, 直接把某个数据表清掉了, 心慌慌. 怪自己学艺不精, 当时整了一下午也没把数据找回来. 当晚回来闭关研究, 终于在凌晨1点多整出来了, 特此记录, 以备不时之需.

对于 MySQL 数据的备份, 主要有两种: 全量备份和增量备份.

全量备份: 将数据库中的所有数据全部进行备份. 相当于复制粘贴的步骤. 全量备份要保存所有数据, 占用空间大, 必然不可能精确到每一秒.

增量备份: 对数据库的所有变动进行备份. 增量备份可以将数据库的变动全部保存下来, 但也不可能一直保存, 否则备份文件的体积超级大.

而对数据库数据的恢复操作, 思路也很简单, 使用全量备份和增量备份相配合, 以某个时间点的全量备份为基础, 通过增量备份使数据库数据恢复到具体的某个时间节点.

为了防止下次遇到这种情况再抓狂, 我决定对数据库进行定时备份. 通过全量和增量的备份文件来面对下一次事故的发生(当然, 我是肯定不会再出现了, 防止之后其他人误操作吧)

数据的备份操作基本如下:

每天对数据库进行全量备份

保持 binlog 的增量备份

当需要恢复时, 找到当天的全量备份数据恢复, 然后在这基础上进行增量恢复即可恢复到某个特定的时间点. 好, 开搞.

定时全量备份

思路很简单, 通过mysqldump命令进行全量备份, 一个简单的定时 shell 脚本即可满足. 脚本如下:

#!/bin/bash

# 备份文件保留天数

FileRetainDay=30

# 备份目录

BakDir=/Users/hujing/dir/tmp/mysqldump_log

# 数据库用户名

User=root

# 数据库密码

Password=root

# 今天的日期

Date=`date +%Y-%m-%d`

cd $BakDir

mysqldump -u$User -p$Password --quick --all-databases --flush-logs > $Date.sql

# --quit: 不使用缓存

# --flush-logs: 刷新数据库的 logbin 文件

# --all-databases: 备份所有数据库

# 删除30天前的备份文件

find $BakDir -mtime +$FileRetainDay -type f -name "*.sql" | xargs rm

脚本很简单. 接下来通过crontab定时每天跑一次:

1 0 * * * bash /Users/hujing/dir/tmp/mysql_back.sh

每天的12点1分跑一次. 这样30天以内的数据备份文件就有了.

增量备份

全量备份搞定了, 剩下增量备份. 增量备份就是 mysql 的binlog了.

以下几个 MySQL 的命令行查询命令可查看当前binlog状态:

show binary logs 查看当前存在的 log 文件

show variables like '%log_bin%' 查看 binlog 是否开启及文件位置

show master status 查看当前使用的 binlog

show binlog events in 'binlog.000001' 查看日志文件内容

binlog相关配置:

# 开启 binlog

log_bin=ON

# binlog 日志文件前缀

log_bin_basename=/var/lib/mysql/binlog

# 索引文件

log_bin_index=/var/lib/mysql/binlog.index

# 文件过期时间, 过期文件会自动删除

expire_logs_days=7

# 每个文件的最大保存大小

max_binlog_size=1024M

很好, 现在我们也有了增量备份文件.

数据恢复

当需要恢复数据时, 如何根据这两个备份文件进行恢复呢?

打个比方, 当前时间是: 2020-9-4 23:00:00. 此时, 我做了清表的疯狂操作. 如何恢复数据?

1. 通过全量备份, 将数据恢复到今天凌晨的时刻

进入 MySQL命令行, 执行数据恢复文件:

source /Users/hujing/dir/tmp/mysqldump_log/2020-09-04.sql

此时, 数据库已经恢复到本日凌晨的时刻. 请注意, 这步操作会将今日生成的数据删除.

2. 通过增量备份, 将数据恢复到指定时间节点

通过mysqlbin工具, 将指定时间的数据库变更操作导出:

mysqlbinlog -v --start-datetime="2020-09-04 00:00:00" --stop-datetime="2020-09-04 23:00:00" /var/lib/mysql/binlog.0000* > s.sql

同样通过source命令执行此sql文件. 此时, 数据就已经恢复了.

肤浅的记录一下, 虽然现在可以恢复数据了, 但是还有很多问题没有解决, 比如, 如何只恢复一张表的数据等等.

吃一堑长一智, 之后对数据库的操作要慎之又慎.