xtrabackup备份与恢复

1、创建备份账号

create user 'bakuser'@'localhost' identified by 'mysql';
grant reload,lock tables,process,replication client on *.* to 'bakuser'@'localhosts';
flush privileges;

2、xtrabackup备份

[root@centos7 mysql3306]# innobackupex  -S /tmp/mysql3306.sock -ubakuser -pmysql /data/bak
170617 14:27:49 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!".

170617 14:27:49  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql3306.sock' as 'bakuser'  (using password: YES).
170617 14:27:49  version_check Connected to MySQL server
170617 14:27:49  version_check Executing a version check against the server...
170617 14:27:49  version_check Done.
170617 14:27:49 Connecting to MySQL server host: localhost, user: bakuser, password: set, port: 3306, socket: /tmp/mysql3306.sock
Using server version 5.7.16-log
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
170617 14:27:49 >> log scanned up to (11911381)
xtrabackup: Generating a list of tablespaces

-----省略部分copy日志-----

170617 14:27:52 >> log scanned up to (11911381)
170617 14:27:52 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
170617 14:27:52 Executing FLUSH TABLES WITH READ LOCK...
170617 14:27:52 Starting to backup non-InnoDB tables and files

-----省略部分copy日志-----

170617 14:27:56 Finished backing up non-InnoDB tables and files
170617 14:27:56 [00] Writing xtrabackup_binlog_info
170617 14:27:56 [00]        ...done
170617 14:27:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '11911372'
xtrabackup: Stopping log copying thread.
.170617 14:27:56 >> log scanned up to (11911381)

170617 14:27:56 Executing UNLOCK TABLES
170617 14:27:56 All tables unlocked
170617 14:27:56 [00] Copying ib_buffer_pool to /data/bak/2017-06-17_14-27-49/ib_buffer_pool
170617 14:27:56 [00]        ...done
170617 14:27:56 Backup created in directory '/data/bak/2017-06-17_14-27-49/'
MySQL binlog position: filename 'mysql-bin.1000017', position '1508', GTID of the last change '0e9bfc86-2c1f-11e7-93f7-0800276e472a:1-115'
170617 14:27:56 [00] Writing backup-my.cnf
170617 14:27:56 [00]        ...done
170617 14:27:56 [00] Writing xtrabackup_info
170617 14:27:56 [00]        ...done
xtrabackup: Transaction log of lsn (11911372) to (11911381) was copied.
170617 14:27:56 completed OK!

由以上日志可以看出innobackupex备份non-InnoDB tables and files（如.frm文件）期间会锁表，库表越多，时间可能越长。

3、apply log

[root@centos7 2017-06-17_14-45-01]# innobackupex --apply-log /data/bak/2017-06-17_14-45-01/
170617 14:57:28 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: cd to /data/bak/2017-06-17_14-45-01/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(11911372)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 11911372
InnoDB: Doing recovery: scanned up to log sequence number 11911381 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 11911381 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 11911381
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 11911409
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=11911409
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 11911692
InnoDB: Doing recovery: scanned up to log sequence number 11911701 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 11911701 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 11911701
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 11911720
170617 14:57:33 completed OK!

如果OS memory资源充足，可以使用--use-memory设置更大内存，加速apply log过程，默认是100MB

4、copy back

[root@centos7 2017-06-17_14-45-01]# innobackupex --apply-log /data/bak/2017-06-17_14-45-01/
170617 14:57:28 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: cd to /data/bak/2017-06-17_14-45-01/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(11911372)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 11911372
InnoDB: Doing recovery: scanned up to log sequence number 11911381 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 11911381 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 11911381
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 11911409
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=11911409
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 11911692
InnoDB: Doing recovery: scanned up to log sequence number 11911701 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 11911701 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 710, file name mysql-bin.1000017
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 11911701
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 11911720
170617 14:57:33 completed OK!

当前datadir必须为空，copy back之后还需要更改文件权限，chown -R mysql:mysql /data/mysql/mysql3306/data/*

5、启动MySQL

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

启动之后观察下error.log确认有没问题