mysql資料備份3種方案

本文源出處：http://www.lustlost.com/?p=101

mysql按照備份恢複方式分為邏輯備份和物理備份

邏輯備份是備份sql語句，在恢複的時候執行備份的sql語句實現資料庫資料的重現

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

這2種備份各有優劣，一般來說，物理備份恢複速度比較快，佔用空間比較大，邏輯備份速度比較慢，佔用空間比較小

下面介紹以下3種常用的備案方法

mysqldump工具備份

mysqldump由於是mysql內建的備份工具，所以也是最常用的mysql資料庫的備份工具。支援基於InnoDB的熱備份。但由於是邏輯備份，所以速度不是很快，適合備份資料量比較小的情境。

mysqldump完全備份+二進位日誌 —>實現時間點恢複

溫備:

在使用MyISAM引擎中，只能使用溫備份，這時候要防止資料的寫入，所以先加上讀鎖

這時候可以進入資料庫手動加讀鎖。這樣比較麻煩，在mysqldump工具中直接有一個加鎖的選項

mysqldump --databases mydatabase --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql

如果是針對某張表備份，只要在資料庫名稱後面加上表名稱就行了

這裡注意，要實現時間點的恢複，加上--flush-logs選項，在使用備份檔案恢複後，然後再基於二進位日誌進行時間點的恢複

時間點的恢複方法

mysqlbinlog mysql-bin.000000x > /tmp/PointTime.sql

然後用mysql命令匯入這個sql指令碼就行了

熱備：

如果使用的是InnoDB引擎，就不必進行對資料庫加鎖的操作，加一個選項既可以進行熱備份：--single-transaction

mysqldump --databases mydb --single-transaction  --flush-logs --master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql

注意點

恢複的時刻關閉二進位日誌

mysql>set sql_log_bin=0;

因為這是基於邏輯備份方式，在恢複日誌時會執行sql語句插入資料，而恢複時候插入資料的日誌沒有意義。

基於LVM快照備份

在物理備份中 ，有基於檔案系統的物理備份LVM的快照），也可以直接用tar之類的命令打包。但這些只能進行冷備份

不同的儲存引擎能備份的層級也不一樣，MyISAM能備份到表層級，而InnoDB不開啟每表一檔案的話就只能備份整個資料庫。

下面就介紹下使用LVM的快照功能進行備份

為了安全 首先在資料庫上施加讀鎖

mysql>FLUSH TABLES WITH READ LOCK;

重新整理一下二進位日誌，便於做時間點恢複

mysql>FLUSH LOGS；

然後建立快照卷

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

最後進入資料庫釋放讀鎖

UNLOCK TABLES;

掛載快照卷進行備份

mount –r /dev/myvg/data-snap /mnt/snap

然後對/mnt/snap下的檔案進行打包備份

還原的時候，關閉mysqld，然後備份二進位日誌後將原來備份的檔案還原進去，然後通過二進位日誌還原到出錯的時間點通過二進位還原時間點的時候不要忘了暫時關閉二進位日誌）

使用percona提供的xtrabackup推薦）

支援InnoDB的物理熱備份，支援完全備份，增量備份，而且速度非常快，而且支援InnoDB引擎的資料在不同資料庫遷移

為了讓xtrabackup支援更多的功能擴充，配置InnoDB每表一個檔案的功能

在my.cnf的mysqld中加入此項： innodb_file_per_table=1

此項不啟用將不支援備份單獨的表

但如果之前沒有啟用這個選項，要實現單表一檔案的話，可以用mysqldump匯出資料，然後啟用該選項，恢複回去後就是單表一檔案了

首先下載xtrabackup

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

可以直接下載rpm包安裝即可

xtrabackup有完全備份，增量備份和部份備份保證開啟innodb_file_per_table和innodb_expand_import）

完全備份整個資料庫

innobackupex --user=root --password=123456 /tmp/backup

此時會在/tmp/backup目錄下產生以時間為名的檔案夾，裡面是備份檔案

在這裡，備份的資料還不能直接用來還原，因為備份資料中會含有尚未提交的事務或者未同步到資料檔案中的事物。這裡需要用prepare復原事物使資料檔案處於一致性。

innobackupex --apply-log /tmp/backup/dir

處理完成後才能用來還原資料，用此命令還原

innobackupex --copy-back /tmp/backup/dir

要實現還原時間點，還是需要使用二進位日誌

增量備份

增量備份支援Innodb,對於MyISAM只能完全備份

innobackupex –incremental /tmp/backup/incremental --incremental-basedir=/tmp/backup/dir

在進行一次增量備份--incremental-basedir要指向上一次增量備份的目錄

如果要進行還原，先進行prepare處理

這裡處理的方式，將備份合并

innobackupex --apply-log --redo-only /tmp/backup/dir

innobackupex --apply-log --redo-only /tmp/backup/dir --incremental-dir=/tmp/backup/incremental

最後使用完全備份的那個備份還原

至於差異備份，只要每次將basedir指向完全備份檔案夾就行了

最後再廢話一句：要實現還原時間點，是需要使用二進位日誌的，所以備份好二進位日誌至關重要。除非在恢復點和上一次備份時間點這段時間的資料對你來說無所謂。。。

本文出自 “lustlost-迷失在慾望之中” 部落格，請務必保留此出處http://lustlost.blog.51cto.com/2600869/974762