一.在没有备份数据的情况下，突然断电导致表损坏的修复方法。
Mysql生产案例（这里用world.sql模拟）
在没有备份数据的情况下，突然断电导致表损坏的修复方法。
[root@db01 ~]# cd /usr/local/mysql/data
#打包库目录
[root@db01 data]# tar czf world.tar.gz world
#查看库目录
[root@db01 data]# ll /usr/local/mysql/data
total 227524
drwx------ 2 mysql mysql 144 Jul 16 09:58 world
-rw-r--r-- 1 root root 164896 Jul 16 19:18 world.tar.gz
#将打包好的world库目录远程拷贝到db04(10.0.0.53)
[root@db01 data]# scp world.tar.gz 10.0.0.54:/usr/loca/mysql/data/
The authenticity of host '10.0.0.53 (10.0.0.53)' can't be established.
ECDSA key fingerprint is SHA256:KUGHqLcxD0N8xp81uH8qyAu+/AndEmwYkCYOC0K8CfQ.
ECDSA key fingerprint is MD5:cb:6c:73:26:13:95:05:a0:60:24:e3:c6:cb:c3:e6:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.53' (ECDSA) to the list of known hosts.
root@10.0.0.54's password:
world.tar.gz 100%
161KB 31.6MB/s
#在db04上查看库目录包是否过来
[root@db04 data]# ll
total 110776
-rw-r--r-- 1 root root 164896 Jul 16 19:19 world.tar.gz
#解压库目录包
[root@db04 data]# tar xf world.tar.gz
#启动数据库
[root@db04 data]# /etc/init.d/mysqld start
#登录数据库查看
mysql> show databases;
#查询表中数据
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist
#找到以前的表结构在新库中创建表(db01)
mysql> show create table world.city;
##删除外键创建语句
CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
#查询表数据
mysql> select * from city_new;
Empty set (0.00 sec)
#删除表空间
mysql> alter table city_new discard tablespace;
Query OK, 0 rows affected (0.01 sec)
#拷贝旧表空间文件
[root@db04 world]# cp city.ibd city_new.ibd
#授权
[root@db04 world]# chown -R mysql.mysql city_new.ibd
#导入表空间
mysql> alter table city_new import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)
二.企业案例（mysqldump）
1.背景
1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？
2.思路
1.停库，避免二次伤害
2.创建新库
3.倒入前一天的全备
4.通过binlog找到前一天23：00到第二天10点之间的数据
5.导入找到的新数据
6.恢复业务
a.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
b.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
3.模拟案例
1）模拟生产数据
mysql> create database dump;
mysql> use dump
mysql> create table dump(id int);
mysql> insert dump values(1),(2),(3),(4);
mysql> select * from dump;
2）模拟23:00全备
[root@db03 mysql]# mysqldump -uroot -p123 -B dump -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
3）模拟23：00到10:00的数据操作
mysql> use dump
mysql> insert dump values(1000),(2000),(3000),(4000);
4）模拟删库
mysql> drop database dump;
Query OK, 1 row affected (0.00 sec)
4.恢复数据
1）停库，避免二次伤害
[root@db03 mysql]# systemctl stop mysqld
2）创建新库
3）导入前一天的全备
#老库将数据传输到新库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
#新库导入全备数据
[root@db02 ~]# mysql < /tmp/full.sql
4）通过binlog找到前一天23：00到第二天10点之间的数据
1.找到binlog的起始位置点
[root@db03 data]# head -22 /tmp/full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=971790;
2.找到结束位置点
[root@db03 data]# mysqlbinlog mysql-bin.000006 > 1.txt
[root@db03 data]# vim 1.txt
过滤 drop database dump，取出命令上面的位置点
# at 972068
......
drop database dump
3.取出位置点之间的数据
[root@db03 data]# mysqlbinlog -d dump --start-position=971790 --stop-position=972068 mysql-bin.000006 > /tmp/new.sql
5）导入找到的新数据
#老库将binlog数据传到新库
[root@db03 ~]# scp /tmp/new.sql 172.16.1.52:/tmp/
#新库导入新的数据
[root@db02 ~]# mysql < /tmp/new.sql
6）确认数据
mysql> use dump
mysql> show tables;
+----------------+
| Tables_in_dump |
+----------------+
| dump |
+----------------+
mysql> select * from dump;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1000 |
| 2000 |
| 3000 |
| 4000 |
+------+
7）恢复业务
1.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
2.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
1）新库导出指定业务库
[root@db02 ~]# mysqldump dump > /tmp/dump.sql
2）新库将数据推送回老库
[root@db02 ~]# scp /tmp/dump.sql 172.16.1.53:/tmp
3）将恢复的数据导入老库
mysql> create database dump;
mysql> use dump;
mysql> source /tmp/dump.sql
三.物理备份Xtrabackup
原理
物理备份(Xtrabackup)
相对于逻辑备份利用查询提取数据中的所有记录，物理备份更直接，拷贝数据库文件和日志来完成备份，因此速度会更快。当然，无论是开源的Mydumper还是官方最新的备份工具(5.7.11的mysqlpump)都支持了多线程备份，所以速度差异可能会进一步缩小，至少从目前生产环境来看，物理备份使用还是比较多的。由于Xtrabackup支持备份innodb表，实际生产环境中我们使用的工具是innobackupex，它是对xtrabackup的一层封装。innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，innobackupex的基本流程如下：
1.开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志；
2.开启ibd文件拷贝线程，拷贝innodb表的数据
3.ibd文件拷贝结束，通知调用FTWRL，获取一致性位点
4.备份非innodb表(系统表)和frm文件
5.由于此时没有新事务提交，等待redo日志拷贝完成
6.最新的redo日志拷贝完成后，相当于此时的innodb表和非innodb表数据都是最新的
7.获取binlog位点，此时数据库的状态是一致的。
8.释放锁，备份结束。
Xtrabackup的改进
无论是mysqldump，还是innobackupex备份工具，为了获取一致性位点，都强依赖于FTWRL。这个锁杀伤力非常大，因为持有锁的这段时间，整个数据库实质上不能对外提供写服务的。此外，由于FTWRL需要关闭表，如有大查询，会导致FTWRL等待，进而导致DML堵塞的时间变长。即使是备库，也有SQL线程在复制来源于主库的更新，上全局锁时，会导致主备库延迟。从前面的分析来看，FTWRL这把锁持有的时间主要与非innodb表的数据量有关，如果非innodb表数据量很大，备份很慢，那么持有锁的时间就会很长。即使全部是innodb表，也会因为有mysql库系统表存在，导致会锁一定的时间。
为了解决这个问题，Percona公司对Mysql的Server层做了改进，引入了BACKUP LOCK。
具体而言，通过”LOCK TABLES FOR BACKUP”命令来备份非innodb表数据；通过”LOCK BINLOG FOR BACKUP”来获取一致性位点，尽量减少因为数据库备份带来的服务受损。我们看看采用这两个锁与FTWRL的区别：
LOCK TABLES FOR BACKUP
作用：备份数据
1.禁止非innodb表更新
2.禁止所有表的ddl
优化点：
1.不会被大查询堵塞(关闭表)
2.不会堵塞innodb表的读取和更新，这点非常重要，对于业务表全部是innodb的情况，则备份过程中DML完全不受损
UNLOCK TABLES
LOCK BINLOG FOR BACKUP
作用：获取一致性位点。
1.禁止对位点更新的操作
优化点：
1.允许DDl和更新，直到写binlog为止。
UNLOCK BINLOG
原理图
完整备份过程如图:：
思考
企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
全量备份与恢复步骤
## 安装Xtrabackup
# 1.上传rpm安装包
[root@db01 ~]# rz -E percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 2.安装依赖
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# 3.安装Xtrabackup
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 4.准备备份目录
[root@db01 ~]# mkdir /backup
# 5.全备
#去掉时间戳进行备份
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
# 6.查看全备内容
[root@db01 ~]# ll /backup/full/
total 12316
-rw-r----- 1 root root 418 Aug 11 19:03 backup-my.cnf
-rw-r----- 1 root root 12582912 Aug 11 19:02 ibdata1
drwxr-x--- 2 root root 4096 Aug 11 19:03 mysql
drwxr-x--- 2 root root 4096 Aug 11 19:03 performance_schema
drwxr-x--- 2 root root 56 Aug 11 19:03 test
drwxr-x--- 2 root root 144 Aug 11 19:03 world
-rw-r----- 1 root root 27 Aug 11 19:03 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Aug 11 19:03 xtrabackup_checkpoints
-rw-r----- 1 root root 492 Aug 11 19:03 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 11 19:03 xtrabackup_logfile
# 7.删除库
mysql> drop database world;
Query OK, 3 rows affected (0.14 sec)
mysql> drop database test;
Query OK, 1 row affected (0.42 sec)
mysql> drop database performance_schema;
Query OK, 52 rows affected (0.02 sec)
# 8.停止数据库
[root@db01 ~]# systemctl stop mysqld.service
# 9.手动模拟CSR过程
#将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full
# 10.恢复数据
##进入mysql目录下
[root@db01 ~]# cd /usr/local/mysql
##移走原数据目录
[root@db01 mysql]# mv data data.back
##将全备的数据目录迁移回来
[root@db01 mysql]# cp -r /backup/full ./data
##授权
[root@db01 mysql]# chown -R mysql.mysql data
# 11.启动数据库再次查看数据
[root@db01 data]# systemctl start mysqld
[root@db01 data]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| world |
+--------------------+
5 rows in set (0.01 sec)
数据恢复
增量备份与恢复步骤
1）先全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
2）写入新数据
[root@db03 ~]# mysql -uroot -p123
mysql> use test;
mysql> create table usertb2(id int,name varchar(12));
Query OK, 0 rows affected (0.01 sec)
mysql> insert usertb2(id,name) values(1,'zx');
Query OK, 1 row affected (0.01 sec)
mysql> select * from usertb2;
+------+------+
| id | name |
+------+------+
| 1 | zx |
+------+------+
1 row in set (0.00 sec)
3）第一次增备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-08-11 /backup/inc1
参数说明:
--incremental：开启增量备份功能
--incremental-basedir：上一次备份的路径
#验证
[root@db01 ~]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1264390194
last_lsn = 1264390194
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264390194 #确保与上一次全备数据一致
to_lsn = 1264397384
last_lsn = 1264397384
compact = 0
recover_binlog_info = 0
4）再次写入数据
[root@db01 ~]# mysql -uroot -p123
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertb |
| usertb2 |
+----------------+
2 rows in set (0.01 sec)
mysql> insert usertb2(id,name) values(2,'zjy'),(3,'gcy'),(4,'nl');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from usertb2;
+------+------+
| id | name |
+------+------+
| 1 | zx |
| 2 | zjy |
| 3 | gcy |
| 4 | nl |
+------+------+
4 rows in set (0.00 sec)
5）第二次增备
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2
#验证
[root@db01 ~]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1264390194
last_lsn = 1264390194
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264390194 #确保与上一次增备数据一致
to_lsn = 1264397384
last_lsn = 1264397384
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264397384 #确保与上一次增备数据一致
to_lsn = 1264401511
last_lsn = 1264401511
compact = 0
recover_binlog_info = 0
6）再次写入数据
[root@db01 ~]# mysql -uroot -p123
mysql> use test
mysql> insert usertb2(id,name) values(5,'aaa'),(6,'bbb'),(7,'ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from usertb2;
+------+------+
| id | name |
+------+------+
| 1 | zx |
| 2 | zjy |
| 3 | gcy |
| 4 | nl |
| 5 | aaa |
| 6 | bbb |
| 7 | ccc |
+------+------+
7 rows in set (0.00 sec)
7）第三次增量备份
[root@db03 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc2/ /backup/inc3
##验证
[root@db01 ~]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1264390194
last_lsn = 1264390194
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264390194 #确保与上一次增备数据一致
to_lsn = 1264397384
last_lsn = 1264397384
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264397384 #确保与上一次增备数据一致
to_lsn = 1264401511
last_lsn = 1264401511
compact = 0
recover_binlog_info = 0
[root@db01 ~]# cat /backup/inc3/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1264401511 #确保与上一次增备数据一致
to_lsn = 1264404150
last_lsn = 1264404150
compact = 0
recover_binlog_info = 0
6.Xtrabackup增量恢复数据
1）将全备执行redo
[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-08-11
2）将第一次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-08-11
#验证
[root@db01 backup]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1264397384
last_lsn = 1264397384 #该值本来是inc1的位置点
compact = 0
recover_binlog_info = 0
3）将第二次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-08-11
#验证
[root@db01 backup]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1264401511
last_lsn = 1264401511 #该值本来是inc2的位置点
compact = 0
recover_binlog_info = 0
4）将最后一次增备执行redo和undo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-08-11
#验证
[root@db01 backup]# cat /backup/full_2020-08-11/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1264404150
last_lsn = 1264404150 #该值为inc3的位置点
compact = 0
recover_binlog_info = 0
5）将整体数据进行一次CSR
[root@db01 backup]# innobackupex --apply-log /backup/full_2020-08-11/
6）恢复数据
[root@db01 mysql]# mv data data.bak
[root@db01 mysql]# innobackupex --copy-back /backup/full_2020-08-11/
[root@db01 mysql]# chown -R mysql.mysql data
[root@db01 mysql]# systemctl restart mysqld
[root@db01 mysql]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| world |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertb |
| usertb2 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from usertb2;
+------+------+
| id | name |
+------+------+
| 1 | zx |
| 2 | zjy |
| 3 | gcy |
| 4 | nl |
| 5 | aaa |
| 6 | bbb |
| 7 | ccc |
+------+------+
7 rows in set (0.01 sec)
##数据恢复
7.总结
1.增备：
优点：占用磁盘空间小，没有重复数据
缺点：恢复麻烦
2.全备：
优点：恢复只需一次
缺点：占用磁盘空间，每次全备都有重复数据
