數據庫的備份和恢復

一 （理論篇）、數據庫備份與恢復基礎

1、數據庫的備份與恢復

• 備份：存儲的數據副本；

    原始數據：持續改變；
  

• 恢復：把副本應用到線上系統；

    僅能恢復至備份操作時刻的數據狀態；
  

  時間點恢復：

        binary logs; 
  

2、為什么備份？

災難恢復： 硬件故障（冗余）、軟件故障（bug）、自然災害、黑客攻擊、誤操作、… 測試；

3.備份時應該注意事項：

• 能容忍最多丟失多少數據；
• 恢復數據需要在多長時間內完成；
• 需要恢復哪些數據；

 做恢復演練：
    測試備份的可用性；
    增強恢復操作效率；
    ...

4. 備份類型：

備份的數據的及范圍：

完全備份和部分備份
        完全備份：整個數據集；
        部分備份：數據集的一部分，比如部分表；

完全備份、增量備份、差異備份區別

    完全備份
    增量備份：僅備份自上一次完全備份或 增量備份以來變量的那部數據；
    差異備份：僅備份自上一次完全備份以來變量的那部數據；

物理備份、邏輯備份：

    物理備份：復制數據文件進行備份；
    邏輯備份：從數據庫導出數據另存在一個或多個文件中；

根據數據服務是否在線：

    熱備：讀寫操作均可進行的狀態下所做的備份；
    溫備：可讀但不可寫狀態下進行的備份；
    冷備：讀寫操作均不可進行的狀態下所做的備份；

    MyISAM:溫備，不能熱備
    Innodb：熱備

5、備份需要考慮因素：

    鎖定資源多長時間？
    備份過程的時長？
    備份時的服務器負載？
    恢復過程的時長？

6、備份策略：

        完全+差異
        完全+增量 

7、備份手段：物理、邏輯

9、備份什么？

    數據
    二進制日志、InnoDB的事務日志；
    代碼（存儲過程、存儲函數、觸發器、事件調度器）
    服務器的配置文件

10、備份工具：

mysqldump：mysql服務自帶的備份工具；邏輯備份工具；

        完全、部分備份；
        InnoDB：熱備；
        MyISAM：溫備；

cp/tar等復制歸檔工具：物理備份工具，適用于所有存儲引擎；冷備；完全備份

lvm2：快照（請求一個全局鎖），之后立即釋放鎖，達到幾乎熱備的效果；物理備份；借助于文件系統的管理工具

注意：不能僅備份數據文件；要同時備份事務日志；
    前提：要求數據文件和事務日志位于同一個邏輯卷；

xtrabackup：

    由Percona提供，開源工具，支持對InnoDB做熱備，物理備份工具；
        完全備份、部分備份；
        完全備份、增量備份；
        完全備份、差異備份；

mysqlhotcopy：幾乎冷備；僅適用于MyISM存儲引擎
select：

        備份：SELECT cluase INTO OUTFILE 'FILENAME';
        恢復：CREATE TABLE 
        導入：LOAD DATA 

二、實戰篇

（一）、備份工具的選擇：

1、 mysqldump+復制binlog：這種可以遠程備份
mysqldump：做完全備份
復制binlog中指定范圍的event：增量備份;

2、lvm2快照+復制binlog：
使用cp或tar做物理備份；完全備份；
復制binlog中指定范圍的event：增量備份;

3、xtrabackup(innodb)+binlog：

由Percona提供的支持對InnoDB做熱備（物理備份）的工具；
完全備份、增量備份！

（二）準備工作

首先查看二進制日志是否開啟

]> show global variables like '%log%';

如果沒開啟
可以用 set global log_bin=ON；
要是不行就改配置文件 mysql.conf*,加上log_bin

查看二進制日志文件

]> show master logs;

實戰（1）mysqldump：

邏輯備份、完全備份、部分備份；

二次封裝工具：

• mydumper
• phpMyAdmin

這是一個巨大的SQL語句、單個巨大的備份工具。
這是個客戶端命令工具，通過mysql協議連接至mysql服務器。這個備份相當于查詢庫中所有的數據并用插入的方式存儲在另一個文件中。

用法：

• mysqldump [OPTIONS] db_name [tbl_name ...] 這種不會創建備份的這個數據庫！！

• mysqldump [options] --databases db_name ... 這種即便備份庫中的一個表也會創建這個庫。

• mysqldump [OPTIONS] --all-databases [OPTIONS] 自動創建數據庫！

MyISAM存儲引擎：支持溫備，備份時要鎖定表；

    -x, --lock-all-tables：鎖定所有庫的所有表，讀鎖；
    -l, --lock-tables：鎖定指定庫所有表；    

InnoDB存儲引擎：支持溫備和熱備；

--single-transaction：創建一個事務，基于此快照執行備份；

其它選項：

    -R, --routines：備份所有存儲過程和存儲函數；
    --triggers：備份表相關的觸發器此為默認選項可以不加
    -E, --events ：備份指定數據庫相關的event scheduler(調度程序)     

     --master-data[=#]
        此語句用于在二進制文件中記錄備份時間點標記
        1：記錄為CHANGE MASTER TO語句，此語句不被注釋；
        2：記錄為CHANGE MASTER TO語句，此語句被注釋；
        以下就是=2時備份文件中出現的內容
        -- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=15335;
            表示指明那個二進制文件，和保存時處于什么位置    

    --flush-logs：鎖定表完成后，即進行日志刷新操作（只能滾動二進制日志）；

mysqldump備份使用方法

備份數據庫

 ~]# mysqldump -uroot -h127.0.0.1 -p --databases hellodb > /tmp/hellodb.sql

備份多個數據庫

~]# mysqldump -uroot -h127.0.0.1 -p --databases hellodb  mysql > /tmp/hellodb_mydb.sql            

備份與還原

備份，完全備份

~]# mysqldump -uroot  -hlocalhost --all-databases --lock-all-tables --master-data=2 > /root/all.sql

還原完全備份的文件+binlog

（1）還原完全備份
1、關閉二進制日志功能；

set sql_log_bin=OFF;

2、啟動數據庫

3、導入之前備份的文件并導入

 ~]# mysql < all.sql 或者 > source /tmp/all.sql

（2）還原至時間點
1、首先查看備份時的二進制文件位置

2、由二進制日志文件導出還原點之前的二進制日志

 ~]# mysqlbinlog --start-position=16842 ./mariadb-bin.000003 > binlog1.sql

3、把此文件導入mysql

~]# mysql < binlog1.sql 

（3）打開二進制日志功能并刷新日志
1、開啟日志功能

2、刷新日志文件

> flush logs;

注意：

1、二進制日志文件不應該與數據文件放在同一磁盤上
2、如果數據不大最少應該每周做一次完全備份，每天或著每6個小時來刷新日志（flush logs），復制二進制日志文件做增量備份

實戰（2）基于lvm2的備份：

    前提：數據目錄位于邏輯卷，包含了數據文件和事務日志；

    (1) 請求鎖定所有表；
        mysql> FLUSH TABLES WITH READ LOCK;

    (2) 記錄二進制文件事件位置；
        mysql> FLUSH LOGS;
        mysql> SHOW MASTER STATUS;

        mysql  -e  'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE

    (3) 創建快照卷
        lvcreate  -L # -s -p r - SNAM-NAME /dev/VG-NAME/LV-NAME 

    (4) 釋放鎖
        mysql> UNLOCK TABLES

    (5) 掛載快照卷，并執行備份；

    (6) 周期性備份二進制日志,通過原卷備份二進制日志； 

實戰（3）Xtrabackup：

    MyISAM：溫備，不支持增量備份；
    InnoDB：熱備，增量；

物理備份，速率快、可靠；備份完成后自動校驗備份結果集是否可用；還原速度快；

Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]

完全+binlog(總結)：

預備：要確認innodb_file_per_table=ON

（1）準備工作

從官方找到對應版本的rpm包并下載

https://www.percona.com/downloads/XtraBackup/LATEST/

這里我下載的是 percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
1、創建用戶并授權能訪問

mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

全庫備份：

1、物理上的數據文件備份

innobackupex  --user  --password=  --host=  /PATH/TO/BACKUP_DIR 

2、把此生成的文件保存下來

恢復（期間mysql是關閉狀態）：

1、刪除原有的數據保存目錄下（/var/lib/mysql/*）所有文件

2、整理事務日志中的已提交的事務數據：

innobackupex --apply-log  /PATH/TO/BACKUP_DIR 

3、執行數據的復制操作

innobackupex --copy-back  /PATH/TO/BACKUP_DIR

4、把數據保存目錄下的所有文件改屬主屬組。

chown -R mysql.mysql /var/lib/mysql/*

5、這里還是會啟動不了可以，刪除這里的事務日志，因為與默認的不相符，或者按下邊日志上說的做

注意：–copy-back需要在mysqld主機本地進行，mysqld服務不能啟動；
innodb_log_file_size可能要重新設定；

增量備份

1.執行增量備份

innobackupex --user  --password=''  --host=''  --incremental=BBB  --incremental-basedir=/AAA
表示相對于/AAA 這個備份目錄做備份操作，備份后的保存位置/BBB 

2.恢復：

“準備”(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：
(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”。“重放”之后，所有的備份數據將合并到完全備份上。
(2)基于所有的備份將未提交的事務進行“回滾”。

1.于是，操作就變成了（整理完全備份并應用事務日志）：
# innobackupex --apply-log --redo-only BASE-DIR

2.接著執行整理第一個增量備份：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

3.而后是第二個增量：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2        

4.執行恢復操作

innobackupex --copy-back BASEDIR

改下屬主屬組，然后確保事務日志是否為默認大小，或者刪了也行

5.最后應用二進制日志文件

mysqlbinlog --start-position=1714 ./mariadb-bin.000002 > binlog1.sql

~]# mysql < binlog1.sql 

啟動就OK！

備份單庫：

--databases 

總結：完全+增量+binlog
備份：完全+增量+增量+…
完全+差異

---

使用Xtrabackup進行MySQL備份：

一、安裝

1、簡介

Xtrabackup是由percona提供的mysql數據庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb數據庫進行熱備的工具。特點：
(1)備份過程快速、可靠；
(2)備份過程不會打斷正在執行的事務；
(3)能夠基于壓縮等功能節約磁盤空間和流量；
(4)自動實現備份檢驗；
(5)還原速度快；

2、安裝

其最新版的軟件可從 http://www.percona.com/software/percona-xtrabackup/ 獲得。本文基于RHEL5.8的系統，因此，直接下載相應版本的rpm包安裝即可，這里不再演示其過程。

二、備份的實現

1、完全備份

# innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

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

mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命名的目錄中。

在備份的同時，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、準備(prepare)一個完全備份

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

innobakupex命令的–apply-log選項可用于實現上述功能。如下面的命令：

innobackupex --apply-log  /path/to/BACKUP-DIR

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

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407  9:01:36  InnoDB: Starting shutdown...
120407  9:01:40  InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40  innobackupex: completed OK!

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

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

注意：恢復不用啟動MySQL

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

    innobackupex --copy-back  /path/to/BACKUP-DIR

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

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.

120407 09:36:10  innobackupex: completed OK!

請確保如上信息的最行一行出現“innobackupex: completed OK!”。

當數據恢復至DATADIR目錄以后，還需要確保所有數據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如：

chown -R  mysql:mysql  /mydata/data/

4、使用innobackupex進行增量備份

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

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

innobackupex --incremental /backup --incremental-basedir=BASEDIR

其中，BASEDIR指的是完全備份所在的目錄，此命令執行結束后，innobackupex命令會在/backup目錄中創建一個新的以時間命名的目錄以存放所有的增量備份數據。另外，在執行過增量備份之后再一次進行增量備份時，其–incremental-basedir應該指向上一次的增量備份所在的目錄。

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

“準備”(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：

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

于是，操作就變成了：
# innobackupex --apply-log --redo-only BASE-DIR

接著執行：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二個增量：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

其中BASE-DIR指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，INCREMENTAL-DIR-2指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執行如上操作；

5、Xtrabackup的“流”及“備份壓縮”功能

Xtrabackup對備份的數據文件支持“流”功能，即可以將備份的數據通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。

要使用此功能，僅需要使用–stream選項即可。如：

# innobackupex --stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用類似如下命令將數據備份至其它服務器：

# innobackupex --stream=tar  /backup | ssh user@www.magedu.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar" 

此外，在執行本地備份時，還可以使用–parallel選項對多個文件進行并行復制。此選項用于指定在復制時啟動的線程數目。當然，在實際進行備份時要利用此功能的便利性，也需要啟用innodb_file_per_table選項或共享的表空間通過innodb_data_file_path選項存儲在多個ibdata文件中。

對某一數據庫的多個文件的復制無法利用到此功能。其簡單使用方法如下：

# innobackupex --parallel  /path/to/backup

同時，innobackupex備份的數據文件也可以存儲至遠程主機，這可以使用–remote-host選項來實現：

# innobackupex --remote-host=root@www.magedu.com  /path/IN/REMOTE/HOST/to/backup

6、導入或導出單張表

默認情況下，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選項將某表導出了：

# innobackupex --apply-log --export /path/to/backup
此命令表示對 /path/to/backup 這個完全備份的目錄進行導出操作

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

(2)“導入”表

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

mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;
此命令表示創建一個對應文件名的表可以先用 · show create table hellodb.students；· 來查看那張表的表空間

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

mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

更改屬主屬組

chown mysql.mysql ...

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

mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

7、使用Xtrabackup對數據庫進行部分備份

Xtrabackup也可以實現部分備份，即只備份某個或某些指定的數據庫或某數據庫中的某個或某些表。但要使用此功能，必須啟用innodb_file_per_table選項，即每張表保存為一個獨立的文件。同時，其也不支持–stream選項，即不支持將數據通過管道傳輸給其它程序進行處理。

此外，還原部分備份跟還原全部數據的備份也有所不同，即你不能通過簡單地將prepared的部分備份使用–copy-back選項直接復制回數據目錄，而是要通過導入表的方向來實現還原。當然，有些情況下，部分備份也可以直接通過–copy-back進行還原，但這種方式還原而來的數據多數會產生數據不一致的問題，因此，無論如何不推薦使用這種方式。

(1)創建部分備份

創建部分備份的方式有三種：正則表達式(–include), 枚舉表文件(–tables-file)和列出要備份的數據庫(–databases)。

(a)使用–include
使用–include時，要求為其指定要備份的表的完整名稱，即形如databasename.tablename，如：

# innobackupex --include='^mageedu[.]tb1'  /path/to/backup

(b)使用–tables-file
此選項的參數需要是一個文件名，此文件中每行包含一個要備份的表的完整名稱；如：

# echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt
# innobackupex --tables-file=/tmp/tables.txt  /path/to/backup

(c)使用–databases
此選項接受的參數為數據名，如果要指定多個數據庫，彼此間需要以空格隔開；同時，在指定某數據庫時，也可以只指定其中的某張表。此外，此選項也可以接受一個文件為參數，文件中每一行為一個要備份的對象。如：

# innobackupex --databases="mageedu testdb"  /path/to/backup

(2)整理(preparing)部分備份

prepare部分備份的過程類似于導出表的過程，要使用–export選項進行：

# innobackupex --apply-log --export  /pat/to/partial/backup

此命令執行過程中，innobackupex會調用xtrabackup命令從數據字典中移除缺失的表，因此，會顯示出許多關于“表不存在”類的警告信息。同時，也會顯示出為備份文件中存在的表創建.exp文件的相關信息。

(3)還原部分備份

還原部分備份的過程跟導入表的過程相同。當然，也可以通過直接復制prepared狀態的備份直接至數據目錄中實現還原，不要此時要求數據目錄處于一致狀態。