mysql的資料備份方案

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;

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