• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

MySQL模拟生产备份并恢复数据(三种方法)

MySQL zkinogg 5个月前 (08-11) 54次浏览 0个评论

MySQL模拟生产备份并恢复数据

一.在没有备份数据的情况下,突然断电导致表损坏的修复方法。

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.全备:
    优点:恢复只需一次
    缺点:占用磁盘空间,每次全备都有重复数据


极客公园 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL模拟生产备份并恢复数据(三种方法)
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!