xtrabackup2.4备份部分表和数据库

备份部分表

[root@lineqi backups]# cat table.txt
test.test1
test.test2

[root@lineqi backups]# innobackupex --tables-file=/mysqldata/backups/table.txt --user=bkpuser --port=3306 --password=Bkpuser#123 /opt/test

[root@lineqi test]# ls -l 2020-05-17_17-26-54/
total 208920
-rw-r----- 1 root root 426 May 17 17:26 backup-my.cnf
-rw-r----- 1 root root 1215 May 17 17:26 ib_buffer_pool
-rw-r----- 1 root root 213909504 May 17 17:26 ibdata1
drwxr-x— 2 root root 74 May 17 17:26 test
-rw-r----- 1 root root 21 May 17 17:26 xtrabackup_binlog_info
-rw-r----- 1 root root 121 May 17 17:26 xtrabackup_checkpoints
-rw-r----- 1 root root 527 May 17 17:26 xtrabackup_info
-rw-r----- 1 root root 2560 May 17 17:26 xtrabackup_logfile

[root@lineqi test]# ls -l 2020-05-17_17-26-54/test/
total 248
-rw-r----- 1 root root 8650 May 17 17:26 test1.frm
-rw-r----- 1 root root 114688 May 17 17:26 test1.ibd
-rw-r----- 1 root root 8618 May 17 17:26 test2.frm
-rw-r----- 1 root root 114688 May 17 17:26 test2.ibd

说明：这里虽然只备份了两张表，但ibdata1给备份出来了，如果该文件较大，需要额外时间和空间，但好处就是可以用来迁移表

备份部分数据库

[root@lineqi backups]# innobackupex –databases=“test.test1 webdata” --user=bkpuser --port=3306 --password=Bkpuser#123 /opt/test

[root@lineqi test]# ls -l 2020-05-17_17-40-18/
total 208932
-rw-r----- 1 root root 426 May 17 17:42 backup-my.cnf
-rw-r----- 1 root root 1215 May 17 17:42 ib_buffer_pool
-rw-r----- 1 root root 213909504 May 17 17:40 ibdata1
drwxr-x— 2 root root 40 May 17 17:42 test
drwxr-x— 2 root root 8192 May 17 17:42 webdata
-rw-r----- 1 root root 21 May 17 17:42 xtrabackup_binlog_info
-rw-r----- 1 root root 121 May 17 17:42 xtrabackup_checkpoints
-rw-r----- 1 root root 515 May 17 17:42 xtrabackup_info
-rw-r----- 1 root root 2560 May 17 17:42 xtrabackup_logfile
[root@lineqi test]# ls -l 2020-05-17_17-40-18/test
total 124
-rw-r----- 1 root root 8650 May 17 17:42 test1.frm
-rw-r----- 1 root root 114688 May 17 17:41 test1.ibd

恢复部分表

[root@lineqi backups]# innobackupex --apply-log --export /opt/test/2020-05-17_17-26-54
[root@lineqi test]# ls -lt 2020-05-17_17-26-54/test
total 288
-rw-r----- 1 root root 16384 May 17 17:48 test1.exp
-rw-r–r-- 1 root root 624 May 17 17:48 test1.cfg
-rw-r----- 1 root root 16384 May 17 17:48 test2.exp
-rw-r–r-- 1 root root 568 May 17 17:48 test2.cfg
-rw-r----- 1 root root 8618 May 17 17:26 test2.frm
-rw-r----- 1 root root 8650 May 17 17:26 test1.frm
-rw-r----- 1 root root 114688 May 17 17:26 test1.ibd
-rw-r----- 1 root root 114688 May 17 17:26 test2.ibd
恢复步骤（异机或异库恢复，再导出）

OTHERSERVER|mysql> CREATE TABLE mytable (…) ENGINE=InnoDB;
OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

$ chown -R mysql:mysql /datadir/db_name/table_name.*

遇到的问题

tables-file 需要根据绝对路径，否则会报找不到文件
[root@lineqi backups]# innobackupex --tables-file=table.txt --user=bkpuser --port=3306 --password=Bkpuser#123 /opt/test
xtrabackup: cannot open table.txt