MySQL全备数据文件恢复单库单表方法

一、从mysqldump备份文件中恢复单库单表方法

1.全备中恢复指定库

从mysqldump全备中恢复指定库，使用 --one-database
 简写 -o
的参数，极大地方便了我们恢复数据的灵活性。

# mysql -uroot -p --one-database DBname < dump.sql
复制

2.全备文件中提取指定表结构和数据

# sed -n -e '/CREATE TABLE `tbname`/,/UNLOCK TABLES/p'  dump.sql > tb_name_$(date +%F).sql
复制

这种方法，最好一个实例里面表名称不要相同，否则相同表名称的数据都会过滤出来。

可以用下面的脚本来测试

#!/bin/bash
# by pangguoping

for i in `seq 10`
do
        mysql -uroot -punixfbi -S /tmp/mysql3307.sock -e "create database db$i;"
        for a in `seq 10`
        do
                mysql -uroot -punixfbi -S /tmp/mysql3307.sock -e "create table db$i.tb${a} (uid  int(11)  NOT NULL  AUTO_INCREMENT,name varchar(32)  DEFAULT NULL,add_time  datetime DEFAULT NULL,PRIMARY KEY (uid)) ENGINE=InnoDB;"
                for n in `seq 10`
                do
                        sleep 1;
                        mysql -uroot -punixfbi -S /tmp/mysql3307.sock -e "insert into db$i.tb${a}(name,add_time) values('db${i}_tb${a}_${n}',now());"
                done
        done


done
复制

3.根据控制MySQL权限进行恢复

这种方法是根据对表的权限控制来实现的，例如我想恢复db1.tb2表中的数据，那么我们可以创建一个用户，该用户只对db1.tb2表有权限，这样就可以实现。下面我们来试一下。 例如：我们根据3307实例的全备数据，把db1.tb2表中的数据恢复到3308实例 在3308实例创建用户：

mysql> grant  all   privileges   on  db1.tb2  to  'unixfbi'@'%' identified  by '123456';
复制

创建db1库：

mysql> create database db1;
复制

把3307实例数据进行全备：

# mysqldump -uroot -punixfbi -S /tmp/mysql3307.sock -A -B --single-transaction --master-data=2 > backup.sql
复制

把数据导入到3308实例：

# mysql -uunixfbi -p123456 -f -S /tmp/mysql3308.sock db1 <  backup.sql 2>/dev/null 
复制

需要使用 -f
参数,强制导入。否则一直报 ERROR1210(HY000)at line21:Incorrectarguments to EXECUTE
 的错误，无法导入到3308实例中。还有如果不使用 2>/dev/null
 的话，导入数据时就会显示满屏去权限的报错。

4.全备文件中提取指定表结构

例如从全备文件中提取tb3的表结构

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `tb3`/!d;q'  dump.sql

DROP TABLE IF EXISTS `tb3`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb3` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
复制

这种方法有个缺陷：如果实例库中有多个相同的表名称，那么只能提取出一个表结构。

二、从Xtrabackup备份中恢复单个表

从Xtrabackup全备中恢复单个表，主要是利用MySQL表空间传输技术进行恢复。 下面我们来恢复3307实例中的db1.tb1表数据，恢复到3308实例中。

1.创建测试数据

root@localhost:mysql3307.sock [(none)]>  create database db1;
root@localhost:mysql3307.sock [(none)]>  create table db1.tb1 (uid  int(11)  NOT NULL  AUTO_INCREMENT,name varchar(32)  DEFAULT NULL,add_time  datetime DEFAULT NULL,PRIMARY KEY (uid)) ENGINE=InnoDB;
root@localhost:mysql3307.sock [(none)]> insert into db1.tb1(name,add_time) values('db1_tb1_1',now());
复制

查看创建后的数据：

root@localhost:mysql3307.sock [db1]> select * from tb1;
+-----+------------+---------------------+
| uid | name       | add_time            |
+-----+------------+---------------------+
|   1 | db1_tb1_1  | 2018-06-15 17:12:53 |
|   2 | db1_tb1_2  | 2018-06-15 17:12:54 |
|   3 | db1_tb1_3  | 2018-06-15 17:12:55 |
|   4 | db1_tb1_4  | 2018-06-15 17:12:56 |
|   5 | db1_tb1_5  | 2018-06-15 17:12:57 |
|   6 | db1_tb1_6  | 2018-06-15 17:12:58 |
|   7 | db1_tb1_7  | 2018-06-15 17:12:59 |
|   8 | db1_tb1_8  | 2018-06-15 17:13:00 |
|   9 | db1_tb1_9  | 2018-06-15 17:13:01 |
|  10 | db1_tb1_10 | 2018-06-15 17:13:02 |
+-----+------------+---------------------+
10 rows in set (0.00 sec)
复制

2.在3307实例上备份数据

# innobackupex --defaults=/data/mysql/mysql3307/my3307.cnf --socket=/tmp/mysql3307.sock --user=root --password=unixfbi /backup/
复制

执行apply-log:

# innobackupex  --apply-log /backup/2018-06-15_17-58-07
复制

3.从备份中获取表结构

我们使用 mysqlfrm
来获取表结构，首先我们要安装 mysql-utilities

# yum install mysql-utilities -y
复制

安装完成后，我们来获取tb1表结构

# mysqlfrm --diagnostic /backup/2018-06-15_17-58-07/db1/tb1.frm 

CREATE TABLE `db1`.`tb1` (
  `uid` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(96) DEFAULT NULL, 
  `add_time` datetime DEFAULT NULL, 
PRIMARY KEY `PRIMARY` (`uid`)
) ENGINE=InnoDB;
复制

4.在3308实例上创建要恢复的表结构

root@localhost:mysql3308.sock [(none)]> create database db1;
root@localhost:mysql3308.sock [(none)]> CREATE TABLE `db1`.`tb1` (
  `uid` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(96) DEFAULT NULL, 
  `add_time` datetime DEFAULT NULL, 
PRIMARY KEY `PRIMARY` (`uid`)
) ENGINE=InnoDB;
复制

5.把3308实例上指定表空间释放掉

root@localhost:mysql3308.sock [(none)]> alter table db1.tb1 discard tablespace;   
复制

这一步也就是删除tb1.ibd 文件。

6.拷贝备份中的tb1.ibd文件，然后修改权限

# cp /backup/2018-06-15_17-58-07/db1/tb1.ibd  /data/mysql/mysql3308/data/db1/
# chown --reference=/data/mysql/mysql3308/data/db1/tb1.frm  /data/mysql/mysql3308/data/db1/tb1.ibd 
复制

7.3308实例导入表空间

root@localhost:mysql3308.sock [(none)]> alter table db1.tb1 import tablespace;
复制

8.查看数据是否恢复成功

root@localhost:mysql3308.sock [(none)]> select * from db1.tb1;
+-----+------------+---------------------+
| uid | name       | add_time            |
+-----+------------+---------------------+
|   1 | db1_tb1_1  | 2018-06-15 17:12:53 |
|   2 | db1_tb1_2  | 2018-06-15 17:12:54 |
|   3 | db1_tb1_3  | 2018-06-15 17:12:55 |
|   4 | db1_tb1_4  | 2018-06-15 17:12:56 |
|   5 | db1_tb1_5  | 2018-06-15 17:12:57 |
|   6 | db1_tb1_6  | 2018-06-15 17:12:58 |
|   7 | db1_tb1_7  | 2018-06-15 17:12:59 |
|   8 | db1_tb1_8  | 2018-06-15 17:13:00 |
|   9 | db1_tb1_9  | 2018-06-15 17:13:01 |
|  10 | db1_tb1_10 | 2018-06-15 17:13:02 |
+-----+------------+---------------------+
10 rows in set (0.00 sec)
复制

可以看出 db1.tb1
数据已经恢复成功了。

参考文档

http://www.ywnds.com/?p=13339

https://www.cnblogs.com/xuanzhi201111/p/6609867.html

本文出自 “运维特工” 博客，转载请务必保留原文链接 和 http://www.unixfbi.com

您的阅读和关注就是对我们最大的鼓励与支持。请长按下面二维码关注“运维特工”公众号。

运维特工，战胜心魔！！