DBA 必知的 MYSQL 備份與還原方法

標籤：nmf   adb   自動   編譯   方法   測試   最大   cti   hwm   

一mysqldump 備份結合 binlog 日誌恢複

說明：MySQL 備份一般採取全庫備份加記錄備份的方式，例如每天執行一次全備份，每小時執行一次二進位記錄備份。這樣在 MySQL 故障後可以使用全備份和記錄備份將資料恢複到最後一個二進位記錄備份前的任意位置或時間。

Binlog 功能預設是關閉的，沒有開啟。

查看 binlog，用 mysqlbinlog -v mysql-bin.000001

主從同步

恢複資料庫

開啟 binary log 功能：通過編輯 my.cnf 中的 log-bin 選項可以開啟二進位日誌；形式如右：log-bin[=DIR/[filename]] ，注釋：每次重啟 mysql 服務或運行 mysql> flush logs;都會產生一個新的二進位記錄檔，這些記錄檔的 number 會不斷地遞增，除了產生上述的檔案外還會產生一個名為 filename.index 的檔案。這個檔案中儲存所有二進位記錄檔的清單又稱為二進位檔案的索引。

說明：bin-log 因為是二進位檔案，不能通過檔案內容查看命令直接開啟查看，mysql 提供兩種方式查看方式。

查看指定的二進位日誌中的事件：

該命令還包含其他選項以便靈活查看：

總結：上述方式可以查看到伺服器上存在的二進位記錄檔及檔案中的事件，但是想查看到檔案中具體的內容並應於恢複情境還得藉助 mysqlbinlog 這個工具。

文法格式：mysqlbinlog [options] log_file ...

輸出內容會因記錄檔的格式以及 mysqlbinlog 工具使用的選項不同而略不同。

mysqlbinlog 的可用選項可參考 man 手冊。

說明：無論是本地二進位記錄檔還是遠程伺服器上的二進位記錄檔，無論是行模式、語句模式還是混合模式的二進位記錄檔，被 mysqlbinlog 工具解析後都可直接應用與MySQL Server 進行基於時間點、位置或資料庫的恢複。

下面我們就來示範如何使用 binlog 恢複之前刪除資料（id=2那條記錄）

注意：在實際生產環境中，如果遇到需要恢複資料庫的情況，不要讓使用者能訪問到資料庫，以避免新的資料插入進來，以及在主從的環境下，關閉主從。

delete from bdqn.test where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog -v mysql-bin.000002

顯示結果如下：（複製的日誌）

# at 219

#170316 21:52:28 server id 1 end_log_pos 287 CRC32 0xff83a85b Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1489672348/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

[email protected]@session.character_set_client=33,@@session.collation_connection=33,

@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 287

#170316 21:52:28 server id 1 end_log_pos 337 CRC32 0x343e7343 Table_map: `bdqn`.`test` mapped to number 108

# at 337

#170316 21:52:28 server id 1 end_log_pos 382 CRC32 0xa3d1ce0d Delete_rows: table id 108 flags: STMT_END_F

BINLOG ‘

nJjKWBMBAAAAMgAAAFEBAAAAAGwAAAAAAAEABGJkcW4ABHRlc3QAAgMPAjwAAkNzPjQ

=nJjKWCABAAAALQAAAH4BAAAAAGwAAAAAAAEAAgAC//wCAAAABGxpc2kNztGj

‘/*!*/;

### DELETE FROM `bdqn`.`test`

### WHERE

### @1=2

### @2=‘lisi‘

# at 382

#170316 21:52:28 server id 1 end_log_pos 413 CRC32 0x257e7073 Xid = 10

COMMIT/*!*/;

說明：可以從可以看出來 delete 時間發生 position 是 287，事件結束 position 是413。

②恢複流程：直接用 bin-log 日誌將資料庫恢複到刪除位元置 287 前,然後跳過故障點,再進行恢複下面所有的操作,命令如下

由於之前沒有做過全庫備份，所以要使用所有 binlog 日誌恢複，所以生產環境中需要很長時間恢複，匯出相關 binlog 檔案。

③刪除 bdqn 資料庫（刪除 bdqn 和恢複資料之前，要關閉 binlog 功能）

   

作用：mysqldump 是 mysql 內建的備份和資料轉移的工具。

它只產生 sql 語句（即 sql 命令）封裝在檔案，而不是真實的資料。

Mysqldump 是邏輯備份，不是物理備份，備份的是 SQL 陳述式，而不是資料檔案。

Mysqldump 適用於小型資料庫，資料容量一般是在幾個 G 大小，當資料量很大的情況下，不建議使用 mysqldump。

匯出對象：可以針對單個表、多個表、單個資料庫、多個資料庫、所有資料庫。

#mysqldump [選項] 庫名 [表名1] [表名2] … > /備份路徑/備份檔案名

//匯出指定資料庫的單個或多個表

#mysqldump [選項] --databases 庫名1 [庫名2] … > /備份路徑/備份檔案名

//匯出指定的資料庫或多個資料庫

#mysqldump [選項] --all-databases > /備份路徑/備份檔案名

//匯出所有的資料庫

#mysqldump -uroot -p123456 --flush-logs bdqn > /opt/bdqn.sql

//匯出資料庫bdqn,其中“—flush-logs”這個選項是完整備份完畢後開啟一個新的binlog

#mysql -uroot -p123456 bdqn < /opt/bdqn.sql

//從備份檔案匯入資料庫bdqn

下面用一個具體的實驗說明用mysqldump實現全庫備份+binlog的資料恢複

  

4）開始全庫備份（注意：全庫備份不會備份 binlog 記錄檔）

 

5）備份 mysqldump 全庫備份之前的所有的 binlog 記錄檔（注意：真是生產環境中可能不止一個 binlog 檔案）

6）因為全庫備份之前的 binlog 已經備份了，現在就刪除它們（即新產生的 binlog 之前的所有的 binlog 刪除）

①使用 mysqldump 的備份進行全庫恢複（即恢複到全部備份時候的所有資料）

②分析新開啟的 binlog 記錄檔（我這裡是 mysql-bin.000002）裡面誤操作的事件的起始位置和終止位置，只要跳過這一段事件即可

複製的日誌：

# at 219

#170318 21:14:42 server id 1 end_log_pos 291 CRC32 0xddbf8eff Query thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1489842882/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.

collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 291

#170318 21:14:42 server id 1 end_log_pos 339 CRC32 0x4a9ec8f2 Table_map: `bdqn`.`it` mapped to number 108

# at 339

#170318 21:14:42 server id 1 end_log_pos 388 CRC32 0x2e8a3da8 Delete_rows: table id 108 flags: STMT_END_F

BINLOG ‘

wjLNWBMBAAAAMAAAAFMBAAAAAGwAAAAAAAEABGJkcW4AAml0AAIDDwI8AALyyJ5K

wjLNWCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wBAAAACHpoYW5nc2FuqD2KLg

==‘/*!*/;

### DELETE FROM `bdqn`.`it`

### WHERE

### @1=1

### @2=‘zhangsan‘

# at 388

#170318 21:14:42 server id 1 end_log_pos 419 CRC32 0xa1c06a4f Xid = 43

COMMIT/*!*/;

③開始使用全庫備份後的增量備份的 binlog 記錄檔備份檔案進行對全庫恢複後的增量資料的恢複

總結：從顯示可以看出資料恢複到正常狀態，實際生產環境中 mysql 資料庫的備份是周期性重複操作，所有通常是要編寫指令碼實現，通過 crond 計劃任務周期性執行備份指令碼。

周日淩晨1點全庫備份；

周一到周六淩晨每隔4個小時增量備份一次

設定 crontab 任務，每天執行備份指令碼：

2）編寫 mysqlfullbackup.sh 指令碼（即 mysql 全庫備份指令碼）

3）編寫 mysqldailybackup.sh 指令碼（即 mysql 增量備份指令碼）

發送郵件測試：

 

安裝 libmysqlclient.so.18

  

再次測試：

 

二使用 xtrabackup 進行 MySQL 資料庫備份

前面介紹mysqldump 備份方式是採用邏輯備份，其最大的缺陷就是備份和恢複速度都慢，對於一個小於 50G 的資料庫而言，這個速度還是能接受的，但如果資料庫非常大，那再使用 mysqldump 備份就不太適合了。

這時就需要一種好用又高效的工具，xtrabackup就是其中一款，號稱免費版的 InnoDB HotBackup。

目前主流的有兩個工具可以實現物理熱備：ibbackup 和 xtrabackup；

ibbackup 是商業軟體，需要授權，非常昂貴。

xtrabackup 功能比 ibbackup 還要強大，但卻是開源的。因此我們這裡就來介紹 xtrabackup 的使用。

xtrabackup：專用於備份 InnoDB 和 XtraDB 引擎的資料；

innobackupex：這是一個 perl 指令碼，在執行過程中會調用 xtrabackup 命令，這樣用該命令即可以實現備份 InnoDB，也可以備份 MyISAM 引擎的對象。

(1)備份過程快速、可靠；

(2)備份過程不會打斷正在執行的事務；

(3)能夠基於壓縮等功能節約磁碟空間和流量；

(4)自動實現備份檢驗；

(5)還原速度快。

官方連結地址：

http://www.percona.com/software/percona-xtrabackup；

可以下載源碼編譯安裝，也可以下載適合的 RPM 包或使用 yum 進行安裝或者下載二進位源碼包。

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

Xtrabackup 中主要包含兩個工具：

xtrabackup：是用於熱備份 innodb,xtradb 表中資料的工具，支援線上熱備份，可以在不加鎖的情況下備份 Innodb 資料表，不過此工具不能操作 Myisam 引擎表；

innobackupex：是將 xtrabackup 進行封裝的 perl 指令碼，能同時處理 Innodb 和 Myisam，但在處理 Myisam 時需要加一個讀鎖。

由於操作 Myisam 時需要加讀鎖，這會堵塞線上服務的寫操作，而 Innodb 沒有這樣的限制，所以資料庫中 Innodb 表類型所佔的比例越大，則越有利。

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

至此就完成了xtrabackup的安裝，下面就可以啟動備份了。

 

2）建立備份用的目錄（full：全備存放的目錄；inc：增量備份存放的目錄）

   

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

還可以加 —database 選項指定要備份的資料庫，這裡指定的資料庫只對 MyISAM 表有效，對於 InnoDB 資料來說都是全備（所有資料庫中的 InnoDB 資料都進行了備份，不是只備份指定的資料庫，恢複時也一樣）。

 

下面我們大體看一下這幾個檔案：

  

說明：xtrabackup_binlog_pos_innodb 和 xtrabackup_binary 在這個版本裡面沒有了，因為版本較新，這兩個檔案在新版給刪除了，只存在於老版本。

    

我這裡直接使用刪除資料目錄檔案來類比損壞。

一般情況下，在備份完成後，資料尚且不能用於恢複操作，因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務。因此，此時資料檔案仍處理不一致狀態。“準備”的主要作用正是通過復原未提交的事務及同步已經提交的事務至資料檔案也使得資料檔案處於一致性狀態。

在準備（prepare）過程結束後，InnoDB 表資料已經前滾到整個備份結束的點，而不是復原到 xtrabackup 剛開始時的點。

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

--apply-log 指明是將日誌應用到資料檔案上，完成之後將備份檔案中的資料恢複到資料庫中：

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

說明：innobackupex 命令的 --copy-back 選項用於執行恢複操作，其通過複製所有資料相關的檔案至 mysql 伺服器 DATADIR 目錄中來執行恢複過程。innobackupex 通過backup-my.cnf 來擷取 DATADIR 目錄的相關資訊。

⑧正式開始還原增量備份

⑨重新啟動二進位日誌並驗證還原資料

附：Xtrabackup 的“流”及“備份壓縮”功能

作用：Xtrabackup 對備份的資料檔案支援“流”功能，即可以將備份的資料通過 STDOUT 傳輸給 tar 程式進行歸檔，而不是預設的直接儲存至某備份目錄中。

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

看不清的可以看下面複製粘貼的命令：

# innobackupex --user=root --password="123456"--stream=tar /opt/mysqlbackup/full/ | gzip >/opt/mysqlbackup/full/full_`date+%F_%H%M%S`.tar.gz

（再補充一句，現實生產環境中，基本上都要用流與備份壓縮功能，因為這樣可以很大程度上節省空間的）

連結：http://zpf666.blog.51cto.com/11248677/1913451

DBA 必知的 MYSQL 備份與還原方法