MySQL企业案例
企业案例
1.企业故障恢复案例
背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本
故障时间点：
上午10点开发人员误删除一个核心业务表，如何恢复？
思路：
1）停业务避免数据的二次伤害
2）找一个临时的库，恢复前一天的全备
3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4）测试可用性和完整性
5）开启业务前的两种方式
a.直接使用临时库顶替原生产库，前端应用割接到新库
b.将误删除的表单独导出，然后导入到原生产环境
6）开启业务
模拟数据
#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into proc.proc1 value($num);commit;"
(( num++ ))
sleep 1
done
备份
[root@db02 ~]# mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz
模拟误删除数据
mysql> drop table proc.proc;
恢复思路
1）停业务避免数据的二次伤害
[root@db02 ~]# /etc/init.d/mysqld stop
2) 准备新环境
[root@m01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@m01 scripts]# /etc/init.d/mysqld start
3）找一个临时的库，恢复前一天的全备
[root@db02 ~]# scp /tmp/full_2022-08-19.sql.gz 172.16.1.61:/tmp/
[root@m01 scripts]# zcat /tmp/full_2022-08-19.sql.gz |mysql
3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
起始位置点：
[root@db02 ~]# zcat /tmp/full_2022-08-19.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=7138;
结束位置点：42855
第二段起始位置点：42975
第二段结束位置点：58870
[root@db02 ~]# mysqlbinlog --start-position=7138 --stop-position=42855 /application/mysql/data/mysql-bin.000002 > /tmp/inc1.sql
[root@db02 ~]# mysqlbinlog --start-position=42975 --stop-position=58870 /application/mysql/data/mysql-bin.000002 > /tmp/inc2.sql
[root@db02 ~]# scp /tmp/inc* 172.16.1.61:/tmp/
4）测试可用性和完整性
5）开启业务前的两种方式
a.直接使用临时库顶替原生产库，前端应用割接到新库
b.将误删除的表单独导出，然后导入到原生产环境
6）开启业务
2.企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？？？
模拟数据
#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into proc.proc1 value($num);commit;"
(( num++ ))
sleep 1
done
备份
## 上周六全备 周六 00点 备周一到周五数据
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
[root@db02 ~]# cat /backup/full_2022-08-19/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2335986976
last_lsn = 2335986976
compact = 0
recover_binlog_info = 0
## 第一次增备 周日的00点 备的周六增量数据 周六00点之后到周日00点之前
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/full_$(date +%F) /backup/inc_6
[root@db02 ~]# cat /backup/inc_6/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335986976
to_lsn = 2336208335
last_lsn = 2336223316
compact = 0
recover_binlog_info = 0
## 第二次增备 周一的00点 备的周日增量数据 周日00点之后到周一00点之前
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_6 /backup/inc_7
[root@db02 ~]# cat /backup/inc_7/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2336208335
to_lsn = 2336236884
last_lsn = 2336249656
compact = 0
recover_binlog_info = 0
## 第三次增备 周二的00点 备的周一增量数据 周一00点之后到周二00点之前
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_7 /backup/inc_1
[root@db02 ~]# cat /backup/inc_1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2336236884
to_lsn = 2336264378
last_lsn = 2336264942
compact = 0
recover_binlog_info = 0
## 第四次增备 周三的00点 备的周二增量数据 周二00点之后到周三00点之前
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_1 /backup/inc_2
[root@db02 ~]# cat /backup/inc_2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2336264378
to_lsn = 2336273708
last_lsn = 2336273708
compact = 0
recover_binlog_info = 0
## binlog截取 周三00点之后到周三下午14点之间的数据
删除数据
mysql> select * from ts;
+----+------+
| id | A |
+----+------+
| 1 | 300 |
| 2 | 200 |
+----+------+
mysql> drop table test.ts;
恢复思路
1.停业务，停库
[root@db02 ~]# /etc/init.d/mysqld stop
2.准备新环境
3.清空data目录
[root@db02 ~]# mv /application/mysql/data/ /usr/local/src/
4.重做数据
1)全备只做redo不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only /backup/full_2022-08-19/
2)周六的增量数据合并到full中只做redo不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_6 /backup/full_2022-08-19/
3)周日六的增量数据合并到full中只做redo不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_7 /backup/full_2022-08-19/
4)周一的增量数据合并到full中只做redo不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_1 /backup/full_2022-08-19/
5)周二的增量数据合并到full中redo和undo都做
[root@db02 ~]# innobackupex --apply-log --incremental-dir=/backup/inc_2 /backup/full_2022-08-19/
6)全备整体做一遍redo和undo
[root@db02 ~]# innobackupex --apply-log /backup/full_2022-08-19/
5.恢复数据
[root@db02 ~]# innobackupex --copy-back /backup/full_2022-08-19/
6.授权
[root@db02 ~]# chown -R mysql.mysql /application/mysql/data
7.启动数据库
[root@db02 ~]# /etc/init.d/mysqld start
8.binlog截取 周三00点之后到周三下午14点之间的数据
第一段起始位置点：184023
[root@db02 ~]# cat /backup/full_2022-08-19/xtrabackup_binlog_info
mysql-bin.000003 184023
[root@db02 ~]# mysqlbinlog -vvv --base64-output=decode-row /usr/local/src/data/mysql-bin.000003 |grep -i drop -C 5
第一段结束位置点：200666
第二段起始位置点：200781
[root@db02 ~]# mysqlbinlog -vvv --base64-output=decode-row /usr/local/src/data/mysql-bin.000003
第二段结束位置点：205830
## 截取
[root@db02 ~]# mysqlbinlog --start-position=184023 --stop-position=200666 /usr/local/src/data/mysql-bin.000003 > /tmp/inc_1.sql
[root@db02 ~]# mysqlbinlog --start-position=200781 --stop-position=205830 /usr/local/src/data/mysql-bin.000003 > /tmp/inc_2.sql
