生产数据库数据误删、错刷恢复实战
文章目录
故障起因
事件：开发错刷数据
错刷时间：
2022-06-29 10:30:00需求：恢复数据时间点到
2022-06-29 10:02:00之前PS：错刷和删除导致的影响是一样的，此案例只是错刷了数据，不过需求是一样的，都是需要恢复到指定时间点的状态
前提
全备、增备
全备
全备脚本
#!/bin/bash
BakDir=/backup/mysql
LogFile=/backup/mysql/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
/usr/bin/mysqldump -h xxx -uroot -pxxx --quick --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile
/bin/tar czvf $GZDumpFile $DumpFile
/bin/rm -f $DumpFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile successful >> $LogFile
if [ -d $BakDir/daily ]
then
cd $BakDir/daily
/bin/rm -f *
fi
增备
数据库配置要求
在开始增量备份之前，我们需要检查
mysql是否开开启了binlog日志，可以登录mysql，通过如下指令查看：如果
log_bin的value值为ON则表示开启了，如果是OFF，则需要到my.cnf配置文件中的mysqld节点下新增log-bin=mysql-bin的配置，当然这里mysql-bin是自己定义的值，表示logbin的文件名，你可以根据自己需求指定到某个目录下。然后再重启mysql即可。
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /home/mysql/mysql/data/mysql-bin |
| log_bin_index | /home/mysql/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
增备脚本
#!/bin/bash
#################### 变量 ####################
#!/bin/bash
# 增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
BakDir=/backup/mysql/daily
BinDir=/home/mysql/binlog
LogFile=/backup/mysql/bak.log
BinFile=/home/mysql/binlog/mysql-bin.index
#################### 命令 ####################
if [ ! -d $BakDir ]
then
mkdir $BakDir
fi
/usr/bin/mysqladmin -h xxx -uroot -pxxx flush-logs
#这个是用于产生新的mysql-bin.00000*文件
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。
for file in `cat $BinFile`
do
base=`basename $file`
#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
if (test -e $dest)
#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去。
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` Bakup successful! >> $LogFile
定时备份
0 */1 * * * /usr/bin/sh /script/local_mysqldaily.sh &>/dev/null # 增量备份
5 0 * * * /usr/bin/sh /script/local_mysqlfull.sh &>/dev/null # 全量备份
故障处理
思路
1、恢复昨晚的全备；
2、恢复增备：昨晚全备时间前的整点~当前时间的整点
PS1：业务不忙时，暂停一切对数据库的插入操作，包括开发、测试，直接恢复数据到原数据库，完成后通知开发和测试；
PS2：业务繁忙时，临时紧急部署新的数据库，全备源数据库的数据，导入到新数据库，再陆续导入增备
binlog即可，完成后通知开发和测试；
全备恢复
# 恢复昨晚的全备
[root@h156 ~]# ll /backup/mysql/ | grep 20220628
-rw-r--r-- 1 root root 1.8G Jun 29 11:19 20220628.sql.tgz
# 导入到数据库
[root@h156 ~]# mysql -h test.ehoo100.com -uroot -peHu2016 < tmp/20220628.sql
解析增备
需求是恢复到
10:01，故只导出29号00:00~10:53的binlog数据即可
2022-06-29 13:09:50开始恢复
新建binlog解析导出目录
用作导出
binlog日志数据数据的目录
# 新建binlog导出恢复数据的目录
[root@h156 ~]# mkdir -p /backup/mysql/daily
查看自动增量备份的binlog
查看并筛选
binlog备份的数据PS：只导出整点的备份即可，记住是整点（如
00:00、01:00）了解：假设如需恢复数据到
00:05，则可以指定到mysql-bin.052844这个文件哦
[root@h156 ~]# ll /home/mysql/
···
-rw-r----- 1 polkitd input 2.4M Jun 28 22:00 mysql-bin.052841
-rw-r----- 1 polkitd input 2.2M Jun 28 23:00 mysql-bin.052842
-rw-r----- 1 polkitd input 2.3M Jun 29 00:00 mysql-bin.052843
-rw-r----- 1 polkitd input 239K Jun 29 00:05 mysql-bin.052844
-rw-r----- 1 polkitd input 2.0M Jun 29 01:00 mysql-bin.052845
-rw-r----- 1 polkitd input 2.2M Jun 29 02:00 mysql-bin.052846
-rw-r----- 1 polkitd input 2.3M Jun 29 03:00 mysql-bin.052847
-rw-r----- 1 polkitd input 2.2M Jun 29 04:00 mysql-bin.052848
-rw-r----- 1 polkitd input 2.2M Jun 29 05:00 mysql-bin.052849
-rw-r----- 1 polkitd input 2.3M Jun 29 06:00 mysql-bin.052850
-rw-r----- 1 polkitd input 2.1M Jun 29 07:00 mysql-bin.052851
-rw-r----- 1 polkitd input 2.2M Jun 29 08:00 mysql-bin.052852
-rw-r----- 1 polkitd input 2.2M Jun 29 09:00 mysql-bin.052853
-rw-r----- 1 polkitd input 2.3M Jun 29 10:00 mysql-bin.052854
-rw-r----- 1 polkitd input 2.1M Jun 29 10:53 mysql-bin.052855
-rw-r----- 1 polkitd input 253K Jun 29 11:00 mysql-bin.052856
-rw-r----- 1 polkitd input 312K Jun 29 11:07 mysql-bin.052857
-rw-r----- 1 polkitd input 625M Jun 29 12:00 mysql-bin.052858
-rw-r----- 1 polkitd input 1.1G Jun 29 12:09 mysql-bin.052859
-rw-r----- 1 polkitd input 1.1G Jun 29 12:20 mysql-bin.052860
-rw-r----- 1 polkitd input 1.1G Jun 29 12:31 mysql-bin.052861
-rw-r----- 1 polkitd input 1.1G Jun 29 12:41 mysql-bin.052862
-rw-r----- 1 polkitd input 668M Jun 29 12:46 mysql-bin.052863
-rw-r----- 1 polkitd input 225K Jun 29 12:41 mysql-bin.index
···
解析导出binlog日志数据
最后一条导出要指定截止时间，需求是
10:01，则指定为2022-06-29 10:02:00，最终导出结果是：2022-06-29 10:01:58
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/11.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/2.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/3.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/4.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/5.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/6.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/7.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/8.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/9.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/10.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysql -h xxx -uroot -pxxx < /backup/mysql/daily/11.sql
Warning: Using a password on the command line interface can be insecure.
[root@h156 ~]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v --stop-datetime="2022-06-29 10:02:00" /home/mysql/mysql-bin.052855 > /backup/mysql/daily/12.sql
Warning: Using a password on the command line interface can be insecure.
# 查看导出数据
[root@h156 ~]# ll /backup/mysql/daily/
total 43M
-rw-r--r-- 1 root root 4.0M Jun 29 11:52 10.sql
-rw-r--r-- 1 root root 4.0M Jun 29 11:52 11.sql
-rw-r--r-- 1 root root 175K Jun 29 11:57 12.sql
-rw-r--r-- 1 root root 4.0M Jun 29 11:49 1.sql
-rw-r--r-- 1 root root 3.5M Jun 29 11:50 2.sql
-rw-r--r-- 1 root root 3.8M Jun 29 11:51 3.sql
-rw-r--r-- 1 root root 4.0M Jun 29 11:51 4.sql
-rw-r--r-- 1 root root 3.8M Jun 29 11:51 5.sql
-rw-r--r-- 1 root root 3.8M Jun 29 11:51 6.sql
-rw-r--r-- 1 root root 4.0M Jun 29 11:51 7.sql
-rw-r--r-- 1 root root 3.8M Jun 29 11:52 8.sql
-rw-r--r-- 1 root root 3.8M Jun 29 11:52 9.sql
# 查看最后一条数据时间戳，时间为：10:01:57，达到了我们的要求
[root@h156 ~]# tail -20 /backup/mysql/daily/12.sql | grep end_log_pos
#220629 10:01:57 server id 1001 end_log_pos 101945 CRC32 0xc6d0a797 Xid = 85545215
增备恢复
方式2：库内导入
推荐，速度快、且不泄露数据库密码
登入数据库，
source导出的sql即可（严格按照顺序执行）
source /backup/mysql/daily/1.sql;
source /backup/mysql/daily/2.sql;
source /backup/mysql/daily/3.sql;
source /backup/mysql/daily/4.sql;
source /backup/mysql/daily/5.sql;
source /backup/mysql/daily/6.sql;
source /backup/mysql/daily/7.sql;
source /backup/mysql/daily/8.sql;
source /backup/mysql/daily/9.sql;
source /backup/mysql/daily/10.sql;
source /backup/mysql/daily/11.sql;
source /backup/mysql/daily/12.sql;
方式2：库外导入
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/1.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/2.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/2.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/3.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/4.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/5.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/6.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/7.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/8.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/9.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/10.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/11.sql
mysql -h test.ehoo100.com -uroot -peHu2016 < /backup/mysql/daily/12.sql
检查数据恢复
查看最新数据是否为
2022-06-29 10:02:00之前
恢复之前查看
最新数据为：
2022-06-28 20:31:24
恢复之后查看
最新数据为：
2022-06-29 10:00:20，至此数据恢复成功！
