备份部分表
[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