(轉)解鎖MySQL備份恢複的4種正確姿勢

標籤：並行   沒有   後台進程   是你   關係   values   star   密碼   jmh   

本文根據DBAplus社群第104期線上分享整理而成。

 原文：http://dbaplus.cn/news-11-1267-1.html

講師介紹  

馮帥

點融網進階DBA

 

 

• 獲有Oracle OCM、MySQL OCP；

• 目前從事MySQL相關的營運和架構工作，擅長異構資料庫互動。

 

分享大綱：

1. mysqldump

2. mysqlbackup

3. mysqlhotcopy

4. xtrabackup/innobackupex

 

備份高於一切，今天匯總一下常用的幾種備份方法，以及恢複的步驟。

 

一、mysqldump

 

在日常工作中，我們會使用mysqldump命令建立SQL格式的轉儲檔案來備份資料庫。或者我們把資料匯出後做資料移轉，主備搭建等操作。mysqldump是一個邏輯備份工具,複製原始的資料庫物件定義和表資料產生一組可執行檔SQL語句。 預設情況下,產生insert語句，也能產生其它分隔字元的輸出或XML格式的檔案。

shell> mysqldump [arguments] > file_name

 

我們簡單來看一下日常的用法：

 

備份所有的資料庫：

shell> mysqldump --all-databases > dump.sql (不包含INFORMATION_SCHEMA,performance_schema,sys，如果想要匯出的話還要結合--skip-lock-tables和--database一起用)

 

備份指定的資料庫：

shell> mysqldump --databases db1 db2 db3 > dump.sql

 

當我們只備份一個資料的時候可以省去 --databases 直接寫成：mysqldump test > dump.sql 不過有一些細微的差別，如果不加的話，資料庫轉儲輸出不包含建立資料庫和use語句，所以可以不加這個參數直接匯入到其它名字的資料庫裡。

 

當然我們也可以只備份某個表 ： 

mysqldump --user [username] --password=[password] [database name] [table name] table_name.sql

 

瞭解了簡單的一些用法後我們再著重看一下幾個參數：

 

• master-data 擷取備份資料的Binlog位置和Binlog檔案名稱，用於通過備份恢複的執行個體之間建立複製關係時使用，該參數會預設開啟。

• dump-slave 用於在slave上dump資料，建立新的slave。因為我們在使用mysqldump時會鎖表，所以大多數情況下，我們的匯出操作一般會在唯讀備庫上做，為了擷取主庫的Relay_Master_Log_File和Exec_Master_Log_Pos，需要用到這個參數，不過這個參數只有在5.7以後的才會有

• no-data, -d 不匯出任何資料，只匯出資料庫表結構

 

剛剛我們說過在使用mysqldump的時候會鎖表，我們來詳細看一下它的鎖機制。

 

我們開兩個視窗，在第一個裡面執行mysqldump -uroot -pxxxxx --master-data=2 --databases dbname > /tmp/dbnamedate +%F.sql 然後第二個視窗登陸進去，使用show process的命令可以看到目前dump的session正在執行。

 

SELECT /!40001 SQL_NO_CACHE / * FROM table_name; 可以看到這條SQL正在以no_cache的模式查詢資料。

 

然後我們在同樣的表上執行一下select，發現被阻塞了。游標一直不返回。

 

 

一般遇到這種檔案，我們會想是不是有鎖呢？ 為了驗證我們查看一下鎖的資訊，可以發現dump的進程實際上是加了鎖的。

 

 

我們把具體的general_log開啟，然後看一下當時的操作：

 

 

4101044 Query FLUSH /!40101 LOCAL / TABLES

4101044 Query FLUSH TABLES WITH READ LOCK

 

（關閉所有開啟的表，同時對於所有資料庫中的表都加一個讀鎖，直到顯示地執行unlock tables，該操作常常用於資料備份的時候。）

 

4101044 Query SHOW MASTER STATUS

 

（這是因為我用了--master-data=2）

 

所以這個時候表就會被鎖住。

 

如果我不加--master-data參數(mysqldump -uroot -pxx --databases db > /tmp/dbnamedate +%F.sql) mysql會顯示的對每一張要備份的表執行 LOCK TABLES table_name1 READ,LOCK TABLES table_name2 READ ，並且也不會有讀的阻塞。

 

那有沒有不鎖的方法，其實也是有的，就是使用--single-transaction把備份的操作放在一個事務裡去進行。

 

帶上--single-transaction參數的mysqldump備份過程：

 

如果是5.6版本的MySQL

 

 

在備份之間同樣的先FLUSH TABLES WITH READ LOCK，然後設定事務層級SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，然後開啟一個事務START TRANSACTION進行備份，這個時候備份的過程就很意思，它先建立了一個savepoint，然後把資料庫裡的表依次的進行備份，備份完成了之後又復原到了之前的savepoint，來保證資料的一致性。

 

如果是5.7版本的MySQL

 

 

備份前的操作相同，只是沒有了savepoint。

 

不過不管是哪個版本，只有InnoDB表是在一個一致性的狀態。其它的任何MyISAM表或記憶體表是沒有用的。 mysqldump的優勢是可以查看或者編輯十分方便，它也可以靈活性的恢複之前的資料。它也不關心底層的儲存引擎，既適用於支援事務的，也適用於不支援事務的表。不過它不能作為一個快速備份大量的資料或可伸縮的解決方案。如果資料庫過大,即使備份步驟需要的時間不算太久,但有可能恢複資料的速度也會非常慢,因為它涉及的SQL語句插入磁碟I/O,建立索引等等。 對於大規模的備份和恢複,更合適的做法是物理備份,複製其原始格式的資料檔案,可以快速恢複:如果你的表主要是InnoDB表,或者如果你有一個InnoDB和MyISAM表,可以考慮使用MySQL的mysqlbackup命令備份。

 

恢複操作：

 

先看一下當前的資料：

[email protected] 11:10:34>select * from t;

+-------+

|  id  |

+-------+

|  1  |

+-------+

1 row in set (0.00 sec)

 

備份；

mysqldump -uroot [email protected] --master-data=1 test >test.sql

 

類比增量操作：

[email protected] 11:15:17>insert into t values (2);

Query OK, 1 row affected (0.00 sec)

 

[email protected] 11:15:36>select * from t; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)

 

類比誤操作：

[email protected] 11:15:41>truncate table t;

Query OK, 0 rows affected (0.01 sec)

 

[email protected] 11:16:14>select * from t;

Empty set (0.00 sec)

 

類比恢複操作：

 

step 1：找到誤操作的log position

[email protected] 11:20:57>show master logs;

[email protected](none) 11:21:37>show binlog events in ‘mysql-bin.000004‘;

 

查看可以看到是444。

 

step 2：恢複到備份

[email protected] 11:16:25>source test.sql

[email protected] 11:17:26>select from t;

+-------+

|  id  |

+-------+

|  1  |

+-------+

1 row in set (0.00 sec)

 

step 3: 因為我們在備份的時候使用了master-data的參數，所以可以直接看到備份時候的最後位置，然後應用中間的log。查看可以看到是187。

 

我們使用mysqlbinlog得到這一段時間的操作，其實我們也可以用這個工具得到操作後使用sed進行undo的操作。

 

mysqlbinlog --start-position=187 --stop-position=444 mysql-bin.000004 > increment.sql

[email protected] 11:44:37>source /u01/my3307/log/increment.sql [email protected] 11:44:50>select from t; +------+ | id | +------+ | 1 | | 2 | +------+

 

至此資料恢複。

 

二、mysqlbackup

 

mysqlbackup是Oracle公司提供的針對企業的備份軟體，全名叫做MySQL Enterprise Backup，是一個收費的軟體。 

 

：https://www.mysql.com/products/enterprise/backup.html ，可以試用下載。

 

我們簡單來看一下這個工具的使用。

 

查看所有的協助：

 

 

我這裡只是截取了一小部分，這個協助很長，參數很多，功能很全，是oracle官方主推的備份方式。

 

全量備份

mysqlbackup --user=root --password=ucjmh --databases=‘t1‘ --encrypt-password=1 --with-timestamp --backup-dir=/u01/backup/ backup

 

解釋一下參數：

• databases 要備份的資料庫

• with-timestamp 產生一個目前時間的備份目錄。mysqlbackup這個工具要求一個空目錄才能做備份。所以這個會常用。

• backup-dir 備份的目錄 。

• compress：壓縮備份 這個提供了多種壓縮方法和壓縮層級。1--9,壓縮比依次遞增。

 

backup 是備份的方式， 一共有如下幾種方式，我會在一個恢複案例裡把常用的幾個都用到。

 

Backup operations: backup, backup-and-apply-log, backup-to-image

Update operations: apply-log, apply-incremental-backup

Restore operations: copy-back, copy-back-and-apply-log

Validation operation: validate

Single-file backup operations: image-to-backup-dir, backup-dir-to-image, list-image, extract

 

其實，在大多數情況下,單個檔案備份,使用backup-to-image命令建立,效能優於backup。buckup這個命令只執行一個完整的備份過程的初始階段。需要通過再次運行mysqlbackup運用apply-log 命令，使備份一致。

 

mysqlbackup --user=root --password=ucjmh --databases=‘t1‘ --encrypt-password=1 --with-timestamp --backup-dir=/u01/backup/2017-04-28_12-49-35/ apply-log

 

當然你可以直接用backup-and-apply-log 不過這個時候的備份將不能用於增量了。

 

增量備份：

mysqlbackup --user=root --password=ucjmh --databases=‘t1‘ --encrypt-password=1 --with-timestamp --backup-dir=/u01/backup/ --incremental --incremental-base=dir:/u01/backup/2017-04-28_12-49-35 --incremental-backup-dir=/u01/backup/incremental backup

 

這個是基於上次的備份做的備份，當然也可以基於某一個log position之後做。

 

--incremental：代表增量備份； 

--incremental-base：上次全備的目錄；

--incremental-backup-dir：增量備份的儲存的目錄

 

再多說一點關於image的備份:

 

使用如下命令可以進行備份：

mysqlbackup --user=root --password=ucjmh --databases=‘t1‘ --encrypt-password=1 --with-timestamp --backup-dir=/u01/backup/ --backup-image=all.mbi backup-to-image

 

備份之後可以很清楚的發現這個比backup要節省很多空間，把所有的檔案都以二進位的方式放在了all.mbi這個檔案裡，可以使用list-image來查看具體內容。

 

mysqlbackup --backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi list-image

 

同樣的也可以使用 mysqlbackup --backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi extract 來解壓出來具體的內容。

 

因為這是一個Oracle出的工具，有很深的rman的影子在，0級，1級備份，加密，異構機器還原等特性。

 

更多的參數可以參看online help： 

• https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/backup-commands-single-file.html

 

恢複操作：

 

查看當前資料

[email protected] 11:51:32>select * from t;

+-------+

|  id  |

+-------+

|  1  |

+-------+

1 row in set (0.01 sec)

 

全量備份

mysqlbackup --user=root [email protected] --databases=‘test‘ --with-timestamp --backup-dir=/data/backup/ backup

 

類比增量操作：

[email protected] 11:54:04>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

+-------+

2 rows in set (0.00 sec)

 

增量備份：

mysqlbackup --user=root [email protected] --databases=‘test‘ --with-timestamp --backup-dir=/data/backup/ --incremental --incremental-base=dir:/data/backup/2017-04-29_11-53-20 --incremental-backup-dir=/data/backup/incremental backup

 

類比無備份操作：

[email protected] 11:57:10>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

|  3  |

+-------+

3 rows in set (0.00 sec)

 

類比誤操作：

[email protected] 11:57:17>truncate table t; Query OK, 0 rows affected (0.01 sec)

 

類比恢複操作：

 

step 1：找到誤操作的log position

[email protected] 11:58:06>show master logs;

[email protected] 11:58:18>show binlog events in ‘mysql-bin.000001‘;

1333

 

step 2：恢複全量

 

檢測並應用日誌：

mysqlbackup --backup-dir=/data/backup/2017-04-29_11-53-20 apply-log

 

step 3：應用增量

mysqlbackup --backup-dir=/data/backup/2017-04-29_11-53-20 --incremental-backup-dir=/data/backup/incremental/2017-04-29_11-55-54 apply-incremental-backup

 

step 4：物理檔案複製還原

mysqlbackup --backup-dir=/data/backup/2017-04-29_11-53-20 copy-back

 

資料恢複到備份的時候：

[email protected] 12:09:49>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

+-------+

2 rows in set (0.00 sec)

 

恢複完成之後，data目錄下會產生backup_variables.txt的檔案（其實在備份的時候就已經有這些檔案的），找到備份的時候的log position，然後從binlog恢複無備份的資料。

 

binlog_position=mysql-bin.000001:1076 mysqlbinlog mysql-bin.000001 --start-position=1076 --stop-position=1333 -vv >increment.sql

 

[email protected] 12:14:07>source /u01/my3307/log/increment.sql [email protected] 12:14:16>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

|  3  |

+-------+

3 rows in set (0.00 sec)

 

至此資料恢複。

 

大致梳理一下操作步驟，來瞭解一下恢複的原理：

 

首先檢測並應用全備交易記錄檔（這裡是因為我備份的時候用的是backup而不是backup-and-apply-log），然後基於全備去應用增量的log。這個時候如果有多次增量備份也可以（基於LSN點向後應用）。 所有的都應用完成之後就是一個可以直接cp的資料庫了。

 

個人感覺這個工具比xtrabackup好用，但是xtrabackup是開源的，所以市場佔有量才會大，才會更有名，更多人用吧。

 

三、mysqlhotcopy

 

 mysqlhotcopy使用lock tables、flush tables和cp或scp來快速備份資料庫.它是備份資料庫或單個表最快的途徑,完全屬於物理備份,但只能用於備份MyISAM儲存引擎和ARCHIVE引擎，並且是一個伺服器命令，只能運行在資料庫目錄所在的機器上.與mysqldump備份不同,mysqldump屬於邏輯備份,備份時是執行的sql語句.使用mysqlhotcopy命令前需要要安裝相應的軟體依賴包. 因為這個功能很弱，我們只簡單的介紹一個怎麼用：

 

備份一個庫

mysqlhotcopy db_name [/path/to/new_directory]

 

備份一張表

mysqlhotcopy db_name./table_name/ /path/to/new_directory

 

更詳細的使用可以使用perldoc mysqlhotcopy查看。

 

四、xtrabackup/innobackupex

 

Percona XtraBackup是一款基於MySQL的熱備份的開源公用程式，它可以備份5.1到5.7版本上InnoDB,XtraDB,MyISAM儲存引擎的表， Xtrabackup有兩個主要的工具：xtrabackup、innobackupex 。

 

（1）xtrabackup只能備份InnoDB和XtraDB兩種資料表，而不能備份MyISAM資料表 　　

（2）innobackupex則封裝了xtrabackup，是一個指令碼封裝，所以能同時備份處理innodb和myisam，但在處理myisam時需要加一個讀鎖。

 

首先我們先來簡單的瞭解一下xtrabackup是怎麼工作的。xtrabackup基於innodb的crash-recovery（執行個體恢複）功能，先copy innodb的物理檔案（這個時候資料的一致性是無法滿足的），然後進行基於redo log進行恢複，達到資料的一致性。

 

詳細的資訊可以參數https://www.percona.com/doc/percona-xtrabackup/LATEST/how_xtrabackup_works.html 我就不翻譯了。

 

我們還是簡單來看一下日常工作中具體的使用：

 

全備：

xtrabackup --backup --target-dir=/data/backup/base

 

可以先看到

 

 

在備份過程中,可以看到很多輸出顯示資料檔案被複製,以及記錄檔線程反覆掃描記錄檔和複製。

 

 

同樣的，它也輸出了當前的binlog filename和position，如果有gtid(同樣也會輸出) 可以用於搭建主從。最後一行一定會是你的lsn被copy的資訊。 這是因為每次啟動備份，都會記錄170429 12:54:10 >> log scanned up to (1676085)），然後開始拷貝檔案，一般來講資料庫越大拷貝檔案是要花費越長的時間，所以說這期間一般情況都會有新的操作，所以說所有檔案也可能記錄的並不是一個時間點的資料， 為瞭解決資料這個問題，XtraBackup 就會啟動一個後台進程來每秒1次的觀測mysql的交易記錄，直到備份結束。而且把交易記錄中的改變記錄下來。我們知道事物日誌是會重用的（redo log）,所以這個進程會把redolog寫到自己的記錄檔xtrabackup_log，這個後台監控進程會記錄所有的交易記錄的改變，用於保證資料一致性所。

 

增量備份：

 

當我們做過全量備份以後會在目錄下產生xtrabackup_checkpoints的檔案 這裡面記錄了lsn和備份方式，我們可以基於這次的全量做增量的備份。

 

$cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1676085

last_lsn = 1676085

compact = 0

recover_binlog_info = 0

 

xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

 

這個時候xtrabackup也是去開啟了xtrabackup_checkpoints檔案進行上一次備份的資訊查看。這個時候去查看增量備份的xtrabackup_checkpoints也記錄了這些資訊。

 

$cat xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1676085

to_lsn = 1676085

last_lsn = 1676085

compact = 0

recover_binlog_info = 0

 

這也意味著你可以在增量的備份上繼續增量的備份。

 

同樣的，xtrabackup也支援壓縮（--compress）、加密（--encrypt）、並行(--parallel)等操作，但是和mysqlbackup不同的是這個沒有同時的備份binlog,而mysqlbackup是備份了binlog的。

 

我們來類比一個恢複的過程深入的瞭解一下原理。

 

查看當前資料：

 

[email protected] 03:04:33>select from t;

+-------+

|  id  |

+-------+

|  1  |

+-------+

1 row in set (0.00 sec)

 

全量備份

$xtrabackup --backup --target-dir=/data/backup/base

 

類比增量資料

[email protected] 03:07:16>select from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

+-------+

2 rows in set (0.00 sec)

 

進行增量備份：

$xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

 

類比無備份操作：

[email protected] 03:09:42>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

|  3  |

+-------+

3 rows in set (0.00 sec)

 

類比誤操作：

[email protected] 03:09:45>truncate table t; Query OK, 0 rows affected (0.00 sec)

 

類比恢複操作：

 

step 1：找到誤操作的log position

[email protected] 03:10:19>show master logs;

[email protected] 03:10:47>show binlog events in ‘mysql-bin.000001‘;

1333

 

我們需要分別對全量、增量備份各做一次prepare操作。

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

 

增量

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base \ --incremental-dir=/data/backup/inc1

 

如果我們使用它內建的還原命令的時候就要先把data目錄給清空。不然就會報如下的錯誤

$innobackupex --copy-back /data/backup/base/

170429 15:37:19 innobackupex: Starting the copy-back operation

 

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

At the end of a successful copy-back run innobackupex prints "completed OK!".

 

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7) Original data directory /u01/my3307/data is not empty!

 

當然我們大多資料時候是不會在原來的執行個體上做操作的，都會把相應的備份在奇他的執行個體上進行恢複，然後再匯出匯入到誤操作的執行個體。這裡我們直接清掉目錄，然後再次運行，查看恢複後的資料：

[email protected] 03:41:56>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

+-------+

2 rows in set (0.00 sec)

 

同樣的被恢複的目錄裡會多出來兩個檔案，一個是xtrabackup_binlog_pos_innodb，一個是xtrabackup_info。在這兩個檔案中都可以看到你最後的log,pos。在info裡還可以看到lsn。我們基於這個pos再進行binlog的重演，恢複在binlog沒有被備份的資料。

 

1076

 

$mysqlbinlog mysql-bin.000001 --start-position=1076 --stop-position=1333 -vv >increment.sql

 

[email protected] 03:51:25>source /u01/my3307/log/increment.sql [email protected] 03:51:34>select * from t;

+-------+

|  id  |

+-------+

|  1  |

|  2  |

|  3  |

+-------+

3 rows in set (0.00 sec)

 

至此資料恢複完成。

 

• https://www.percona.com/doc/percona-xtrabackup/LATEST/backup_scenarios/full_backup.html 

 

五、直接複製整個資料庫目錄

 

MySQL還有一種非常簡單的備份方法，就是將MySQL中的資料庫檔案直接複製出來。這是最簡單，速度最快的方法。 不過在此之前，要先將伺服器停止，這樣才可以保證在複製期間資料庫的資料不會發生變化。如果在複製資料庫的過程中還有資料寫入，就會造成資料不一致。這種情況在開發環境可以，但是在生產環境中很難允許備份伺服器。

 

注意：這種方法不適用於InnoDB儲存引擎的表，而對於MyISAM儲存引擎的表很方便。同時，還原時MySQL的版本最好相同。 只所以提這一點是因為當有停機視窗時，搭建主從的時候，這個往往是最快的。

 

一般生產環境的備份都會用percona-xtrabackup或者mysqlbackup，結合自己的情況，選擇合適的備份策略，適時拿出來驗證備份的有效性。

 

Q&A  

 

Q1：用innobackupex備份MySQL5.5版本的資料，恢複的時候用backu5.6恢複，出現ibdata1找不到，這是什麼原因呢？

A1：跨版本恢複以後，恢複出來的資料的元資訊還是以前低版本的，在起動伺服器之前需要先執行一下mysql_upgrade。這其實也是一個升級的問題，我們當然可以採用innobackupex來做升級操作了，同樣的mysqlbackup也是可以的。因為有些時候我們的庫太大了，不適合匯入匯出來升級的操作。

 

Q2：你講的方法適合叢集嗎？如果不適合叢集的話，用什麼方法？

A2：當然，其實都是一樣的。原理只是對資料庫的資料進行複製，主從、PXC、MHA等多種環境都是有在用的。

 

Q3：生產環境，1T資料量用什麼備份好？

A3：我的建議是你可以使用mysqlbackup，比較下來，這個速度算是很快的，不過當你的資料量達到一個T時，也應該去考慮如何分庫分表，當做到這一點的時候，也是化整為零，可以把備份和正常的負載都分攤到多台伺服器上。

 

直播連結  

如需回聽直播，請戳：

https://m.qlchat.com/topic/260000366578461.htm?isGuide=Y

密碼：222

(轉)解鎖MySQL備份恢複的4種正確姿勢