MySQL備份與恢復

1.1.         物理備份方法

 

1.1.1.        InnoDB 物理冷備份

備份過程：

a)          在複製操作期間停止伺服器。

b)          驗證伺服器是否正常關閉，沒有出錯。

c)          生成每個元件的副本：

—    每個 InnoDB 表一個 .frm 檔案

—    表空間檔案：系統表空間、基於每個表的表空間

—    InnoDB 日誌檔案

—    my.cnf 檔案

d)          重新啟動伺服器。

 

進行完整 InnoDB 備份（所有 InnoDB 表的備份）的物理備份操作基於生成 InnoDB 用於管理表空間的所有檔案的完整副本。所有資料庫中的所有 InnoDB 表必須一起備份，因為 InnoDB 會在系統表空間中集中維護某些資訊。

要使用物理備份恢復 InnoDB 表，請停止伺服器，替換其副本在備份過程中生成的所有元件，然後重新啟動伺服器。

注：必須將表空間檔案作為一個組進行復制。也就是說，需要替換伺服器上的所有現存的表空間檔案。不能使用物理備份將一個表空間新增到另一個表空間。

 

1.1.2.        MyISAM 和 ARCHIVE 物理備份

備份過程：

a)          在伺服器執行期間，鎖定要複製的表：

mysql> USE mysql

mysql> FLUSH TABLES users WITH READ LOCK;

要生成 MyISAM 或 ARCHIVE 表，需複製 MySQL 用於代表該表的檔案。對於 MyISAM ，這些檔案是 .frm 、 .MYD 和 .MYI 檔案。對於 ARCHIVE 表，這些檔案是 .frm 和 .ARZ 檔案。在此複製操作過程中，其他程式（包括伺服器）不能使用該表。為了避免伺服器互動問題，要在複製操作過程中停止伺服器。

注：鎖定表而不關閉伺服器的做法在 Linux 系統上有效。在 Windows 上，檔案鎖定行為會導致可能無法複製被伺服器鎖定的表的表檔案。在這種情況下，需要停止伺服器後再複製表檔案。

b)          執行檔案系統複製。

c)          啟動新的二進位制日誌檔案： FLUSH LOGS;

要啟動新的二進位制日誌檔案，可使用 FLUSH LOGS （在 UNLOCK TABLES 之前）。或者， SHOW MASTER STATUS 返回當前二進位制日誌檔案的名稱和位置。新二進位制日誌檔案包含在備份之後更改了資料的所有語句（以及所有後續的二進位制日誌檔案）。

d)          在檔案系統複製後解除鎖定： UNLOCK TABLES;

要從物理備份中恢復 MyISAM 或 ARCHIVE 表，應停止伺服器，將備份表檔案複製到相應的資料庫目錄中，然後重新啟動伺服器。

 

1.1.3.        使用 LVM 快照備份

在以下情況下，使用 LVM 快照執行物理備份：

l   主機支援 LVM ；例如， Linux 支援 LVM2 ；

l   包含 MySQL 資料目錄的檔案系統在邏輯捲上；

備份過程：

a)          生成包含 MySQL 資料目錄的邏輯卷的快照。在備份非 InnoDB 表時，使用 FLUSH TABLES WITH READ LOCK 。

b)          從快照執行物理備份。

c)          刪除快照。

在支援 LVM 的系統（如 Linux ）上，可以建立要包含 MySQL 資料目錄的邏輯卷。可以建立該卷的快照，該快照的行為就像是邏輯卷的即時副本。 LVM 使用稱為“寫入時複製” (copy-on-write) 的機制建立最初不含資料的快照。在從新建立的快照讀取檔案時， LVM 會從原始卷讀取這些檔案。當原始捲髮生變化時， LVM 會在原始捲上的資料發生變化之前，立即將其複製到快照，因此，在生成快照以來發生變化的任何資料都以其原始形式儲存在快照中。這樣做的結果是，當從快照讀取檔案時，將獲得在建立快照時存在的資料版本。因為快照幾乎是即時的，因此可以假定在生成快照過程中底層資料沒有發生任何變化。這使得快照對於在不關閉伺服器的情況下備份 InnoDB 資料庫非常有用。要建立快照，可使用以下語法：

lvcreate -s -n <snapshot-name> -L <size> <original-volume>

選項 -s 指示 lvcreate 建立快照，其他選項指定新快照的名稱和大小以及原始卷的位置。

例如，假定有一個卷組 VG_MYSQL 和一個邏輯卷 lv_datadir ：

lvcreate -s -n lv_datadirbackup -L 2G /dev/VG_MYSQL/lv_datadir

前一條語句建立快照 lv_datadirbackup ，其保留大小為 2 GB 。如果只是短時間需要該快照，則保留大小可以比原始卷的大小少很多，因為快照的儲存僅包含在原始卷中發生更改的資料塊。例如，如果要使用快照執行備份，則保留大小僅儲存在執行備份以及刪除快照的時間內所做的更改。

可以像掛載標準卷一樣掛載快照。掛載了快照後，就像處理其他任何物理備份一樣，從該卷執行物理備份（例如，透過使用 tar 或 cp ）。從快照備份時，資料庫在備份過程中不能有更改。您肯定會獲得與備份時一樣的一致資料檔案版本，無需停止伺服器。隨著時間的推移，快照的空間要求通常會增長到原始卷的大小。此外，對原始捲上資料塊的每項初始資料更改將導致兩次向卷組寫入資料：請求的更改和對快照的寫入時複製。這可能會影響快照保留期間的效能。由於以上原因，應在執行了備份之後儘快刪除快照。要刪除由前一條語句建立的快照，可使用以下語句：

lvremove VG_MYSQL/lv_datadirbackup

 

1.1.4.        物理備份的可移植性

l   可在 MySQL 伺服器之間複製二進位制資料庫。當將一臺計算機上生成的二進位制備份拿到具有不同體系結構的另一臺計算機上時，二進位制可移植性會很有用。例如，使用二進位制備份是將資料庫在 MySQL 伺服器之間複製資料庫的一種方法。

l   InnoDB 資料庫的所有表空間和日誌檔案都可直接複製。源系統與目標系統上的資料庫目錄名稱必須相同。對於 InnoDB ，利用二進位制可移植性可在不同計算機上的不同 MySQL 伺服器之間直接複製表空間，其他 MySQL 伺服器可以訪問該表空間。

l   MyISAM 、 ARCHIVE ，單個表的所有檔案都可直接複製。對於 MyISAM 和 ARCHIVE ，二進位制可移植性意味著可在不同計算機上的 MySQL 伺服器之間直接複製 MyISAM 或 ARCHIVE 表的檔案，其他 MySQL 伺服器可以訪問該表。

l   Windows 相容性，在 Windows 系統上， MySQL 伺服器在內部儲存小寫的資料庫和表名稱。對於區分大小寫的檔案系統，可使用選項檔案語句：

lower_case_table_names=1

 

1.2.         備份日誌和狀態檔案

Ø   二進位制日誌檔案儲存備份完成後所做的更新；

Ø   伺服器使用的選項檔案（ my.cnf 和 my.ini 檔案）包含在系統崩潰後必須恢復的配置資訊。

Ø   複製檔案：複製 slave 建立一個包含連線到 master 所需資訊的 master.info 檔案，以及一個指示當前的中繼日誌處理進度的 relay-log.info 檔案。

Ø   複製 slave 資料檔案：複製 slave 建立用於處理 LOAD DATA INFILE 語句的資料檔案。這些檔案位於 slave_load_tmpdir 系統變數指定的目錄中，在伺服器啟動時使用 --slave-load-tmpdir 選項可設定該變數。如果不設定 slave_load_tmpdir ，則應用 tmpdir 系統變數的值。要保護複製 slave 資料，需要備份以 SQL_LOAD- 開頭的檔案。

Ø   MySQL 二進位制檔案和庫

Ø   策略：

—    靜態檔案：使用常規系統工具在伺服器執行的情況下備份

—    動態檔案：使用常規系統工具在伺服器停止的情況下備份

 

1.3.         將複製用作備份的輔助工具

Ø   master 可以繼續執行。

如果 MySQL 伺服器在複製設定中用作 master ，則可使用 slave 生成備份，而不備份 master 。透過使用 slave 進行備份， master 不會被中斷，備份過程不會對 master 增加處理負荷，也不要求增加硬碟空間或進行額外處理。

Ø   可以停止 slave 以生成備份

關閉 mysqld 程式，或者發出 STOP SLAVE SQL_THREAD 語句以停止伺服器處理其從 master 收到的更新。在後一種情況下，必須重新整理表以強制掛起對磁碟的更改。

Ø   備份 slave 的資料庫

生成 slave 資料庫的備份。可用的方法取決於伺服器處於停止狀態還是仍在執行。例如，如果伺服器已停止，則不能使用連線到伺服器的 mysqldump 或 mysqlhotcopy 之類的工具。停止的伺服器可以使用系統工具； slave 執行緒停止，但仍在執行的伺服器可以使用任何 MySQL 工具。

Ø   啟動伺服器：啟動停止的伺服器、 START SLAVE SQL_THREAD

重新啟動停止的伺服器。如果伺服器仍在執行，則可透過發出 START SLAVE SQL_THREAD 語句重新啟動 SQL 執行緒。

 

1.4.         備份方法比較

                                              快照 ：並非所有引擎都以相同方式處理快照。例如， InnoDB 表不需要 FLUSH TABLES WITH READ LOCK 就能啟動快照，但 MyISAM 表卻需要。

 

1.5.         備份策略

流程圖表示可用於確定備份策略的決策流程。可能在此流程中提出的問題包括：

n   我們的系統能否承受長時間停機（停機時間）？

n   有多少資料要備份？

n   使用哪些儲存引擎來儲存資料（ InnoDB 、 MyISAM 或兩者）？

 

1.6.         mysqlbinlog

a)          確定在備份生成後寫入哪些日誌

在恢復了二進位制備份檔案或重新裝入了文字備份檔案後，透過重新處理在伺服器的二進位制日誌中記錄的資料更改，完成恢復操作。為此，必須確定在生成備份後寫入哪些日誌。然後，需要使用 mysqlbinlog 程式將這些二進位制日誌的內容轉換成文字 SQL 語句，以便使用 mysql 處理結果語句。

b)          使用 mysqlbinlog 一個命令處理所有 binlog

mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

c)          恢復部分 Binlog

--start-datetime / --stop-datetime

 --start-position / --stop-position

mysqlbinlog --start-position=23456 binlog.000004 | mysql

如果指定的二進位制日誌檔案在備份過程中正在寫入，則必須從中僅提取在備份後寫入的分，再加上之後寫入的所有日誌檔案。此外，如果意外刪除了表或資料庫（或者發生了他資料損壞情況），則可使用二進位制日誌中記錄的增量活動恢復備份。為了避免重複執行問題語句，可以透過獲取到該點之前的二進位制日誌檔案，執行問題語句之前的所有語句。為了處理部分檔案提取， mysqlbinlog 支援一些允許指定時間的選項，或者允許指定日誌中開始提取日誌內容的位置的選項：

--start-datetime 選項：指定開始提取的日期和時間，其中選項引數採用 DATETIME 格式。但是 --start-datetime 的粒度僅有一秒，因此可能不夠精確，不能指定開始的確切位置。

--start-position 選項：可用於指定在給定的日誌位置開始提取。

另外還有一些對應的 --stop-datetime 和 --stop-position 選項，用於指定停止提取日誌內容的位置。

如果您不確定日誌檔案中對應於處理開始點的時間戳或位置，可使用 mysqlbinlog （不帶 mysql ）顯示日誌內容進行檢查：

shell> mysqlbinlog file_name | more