环境说明:
主库: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
171015 20:33:07 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
171015 20:33:08 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
171015 20:33:08 version_check Connected to MySQL server
171015 20:33:08 version_check Executing a version check against the server...
171015 20:33:08 version_check Done.
171015 20:33:08 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.18-log
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
171015 20:33:08 >> log scanned up to (233975916)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 79 for chenfeng/duansf, old maximum was 0
171015 20:33:09 [01] Copying ./ibdata1 to /data/backup/2017-10-15_20-33-07/ibdata1
171015 20:33:09 [01] ...done
171015 20:33:09 [01] Copying ./chenfeng/duansf.ibd to /data/backup/2017-10-15_20-33-07/chenfeng/duansf.ibd
171015 20:33:09 [01] ...done
171015 20:33:09 >> log scanned up to (233975916)
171015 20:33:10 Slave open temp tables: 0
171015 20:33:10 Slave is safe to backup
171015 20:33:10 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
171015 20:33:10 Executing FLUSH TABLES WITH READ LOCK...
171015 20:33:10 Starting to backup non-InnoDB tables and files
171015 20:33:10 [01] Skipping ./ibdata1.
171015 20:33:10 [01] Skipping ./ib_logfile1.
171015 20:33:10 [01] Skipping ./ib_logfile0.
171015 20:33:10 [01] Skipping ./mysql/db.opt.
171015 20:33:10 [01] Skipping ./mysql/plugin.ibd.
171015 20:33:10 [01] Skipping ./mysql/tables_priv.MYI.
171015 20:33:10 [01] Skipping ./mysql/time_zone.frm.
171015 20:33:10 [01] Skipping ./mysql/time_zone_leap_second.frm.
171015 20:33:10 [01] Skipping ./mysql/innodb_ind