mysql备份灵活恢复

mysql备份灵活恢复

服务上线遇到一个问题，开始操作前做了全库备份，但是没有做要操作的库备份，如果操作过程出现失败情况需要回退时，直接用全备文件做全库恢复很不妥当。

通过mysql的全备份文件，可以比较灵活的筛选出某个单独的 库或者单独的表的备份内容，从而灵活的恢复。同时，也可以直接用全备文件恢复单独的库。下面记录一下这些操作方式。

从全备份文件中恢复指定库

在/opt/目录下有一个full_bak.sql备份文件，对当前数据库做了全备

[root@node1 opt]# ls
full-bak.sql  mysql_fulldump-2019-03-17.sql

数据库中原来的 pay 数据库被我删除，现在从上面的全库备份文件中单独恢复pay。 执行指令如下：

• 登录数据库创建同名数据库

create database pay;

• 从全备文件恢复pay

[root@node1 opt]# mysql -u root -p pay --one-database < full-bak.sql

--one-database选项可以使用 -o 参数

• 登录数据库查看pay库数据

mysql> use pay;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_pay   |
+-----------------+
| ws_pay_order    |
| ws_wxpay_notify |
+-----------------+
2 rows in set (0.01 sec)

mysql> select count(*) from ws_pay_order;
+----------+
| count(*) |
+----------+
|      185 |
+----------+
1 row in set (0.00 sec)

指定恢复的库需要先在数据库中创建同名数据库

从全备文件中筛选数据库或数据表

还有一种方式，通过把备份文件中的备份语句筛选出来导入新的SQL文件，使用新的SQL文件来恢复指定的库或者表。

下面用使用比较大的备份文件mysql_fulldump-2019-03-17.sql进行筛选

使用正则表达式将备份文件中的 mall库筛选出来：

[root@node1 opt]# sed -n '/^-- Current Database: `mall`/,/^-- Current Database: `/p' mysql_fulldump-2019-03-17.sql > mall.sql

查看筛选文件如下：

[root@node1 opt]# ls
full-bak.sql  mall.sql  mysql_fulldump-2019-03-17.sql
[root@node1 opt]# du -sh mall.sql 
345M	mall.sql

上面这种方式筛选的备份语句，把建库和建表语句都找出来了，所以可以直接执行mysql指令进行数据导入恢复。

• 恢复mall数据

[root@node1 opt]# mysql -uroot -p < mall.sql 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| admin              |
| information_schema |
| mall               |
| mysql              |
| pay                |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

从备份文件中筛选某个表的备份语句进行单表恢复

接着上面的恢复内容，将pay库中的一个表删除，然后从备份文件中将该表的备份语句筛选出来进行导入恢复

Database changed
mysql> show tables;
+-----------------+
| Tables_in_pay   |
+-----------------+
| ws_pay_order    |
| ws_wxpay_notify |
+-----------------+
2 rows in set (0.00 sec)

mysql> drop table ws_pay_order;
Query OK, 0 rows affected (0.15 sec)

筛选表的备份分为两步，第一步先找到该表ws_pay_order的表结构，正则表达式如下：

[root@node1 opt]#  sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `ws_pay_order`/!d;q' full-bak.sql > ws_pay_order.sql

# 表结构重定向至ws_pay_order.sql，随后将insert语句追加到该文件中：如下

[root@node1 opt]# grep 'INSERT INTO `ws_pay_order`' full-bak.sql >> ws_pay_order.sql

# 此时表的备份文件被筛选出来

• 恢复数据

mysql> source /opt/ws_pay_order.sql;

还有另一种方式来做匹配，和匹配数据库一样，执行指令如下:

[root@node1 opt]# sed -n -e '/CREATE TABLE.*`ws_pay_order`/ ,/UNLOCK TABLES/p' full-bak.sql > ws_pay_order.sql

#导入方式与上面一样