目录
基于Xtrabackup 全备和还原以及增量备份和还原 的环境和数据准备的操作
测试数据准备
在
friends
表中录入一条数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xtra_test |
+--------------------+
4 rows in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_xtra_test |
+---------------------+
| friends |
+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO friends(id, name) VALUES (1, 'tom' );
Query OK, 1 row affected (0.00 sec)
mysql> select * from friends;
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 1 | tom |
+----+------+
2 rows in set (0.00 sec)
进行全量备份
[root@node ~]# cd /opt/backup/
[root@node backup]# pwd
/opt/backup
[root@node backup]# ls
full incr
full 目录存放全量备份的数据
incr 目录存放增量备份的数据
[root@node 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!
备份好了,下面查看
[root@node 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
跟的参数是全量备份的目录
[root@node 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!
第一次增量备份完成,下面进行查看
[root@node 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
参数后面跟的是上一次增量备份的目录;
[root@node 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!
备份完成,下面查看
[root@node 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位置
[root@node backup]# cat full/2018-12-11_15-35-25/xtrabackup_binlog_info
mysql-bin.000001 372
[root@node backup]# cat incr/2018-12-11_15-46-22/xtrabackup_binlog_info
mysql-bin.000001 628
[root@node backup]# cat incr/2018-12-11_15-49-41/xtrabackup_binlog_info
mysql-bin.000001 883
查看xtrabackup_checkpoints中lsn
[root@node 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
[root@node 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
[root@node 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
数据库,来模拟数据丢失等情况
[root@node 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)
本次仅仅是演示,请勿在生产操作次命令;
数据恢复操作
恢复具体步骤
- 先把xtrabackup备份下来的目录进行再次备份,防止在还原的时候误操作,导致备份不可用;
- 关闭要回复的数据库实例;
- 移除需要恢复数据的MySQL实例下的data目录,建议
mv
移动到别的目录下; - 操作全量备份使用
--apply-log
和--redo-only
参数对全量备份进行恢复前准备操作,如事物回滚等操作; - 操作第一次增量备份将第一次增量备份应用到完全备份,同用使用
--apply-log
和--redo-only
参数 - 操作第二次增量备份将第二次增量备份应用到完全备份,使用
--apply-log
参数,不要加--redo-only
参数。 - 把所有合在一起的完全备份整体进行一次apply操作,回滚未提交的数据
- 使用
--copy-back
和--rsync
参数直接对全量备份操作后的目录进行恢复; - 授权MySQL实例下的data目录后,启动数据库
- 验证恢复数据
第一步 备份
[root@node backup]# cd full/
[root@node full]# pwd
/opt/backup/full
[root@node full]# ls
2018-12-11_15-35-25
[root@node full]# cp -a 2018-12-11_15-35-25/ 2018-12-11_15-35-25_bak
[root@node 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
[root@node full]# cd ../incr/
[root@node 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
[root@node incr]# cp -a 2018-12-11_15-46-22/ 2018-12-11_15-46-22_bak
[root@node incr]# cp -a 2018-12-11_15-49-41 2018-12-11_15-49-41_bak
[root@node 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实例
[root@node incr]# /etc/init.d/mysqld stop
Shutting down MySQL.. [ OK ]
[root@node incr]# ps aux|grep mysql
root 20447 0.0 0.1 112720 2180 pts/1 S+ 16:18 0:00 grep --color=auto mysql
[root@node incr]# netstat -lntup | grep 3306
[root@node incr]#
第三步 移除要恢复数据的MySQL的data目录
[root@node 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
[root@node incr]# mv /opt/mysql/data /tmp/
[root@node incr]# ls -ld /tmp/data/
drwxr-x--- 5 mysql mysql 4096 Dec 11 15:04 /tmp/data/
第四步 准备全量备份
[root@node 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!
第五步 应用第一次增量备份到全量备份
[root@node 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
[root@node 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
参数。
[root@node 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
[root@node 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操作
[root@node 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目录
[root@node 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!
验证操作
[root@node 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
[root@node full]# chown -R mysql.mysql /opt/mysql/data
[root@node 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
[root@node full]# /etc/init.d/mysqld start
Starting MySQL. [ OK ]
[root@node 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
[root@node full]# netstat -lntup | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 22340/mysqld
第十步 验证数据
[root@node 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)
上面的验证操作可以看出,备份已经完全恢复