怎樣進(jìn)行mysql中的Innobackupex全備恢復(fù)

這篇文章給大家介紹怎樣進(jìn)行MySQL中的 Innobackupex全備恢復(fù)，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

十多年專注成都網(wǎng)站制作，成都定制網(wǎng)站，個人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識、方案，網(wǎng)站設(shè)計(jì)流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),專注于成都定制網(wǎng)站,高端網(wǎng)頁制作,對自上料攪拌車等多個行業(yè)，擁有豐富的營銷推廣經(jīng)驗(yàn)。

一、  Innobackupex恢復(fù)原理
    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數(shù)據(jù)時)事務(wù)存在不一致，即copy開始時，有些事務(wù)已開始，有些剛剛開始，而copy結(jié)束前或結(jié)束后才提交或回滾。
    這些不確定的事務(wù)需要在恢復(fù)前來確定最終是否最終提交或回滾。在這個階段的操作稱之為prepare階段。
    這個prepare階段依賴于備份時的xtrabackup log(來自innodb logfile)，使用--apply-log參數(shù)實(shí)現(xiàn)一致性。
    --apply-log參數(shù)會根據(jù)xtrabackup log做相應(yīng)的前滾或回滾，完成后會重建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:
    恢復(fù)期間，--use-memory選項(xiàng)可以加速prepare過程，如果系統(tǒng)可用內(nèi)存夠大的話，該值缺省被設(shè)置為100MB。

Innobackupex恢復(fù)示意圖

二、  全備恢復(fù)流程

1. 當(dāng)前環(huán)境 
mysql> show variables like 'version'; 
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.25-log |
+---------------+------------+
1 row in set (0.00 sec)
2. 準(zhǔn)備實(shí)驗(yàn)數(shù)據(jù)
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. 數(shù)據(jù)庫全備 
[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. 查看備份產(chǎn)生的內(nèi)容
[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. 在線刪除數(shù)據(jù)，做恢復(fù)對比
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數(shù)據(jù)庫
[root@mysql01 ~]# service mysql stop
Shutting down MySQL.... [ OK ]
[root@mysql01 ~]# netstat -nltp|grep mysql|grep 3606

7. apply-log ，準(zhǔn)備全備文件 
[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實(shí)例恢復(fù) 
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! ##成功恢復(fù)

8. 查看恢復(fù)后文件的相關(guān)信息 
（時間為 20:56 的文件是剛才更新的或者生成的，主要是生成了系統(tǒng)表空間數(shù)據(jù)文件及innodb日志文件，更新了Innodb的檢查點(diǎn)文件） 
[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. 將原有文件夾重命名到新位置，并創(chuàng)建原文件夾 
[root@mysql02 mysql]# mv /data/mysql /data/mysqlbak
[root@mysql02 mysql]# mkdir -p /data/mysql

10. 將已經(jīng)恢復(fù)好的數(shù)據(jù)文件復(fù)制回原始位置 
[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 ##啟動將備份的文件復(fù)制回原路徑 
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結(jié)束

11. 權(quán)限修改
[root@mysql02 ~]# mkdir -p /data/mysql/binarylog （說明：這里我binlog在datadir在路徑下，所以要單獨(dú)為binlog創(chuàng)建目錄）
chown -R mysql:mysql /data/mysql 
12. 啟動被恢復(fù)的實(shí)例
[root@mysql02 mysql]# mysqld_safe --defaults-file=/etc/my.cnf & 
13. 登錄，驗(yàn)證
[root@mysql02 ~]# mysql -uroot -poracle
mysql> select * from test;
| id |

| 1 | >恢復(fù)成功！

關(guān)于怎樣進(jìn)行mysql中的 Innobackupex全備恢復(fù)就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

分享文章：怎樣進(jìn)行mysql中的Innobackupex全備恢復(fù)
本文路徑：http://bm7419.com/article38/jjscpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、全網(wǎng)營銷推廣、搜索引擎優(yōu)化、營銷型網(wǎng)站建設(shè)、動態(tài)網(wǎng)站、企業(yè)網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)