利用xtrabackup进行增量备份和增量恢复的实验

in Linux with 0 comment

利用xtrabackup进行增量备份和增量恢复的实验

进行全量备份

[[email protected] ~]# cd /opt/backup/
[[email protected] backup]# pwd
/opt/backup
[[email protected] backup]# ls
full  incr

full 目录存放全量备份的数据
incr 目录存放增量备份的数据

[[email protected] backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --port=3306 /opt/backup/full/
trabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:35:25 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!".
... ...
181211 15:35:28 Executing UNLOCK TABLES
181211 15:35:28 All tables unlocked
181211 15:35:28 Backup created in directory '/opt/backup/full/2018-12-11_15-35-25/'
MySQL binlog position: filename 'mysql-bin.000001', position '372'
181211 15:35:28 [00] Writing /opt/backup/full/2018-12-11_15-35-25/backup-my.cnf
181211 15:35:28 [00] ...done
181211 15:35:28 [00] Writing /opt/backup/full/2018-12-11_15-35-25/xtrabackup_info
181211 15:35:28 [00] ...done
xtrabackup: Transaction log of lsn (1654624) to (1654624) was copied.
181211 15:35:28 completed OK!

备份好了,下面查看

[[email protected] backup]# ls -l full/2018-12-11_15-35-25/
total 12320
-rw-r----- 1 root root      481 Dec 11 15:35 backup-my.cnf
-rw-r----- 1 root root 12582912 Dec 11 15:35 ibdata1
drwxr-x--- 2 root root     4096 Dec 11 15:35 mysql
drwxr-x--- 2 root root     4096 Dec 11 15:35 performance_schema
-rw-r----- 1 root root       21 Dec 11 15:35 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Dec 11 15:35 xtrabackup_checkpoints
-rw-r----- 1 root root      521 Dec 11 15:35 xtrabackup_info
-rw-r----- 1 root root     2560 Dec 11 15:35 xtrabackup_logfile
drwxr-x--- 2 root root     4096 Dec 11 15:35 xtra_test

第一次增量插入一条数据

mysql> INSERT INTO friends(id, name) VALUES (2, 'winstom' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name |
+----+---------+
| 1 | jack |
| 1 | tom |
| 2 | winstom |
+----+---------+
3 rows in set (0.00 sec)

mysql>

进行第一次增量备份

注意

特别强调

第一次增量备份,要以全量备份为节点,进行备份

增量备份使用--incremental参数加 增量备份的目录。

所以 --incremental-basedir 跟的参数是全量备份的目录

[[email protected] backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --incremental /opt/backup/incr/ --incremental-basedir=/opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:46:22 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!".
... ...
181211 15:46:24 Executing UNLOCK TABLES
181211 15:46:24 All tables unlocked
181211 15:46:24 Backup created in directory '/opt/backup/incr/2018-12-11_15-46-22/'
MySQL binlog position: filename 'mysql-bin.000001', position '628'
181211 15:46:24 [00] Writing /opt/backup/incr/2018-12-11_15-46-22/backup-my.cnf
181211 15:46:24 [00] ...done
181211 15:46:24 [00] Writing /opt/backup/incr/2018-12-11_15-46-22/xtrabackup_info
181211 15:46:24 [00] ...done
xtrabackup: Transaction log of lsn (1654936) to (1654936) was copied.
181211 15:46:24 completed OK!

第一次增量备份完成,下面进行查看

[[email protected] backup]# ll incr/2018-12-11_15-46-22/
total 132
-rw-r----- 1 root root   481 Dec 11 15:46 backup-my.cnf
-rw-r----- 1 root root 98304 Dec 11 15:46 ibdata1.delta
-rw-r----- 1 root root    44 Dec 11 15:46 ibdata1.meta
drwxr-x--- 2 root root  4096 Dec 11 15:46 mysql
drwxr-x--- 2 root root  4096 Dec 11 15:46 performance_schema
-rw-r----- 1 root root    21 Dec 11 15:46 xtrabackup_binlog_info
-rw-r----- 1 root root   117 Dec 11 15:46 xtrabackup_checkpoints
-rw-r----- 1 root root   589 Dec 11 15:46 xtrabackup_info
-rw-r----- 1 root root  2560 Dec 11 15:46 xtrabackup_logfile
drwxr-x--- 2 root root  4096 Dec 11 15:46 xtra_test

第二次增量插入一条数据

mysql> INSERT INTO friends(id, name) VALUES (3, 'tracey' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name |
+----+---------+
| 1 | jack |
| 1 | tom |
| 2 | winstom |
| 3 | tracey |
+----+---------+
4 rows in set (0.00 sec)

进行第二次增量备份

注意

特别强调,单独拉了一个菜单

在第二次增量备份的时候,不再是以全量为节点,要以上一次增量备份为节点来进行第二次的增量备份

所以--incremental-basedir参数后面跟的是上一次增量备份的目录;

[[email protected] backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --incremental /opt/backup/incr/ --incremental-basedir=/opt/backup/incr/2018-12-11_15-46-22/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:49:41 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!".
... ...
181211 15:49:43 Executing UNLOCK TABLES
181211 15:49:43 All tables unlocked
181211 15:49:43 Backup created in directory '/opt/backup/incr/2018-12-11_15-49-41/'
MySQL binlog position: filename 'mysql-bin.000001', position '883'
181211 15:49:43 [00] Writing /opt/backup/incr/2018-12-11_15-49-41/backup-my.cnf
181211 15:49:43 [00] ...done
181211 15:49:43 [00] Writing /opt/backup/incr/2018-12-11_15-49-41/xtrabackup_info
181211 15:49:43 [00] ...done
xtrabackup: Transaction log of lsn (1655979) to (1655979) was copied.
181211 15:49:43 completed OK!

备份完成,下面查看

[[email protected] backup]# ll incr/2018-12-11_15-49-41/
total 228
-rw-r----- 1 root root    481 Dec 11 15:49 backup-my.cnf
-rw-r----- 1 root root 196608 Dec 11 15:49 ibdata1.delta
-rw-r----- 1 root root     44 Dec 11 15:49 ibdata1.meta
drwxr-x--- 2 root root   4096 Dec 11 15:49 mysql
drwxr-x--- 2 root root   4096 Dec 11 15:49 performance_schema
-rw-r----- 1 root root     21 Dec 11 15:49 xtrabackup_binlog_info
-rw-r----- 1 root root    117 Dec 11 15:49 xtrabackup_checkpoints
-rw-r----- 1 root root    589 Dec 11 15:49 xtrabackup_info
-rw-r----- 1 root root   2560 Dec 11 15:49 xtrabackup_logfile
drwxr-x--- 2 root root   4096 Dec 11 15:49 xtra_test

查看xtrabackup_binlog_info中的binlog位置

[[email protected] backup]# cat full/2018-12-11_15-35-25/xtrabackup_binlog_info 
mysql-bin.000001	372
[[email protected] backup]# cat incr/2018-12-11_15-46-22/xtrabackup_binlog_info 
mysql-bin.000001	628
[[email protected] backup]# cat incr/2018-12-11_15-49-41/xtrabackup_binlog_info 
mysql-bin.000001	883

查看xtrabackup_checkpoints中lsn

[[email protected] backup]# cat full/2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1654624
last_lsn = 1654624
compact = 0
recover_binlog_info = 0
[[email protected] backup]# cat incr/2018-12-11_15-46-22/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1654624
to_lsn = 1654936
last_lsn = 1654936
compact = 0
recover_binlog_info = 0
[[email protected] backup]# cat incr/2018-12-11_15-49-41/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1654936
to_lsn = 1655979
last_lsn = 1655979
compact = 0
recover_binlog_info = 0

这里可以看到,全备的lsn是从0开始的,第一次增量备份的lsn是从全量备份的lsn结束的位置开始的,第二次增量备份同理,从第一次增量备份的lsn结束的位置开始的。

模拟数据删除

之间删除xtra_test数据库,来模拟数据丢失等情况

[[email protected] full]# mysql -uroot -p234567
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xtra_test |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database xtra_test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

本次仅仅是演示,请勿在生产操作次命令;

数据恢复操作

恢复具体步骤

  1. 先把xtrabackup备份下来的目录进行再次备份,防止在还原的时候误操作,导致备份不可用;
  2. 关闭要回复的数据库实例;
  3. 移除需要恢复数据的MySQL实例下的data目录,建议mv移动到别的目录下;
  4. 操作全量备份使用--apply-log--redo-only参数对全量备份进行恢复前准备操作,如事物回滚等操作;
  5. 操作第一次增量备份将第一次增量备份应用到完全备份,同用使用--apply-log--redo-only参数
  6. 操作第二次增量备份将第二次增量备份应用到完全备份,使用--apply-log参数,不要加--redo-only参数。
  7. 把所有合在一起的完全备份整体进行一次apply操作,回滚未提交的数据
  8. 使用--copy-back--rsync 参数直接对全量备份操作后的目录进行恢复;
  9. 授权MySQL实例下的data目录后,启动数据库
  10. 验证恢复数据

第一步 备份

[[email protected] backup]# cd full/
[[email protected] full]# pwd
/opt/backup/full
[[email protected] full]# ls
2018-12-11_15-35-25
[[email protected] full]# cp -a 2018-12-11_15-35-25/ 2018-12-11_15-35-25_bak
[[email protected] full]# ll
total 8
drwxr-x--- 5 root root 4096 Dec 11 15:35 2018-12-11_15-35-25
drwxr-x--- 5 root root 4096 Dec 11 15:35 2018-12-11_15-35-25_bak
[[email protected] full]# cd ../incr/
[[email protected] incr]# ll
total 8
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41
[[email protected] incr]# cp -a 2018-12-11_15-46-22/ 2018-12-11_15-46-22_bak
[[email protected] incr]# cp -a 2018-12-11_15-49-41 2018-12-11_15-49-41_bak
[[email protected] incr]# ll
total 16
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22_bak
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41_bak

第二步 关闭MySQL实例

[[email protected] incr]# /etc/init.d/mysqld  stop
Shutting down MySQL..                                      [  OK  ]
[[email protected] incr]# ps aux|grep mysql
root     20447  0.0  0.1 112720  2180 pts/1    S+   16:18   0:00 grep --color=auto mysql
[[email protected] incr]# netstat -lntup | grep 3306
[[email protected] incr]# 

第三步 移除要恢复数据的MySQL的data目录

[[email protected] incr]# ll /opt/mysql/data/
total 286760
-rw-rw---- 1 mysql mysql        56 Dec 11 15:04 auto.cnf
-rw-r----- 1 mysql mysql  12582912 Dec 11 16:17 ibdata1
-rw-r----- 1 mysql mysql 134217728 Dec 11 16:17 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Dec 11 15:02 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 mysql
-rw-rw---- 1 mysql mysql       906 Dec 11 16:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql        37 Dec 11 15:04 mysql-bin.index
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 performance_schema
-rw-r----- 1 mysql mysql        21 Dec 11 15:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql       509 Dec 11 15:02 xtrabackup_info
-rw-r----- 1 mysql mysql         1 Dec 11 15:02 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 xtra_test
[[email protected] incr]# mv /opt/mysql/data /tmp/
[[email protected] incr]# ls -ld /tmp/data/
drwxr-x--- 5 mysql mysql 4096 Dec 11 15:04 /tmp/data/

第四步 准备全量备份

[[email protected] backup]# innobackupex --apply-log --redo-only /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:40:24 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!".
... ...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1654633
InnoDB: Number of pools: 1
181211 16:40:26 completed OK!

第五步 应用第一次增量备份到全量备份

[[email protected] backup]# innobackupex --apply-log --redo-only /opt/backup/full/2018-12-11_15-35-25/ --incremental-dir=/opt/backup/incr/2018-12-11_15-46-22/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:40:57 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!".
... ...
181211 16:40:59 [01] Copying /opt/backup/incr/2018-12-11_15-46-22/performance_schema/events_waits_summary_global_by_event_name.frm to ./performance_schema/events_waits_summary_global_by_event_name.frm
181211 16:40:59 [01] ...done
181211 16:40:59 [00] Copying /opt/backup/incr/2018-12-11_15-46-22//xtrabackup_binlog_info to ./xtrabackup_binlog_info
181211 16:40:59 [00] ...done
181211 16:40:59 [00] Copying /opt/backup/incr/2018-12-11_15-46-22//xtrabackup_info to ./xtrabackup_info
181211 16:40:59 [00] ...done
181211 16:40:59 completed OK!

查看全量备份的xtrabackup_checkpoints

[[email protected] full]# cat 2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1654936
last_lsn = 1654936
compact = 0
recover_binlog_info = 0

对比之前查看的第一次增量备份的last_lsn位置,在应用第一次增量备份到全量后,可以看到last_lsn已经被应用和第一次全量备份的位置相同了

第六步 应用第二次增量备份到全量备份

注意 在引用最后一次增量备份到全量时,不需要增加--redo-only参数。

[[email protected] backup]# innobackupex --apply-log /opt/backup/full/2018-12-11_15-35-25/ --incremental-dir=/opt/backup/incr/2018-12-11_15-49-41/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:42:04 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!".
... ...
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1655998
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1656332
InnoDB: Doing recovery: scanned up to log sequence number 1656341 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 883, file name mysql-bin.000001
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.19 started; log sequence number 1656341
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1656360
181211 16:42:10 completed OK!

查看全量备份的xtrabackup_checkpoints

[[email protected] full]# cat 2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1655979
last_lsn = 1655979
compact = 0
recover_binlog_info = 0

对比之前查看的第二次增量备份的last_lsn位置,在应用第二次增量备份到全量后,可以看到last_lsn已经被应用和第二次全量备份的位置相同了

第七步 把备份整体进行一次apply操作

[[email protected] full]# innobackupex --apply-log /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 17:13:33 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!".
... ...
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: 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.19 started; log sequence number 1656379
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1656398
181211 17:13:36 completed OK!

第八步 使用--copy-back 参数恢复拷贝到data目录

[[email protected] full]# innobackupex --defaults-file=/opt/mysql-5.6/my.cnf --copy-back /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 17:14:21 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
... ...
181211 17:14:25 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /opt/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
181211 17:14:25 [01] ...done
181211 17:14:25 [01] Copying ./performance_schema/mutex_instances.frm to /opt/mysql/data/performance_schema/mutex_instances.frm
181211 17:14:25 [01] ...done
181211 17:14:25 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /opt/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm
181211 17:14:25 [01] ...done
181211 17:14:25 [01] Copying ./xtrabackup_info to /opt/mysql/data/xtrabackup_info
181211 17:14:25 [01] ...done
181211 17:14:25 [01] Copying ./xtrabackup_master_key_id to /opt/mysql/data/xtrabackup_master_key_id
181211 17:14:25 [01] ...done
181211 17:14:25 completed OK!

验证操作

[[email protected] full]# ls -l /opt/mysql/data/
total 286744
-rw-r----- 1 root root  12582912 Dec 11 17:14 ibdata1
-rw-r----- 1 root root 134217728 Dec 11 17:14 ib_logfile0
-rw-r----- 1 root root 134217728 Dec 11 17:14 ib_logfile1
-rw-r----- 1 root root  12582912 Dec 11 17:14 ibtmp1
drwxr-x--- 2 root root      4096 Dec 11 17:14 mysql
drwxr-x--- 2 root root      4096 Dec 11 17:14 performance_schema
-rw-r----- 1 root root        21 Dec 11 17:14 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       589 Dec 11 17:14 xtrabackup_info
-rw-r----- 1 root root         1 Dec 11 17:14 xtrabackup_master_key_id
drwxr-x--- 2 root root      4096 Dec 11 17:14 xtra_test

第九步 授权并启动MySQL

[[email protected] full]# chown -R mysql.mysql /opt/mysql/data
[[email protected] full]# ls -l /opt/mysql-5.6/data/
total 286744
-rw-r----- 1 mysql mysql  12582912 Dec 11 17:14 ibdata1
-rw-r----- 1 mysql mysql 134217728 Dec 11 17:14 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 Dec 11 17:14 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Dec 11 17:14 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 mysql
-rw-rw---- 1 mysql mysql         0 Dec 11 17:19 mysql-bin.index
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 performance_schema
-rw-r----- 1 mysql mysql        21 Dec 11 17:14 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql       589 Dec 11 17:14 xtrabackup_info
-rw-r----- 1 mysql mysql         1 Dec 11 17:14 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 xtra_test
[[email protected] full]# /etc/init.d/mysqld  start
Starting MySQL.                                            [  OK  ]
[[email protected] full]# ps aux|grep mysql
root     21533  0.6  0.1 113324  3208 pts/2    S    17:19   0:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/mysqld.pid
mysql    22340 10.4 44.2 2299484 903940 pts/2  Sl   17:19   0:00 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/error.log --open-files-limit=4161 --pid-file=/opt/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root     22369  0.0  0.1 112720  2400 pts/2    S+   17:19   0:00 grep --color=auto mysql
[[email protected] full]# netstat -lntup | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      22340/mysqld

第十步 验证数据

[[email protected] full]# mysql -uroot -p234567
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xtra_test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use xtra_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_xtra_test |
+---------------------+
| friends |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name |
+----+---------+
| 1 | jack |
| 1 | tom |
| 2 | winstom |
| 3 | tracey |
+----+---------+
4 rows in set (0.00 sec)

上面的验证操作可以看出,备份已经完全恢复