怎樣進行mysql中的 Innobackupex全備恢復

這篇文章給大家介紹怎樣進行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全備恢復就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。