生产数据库数据误删、错刷恢复实战

文章目录

• 故障起因
• 前提
• • 全备
  • • 全备脚本
  • 增备
  • • 数据库配置要求
    • 增备脚本
  • 定时备份
• 故障处理
• • 思路
  • 全备恢复
  • 解析增备
  • • 新建binlog解析导出目录
    • 查看自动增量备份的binlog
    • 解析导出binlog日志数据
  • 增备恢复
  • • 方式2：库内导入
    • 方式2：库外导入
  • 检查数据恢复
  • • 恢复之前查看
    • 恢复之后查看

故障起因

事件：开发错刷数据

错刷时间：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是否开开启了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，

至此数据恢复成功！