操作mysqldump全量、增量備份的詳細方法

本文主要給大家簡單講講操作mysqldump全量、增量備份的詳細方法，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望操作mysqldump全量、增量備份的詳細方法這篇文章可以給大家?guī)硪恍嶋H幫助。

在日常運維工作中，對mysql數(shù)據(jù)庫的備份是萬分重要的，以防在數(shù)據(jù)庫表丟失或損壞情況出現(xiàn)，可以及時恢復(fù)數(shù)據(jù)。

線上數(shù)據(jù)庫備份場景：
每周日執(zhí)行一次全量備份，然后每天下午1點執(zhí)行MySQLdump增量備份.

下面對這種備份方案詳細說明下：
1.MySQLdump增量備份配置
執(zhí)行增量備份的前提條件是MySQL打開binlog日志功能，在my.cnf中加入
log-bin=/opt/Data/MySQL-bin
“l(fā)og-bin=”后的字符串為日志記載目錄，一般建議放在不同于MySQL數(shù)據(jù)目錄的磁盤上。

2.MySQLdump增量備份
假定星期日下午1點執(zhí)行全量備份，適用于MyISAM存儲引擎。
[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_PM.sql

對于InnoDB將--lock-all-tables替換為--single-transaction
--flush-logs為結(jié)束當(dāng)前日志，生成新日志文件；
--master-data=2 選項將會在輸出SQL中記錄下完全備份后新日志文件的名稱，

用于日后恢復(fù)時參考，例如輸出的備份SQL文件中含有：
CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;

3.MySQLdump增量備份其他說明：
如果MySQLdump加上–delete-master-logs 則清除以前的日志，以釋放空間。但是如果云服務(wù)器配置為鏡像的復(fù)制主云服務(wù)器，用MySQLdump –delete-master-logs刪掉MySQL二進制日志很危險，因為從云服務(wù)器可能還沒有完全處理該二進制日志的內(nèi)容。在這種情況下，使用 PURGE MASTER LOGS更為安全。

每日定時使用 MySQLadmin flush-logs來創(chuàng)建新日志，并結(jié)束前一日志寫入過程。并把前一日志備份，例如上例中開始保存數(shù)據(jù)目錄下的日志文件 MySQL-bin.000002 , ...

1.恢復(fù)完全備份
mysql -u root -p < backup_sunday_1_PM.sql

2.恢復(fù)增量備份
mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢復(fù)過程亦會寫入日志文件，如果數(shù)據(jù)量很大，建議先關(guān)閉日志功能

--compatible=name
它告訴 MySQLdump，導(dǎo)出的數(shù)據(jù)將和哪種數(shù)據(jù)庫或哪個舊版本的 MySQL云服務(wù)器相兼容。值可以為 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用幾個值，用逗號將它們隔開。當(dāng)然了，它并不保證能完全兼容，而是盡量兼容。

--complete-insert，-c
導(dǎo)出的數(shù)據(jù)采用包含字段名的完整 INSERT 方式，也就是把所有的值都寫在一行。這么做能提高插入效率，但是可能會受到 max_allowed_packet 參數(shù)的影響而導(dǎo)致插入失敗。因此，需要謹慎使用該參數(shù)，至少我不推薦。

--default-character-set=charset
指定導(dǎo)出數(shù)據(jù)時采用何種字符集，如果數(shù)據(jù)表不是采用默認的 latin1 字符集的話，那么導(dǎo)出時必須指定該選項，否則再次導(dǎo)入數(shù)據(jù)后將產(chǎn)生亂碼問題。

--disable-keys
告訴 MySQLdump 在 INSERT 語句的開頭和結(jié)尾增加 /!40000 ALTER TABLE table DISABLE KEYS /; 和 /!40000 ALTER TABLE table ENABLE KEYS /; 語句，這能大大提高插入語句的速度，因為它是在插入完所有數(shù)據(jù)后才重建索引的。該選項只適合 MyISAM 表。

--extended-insert = true|false
默認情況下，MySQLdump 開啟 --complete-insert 模式，因此不想用它的的話，就使用本選項，設(shè)定它的值為 false 即可。

--hex-blob
使用十六進制格式導(dǎo)出二進制字符串字段。如果有二進制數(shù)據(jù)就必須使用本選項。影響到的字段類型有 BINARY、VARBINARY、BLOB。

--lock-all-tables，-x
在開始導(dǎo)出之前，提交請求鎖定所有數(shù)據(jù)庫中的所有表，以保證數(shù)據(jù)的一致性。這是一個全局讀鎖，并且自動關(guān)閉 --single-transaction 和 --lock-tables 選項。

--lock-tables
它和 --lock-all-tables 類似，不過是鎖定當(dāng)前導(dǎo)出的數(shù)據(jù)表，而不是一下子鎖定全部庫下的表。本選項只適用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 選項。

--no-create-info，-t
只導(dǎo)出數(shù)據(jù)，而不添加 CREATE TABLE 語句。

--no-data，-d
不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)。
mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump
將只備份表結(jié)構(gòu)。--databases指示主機上要備份的數(shù)據(jù)庫。

--opt
這只是一個快捷選項，等同于同時添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 選項。本選項能讓 MySQLdump 很快的導(dǎo)出數(shù)據(jù)，并且導(dǎo)出的數(shù)據(jù)能很快導(dǎo)回。該選項默認開啟，但可以用 --skip-opt 禁用。注意，如果運行 MySQLdump 沒有指定 --quick 或 --opt 選項，則會將整個結(jié)果集放在內(nèi)存中。如果導(dǎo)出大數(shù)據(jù)庫的話可能會出現(xiàn)問題。

--quick，-q
該選項在導(dǎo)出大表時很有用，它強制 MySQLdump 從云服務(wù)器查詢?nèi)〉糜涗浿苯虞敵龆皇侨〉盟杏涗浐髮⑺鼈兙彺娴絻?nèi)存中。

--routines，-R
導(dǎo)出存儲過程以及自定義函數(shù)。

--single-transaction
該選項在導(dǎo)出數(shù)據(jù)之前提交一個 BEGIN SQL語句，BEGIN 不會阻塞任何應(yīng)用程序且能保證導(dǎo)出時數(shù)據(jù)庫的一致性狀態(tài)。它只適用于事務(wù)表，例如 InnoDB 和 BDB。本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何掛起的事務(wù)隱含提交。要想導(dǎo)出大表的話，應(yīng)結(jié)合使用 --quick 選項。

--triggers
同時導(dǎo)出觸發(fā)器。該選項默認啟用，用 --skip-triggers 禁用它。

跨主機備份
使用下面的命令可以將host1上的sourceDb復(fù)制到host2的targetDb，前提是host2主機上已經(jīng)創(chuàng)建targetDb數(shù)據(jù)庫：
-C 指示主機間的數(shù)據(jù)傳輸使用數(shù)據(jù)壓縮
mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

結(jié)合Linux的cron命令實現(xiàn)定時備份
比如需要在每天凌晨1:30備份某個主機上的所有數(shù)據(jù)庫并壓縮dump文件為gz格式
30 1 * mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_date '+%m-%d-%Y'.sql.gz

一個完整的Shell腳本備份MySQL數(shù)據(jù)庫示例。比如備份數(shù)據(jù)庫opspc
[root@test-huanqiu ~]# vim /root/backup.sh
#!bin/bash
echo "Begin backup mysql database"
mysqldump -u root -ppassword opspc > /home/backup/mysqlbackup-date +%Y-%m-%d.sql
echo "Your database backup successfully completed"

[root@test-huanqiu ~]# crontab -e
30 1 * /bin/bash -x /root/backup.sh > /dev/null 2>&1

mysqldump全量備份+mysqlbinlog二進制日志增量備份
1）從mysqldump備份文件恢復(fù)數(shù)據(jù)會丟失掉從備份點開始的更新數(shù)據(jù)，所以還需要結(jié)合mysqlbinlog二進制日志增量備份。
首先確保已開啟binlog日志功能。在my.cnf中包含下面的配置以啟用二進制日志：
[mysqld]
log-bin=mysql-bin

2）mysqldump命令必須帶上--flush-logs選項以生成新的二進制日志文件：
mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql
其中參數(shù)--master-data=[0|1|2]
0: 不記錄
1：記錄為CHANGE MASTER語句
2：記錄為注釋的CHANGE MASTER語句

mysqldump全量+增量備份方案的具體操作可參考下面兩篇文檔：
http://www.cnblogs.com/kevingrace/p/5904800.html
http://www.cnblogs.com/kevingrace/p/5907254.html

下面分享一下自己用過的mysqldump全量和增量備份腳本

應(yīng)用場景：
1）增量備份在周一到周六凌晨3點，會復(fù)制mysql-bin.00000到指定目錄；
2）全量備份則使用mysqldump將所有的數(shù)據(jù)庫導(dǎo)出，每周日凌晨3點執(zhí)行，并會刪除上周留下的mysq-bin.00000，然后對mysql的備份操作會保留在bak.log文件中。

腳本實現(xiàn)：
1）全量備份腳本（假設(shè)mysql登錄密碼為123456；注意腳本中的命令路徑）：
[root@test-huanqiu ~]# vim /root/Mysql-FullyBak.sh
#!/bin/bash
#Program
#use mysqldump to Fully backup mysql data per week!
#History
#Path
BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
Date=date +%Y%m%d
Begin=date +"%Y年%m月%d日 %H:%M:%S"
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
/usr/local/mysql/bin/mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile
/bin/tar -zvcf $GZDumpFile $DumpFile
/bin/rm $DumpFile
Last=date +"%Y年%m月%d日 %H:%M:%S"
echo 開始:$Begin 結(jié)束:$Last $GZDumpFile succ >> $LogFile
cd $BakDir/daily
/bin/rm -f *

2）增量備份腳本（腳本中mysql的數(shù)據(jù)存放路徑是/home/mysql/data，具體根據(jù)自己的實際情況進行調(diào)整）
[root@test-huanqiu ~]# vim /root/Mysql-DailyBak.sh
#!/bin/bash
#Program
#use cp to backup mysql data everyday!
#History
#Path
BakDir=/home/mysql/backup/daily                     //增量備份時復(fù)制mysql-bin.00000的目標目錄，提前手動創(chuàng)建這個目錄
BinDir=/home/mysql/data
LogFile=/home/mysql/backup/bak.log
BinFile=/home/mysql/data/mysql-bin.index
/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs
#這個是用于產(chǎn)生新的mysql-bin.00000文件
Counter=wc -l $BinFile |awk '{print $1}'
NextNum=0
#這個for循環(huán)用于比對$Counter,$NextNum這兩個值來確定文件是不是存在或最新的
for file in cat $BinFile
do
base=basename $file
#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
NextNum=expr $NextNum + 1
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
if(test -e $dest)
#test -e用于檢測目標文件是否存在，存在就寫exist!到$LogFile去
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
echo date +"%Y年%m月%d日 %H:%M:%S" $Next Bakup succ! >> $LogFile

3）設(shè)置crontab任務(wù)，執(zhí)行備份腳本。先執(zhí)行的是增量備份腳本，然后執(zhí)行的是全量備份腳本：
[root@test-huanqiu ~]# crontab -e
#每個星期日凌晨3:00執(zhí)行完全備份腳本
0 3 0 /bin/bash -x /root/Mysql-FullyBak.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量備份
0 3 1-6 /bin/bash -x /root/Mysql-DailyBak.sh >/dev/null 2>&1

4）手動執(zhí)行上面兩個腳本，測試下備份效果
[root@test-huanqiu backup]# pwd
/home/mysql/backup
[root@test-huanqiu backup]# mkdir daily
[root@test-huanqiu backup]# ll
total 4
drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily
[root@test-huanqiu backup]# ll daily/
total 0

先執(zhí)行增量備份腳本
[root@test-huanqiu backup]# sh /root/Mysql-DailyBak.sh
[root@test-huanqiu backup]# ll
total 8
-rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log
drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily
[root@test-huanqiu backup]# ll daily/
total 8
-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030
-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031
[root@test-huanqiu backup]# cat bak.log
mysql-binlog.000030 copying
mysql-binlog.000031 copying
mysql-binlog.000032 skip!
2016年11月29日 11:29:32 Bakup succ!

然后執(zhí)行全量備份腳本
[root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh
20161129.sql
[root@test-huanqiu backup]# ll
total 152
-rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz
-rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log
drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily
[root@test-huanqiu backup]# ll daily/
total 0
[root@test-huanqiu backup]# cat bak.log
mysql-binlog.000030 copying
mysql-binlog.000031 copying
mysql-binlog.000032 skip!

操作mysqldump全量、增量備份的詳細方法就先給大家講到這里，對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。