• 运维特工,战胜心魔!!
  • 有些事情其实没有那么难,只是我们感觉难,走出第一步!
  • 你所浪费的今天,是昨天死去的人奢望的明天!!!
  • 欢迎访问 unixfbi.com 运维特工社区http://unixfbi.com/
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧
  • 运维特工:http://www.unixfbi.com
  • 人生没有白走的路,每一步都算数!!
  • 空杯心态,沉淀自己!

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

MySQL unixfbi 1年前 (2018-05-24) 6633次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

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参数,强制导入。否则一直报ERROR 1210 (HY000) at line 21: Incorrect arguments 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


UnixFBI 运维特工 , 版权所有丨如未注明 , 均为原创丨 转载请务必注明原文链接http://www.unixfbi.com/504.html
喜欢 (0)
[支付宝]
分享 (0)
unixfbi
关于作者:
运维工程师一枚,做有价值的事情!
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址