Linux命令：MySQL系列之十四--MySQL備份與還原（xtrabackup工具重要章節）

一、percona-xtrabckup安裝
環境：redhat 6.0
數據庫：mysql 5.5.28  (編譯安裝)

1，官網下載并安裝(下載地址文章尾部)

[root@lamp ~]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm 

warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

error: Failed dependencies:

mysql is needed by percona-xtrabackup-2.0.0-417.rhel6.x86_64

提示安裝percona-xtrabackup需要mysql(因為編譯安裝的路徑與yum安裝的目錄不同所以需要通過yum安裝mysql,但是不啟用該方式安裝的mysql)

[root@lamp ~]# yum install mysql

Loaded plugins: refresh-packagekit, rhnplugin

--> Running transaction check

---> Package mysql.x86_64 0:5.1.47-4.el6 set to be updated

--> Finished Dependency Resolution

...........

Installing:

 mysql                x86_64                5.1.47-4.el6                Server                889 k

Transaction Summary

====================================================================================================

Warning: RPMDB altered outside of yum.

  Installing     : mysql-5.1.47-4.el6.x86_64                                                    1/1 

Installed:

  mysql.x86_64 0:5.1.47-4.el6                                                                       

Complete!                  #yum安裝mysql完成

[root@lamp ~]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm   #再次執行rpm安裝 

warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing...                ########################################### [100%]

   1:percona-xtrabackup     ########################################### [100%]

# percona-xtrabackup 安裝完成

[root@lamp ~]# rpm -ql percona-xtrabackup  #查看安裝的相關目錄 （綠色目錄為主要使用目錄及運行程序）

/usr/bin/innobackupex

/usr/bin/innobackupex-1.5.1

/usr/bin/xbstream

/usr/bin/xtrabackup

/usr/bin/xtrabackup_51

/usr/bin/xtrabackup_55

/usr/share/doc/percona-xtrabackup-2.0.0

/usr/share/doc/percona-xtrabackup-2.0.0/COPYING

/usr/share/percona-xtrabackup-test

/usr/share/percona-xtrabackup-test/bootstrap.sh

/usr/share/percona-xtrabackup-test/disabled

/usr/share/percona-xtrabackup-test/disabled/ib_include.sh

/usr/share/percona-xtrabackup-test/disabled/tar_compressed.sh

/usr/share/percona-xtrabackup-test/disabled/xb_lru_dump.sh

/usr/share/percona-xtrabackup-test/experimental

...............

[root@lamp ~]# innobackupex --help  #查看相關幫助

注意：
1，之前我安裝的2.2.9，但是提示perl-DBI，perl-DBD-mysql的問題，一直無法解決，換成低版本的xtrabackup就可以。
2，如果提示mysql sock問題，在參數中指定sock的路徑。

二、使用

備份
備份時無法指定備份名，每一個備份文件夾都是以時間來命名的，里面存放的是數據文件、日志文件，目錄需存在，沒有則先創建。

1，全量備份
[root@lamp ~]# innobackupex --user=root --password=redhat --host=127.0.0.1 /backup/

  #--user:指定mysql用戶  --password:指定用戶的密碼  --host:指定主機(如果是本機，可以省略此選項)   /backup:指定備份至的目錄

***如果要使用一個最小權限的用戶進行備份，則可基于如下命令創建此類用戶：

mysql > CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'redhat'; #創建用戶bkpuser備份用戶，并通過IDENTIFIED BY 'password'設定密碼。

mysql > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkpuser'; #revoke取消ALL PRIVILEGES所有授權，GRANT OPTION授權選項從bkpuser用戶中。及把bkpuser所有的

操作數據庫的權限取消掉

mysql＞GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; 

#GRANT授權，RELOAD:重新加載的權限，LOCK TABLES:鎖表的權限，REPLICATION CLIENT:

復制客戶端的權限，ON *.*：對于那些數據有權限，TO:指定授權給那個用戶。

mysql > FLUSH PRIVILEGES;  #刷新權限列表，使得權限生效。

1、執行一次完全備份

在備份的同時，innobackupex還會在備份目錄中創建如下文件：

(1)xtrabackup_checkpoints ---該文件記錄備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN（日志系列號）范圍信息，每個InnoDB頁（通常為16K大小）都會包含一個

日志系列號，即LSN,LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面

最近是如何發生改變的。

(2)xtrabackup_binlog_info ---mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb ---二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。

(4)xtrabackup_binary  ---備份中用到的xtrabackup的可執行文件；

(5)backup-my.cnf  ---備份命令用到的配置選項信息；

在使用innobackupex進行備份時，還可以使用--no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。

2、執行完全備份后的準備:

一般情況下，在完全備份后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致的狀態。‘準備’的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。

innobackupex命令的--apply-log選項可用于實現上述功能，如下面的命令；

[root@lamp ~]# innobackupex --apply-log /PATH/TO/BACKUP-DIR  #/PATH/TO/BACKUP-DIR為剛才執行完全備份所保存的目錄。

如果執行正確，其 最后輸出的幾行信息通常如下：

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

170610 17:26:33 InnoDB: Starting shutdown...

170610 17:26:46 InnoDB: Shutdown completed; log sequence number 92036620

170610 17:26:58 innobackupex: completed OK!

在實現“準備”的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的內存的大小，默認通常為100M。如果有足夠的內存可用，可以多劃分一些內存給prepare的過程，

以提高其完成的速度。

3、從一個完全備份中恢復數據

innobackupex 命令的--copy-back選項用于執行恢復操作，其通過復制所有數據文件相關的文件至mysql服務器DATADIR目錄中來執行恢復過程，innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。

4，使用innobackupex進行增量備份

每個InnoDB的頁面都會包含一個LSN信息，每當相關的數據發生改變，相關的頁面的LSN就會自動增長，這正式InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之后發生改變的頁面來實現。

要實現第一次增量備份，可以使用下面的命令進行：

# innobackupex --user=root --password=redhat --host=127.0.0.1 --incremental /backup --incremental-basedir=BASEDIR  #--user:指定用戶，--password:指定密碼，--host:指定需要備份的主機(如果備份本機可以省略此選項)，--incremental:指定增量備份存放的目錄，--incremental-basedir:指定完全備份的所在的目錄。

[root@lamp ~]# innobackupex --user=root --password=redhat --

host=127.0.0.1  --incremental  /backup/ --incremental-basedir=/backup/2017-06-09_16-34-35/ 

***執行增量備份命令后，innobackuppex 命令會在/backup目錄中創建一個新的以時間命名的目錄以存放所有的增量備份數據，另外，在執行增量備份后再一次進行增量備份時，--incremental-basedir應該指向上一次的增量備份所在的目錄。

需要注意的是，增量備份僅能用于InnoDB或xtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。

**準備**（prepare）增量備份與準備完全備份有著一些不同，尤其要注意的是:

(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”，“重放”之后，所有的備份數據將合并到完全備份上。

(2)基于所有的備份將未提交的事務進行“回滾”

于是，準備操作就變成了：

  1、先準備完全備份的操作:

[root@lamp ~]# innobackupex --apply-log --redo-only BASE-DIR #執行完全備份的準備，

BASE-DIR:是完全備份存放的路徑，--redo-only:執行事務選擇redo

  2、執行提交第一次增量備份的操作:

[root@lamp ~]# innobackupex -apply-log -redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1  #其中BASE-DIR:指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄

  3、執行提交第二次增量備份的操作：

[root@lamp ~]# innobackupex -apply-log -redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2  #其中BASE-DIR:指的是完全備份所在的目錄，而INCREMENTAL-DIR-2指的是第二次增量備份的目錄

 如果有多次增量備份，每一次備份完后都要執行如上準備操作，才可以用于后期的恢復操作。執行完整備份和多次增量備份準備工作后，所有數據都會保存在指定的完全備份BASE-DIR路徑，后期恢復的時候

只要執行innobackupex --copy-back BASE-DIR  就可以把所有數據恢復回來。BASE-DIR為完全備份存放的目錄。

5、導入或導出單張表:

默認情況下，InnoDB表不能通過直接復制表文件的方式再mysql服務器之間進行移植，及時使用innodb_file_per_table選項，而使用Xtrabackup工具可以實現此種功能，不過，此時需要“導出”表的mysql服務器啟用了innodb_file_per_table選項(嚴格來說，是要“導出”的表在其創建之前，mysql服務

器就啟用了innodb_file_per_table選項及每表一個表空間)，并且“導入”表的服務器同時啟用了innodb_file_per_table和innodb_expand_import選項。

  (1)“導出”表

   導出表是在備份的prepare階段進行的，因此，一旦完全備份完成，就可以在prepare過程中通過

--export選項將某表導出了；

[root@lamp ~]# innobackupex --apply-log --export /path/to/backup

#此命令會在每個innodb表的表空間創建一個以.exp結尾的文件，這些以.exp結尾的文件則可以用于導入至其他服務器。

  (2)“導入”表

    要在mysql服務器上導入來自于其他服務器的某innodb表，需要先在當前服務器上創建一個跟原來表表結構一致的表，而后才能實現將表導入:

 mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

然后將此表的表空間刪除；

 mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

接下來，將來自于“導出”表的服務器的mytable表的mytable.ibd和mytable.exp文件復制到當前服務器的數據目錄，然后使用如下命令將其“導入”；

mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

***************************************************************************************

實例：執行備份恢復過程及步驟

先執行一次完全備份操作：

[root@lamp ~]# innobackupex --user=root --password=redhat /backup #執行完全備份

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under

..............................

170609 16:34:54  innobackupex: All tables unlocked

170609 16:34:54  innobackupex: Connection to database serve               r closed

innobackupex: Backup created in directory '/backup/2017-06-09_16-34-35'

innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 107

170609 16:34:54  innobackupex: completed OK!   #備份完成

[root@lamp ~]# ls /backup  #紅色字體目錄為剛才備份的數據目錄

2017-06-09_16-34-35     incremental-2017-06-06-17-01-41.sql

full-backup-2017-06-06  master-2017-06-06.info

[root@lamp ~]#  cd /backup/2017-06-09_16-34-35/

[root@lamp 2017-06-09_16-34-35]# ls

backup-my.cnf  jiaowu  performance_schema  testdb                  xtrabackup_checkpoints

hellodb        mydb    stu                 xtrabackup_binary       xtrabackup_logfile

ibdata1        mysql   test                xtrabackup_binlog_info

[root@lamp ~]# innobackupex --apply-log --redo-only /backup/2017-06-09_16-34-35  #執行完全備份的準備，把相關事務寫入到完全備份目錄中。

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under

..........................................

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

170612 11:38:51  InnoDB: Starting shutdown...

170612 11:38:56  InnoDB: Shutdown completed; log sequence number 1631244

170612 11:38:56  innobackupex: completed OK!  #執行準備完成

然后把相關的二進制日志備份好，以備到時還原所用。

[root@lamp data]# cp -a mysql-bin.* /backup/  #復制二進制日志文件至備份目錄 -a:復制文件時連帶權限屬性一起復制。

[root@lamp data]#  ls /backup/

2017-06-09_16-34-35     incremental-2017-06-06-17-01-41.sql  mysql-bin.000001  mysql-bin.000003      mysql-bin.000002    mysql-bin.index

[root@lamp data]# service mysqld stop  #停止mysqld服務

Shutting down MySQL..                                      [  OK  ]

[root@lamp data]# rm -rf ./*   #模擬mysqld數據目錄丟失

[root@lamp data]# ls

[root@lamp data]# pwd

/mydata/data

[root@lamp data]# service mysqld start  #啟動mysqld服務，此時由于數據丟失無法啟動

Starting MySQL....The server quit without updating PID file[FAILED]a/data/lamp.pid).

[root@lamp ~]# innobackupex --copy-back /backup/2017-06-09_16-34-35/   #執行完全備份的數據恢復

(不用初始化數據庫，可以直接執行數據恢復操作)

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.

           At the end of a successful copy-back run innobackupex

           prints "completed OK!".

innobackupex: Starting to copy files in '/backup/2017-06-09_16-34-35'

innobackupex: back to original data directory '/mydata/data'

...............................................

innobackupex: in '/backup/2017-06-09_16-34-35'

innobackupex: back to original InnoDB log directory '/mydata/data'

innobackupex: Finished copying back files.

170612 14:46:18  innobackupex: completed OK!   #數據恢復完成

[root@lamp data]# pwd

/mydata/data

[root@lamp data]# ll  #恢復完成后數據目錄的屬主和屬主都為root，所以需要更改數據目錄的屬主和屬組。

total 28712

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 hellodb

-rw-r-----. 1 root root 18874368 Jun 12 11:38 ibdata1

-rw-r--r--. 1 root root  5242880 Jun 12 14:46 ib_logfile0

-rw-r--r--. 1 root root  5242880 Jun 12 14:46 ib_logfile1

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 jiaowu

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 mydb

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 mysql

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 performance_schema

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 stu

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 test

drwxr-xr-x. 2 root root     4096 Jun 12 14:46 testdb

-rw-r--r--. 1 root root       24 Jun 12 14:46 xtrabackup_binlog_pos_innodb

-rw-r--r--. 1 root root       77 Jun 12 14:46 xtrabackup_checkpoints

[root@lamp data]# chown -R mysql:mysql /mydata/data  #修改目錄的屬主和屬組，使得mysql用戶有權限操作該目錄下的所有文件，-R遞歸修改。

[root@lamp data]# ll

total 28712

drwxr-xr-x. 2 mysql mysql     4096 Jun 12 14:46 hellodb

-rw-r-----. 1 mysql mysql 18874368 Jun 12 11:38 ibdata1

-rw-r--r--. 1 mysql mysql  5242880 Jun 12 14:46 ib_logfile0

-rw-r--r--. 1 mysql mysql  5242880 Jun 12 14:46 ib_logfile1

drwxr-xr-x. 2 mysql mysql      4096 Jun 12 14:46 jiaowu

drwxr-xr-x. 2 mysql mysql     4096 Jun 12 14:46 mydb

drwxr-xr-x. 2 mysql mysql    4096 Jun 12 14:46 mysql

drwxr-xr-x. 2 mysql mysql     4096 Jun 12 14:46 performance_schema

drwxr-xr-x. 2 mysql mysql     4096 Jun 12 14:46 stu

drwxr-xr-x. 2 mysql mysql     4096 Jun 12 14:46 test

drwxr-xr-x. 2 mysql mysql    4096 Jun 12 14:46 testdb

-rw-r--r--. 1 mysql mysql       24 Jun 12 14:46 xtrabackup_binlog_pos_innodb

-rw-r--r--. 1 mysql mysql       77 Jun 12 14:46 xtrabackup_checkpoints

[root@lamp data]#  service mysqld start  #啟動mysqld服務

Starting MySQL..                                           [  OK  ]

[root@lamp data]# mysql -uroot -p  #登錄mysql客戶端

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, 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 |

| hellodb            |

| jiaowu             |

| mydb               |

| mysql              |

| performance_schema |

| stu                |

| test               |

| testdb             |

+--------------------+

9 rows in set (0.00 sec)

mysql> SELECT * FROM tutors;  #查看表內容

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|   1 | HongQigong   | M      |   93 |

|   2 | HuangYaoshi  | M      |   63 |

|   3 | Miejueshitai | F      |   72 |

|   4 | OuYangfeng   | M      |   76 |

|   5 | YiDeng       | M      |   90 |

|   6 | YuCanghai    | M      |   56 |

|   7 | Jinlunfawang | M      |   67 |

|   8 | HuYidao      | M      |   42 |

|   9 | NingZhongze  | F      |   49 |

+-----+--------------+--------+------+

9 rows in set (0.00 sec)

打開另一個終端連接，把二進制日志導出

[root@lamp ~]#mysqlbinlog /backup/mysql-bin.000001 > /tmp/test.sql #導出二進制日志文件至

/tmp目錄下的test.sql以便實現及時點恢復

mysql> SET sql_log_bin=0;  #導入二進制日志文件時，先關閉二進制日志記錄功能

Query OK, 0 rows affected (0.00 sec)

mysql> SOURCE /tmp/test.sql   #導入二進制日志文件test.sql到數據庫 source相當于./

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|   1 | HongQigong   | M      |   93 |

|   2 | HuangYaoshi  | M      |   63 |

|   3 | Miejueshitai | F      |   72 |

|   4 | OuYangfeng   | M      |   76 |

|   5 | YiDeng       | M      |   90 |

|   6 | YuCanghai    | M      |   56 |

|   7 | Jinlunfawang | M      |   67 |

|   8 | HuYidao      | M      |   42 |

|   9 | NingZhongze  | F      |   49 |

+-----+--------------+--------+------+

9 rows in set (0.00 sec)

mysql> SET sql_log_bin=1;  #恢復完二進制日志后，千萬別忘記把二進制日志記錄功能打開。

Query OK, 0 rows affected (0.00 sec)

對于增量備份也可以通過增量備份準備把所有增量備份的數據都寫入到完全備份保存的目錄，到時候恢復只要直接回復完全備份的內容即可，當然下一次再次執行數據備份時，需再次做一次完全備份，然后再做增量備份。

**準備**（prepare）增量備份與準備完全備份有著一些不同，尤其要注意的是:

(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”，“重放”之后，所有的備份數據將合并到完全備份上。

(2)基于所有的備份將未提交的事務進行“回滾”

于是，準備操作就變成了：

  1、先準備完全備份的操作:

[root@lamp ~]# innobackupex --apply-log --redo-only BASE-DIR #執行完全備份的準備，

BASE-DIR:是完全備份存放的路徑，--redo-only:執行事務選擇redo

  2、執行提交第一次增量備份的操作:

[root@lamp ~]# innobackupex -apply-log -redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1  #其中BASE-DIR:指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄

  3、執行提交第二次增量備份的操作：

[root@lamp ~]# innobackupex -apply-log -redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2  #其中BASE-DIR:指的是完全備份所在的目錄，而INCREMENTAL-DIR-2指的是第二次增量備份的目錄

 如果有多次增量備份，每一次備份完后都要執行如上準備操作，才可以用于后期的恢復操作。執行完整備份和多次增量備份準備工作后，所有數據都會保存在指定的完全備份BASE-DIR路徑，后期恢復的時候

只要執行innobackupex --copy-back BASE-DIR  就可以把所有數據恢復回來。BASE-DIR為完全備份存放的目錄。

**************************************************************************************************

遠程備份
壓縮備份
[root@lamp ~]#  innobackupex  --user=root --password=redhat --host=127.0.0.1 --stream=tar /tmp | ssh root@10.1.2.208  "gzip - > /tmp/bak.tar.gz"
或者
[root@lamp ~]#  innobackupex  --user=root --password=redhat --host=127.0.0.1 --stream=tar /tmp | gzip | ssh root@10.1.2.208  " /tmp/bak.tar.gz"
--stream=tar：tar格式
gzip：壓縮
非壓縮備份
[root@lamp ~]# innobackupex  --user=root --password=redhat --host=127.0.0.1 --stream=tar /tmp | ssh root@10.1.2.208  "cat - > /tmp/bak.tar"

遠程恢復
數據壓縮的文件需要加上 “i”
[root@lamp ~]# tar -izxvf bak.tar.gz

percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

下載地址：https://www.percona.com/downloads/XtraBackup/LATEST/