一.在没有备份数据的情况下,突然断电导致表损坏的修复方法。
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.全备:
优点:恢复只需一次
缺点:占用磁盘空间,每次全备都有重复数据