MySQL備份與恢復簡介

MySQL備份與恢復

數 據庫分邏輯備份\物理備份；物理備份又分冷備和熱備

A.直接複製資料檔案到安全地方進行儲存

B.使用MYSQLHOSTCOPY備分資料

C.使用MYSQLDUMP備份資料

D.使用MYSQL的同步複製，實現資料實時資料同步備份

1） 邏輯備份

常用的邏輯備份主要就是兩種：

l  一種是將資料生成為可以完全重現當前資料庫中的資料的insert語句

l  一種是將資料透過邏輯備份軟體，將資料庫表的資料以特定分隔符進行分割後記錄在文字中。

對於第一種生成insert語句來說我們可以直接使用mysql自帶的工具mysqldump來完成。這種方式不好之處在於可能導致資料的不一致，或是不完整。

解決辦法：一個是透過在資料庫系統中加入寫入鎖，只提供資料庫的查詢服務；第二種是對於支援事務的儲存引擎來說，INNODB BDB可以透過將整個備份過程控制在一個事務中，來達到備份資料的一致性和完整性；並且可以不用影響到資料庫的正常執行。

如何保證資料備份的一致性？

要想保證資料的一致性可以透過以下兩種方法做到：

第一、同一時刻取出所有資料

對於事務支援的儲存引擎，如Innodb或者BDB 等，可以透過控制將整個備份過程在同一個事務中，使用“--single-transaction”選項。

例如：

# mysqldump --single-transaction test > test_backup.sql

 

第二、資料庫中的資料處於靜止狀態

透過鎖表引數

--lock-tables 每次鎖定一個資料庫的表，此引數是預設為true(見上面備份內容例項)；

--lock-all-tables 一次鎖定所有的表，適用於dump的表分別處於各個不同的資料庫中的情況

恢復方式則是透過mysql<backup.sql直接執行的。

例如：mysql -uroot -p mydb1 <bkmydb1.dt

第二種直接生成資料格式。佔用的空間小，資料格式清晰。可是沒有資料庫結構的指令碼。不容易控制

實現方式：透過select******* to outfile from***命令來實現。

例如：mysql> select * into outfile '/tmp/tb1.txt'

    -> fields terminated by ','

    -> optionally enclosed by '"'

    -> lines terminated by '\n' --預設

    -> from tb1 limit 50;

恢復方式就是透過，load data infile和mysqlimport命令來做。

這一過程相當的複雜需要實時的進行恢復測試，保證備份資料是可用的。

2） 物理備份

資料庫的物理備份，主要的物件是資料庫的物理資料檔案，日誌檔案以及配置檔案等。物理資料檔案有哪些呢？

一是日誌檔案6大類：錯誤日誌error Log、二進位制日誌binary Log、更新日誌 update log（5.0後被bin log代替）、查詢日誌 query log、慢查詢日誌 slow query log、innodb的redo日誌。

二是資料檔案：對於myisam來說的話，.frm表結構資訊、.myd資料資訊、.myi資料的索引資訊。對於Innodb來說的話.ibd檔案（獨享表空間）和.ibdata（共享表空間）檔案。

三是replication檔案：master.info儲存在slave端的資料目錄下，存放了slave和master的相關資訊，relay log和 relay log index主要儲存了I/O程式從Master端讀取到的binary log資訊，然後由slave端的SQL執行緒從該binary log中讀取解析過的日誌資訊，轉化成master所能執行的query語句。index則是存放binarylog的路徑也就是目錄檔案。

四是系統檔案：如my.cnf、pid檔案是mysqld應用程式中的一個程式檔案存放自己的程式id，還有就是socket檔案它只有在linux下才有的，可以不透過tcp/ip網路協議直接連線mysql

l  如果是做冷備的話，直接複製所有的資料檔案和日誌檔案到備份集存放的地方，

l  熱備的方法針對不多的資料庫有不同的方案

對於MYISAM 儲存引擎來說 ，做法就是給資料庫表加鎖來阻止寫操作，可以直接複製物理檔案，或者也可以透過mysql專門的mysqlhotcopy（原理就是現鎖住表，然後進行操作）程式來完成相應的備份任務。

flush tables with read lock

cp -R test /tmp/backup/test

unlock tables;

innodb 資料庫引擎來說 ，有一款商業軟體ibbackup，線上物理備份功能。還有一款開源的工具xtrabackup；

如果在備份過程中，把INNODB資料檔案備份完成後，會鎖住整個庫，並開始複製MYISAM等非事務引擎的資料和.frm；所以如果你擁有比較多的MYISAM表，鎖庫的時候會持續很長。如果是在主庫上執行，千萬注意。

同樣也只能透過xtrabackup進行增量的備份，其實這一工具只是備份innobd的日誌資訊。

4 xtrabackup/ibbackup

xtrabackup --backup --datadir=/var/lib/mysql/ --target-

dir=/data/backups/mysql/

xtrabackup --backup  --defaults-file=/etc/my.cnf --target-

dir=/data/backups/mysql/

物理備份就是備份資料檔案了，比較形象點就是cp下資料檔案，但真正備份的時候自然不是的cp這麼簡單。

（1）使用 xtrabackup 工具

是一個用來備份 MySQL資料庫的開源工具。

主要特點：

<1>. 線上熱備份。可以備份innodb和myisam。innodb主要應用recovery原理。myisam直接複製檔案。

<2>. 支援流備份。可以備份到disk，tape和remote host。–stream=tar ./ | ssh user@remotehost cat “>” /backup/dir/

<3>. 支援增量備份。可以利用lsn和基礎備份目錄來進行增量備份。

<4>. 支援記錄slave上的master log和master position資訊。

<5>. 支援多個程式同時熱備份，xtrabackup的穩定性還是挺好的。

（2）LVM

特點：熱備、支援所有基於本地磁碟的儲存引擎、快速備份、低開銷、容易保持完整性、快速恢復等。

（3）cp + tar

使用直接複製資料庫檔案的方式進行打包備份，需要注意的是執行步驟：鎖表、備份、解表。

恢復也很簡單，直接複製到之前的資料庫檔案的存放目錄即可。

注意：對於Innodb引擎的表來說，還需要備份日誌檔案，即ib_logfile*檔案。因為當Innodb表損壞時，就可以依靠這些日誌檔案來恢復。

（4）mysqlhotcopy

mysqlhotcopy是一個perl程式，是lock tables、flush tables 和cp或scp來快速備份資料庫。

它是備份資料庫或單個表的最快的途徑，但它只能執行在資料庫檔案（包括資料表檔案、資料檔案、索引檔案）所在的機器上。

mysqlhotcopy 只能用於備份MyISAM 。

（5）使用mysql主從複製

mysql的複製是指將主資料庫的DDL和DML操作透過二進位制檔案（bin-log）傳送到從伺服器上，然後在從伺服器上對這些日誌做重新執行的操作，從而使得從伺服器和主伺服器保持資料的同步。

3） mysql 的備份方式

1.mysqldump

效率比較低，備份和還原的速度都很慢,任何資料插入和更新操作都會被掛起

2.mysqlhotcopy

mysqlhotcopy 是專門針對myisam 資料表進行備份,備份的過程中，任何資料插入和更新操作都會被掛起

3.準備一臺從伺服器,專門做備份（master-slave方式）

4.xtrabackup 是 percona 的一個開源專案，可熱備份innodb ,XtraDB,MyISAM(會鎖表)

 

Xtrabackup有兩個主要的工具：xtrabackup、innobackupex

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

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

?xtra備份原理

記住開始時候的LSN號，然後開始拷備檔案，同時執行一個後臺程式監視重做日誌，並將變化拷備下來到xtrabackup_logfile。

innobackupex可以備份myisam表和frm檔案。當xtrabackup結束後，執行flush tables with read lock，避免資料更改，然後重新整理所有myisam表到磁碟。拷備結束後，釋放鎖。

4） mysqlbinlog

mysqlbinlog 也是一個恢復工具，是基於時間點來處理二進位制檔案的

?備份：直接拷備

?還原：

ü時間點還原：mysqlbinlog --stop-date="2005-04-20 9:59:59"

/var/log/mysql/bin.123456 | mysql -u root –pmypwd

mysqlbinlog --start-date="2005-04-20 10:01:00"

/var/log/mysql/bin.123456| mysql -u root –pmypwd

?日誌點還原：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd \

LVM是 Logical Volume Manager（邏輯卷管理）的簡寫，它是Linux環境下對磁碟分割槽進行管理的一種機制。

LSN定義：

5） 日誌序號

日誌序號 (LSN) 標識特定日誌檔案記錄在日誌檔案中的位置。

LSN 由 DB2? 產品中的許多元件用來維護資料庫一致性和完整性。除其他作用外，LSN 還對於分割槽資料庫環境中的落實和回滾操作、崩潰和前滾恢復以及資料庫操作同步起非常重要的作用。

日誌檔案中 LSN 的增長率與資料庫活動直接相關聯。也就是說，隨著事務發生並且條目被寫入日誌檔案，LSN 會不斷增大。資料庫中的活動越多，LSN 增長得越快。

日誌序號的上限

在 DB2 V9.5 和較早版本中，日誌序號 (LSN) 是 6 位元組數字。從 FP3 開始，LSN 的範圍在 0x0000 0000 0000（首次建立資料庫時）到 0xFFFF 0000 0000（約 256 太位元組）之間。在 FP3 之前，上限為 0xFFFF FFFF FFFF。隨著記錄被新增到日誌檔案中，LSN 在資料庫生命期不斷增長。