mysqldump備份一般方式詳解

本文主要給大家介紹mysqldump備份一般方式詳解，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問(wèn)題需要了解的可以持續(xù)在億速云行業(yè)資訊里面關(guān)注我的更新文章的。

 一、mysqldump備份結(jié)合binlog日志恢復(fù)

MySQL備份一般采取全庫(kù)備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時(shí)執(zhí)行一次二進(jìn)制日志備份。這樣在MySQL故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)二進(jìn)制日志備份前的任意位置或時(shí)間。

1.binlog日志恢復(fù)介紹

·首先要開(kāi)啟binary log功能

通過(guò)編輯my.cnf中的log-bin選項(xiàng)可以開(kāi)啟二進(jìn)制日志；形式如下：

log-bin [=DIR/[filename]]  （配置文件中只寫(xiě)log_bin不寫(xiě)后面的文件名和路徑時(shí)，默認(rèn)存放在/usr/local/mysql/data目錄下，文件名為主機(jī)名-bin.000001…命名）

·下面用一個(gè)例子來(lái)說(shuō)明binlog日志恢復(fù)數(shù)據(jù)的方法

先對(duì)測(cè)試數(shù)據(jù)庫(kù)的表進(jìn)行一下增刪改的操作，否則log里邊數(shù)據(jù)有點(diǎn)空。

重新開(kāi)始一個(gè)新的日志文件，并刪除測(cè)試表中的一行數(shù)據(jù)，模擬錯(cuò)誤操作。

查看MySQL Server上的二進(jìn)制日志

查看二進(jìn)制日志中的事件，默認(rèn)顯示可找到的第一個(gè)二進(jìn)制日志文件中的事件，包含了日志文件名、事件的開(kāi)始位置、事件類型、結(jié)束位置、信息等內(nèi)容。

語(yǔ)法格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

選項(xiàng)解析：

IN 'log_name'      指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)
FROM pos               指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)
offset           偏移量(不指定就是0),例如為3就會(huì)從第3行開(kāi)始
row_count          查詢總條數(shù)(不指定就是所有行)

顯示信息中的參數(shù)介紹：

Format_desc  此事件為格式描述事件
Query  為查詢事件
BEGIN  為事務(wù)開(kāi)始
Table_map  為表映射事件
Write_rows    為我們執(zhí)行的insert事件
XidXid    時(shí)間是自動(dòng)提交事務(wù)的動(dòng)作
Rotate    為日志輪換事件，是我們執(zhí)行flush logs開(kāi)啟新日志文件引起的。
COMMIT    為事務(wù)的提交

查看指定的二進(jìn)制日志中的事件

為了便于查看記錄了行變化信息的事件在當(dāng)時(shí)具體執(zhí)行了什么樣的SQL語(yǔ)句可以使用mysqlbinlog工具的-v（--verbose）選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽SQL語(yǔ)句，如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容。

執(zhí)行以下命令，我們可以看到上圖中顯示的信息：

mysqlbinlog  -v  /usr/local/mysql/data/mysql-bin.000002

另外mysqlbinlog和可以通過(guò)--read-from-remote-server選項(xiàng)從遠(yuǎn)程云服務(wù)器讀取二進(jìn)制日志文件，這時(shí)需要一些而外的連接參數(shù)，如-h，-P，-p，-u等，這些參數(shù)僅在指定了--read-from-remote-server后有效。

從上圖中可以看出delete事件發(fā)生position是291，事件結(jié)束position是420

恢復(fù)流程：直接用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置291前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)。下面所有的操作,命令如下：

常見(jiàn)的選項(xiàng)有以下幾個(gè)：

--start-datetime
從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件。
--stop-datetime
從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件。
--start-position        
從二進(jìn)制日志中讀取指定position 事件位置作為開(kāi)始。
--stop-position
從二進(jìn)制日志中讀取指定position 事件位置作為事件截至。

刪除test數(shù)據(jù)庫(kù)，利用binlog恢復(fù)數(shù)據(jù)，完成后發(fā)現(xiàn)數(shù)據(jù)都恢復(fù)過(guò)來(lái)了。

注意：

（1）在實(shí)際生產(chǎn)環(huán)境中，如果遇到需要恢復(fù)數(shù)據(jù)庫(kù)的情況，不要讓用戶能訪問(wèn)到數(shù)據(jù)庫(kù)，以避免新的數(shù)據(jù)插入進(jìn)來(lái)，以及在主從的環(huán)境下，關(guān)閉主從。

（2）以上方法中僅僅利用了binlog來(lái)恢復(fù)數(shù)據(jù)，并沒(méi)有使用到mysqldump全庫(kù)備份來(lái)恢復(fù)數(shù)據(jù)庫(kù)，所以在恢復(fù)數(shù)據(jù)前要?jiǎng)h除數(shù)據(jù)庫(kù)。

2.利用腳本實(shí)現(xiàn)mysql的備份與恢復(fù)

（1）mysqldump介紹

mysqldump是mysql用于備份和數(shù)據(jù)轉(zhuǎn)移的一個(gè)工具。它主要產(chǎn)生一系列的SQL語(yǔ)句，可以封裝到文件，該文件包含有所有重建你的數(shù)據(jù)庫(kù)所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等?？梢杂脕?lái)實(shí)現(xiàn)輕量級(jí)的快速遷移或恢復(fù)數(shù)據(jù)庫(kù)。

mysqldump 是將數(shù)據(jù)表導(dǎo)成 SQL 腳本文件，在不同的 MySQL 版本之間升級(jí)時(shí)相對(duì)比較合適，這也是最常用的備份方法。

mysqldump一般在數(shù)據(jù)量很小的時(shí)候（幾個(gè)G）可以用于備份。當(dāng)數(shù)據(jù)量比較大的情況下，就不建議用mysqldump工具進(jìn)行備份了。

·數(shù)據(jù)庫(kù)的導(dǎo)出

導(dǎo)出對(duì)象說(shuō)明：

mysqldump可以針對(duì)單個(gè)表、多個(gè)表、單個(gè)數(shù)據(jù)庫(kù)、多個(gè)數(shù)據(jù)庫(kù)、所有數(shù)據(jù)庫(kù)進(jìn)行導(dǎo)出的操作

# mysqldump [options] db_name [tbl_name ...]   //導(dǎo)出指定數(shù)據(jù)庫(kù)或單個(gè)表
# mysqldump [options] --databases db_name ...   //導(dǎo)出多個(gè)數(shù)據(jù)庫(kù)
#mysqldump [options] --all-databases           //導(dǎo)出所有

·數(shù)據(jù)庫(kù)的導(dǎo)入

mysql  -uroot  -p  [options]  [db_name]  [tbl_name]  <  /路徑/備份文件

生產(chǎn)環(huán)境中Mysql數(shù)據(jù)庫(kù)的備份是周期性重復(fù)的操作，所以通常是要編寫(xiě)腳本實(shí)現(xiàn)，通過(guò)crond計(jì)劃任務(wù)周期性執(zhí)行備份腳本。

mysqldump備份方案：

·周日凌晨1點(diǎn)全庫(kù)備份

·周一到周六凌晨每隔4個(gè)小時(shí)增量備份一次

設(shè)置crontab任務(wù)，每天執(zhí)行備份腳本

# crontab –e

#每個(gè)星期日凌晨1:00執(zhí)行完全備份腳本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔4個(gè)小時(shí)增量備份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh腳本內(nèi)容：

#!/bin/sh
#name:mysqlfullbackup.sh
mysqldir=/usr/local/mysql
user=root
userpwd=123456
dbname=test_db
#定義備份目錄
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackdir
#定義郵件正文文件
emailfile=$databackupdir/email.txt
#定義郵件地址
email=root@localhost.localdomain
#定義備份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`data -I`
 
echo "" > $emailfile
echo $(date +"%y-%m-%d%H:%M:%S") >> $emailfile
cd $databackupdir
#定義備份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz
#使用mysqldump備份數(shù)據(jù)庫(kù)
$mysqldir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
#壓縮備份文件
if [ $? -eq 0 ]; then
tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1
echo "backupfilename:$gzdumpfile" >> $emailfile
echo "database backup success!" >> $emailfile
rm -f $dumpfile
else
echo "database backup fail!" >> $emailfile
fi
#寫(xiě)日志文件
echo "------------------------------------------" >> $logfile
cat $emailfile >> $logfile
#發(fā)送郵件通知
cat $emailfile | mail -s "mysql backup" $email
 
mysqldailybackup.sh腳本內(nèi)容：
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定義數(shù)據(jù)庫(kù)目錄和數(shù)據(jù)目錄
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定義用于備份數(shù)據(jù)庫(kù)的用戶名和密碼
user=root
userpwd=123456
# 定義備份目錄，每日備份文件備份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $dailybackupdir/daily
# 定義郵件正文文件
emailfile=$databackupdir/email.txt
# 定義郵件地址
email=root@localhost.localdomain
# 定義日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
# 刷新日志，使數(shù)據(jù)庫(kù)使用新的二進(jìn)制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二進(jìn)制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
icounter=`expr $icounter+1`
done
nextnum=0
ifile=0
for file in $filelist
do
binlogname=`basename $file`
nextnum=`expr $nextnum+1`
if [ $nextnum -eq $icounter ];then
echo "Skip laster!" > /dev/null
else
dest=$dailybackupdir/$binlogname
if [ -e $dest ];then
echo "Skip exist $binlogname" > /dev/null
else
cp $binlogname $dailybackupdir
if [$? -eq 0 ];then
ifile=`expr $ifile+1`
echo "$binlogname backup success!" >> $emailfile
fi
fi
fi
done
if [ $ifile -eq 0 ];then
echo "no binlog backup!" >> $emailfile
else
echo "backup $ifile files" >> $emailfile
fi
# 發(fā)送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email
# 寫(xiě)日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

看了以上關(guān)于mysqldump備份一般方式詳解，希望能給大家在實(shí)際運(yùn)用中帶來(lái)一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問(wèn)題的。