xtrabackup

1.记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN

2.复制共享表空间文件以及独立共享表空间文件。

3.记录复制完表空间文件后，InnoDB存储引擎重做日志文件检查点的LSN。

4.复制在备份时产生的重做日志。

优点：

1.在线备份，不阻塞任何的SQL语句。

2.备份性能好，备份的实质是复制数据库文件和重做日志文件。

3.支持压缩备份，通过选项，可以支持不同级别的压缩。

下载地址：

https://www.percona.com/downloads/XtraBackup/LATEST/

[root@zstedu bin]# ./xtrabackup --version
xtrabackup: recognized server arguments:
./xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
[root@zstedu bin]# ./xtrabackup --backup -S /tmp/mysql3306.sock  -uroot -p 完全备份

xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --backup=1 --socket=/tmp/mysql3306.sock --user=root --password
Enter password:
180708 21:30:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql3306.sock' as 'root'  (using password: YES).
180708 21:30:53  version_check Connected to MySQL server
180708 21:30:53  version_check Executing a version check against the server...
180708 21:30:53  version_check Done.
180708 21:30:53 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql3306.sock
Using server version 5.7.22-log
./xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/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:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 104857600
InnoDB: Number of pools: 1
180708 21:30:53 >> log scanned up to (3466946626)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 62 for mysql/time_zone_transition, old maximum was 0
180708 21:30:54 [01] Copying ./ibdata1 to /data/soft/percona-xtrabackup-2.4.12-Linux-x86_64/bin/xtrabackup_backupfiles/ibdata1
180708 21:30:54 >> log scanned up to (3466946626) 记录了重做日志的位置，然后对备份的InnoDB存储引擎表的物理文件，即共享表空间和独立表空间进行copy操作
180708 21:30:55 >> log scanned up to (3466946626)
180708 21:30:56 >> log scanned up to (3466946626)
180708 21:30:57 >> log scanned up to (3466946626)
180708 21:30:59 >> log scanned up to (3466946626)
180708 21:31:00 >> log scanned up to (3466946626)

。。。。。。。。。。。。。。。。。。。。。。。。。

180708 21:35:13 [01] Copying ./cacti/version.frm to /data/backup/cacti/version.frm 180708 21:35:13 [01]        ...done

180708 21:35:13 [01] Copying ./cacti/version.MYI to /data/backup/cacti/version.MYI 180708 21:35:13 [01]        ...done

180708 21:35:13 [01] Copying ./cacti/version.MYD to /data/backup/cacti/version.MYD 180708 21:35:13 [01]        ...done

180708 21:35:13 Finished backing up non-InnoDB tables and files 180708 21:35:13 [00] Writing /data/backup/xtrabackup_binlog_info

180708 21:35:13 [00]        ...done

180708 21:35:13 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '3466946617' xtrabackup: Stopping log copying thread. .

180708 21:35:13 >> log scanned up to (3466946626)

180708 21:35:13 Executing UNLOCK TABLES

180708 21:35:13 All tables unlocked

180708 21:35:13 [00] Copying ib_buffer_pool to /data/backup/ib_buffer_pool

180708 21:35:13 [00]        ...done

180708 21:35:13 Backup created in directory '/data/backup/' MySQL binlog position: filename 'mysql-bin.000059', position '408474790', GTID of the last change '7f308b50-7701-11e8-8ee3-000c2923f3c9:1-31190' 180708 21:35:13 [00] Writing /data/backup/backup-my.cnf 180708 21:35:13 [00]        ...done

180708 21:35:13 [00] Writing /data/backup/xtrabackup_info 180708 21:35:13 [00]        ...done xtrabackup: Transaction log of lsn (3466946617) to (3466946626) was copied. 180708 21:35:13 completed OK!

 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++指定配置文件++++++++++++++++++++++++++++++++++++++++++++++

[root@zsedu ~]# /data/soft/percona-xtrabackup-2.4.12-Linux-x86_64/bin/xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p

--target-dir=/data/backup/ --backup 2>&1 |tee 1.log

=========================================增量备份=========================================================================

（1）完全备份，指定备份目录：

[root@zstedu bin]# ./xtrabackup --backup -S /tmp/mysql3306.sock  -uroot -p --target-dir=/data/backup

（2）增量备份：

[root@zstedu bin]# ./xtrabackup --backup -S /tmp/mysql3306.sock  -uroot -p --target-dir=/data/backup/1 --incremental-basedir=/data/backup

180708 21:38:21 [01] Copying ./cacti/version.frm to /data/backup/1/cacti/version.frm 180708 21:38:21 [01]        ...done

180708 21:38:21 [01] Copying ./cacti/version.MYI to /data/backup/1/cacti/version.MYI 180708 21:38:21 [01]        ...done

180708 21:38:21 [01] Copying ./cacti/version.MYD to /data/backup/1/cacti/version.MYD 180708 21:38:21 [01]        ...done

180708 21:38:21 Finished backing up non-InnoDB tables and files 180708 21:38:21 [00] Writing /data/backup/1/xtrabackup_binlog_info

180708 21:38:21 [00]        ...done 180708 21:38:21 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '3466946617' xtrabackup: Stopping log copying thread. .180708 21:38:21 >> log scanned up to (3466946626)

180708 21:38:21 Executing UNLOCK TABLES

180708 21:38:21 All tables unlocked

180708 21:38:21 [00] Copying ib_buffer_pool to /data/backup/1/ib_buffer_pool

180708 21:38:21 [00]        ...done

180708 21:38:21 Backup created in directory '/data/backup/1/' MySQL binlog position: filename 'mysql-bin.000059', position '408474790', GTID of the last change '7f308b50-7701-11e8-8ee3-000c2923f3c9:1-31190' 180708 21:38:21 [00] Writing /data/backup/1/backup-my.cnf 180708 21:38:21 [00]        ...done

180708 21:38:21 [00] Writing /data/backup/1/xtrabackup_info

180708 21:38:21 [00]        ...done xtrabackup: Transaction log of lsn (3466946617) to (3466946626) was copied.

（3）prepare

[root@zstedu bin]# ./xtrabackup --backup -S /tmp/mysql3306.sock  -uroot -p --prepare --target-dir=/data/backup/1

(4)apply incremental backup:

[root@zstedu bin]# ./xtrabackup --backup -S /tmp/mysql3306.sock  -uroot -p --prepare  --target-dir=/data/backup/1 --incremental-basedir=/data/backup

 +++++++++++++++++++++++++++++++++++++++++++++++++恢复+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

（1）进入恢复目录下，恢复前需要合并redo日志

[root@zstedu backup]# /data/soft/percona-xtrabackup-2.4.12-Linux-x86_64/bin/innobackupex --apply-log /data/backup/

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1893306 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1893306 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool 180709 22:56:13 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!".

/data/soft/percona-xtrabackup-2.4.12-Linux-x86_64/bin/innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: cd to /data/backup/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup:

xtrabackup_logfile detected: size=8388608, start_lsn=(3466951430) 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 __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 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 3466951430 InnoDB: Doing recovery: scanned up to log sequence number 3466951439 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 408474621, file name mysql-bin.000059 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.19 started; log sequence number 3466951439 InnoDB: xtrabackup: Last MySQL binlog file position 408474621, file name mysql-bin.000059

xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 3466951536 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 __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 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=3466951536 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 3466951692 InnoDB: Doing recovery: scanned up to log sequence number 3466951701 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 408474621, file name mysql-bin.000059 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: page_cleaner: 1000ms intended loop took 11668ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) InnoDB: 5.7.19 started; log sequence number 3466951701 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 3466951720 180709 22:56:28 completed OK!

合并前后文件对比：

（1）合并前[root@zstedu backup]# ll
total 168008
-rw-r----- 1 root root       538 Jul  9 21:45 backup-my.cnf
drwxr-x--- 2 root root      8192 Jul  9 21:45 cacti
-rw-r----- 1 root root     11816 Jul  9 21:45 ib_buffer_pool
-rw-r----- 1 root root 171966464 Jul  9 21:45 ibdata1
drwxr-x--- 2 root root      4096 Jul  9 21:45 mysql
drwxr-x--- 2 root root      8192 Jul  9 21:45 performance_schema
drwxr-x--- 2 root root      8192 Jul  9 21:45 sys
-rw-r----- 1 root root        66 Jul  9 21:45 xtrabackup_binlog_info
-rw-r----- 1 root root       119 Jul  9 21:45 xtrabackup_checkpoints
-rw-r----- 1 root root       621 Jul  9 21:45 xtrabackup_info
-rw-r----- 1 root root      2560 Jul  9 21:45 xtrabackup_logfile

（2）合并后

[root@zstedu backup]# ls -la
total 495696
drwxr-xr-x 6 root root      4096 Jul  9 22:56 .
drwxr-xr-x 6 root root       117 Jul  8 20:55 ..
-rw-r----- 1 root root       538 Jul  9 21:45 backup-my.cnf
drwxr-x--- 2 root root      8192 Jul  9 21:45 cacti
-rw-r----- 1 root root     11816 Jul  9 21:45 ib_buffer_pool
-rw-r----- 1 root root 171966464 Jul  9 22:56 ibdata1
-rw-r----- 1 root root 104857600 Jul  9 22:56 ib_logfile0
-rw-r----- 1 root root 104857600 Jul  9 22:56 ib_logfile1
-rw-r----- 1 root root 104857600 Jul  9 22:56 ib_logfile2
-rw-r----- 1 root root  12582912 Jul  9 22:56 ibtmp1
drwxr-x--- 2 root root      4096 Jul  9 21:45 mysql
drwxr-x--- 2 root root      8192 Jul  9 21:45 performance_schema
drwxr-x--- 2 root root      8192 Jul  9 21:45 sys
-rw-r----- 1 root root        66 Jul  9 21:45 xtrabackup_binlog_info
-rw-r--r-- 1 root root        27 Jul  9 22:56 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       119 Jul  9 22:56 xtrabackup_checkpoints
-rw-r----- 1 root root       621 Jul  9 21:45 xtrabackup_info
-rw-r----- 1 root root   8388608 Jul  9 22:56 xtrabackup_logfile
-rw-r--r-- 1 root root         1 Jul  9 22:56 xtrabackup_master_key_id

 

真正恢复：

注意事项：

（1）一种用copy过去。还一种用copy-back参数即可。

（2）创建data目录，恢复前一定要备份data.mv/cp data data.bak

(3) ./innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --copy-back /data/backup/2018-07-28_22-11-40/ 要指定配置文件路径

（4） chown -R mysql.mysql data 给data目录修改属组等。

（5）开mysqld --defaulst-file=/data/mysql3306/mysql3306.cnf &结束