共计 6963 个字符，预计需要花费 18 分钟才能阅读完成。
这篇文章给大家介绍怎样进行 mysql 中的 Innobackupex 全备恢复，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
一、 Innobackupex 恢复原理
After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data ?les consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
To prepare a backup with innobackupex you have to use the –apply-log and the path to the backup directory as an argument:
Innobackupex replayed the committed transactions in the log ?les (some transactions could have been done while the backup was being done) and rolled back the uncommitted ones. Once this is done, all the information lay in the tablespace (the InnoDB ?les), and the log ?les are re-created.
在备份期间 (copy 数据时) 事务存在不一致，即 copy 开始时，有些事务已开始，有些刚刚开始，而 copy 结束前或结束后才提交或回滚。
这些不确定的事务需要在恢复前来确定最终是否最终提交或回滚。在这个阶段的操作称之为 prepare 阶段。
这个 prepare 阶段依赖于备份时的 xtrabackup log(来自 innodb logfile)，使用 –apply-log 参数实现一致性。
–apply-log 参数会根据 xtrabackup log 做相应的前滚或回滚，完成后会重建 innodb logfile 文件。
The –use-memory option The preparing process can be speed up by using more memory in it. It depends on the free or available RAM on your system, it defaults to 100MB. In general, the more memory available to the process,the better. The amount of memory used in the process can be speci?ed by multiples of bytes:
恢复期间，–use-memory 选项可以加速 prepare 过程，如果系统可用内存够大的话，该值缺省被设置为 100MB。
Innobackupex 恢复示意图
二、 全备恢复流程
1. 当前环境
mysql show variables like version
+—————+————+
| Variable_name | Value |
+—————+————+
| version | 5.6.25-log |
+—————+————+
1 row in set (0.00 sec)
2. 准备实验数据
mysql create database hot_recover;
Query OK, 1 row affected (0.04 sec)
mysql use hot_recover;
Database changed
mysql create table test(id int);
Query OK, 0 rows affected (0.02 sec)
mysql insert into test values(1);
Query OK, 1 row affected (0.01 sec)
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from test;
+——+
| id |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
3. 数据库全备
[root@mysql01 ~]#innobackupex –user=root –password=oracle –port=3606 /xtrabackup/full/
xtrabackup: Transaction log of lsn (1631855) to (1631855) was copied.
170608 20:54:21 completed OK!
4. 查看备份产生的内容
[root@mysql02 mysql]# ll /xtrabackup/full/
drwxr-x—. 6 root root 4096 Jun 8 20:54 2017-06-08_20-54-14
[root@mysql02 mysql]# ll /xtrabackup/full/2017-06-08_20-54-14
total 12324
-rw-r—–. 1 root root 418 Jun 8 20:54 backup-my.cnf
drwxr-x—. 2 root root 4096 Jun 8 20:54 hot_recover
-rw-r—–. 1 root root 12582912 Jun 8 20:54 ibdata1
drwxr-x—. 2 root root 4096 Jun 8 20:54 mysql
drwxr-x—. 2 root root 4096 Jun 8 20:54 performance_schema
drwxr-x—. 2 root root 4096 Jun 8 20:54 test
-rw-r—–. 1 root root 18 Jun 8 20:54 xtrabackup_binlog_info
-rw-r—–. 1 root root 113 Jun 8 20:54 xtrabackup_checkpoints
-rw-r—–. 1 root root 482 Jun 8 20:54 xtrabackup_info
-rw-r—–. 1 root root 2560 Jun 8 20:54 xtrabackup_logfile
5. 在线删除数据，做恢复对比
mysql drop table test;
Query OK, 0 rows affected (0.24 sec)
mysql select * from test;
ERROR 1146 (42S02): Table hot_recover.test doesn t exist
6. 停止 mysql 数据库
[root@mysql01 ~]# service mysql stop
Shutting down MySQL…. [OK]
[root@mysql01 ~]# netstat -nltp|grep mysql|grep 3606
7. apply-log，准备全备文件
[root@mysql02 ~]# innobackupex –apply-log –user=oracle –password=oracle –port=3606 /xtrabackup/full/2017-06-08_20-54-14
170608 20:56:38 innobackupex: Starting the apply-log operation ## 开始 apply-log
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: 6f7a799)
xtrabackup: cd to /xtrabackup/full/2017-06-08_20-54-14/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1631855)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
。。。。省略
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: The log sequence number 1626143 in the system tablespace does not match the log sequence number 1631855 in the ib_logfiles!
InnoDB: Starting crash recovery. ## 开始 innodb 实例恢复
InnoDB: xtrabackup: Last MySQL binlog file position 586, file name binlog.000008
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 1632277
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 1632296
170608 20:56:44 completed OK! ## 成功恢复
8. 查看恢复后文件的相关信息
（时间为 20:56 的文件是刚才更新的或者生成的，主要是生成了系统表空间数据文件及 innodb 日志文件，更新了 Innodb 的检查点文件）
[root@mysql02 mysql]# ll /xtrabackup/full/2017-06-08_20-54-14/
total 131108
-rw-r—–. 1 root root 418 Jun 8 20:54 backup-my.cnf
drwxr-x—. 2 root root 4096 Jun 8 20:54 hot_recover
-rw-r—–. 1 root root 12582912 Jun 8 20:56 ibdata1
-rw-r—–. 1 root root 50331648 Jun 8 20:56 ib_logfile0
-rw-r—–. 1 root root 50331648 Jun 8 20:56 ib_logfile1
-rw-r—–. 1 root root 12582912 Jun 8 20:56 ibtmp1
drwxr-x—. 2 root root 4096 Jun 8 20:54 mysql
drwxr-x—. 2 root root 4096 Jun 8 20:54 performance_schema
drwxr-x—. 2 root root 4096 Jun 8 20:54 test
-rw-r—–. 1 root root 18 Jun 8 20:54 xtrabackup_binlog_info
-rw-r–r–. 1 root root 18 Jun 8 20:56 xtrabackup_binlog_pos_innodb
-rw-r—–. 1 root root 113 Jun 8 20:56 xtrabackup_checkpoints
-rw-r—–. 1 root root 482 Jun 8 20:54 xtrabackup_info
-rw-r—–. 1 root root 8388608 Jun 8 20:56 xtrabackup_logfile
9. 将原有文件夹重命名到新位置，并创建原文件夹
[root@mysql02 mysql]# mv /data/mysql /data/mysqlbak
[root@mysql02 mysql]# mkdir -p /data/mysql
10. 将已经恢复好的数据文件复制回原始位置
[root@mysql02 mysql]# innobackupex –defaults-file=/etc/my.cnf –user=root –password=oracle –port=3606 –copy-back /xtrabackup/full/2017-06-08_20-54-14/
170608 21:01:02 innobackupex: Starting the copy-back operation ## 启动将备份的文件复制回原路径
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
170608 21:01:02 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
170608 21:01:03 [01] …done
170608 21:01:03 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
。。。。省略
170608 21:01:07 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /data/mysql/performance_schema/table_io_waits_summary_by_table.frm
170608 21:01:07 [01] …done
170608 21:01:07 [01] Copying ./performance_schema/events_stages_history_long.frm to /data/mysql/performance_schema/events_stages_history_long.frm
170608 21:01:07 [01] …done
170608 21:01:07 completed OK! ##copy 结束
11. 权限修改
[root@mysql02 ~]# mkdir -p /data/mysql/binarylog（说明：这里我 binlog 在 datadir 在路径下，所以要单独为 binlog 创建目录）
chown -R mysql:mysql /data/mysql
12. 启动被恢复的实例
[root@mysql02 mysql]# mysqld_safe –defaults-file=/etc/my.cnf
13. 登录，验证
[root@mysql02 ~]# mysql -uroot -poracle
mysql select * from test;
| id |
| 1 | 恢复成功！
关于怎样进行 mysql 中的 Innobackupex 全备恢复就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
