xtrabackup 备份 mysql 5.6_MYSQL 5.6从XTRABACKUP完整备份中恢复单表

root@master 15:27: [test]> select * from t1;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | 9 |

| 2 | bb | 9 |

| 3 | cc | 9 |

| 4 | dd | 9 |

| 5 | ee | 9 |

| 6 | ff | 9 |

| 7 | NULL | 10 |

| 8 | NULL | 11 |

+----+------+------+

8 rows in set (0.00 sec)

APPLY LOG一次:

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=xQT0USuwNKNrQ --apply-log /data/backup/20170718/2017-07-18_15-25-24/

首先需要恢复表结构:

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# mysqlfrm --diagnostic test/t1.frm

# WARNING: Cannot generate character set or collation names without the --server option.

# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.

# Reading .frm file for test/t1.frm:

# The .frm file is a TABLE.

# CREATE TABLE Statement:

CREATE TABLE `test`.`t1` (

`id` int(8) NOT NULL,

`name` varchar(27) DEFAULT NULL,

`age` int(8) DEFAULT NULL,

PRIMARY KEY `PRIMARY` (`id`),

KEY `name` (`name`)

) ENGINE=InnoDB;

将读取的表结构拿到数据库进行创建:

root@master 15:30: [test]> CREATE TABLE `test`.`t1` (

-> `id` int(8) NOT NULL,

-> `name` varchar(27) DEFAULT NULL,

-> `age` int(8) DEFAULT NULL,

-> PRIMARY KEY `PRIMARY` (`id`),

-> KEY `name` (`name`)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

为表加写锁:

root@master 15:32: [test]> lock tables t1 write;

Query OK, 0 rows affected (0.00 sec)

COPY备份中的IBD文件,并授权:

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# cp test/t1.ibd /data/database/mysql/3388/data/test/

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# chown -R mysql.mysql /data/database/mysql/3388/data/test/t1.ibd

载入表空间:

root@master 15:33: [test]> alter table t1 import tablespace;

Query OK, 0 rows affected, 1 warning (0.02 sec)

注: 在有从库的时候,需要把上面的IBD文件也COPY到备库一份,否则执行IMPORT会在从库上报错,从而导致复制中断。

检查数据:

root@master 15:36: [test]> select * from t1;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | 9 |

| 2 | bb | 9 |

| 3 | cc | 9 |

| 4 | dd | 9 |

| 5 | ee | 9 |

| 6 | ff | 9 |

| 7 | NULL | 10 |

| 8 | NULL | 11 |

+----+------+------+

8 rows in set (0.00 sec)

无问题后, 解锁表:

root@master 15:37: [test]> unlock tables;

Query OK, 0 rows affected (0.00 sec)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值