mysqldump備份一般方式詳解

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

 一、mysqldump備份結合binlog日志恢復

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

1.binlog日志恢復介紹

·首先要開啟binary log功能

通過編輯my.cnf中的log-bin選項可以開啟二進制日志；形式如下：

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

·下面用一個例子來說明binlog日志恢復數據的方法

先對測試數據庫的表進行一下增刪改的操作，否則log里邊數據有點空。

重新開始一個新的日志文件，并刪除測試表中的一行數據，模擬錯誤操作。

查看MySQL Server上的二進制日志

查看二進制日志中的事件，默認顯示可找到的第一個二進制日志文件中的事件，包含了日志文件名、事件的開始位置、事件類型、結束位置、信息等內容。

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

選項解析：

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

顯示信息中的參數介紹：

Format_desc  此事件為格式描述事件
Query  為查詢事件
BEGIN  為事務開始
Table_map  為表映射事件
Write_rows    為我們執行的insert事件
XidXid    時間是自動提交事務的動作
Rotate    為日志輪換事件，是我們執行flush logs開啟新日志文件引起的。
COMMIT    為事務的提交

查看指定的二進制日志中的事件

為了便于查看記錄了行變化信息的事件在當時具體執行了什么樣的SQL語句可以使用mysqlbinlog工具的-v（--verbose）選項，該選項會將行事件重構成被注釋掉的偽SQL語句，如果想看到更詳細的信息可以將該選項給兩次如-vv，這樣可以包含一些數據類型和元信息的注釋內容。

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

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

另外mysqlbinlog和可以通過--read-from-remote-server選項從遠程云服務器讀取二進制日志文件，這時需要一些而外的連接參數，如-h，-P，-p，-u等，這些參數僅在指定了--read-from-remote-server后有效。

從上圖中可以看出delete事件發生position是291，事件結束position是420

恢復流程：直接用bin-log日志將數據庫恢復到刪除位置291前,然后跳過故障點,再進行恢復。下面所有的操作,命令如下：

常見的選項有以下幾個：

--start-datetime
從二進制日志中讀取指定時間戳或者本地計算機時間之后的日志事件。
--stop-datetime
從二進制日志中讀取指定時間戳或者本地計算機時間之前的日志事件。
--start-position        
從二進制日志中讀取指定position 事件位置作為開始。
--stop-position
從二進制日志中讀取指定position 事件位置作為事件截至。

刪除test數據庫，利用binlog恢復數據，完成后發現數據都恢復過來了。

注意：

（1）在實際生產環境中，如果遇到需要恢復數據庫的情況，不要讓用戶能訪問到數據庫，以避免新的數據插入進來，以及在主從的環境下，關閉主從。

（2）以上方法中僅僅利用了binlog來恢復數據，并沒有使用到mysqldump全庫備份來恢復數據庫，所以在恢復數據前要刪除數據庫。

2.利用腳本實現mysql的備份與恢復

（1）mysqldump介紹

mysqldump是mysql用于備份和數據轉移的一個工具。它主要產生一系列的SQL語句，可以封裝到文件，該文件包含有所有重建你的數據庫所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用來實現輕量級的快速遷移或恢復數據庫。

mysqldump 是將數據表導成 SQL 腳本文件，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。

mysqldump一般在數據量很小的時候（幾個G）可以用于備份。當數據量比較大的情況下，就不建議用mysqldump工具進行備份了。

·數據庫的導出

導出對象說明：

mysqldump可以針對單個表、多個表、單個數據庫、多個數據庫、所有數據庫進行導出的操作

# mysqldump [options] db_name [tbl_name ...]   //導出指定數據庫或單個表
# mysqldump [options] --databases db_name ...   //導出多個數據庫
#mysqldump [options] --all-databases           //導出所有

·數據庫的導入

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

生產環境中Mysql數據庫的備份是周期性重復的操作，所以通常是要編寫腳本實現，通過crond計劃任務周期性執行備份腳本。

mysqldump備份方案：

·周日凌晨1點全庫備份

·周一到周六凌晨每隔4個小時增量備份一次

設置crontab任務，每天執行備份腳本

# crontab –e

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

mysqlfullbackup.sh腳本內容：

#!/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備份數據庫
$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
#寫日志文件
echo "------------------------------------------" >> $logfile
cat $emailfile >> $logfile
#發送郵件通知
cat $emailfile | mail -s "mysql backup" $email
 
mysqldailybackup.sh腳本內容：
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定義數據庫目錄和數據目錄
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定義用于備份數據庫的用戶名和密碼
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
# 刷新日志，使數據庫使用新的二進制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二進制日志列表
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
# 發送郵件通知
cat $emailfile | mail -s "MySQL Backup" $email
# 寫日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

看了以上關于mysqldump備份一般方式詳解，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。