如何用Percona XtraBackup進行MySQL從庫的單表備份和恢複【轉】

前提

應該确定采用的是單表一個表空間，否則不支援單表的備份與恢複。

在配置檔案裡邊的mysqld段加上

innodb_file_per_table = 1

環境說明：

主庫:192.168.0.1

從庫1:192.168.0.2

從庫2:192.168.0.3

備份工具 : Percona xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)

在主庫上建立chenfeng庫:

mysql> create database chenfeng;
Query OK, 1 row affected (0.08 sec)


mysql> use chenfeng
Database changed


mysql> create table duansf(id int (11),name varchar(10));
Query OK, 0 rows affected (0.14 sec)


mysql> insert into duansf values(1,'duansf');
Query OK, 1 row affected (0.01 sec)


mysql> insert into duansf values(2,'duansf');
Query OK, 1 row affected (0.01 sec)      

隻備份chenfeng庫的duansf表:

[root@localhost backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=dsf0723 -S /tmp/mysql.sock --slave-info --safe-slave-backup --include=chenfeng.duansf /data/backup      

innobackup部分參數解釋:

--slave-info會将master的binary log檔案名和偏移量儲存到xtrabackup_slave_info檔案中

--slave-info,備份從庫, 加上 --slave-info 備份目錄下會多生成一個 xtrabackup_slave_info 檔案,

 這裡會儲存主日志檔案以及偏移, 檔案内容類似于: 

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=826270;

--safe-slave-backup會暫停slave的sql線程，待備份結束後再啟動

--include=REGEXP

對xtrabackup參數--tables的封裝，也支援ibbackup。備份包含的庫表，例如：--include="test.*"，意思是要備份test庫中所有的表。

如果需要全備份，則省略這個參數；如果需要備份test庫下的2個表：test1和test2,則寫成：--include="test.test1|test.test2"。也可以使用通配符，如：--include="test.test*"。

本例中隻備份chenfeng庫下的duansf表，可以這麼寫--include=chenfeng.duansf

由于隻備份了chenfeng庫的duansf表，是以我們在生成的時間目錄裡2017-10-15_20-33-07隻看到了chenfeng檔案夾.

[root@localhost 2017-10-15_20-33-07]# ll /data/backup

總用量 12316

-rw-r-----. 1 root root      424 10月 15 20:33 backup-my.cnf

-rw-r-----. 1 root root      593 10月 15 20:33 ib_buffer_pool

-rw-r-----. 1 root root 12582912 10月 15 20:33 ibdata1

drwxr-x---. 2 root root       42 10月 15 20:33 chenfeng

-rw-r-----. 1 root root       21 10月 15 20:33 xtrabackup_binlog_info

-rw-r-----. 1 root root      117 10月 15 20:33 xtrabackup_checkpoints

-rw-r-----. 1 root root      573 10月 15 20:33 xtrabackup_info

-rw-r-----. 1 root root     2560 10月 15 20:33 xtrabackup_logfile

-rw-r-----. 1 root root       76 10月 15 20:33 xtrabackup_slave_info

[root@localhost 2017-10-15_20-33-07]# cd chenfeng

[root@localhost chenfeng]# ll

總用量 108

-rw-r-----. 1 root root  8586 10月 15 20:33 duansf.frm

-rw-r-----. 1 root root 98304 10月 15 20:33 duansf.ibd

把chenfeng目錄打包放到/data/backup/bak目錄下:

[root@localhost 2017-10-15_20-33-07]# tar czvf chenfeng.tar.gz chenfeng

[root@localhost 2017-10-15_20-33-07]# mv chenfeng.tar.gz /data/backup/bak/

解壓縮做恢複用:

恢複資料的時候，要經過prepare（recovery）和restore兩個步驟，

prepare導出表步驟:

[root@localhost backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=dsf0723 -S /tmp/mysql.sock --apply-log --export /data/backup/2017-10-15_20-33-07
171015 20:49:32 innobackupex: Starting the apply-log operation      

在從庫2上删除duansf表:

mysql> use chenfeng
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_chenfeng |
+-----------------+
| duansf          |
+-----------------+
1 row in set (0.00 sec)




mysql> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| duansf          |
+-----------------+
1 row in set (0.00 sec)


mysql> delete from duansf;
Query OK, 2 rows affected (0.06 sec)


mysql> select * from duansf;
Empty set (0.00 sec)      

從xtrabackup備份裡恢複出duansf表資料:

删除表：

mysql> drop table duansf;

Query OK, 0 rows affected (0.06 sec)

重建表結構:

mysql> CREATE TABLE `duansf` (

    ->   `id` int(11) DEFAULT NULL,

    ->   `name` varchar(10) DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)

解除安裝表空間:

mysql> ALTER TABLE chenfeng.duansf DISCARD TABLESPACE;

Query OK, 0 rows affected (0.01 sec)

從備份裡恢複:

[root@localhost chenfeng]# cp

/data/backup/2017-10-15_20-33-07/chenfeng/{duansf.ibd,duansf.cfg,duansf.frm}

/usr/local/mysql/data/chenfeng

總用量 116

-rw-r-----. 1 mysql mysql    65 10月 15 19:00 db.opt

-rw-r--r--. 1 root  root    426 10月 15 21:13 duansf.cfg

-rw-r-----. 1 mysql mysql  8586 10月 15 21:06 duansf.frm

-rw-r-----. 1 root  root  98304 10月 15 21:13 duansf.ibd

root@localhost data]# chown -R mysql:mysql /usr/local/mysql/data/chenfeng

裝載表空間：

mysql> ALTER TABLE chenfeng.duansf import TABLESPACE;

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

檢視duansf表資料:

mysql> select * from duansf;

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

| id   | name   |

|    1 | duansf |

|    2 | duansf |

2 rows in set (0.00 sec)

資料已恢複.