怎么看网站是不是php语言做的,学做网站论坛vip学员码,大丰做网站需要多少钱,y-m-d WordPress目前对MySQL比较流行的备份方式有两种#xff0c;一种上是使用自带的mysqldump#xff0c;另一种是xtrabackup#xff0c;对于数据时大的环境#xff0c;普遍使用了xtrabackupbinlog进行全量或者增量备份#xff0c;那么如何快速的从xtrabackup备份中恢复单张表呢#xf… 目前对MySQL比较流行的备份方式有两种一种上是使用自带的mysqldump另一种是xtrabackup对于数据时大的环境普遍使用了xtrabackupbinlog进行全量或者增量备份那么如何快速的从xtrabackup备份中恢复单张表呢从mysql 5.6版本开始支持可移动表空间Transportable Tablespace利用这个功能也可以实现单表的恢复下面进行从备份中恢复单张innodb表进行演练。 1. 针对InnoDB表恢复 2. 开启了参数innodb_file_per_table 3. 安装工具mysql-utilities其中mysqlfrm可以读取表结构。 进行mysql-utilities安装 yum install mysql-utilities -y 创建一个测试往里面插入数据后进行备份 test(rootlocalhost) [xuanzhi] show create table tb1\G
*************************** 1. row ***************************Table: tb1
Create Table: CREATE TABLE tb1 (id int(11) NOT NULL AUTO_INCREMENT,name char(10) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)test(rootlocalhost) [xuanzhi] insert into tb1 (name) values (aa),(bb),(cc),(dd);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0test(rootlocalhost) [xuanzhi] select * from tb1;
----------
| id | name |
----------
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
----------
4 rows in set (0.00 sec)test(rootlocalhost) [xuanzhi] 进行xtrabackup备份操作 [rootlocalhost data]# innobackupex --defaults-file/data/service/mysql-5.6.25/my.cnf --userroot --password123456 --sock/data/mysql-5.6/mysql.sock /data apply-log [rootlocalhost data]# innobackupex --defaults-file/usr/local/mysql-5.6.25/my.cnf --userroot --password123456 --sock/data/mysql-5.6/mysql.sock --apply-log /data/2017-03-24_09-40-54/ 进行完整备份后我们继续往测试表tb1里插入数据尽量模拟线上环境 test(rootlocalhost) [xuanzhi] insert into tb1 (name) values (aa2),(bb2),(cc2),(dd2);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0test(rootlocalhost) [xuanzhi] select * from tb1;
----------
| id | name |
----------
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | aa2 |
| 6 | bb2 |
| 7 | cc2 |
| 8 | dd2 |
----------
8 rows in set (0.00 sec)test(rootlocalhost) [xuanzhi] xtrabackup备份里只有四条数据备份后的数据我们一会使用binlog来进行恢复。 进行误操操作把表drop了 test(rootlocalhost) [xuanzhi] drop table tb1;
Query OK, 0 rows affected (0.21 sec)test(rootlocalhost) [xuanzhi] show tables;
Empty set (0.02 sec)test(rootlocalhost) [xuanzhi] 使用mysqlfrm从备份中读取表结构 [rootlocalhost data]# mysqlfrm --diagnostic /data/2017-03-24_09-40-54/xuanzhi/tb1.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 /data/2017-03-24_09-40-54/xuanzhi/tb1.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:CREATE TABLE xuanzhi.tb1 (id int(11) NOT NULL AUTO_INCREMENT, name char(30) DEFAULT NULL,
PRIMARY KEY PRIMARY (id)
) ENGINEInnoDB;#...done.
[rootlocalhost data]# 登录数据库进行建表 test(rootlocalhost) [xuanzhi] CREATE TABLE xuanzhi.tb1 (- id int(11) NOT NULL AUTO_INCREMENT, - name char(30) DEFAULT NULL, - PRIMARY KEY PRIMARY (id)- ) ENGINEInnoDB;
Query OK, 0 rows affected (0.05 sec)test(rootlocalhost) [xuanzhi] 加一个写锁确保安全: test(rootlocalhost) [xuanzhi] lock tables tb1 write;
Query OK, 0 rows affected (0.00 sec)test(rootlocalhost) [xuanzhi] 丢弃表空间 test(rootlocalhost) [xuanzhi] alter table tb1 discard tablespace;
Query OK, 0 rows affected (0.01 sec) 从备份中拷贝ibd文件并且修改权限: [rootlocalhost data]# cp /data/2017-03-24_09-40-54/xuanzhi/tb1.ibd /data/mysql-5.6/xuanzhi/
[rootlocalhost data]# chown -R mysql:mysql /data/mysql-5.6/xuanzhi/tb1.ibd 载入表空间 test(rootlocalhost) [xuanzhi] alter table tb1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)test(rootlocalhost) [xuanzhi] show warnings;
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening ./xuanzhi/tb1.cfg, will attempt to import without schema verification |
------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)test(rootlocalhost) [xuanzhi] 有报一个warning但不影响恢复详情可以看https://yq.aliyun.com/articles/59271我们查一下数据 test(rootlocalhost) [xuanzhi] select * from tb1;
----------
| id | name |
----------
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
----------
4 rows in set (0.02 sec)test(rootlocalhost) [xuanzhi] 可以看到备份的数据已经恢复了但是备份后插入的数据明显没有出现这个时候我们只能通过binlog进行恢复了所以binlog的备份也是非常重要的。 我们查看一下备份时的pos点和binlog的文件名 [rootlocalhost data]# cd /data/2017-03-24_09-40-54/
[rootlocalhost 2017-03-24_09-40-54]# cat xtrabackup_binlog_info
mysql-bin.000002 1014 我们知道了备份后的起始POS点还需要找出到误操前的一个POS点进行恢复找到drop table 前的POS点 [rootlocalhost 2017-03-24_09-40-54]# mysqlbinlog -v --base64-outputDECODE-ROWS /data/mysql-5.6/mysql-bin.000002 | grep -C 10 -i DROP
### SET
### 18
### 2dd2
# at 1292
#170324 9:43:00 server id 1313306 end_log_pos 1323 CRC32 0x9f776b03 Xid 198
COMMIT/*!*/;
# at 1323
#170324 9:46:55 server id 1313306 end_log_pos 1445 CRC32 0x3fa6b448 Query thread_id27 exec_time0 error_code0
use xuanzhi/*!*/;
SET TIMESTAMP1490320015/*!*/;
DROP TABLE tb1 /* generated by server */
/*!*/;
# at 1445
#170324 9:51:52 server id 1313306 end_log_pos 1674 CRC32 0xdd5e1448 Query thread_id27 exec_time0 error_code0
SET TIMESTAMP1490320312/*!*/;
CREATE TABLE xuanzhi.tb1 (id int(11) NOT NULL AUTO_INCREMENT, name char(30) DEFAULT NULL,
PRIMARY KEY PRIMARY (id)
) ENGINEInnoDB
/*!*/; 可以看到DROP TABLE 前的POS点是1323那我们可以通过binlog2sql进行标准SQL的生成binlog2sql的使用的和安装请看之前我写的博客http://www.cnblogs.com/xuanzhi201111/p/6602489.html再次为开源数据闪回工具的大神们点赞。 [rootlocalhost binlog2sql]# python binlog2sql.py -uroot -p123456 -dxuanzhi -ttb1 --start-position1014 --stop-position1323 --start-filemysql-bin.000002 recovery_tb1.sql [rootlocalhost binlog2sql]# cat recovery_tb1.sql
FLUSH ENGINE LOGS;
INSERT INTO xuanzhi.tb1(id, name) VALUES (5, aa2); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO xuanzhi.tb1(id, name) VALUES (6, bb2); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO xuanzhi.tb1(id, name) VALUES (7, cc2); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO xuanzhi.tb1(id, name) VALUES (8, dd2); #start 1094 end 1292 time 2017-03-24 09:43:00
[rootpm2 binlog2sql]# 把这sql文件进入导入即可进行备份后的数据恢复导入数据前先进行解锁 test(rootlocalhost) [xuanzhi] unlock tables;
Query OK, 0 rows affected (0.01 sec) [rootlocalhost binlog2sql]# mysql -uroot -p123456 ./recovery_tb1.sql
Warning: Using a password on the command line interface can be insecure.
[rootlocalhost binlog2sql]# 查看数据 test(rootlocalhost) [xuanzhi] select * from tb1;
----------
| id | name |
----------
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | aa2 |
| 6 | bb2 |
| 7 | cc2 |
| 8 | dd2 |
----------
8 rows in set (0.02 sec)test(rootlocalhost) [xuanzhi] 可以看到数据成功的恢复了大家可以多加测试。 转自 从xtrabackup备份恢复单表 - GoogSQL - 博客园http://www.cnblogs.com/xuanzhi201111/p/6609867.html 转载于:https://www.cnblogs.com/paul8339/p/8506063.html
