mysql備份數據(mysql備份數據庫的sql語句)

由于mysql存在多種數據庫備份方式，而且各有利弊,個人覺得，首先要基于公司的需求，考慮能夠容忍丟失多少數據、花多少人力時間成本等，這是我們制定備份方案的依據，同時制定出來的方案要可執行，要執行，不能把方案當作紙上談兵。下面我把我們實際的備份方案整理出來。

作為數據安全的一個重要內容——數據備份的重要性往往被人們所忽視。只要發生數據傳輸、數據存儲和數據交換，就有可能產生數據故障。這時，如果沒有采取數據備份和數據恢復手段與措施，就會導致數據的丟失，有時造成的損失是無法彌補與估量的。結合我們前公司線上業務的實際情況，我們之前的備份方案，主要采取全備+binlog備份方式。其中全備分為邏輯備份+物理備份，同時主從復制也作為一種備份的方式存在，從而最大程度降低數據故障帶來的風險。

· 應用場景

邏輯備份，我們主要用在當數據量較小時，數據庫出現數據故障，對于恢復時間要求不高；搭建主從環境，搭建測試環境及備用庫等方面。

· 備份時間及地點

每日凌晨3:00在從庫上備份，備份文件存放在從庫上的/data/back/mysql/

，當然如果有充足的機器，更安全的方式是備份到遠程服務器。

· 備份方式

采用mysqldump進行全庫備份，通過定時任務，定時執行shell備份腳本。

1）全備腳本如下：

#!/bin/bash

bakdir=/data/back/mysql/full/

datetime=`date +%Y%m%d%H%M%S`

ur=root

password=123456789

mysqldump=/usr/bin/mysqldump

echo "`date` 開始全備" >>$bakdir/bak.log

echo "------show master status--------------" >>$bakdir/bak.log

echo "`mysql -u${ur} -p${password} -e 'show master status;'` " >>$bakdir/bak.log

$mysqldump -u${ur} -p${password} -B -A --single-transaction --flush-logs --master-data=2 --events |gzip > ${bakdir}/all-${datetime}.sql.gz

if [ $? -eq 0 ];then

echo "`date` 全備成功" >>$bakdir/bak.log

el

echo "`date` 全備失敗" >>$bakdir/bak.log

# echo "`date` 全備失敗" |mail -s "`date` 全備失敗" 郵箱地址參數值

fi

2）增量備份全庫腳本

#!/bin/bash

# u cp to backup mysql data everyday!

BakDir=/data/back/mysql/zl

BinDir=/var/lib/mysql/

LogFile=/data/back/mysql/zl/bak.log

BinFile=/var/lib/mysql/mysql-bin.index

mysqladmin -uroot -p123456789 flush-logs

#這個是用于產生新的mysql-bin.00000*文件

Counter=`wc -l $BinFile |awk '{print $1}'`

NextNum=0

#這個for循環用于比對$Counter,$NextNum這兩個值來確定文件是不是存在或最新的

for file in `cat $BinFile`

do

ba=`baname $file`

#baname用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./

NextNum=`expr $NextNum + 1`

if [ $NextNum -eq $Counter ]

then

echo $ba skip! >> $LogFile

el

dest=$BakDir/$ba

if(test -e $dest)

#test -e用于檢測目標文件是否存在，存在就寫exist!到$LogFile去

then

echo $ba exist! >> $LogFile

el

cp $BinDir/$ba $BakDir

echo $ba copying >> $LogFile

fi

fi

done

echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile

3）定時任務

每個星期日凌晨3:00執行完全備份腳本

0 3 * * 0 /bin/bash /data/script/msyql_bak/mysql_full_bak.sh >/dev/null 2>&1

#周一到周六凌晨3:00做增量備份

0 3 * * 1-6 /bin/bash /data/script/msyql_bak/mysql_zl_bak.sh >/dev/null 2>&1

· 應用場景

主要應對要求恢復時間較高；數據量比較大；

· 備份時間及地點

每周一凌晨3:00在主庫上備份。備份文件存放遠程服務器目錄下

· 備份方式

采用percona的社區工具innobackupex，該工具可以在線熱備，不影響線上的業務。

以上兩種方式的備份只能恢復某段時間的數據，對于按照時間點的恢復是無能為力的，那怎么辦呢？binlog日志，是的，我們采取的是實時同步binlog日志到遠程服務器上，這樣理論上是可以恢復到任意時間點的。

· 應用場景

對于一些由于錯誤操作等造成數據丟失錯誤的，需要按照時間點進行還原的情況下。

· 備份時間及地點

備份服務器實時將主庫上binlog同步到遠程服務器上。

· 備份方式

mysqlbinlog工具進行日志拉取，shell腳本如下：

mysqlbinlog --read-from-remote-rver --host=1.1.1.1 --port=3306 --ur="backup" --password="backup" --raw --stop-never mysql-bin.000840 --result-file=/data/backup/binlog/

經過以上三種結合的備份方式，基本上可以滿足在數據異常丟失情況下，恢復到正常狀態。

· 應用場景

主要應用于讀寫分離，故障轉移的情況下

· 備份時間及地點

幾乎可以認為是同步進行數據的復制

· 備份方式

采用mysql提供的復制技術

對于主從復制，如果用于備庫的話，最好是讓sql_thread執行慢一段時間，可以是1天。這個結合實際情況，自己選擇。

備份文件有了之后還需要對其定期的進行恢復測試，不然可能是白忙一場。因為很多情況下，有些備份文件可能已經損壞。當我們遇到數據丟失故障時，在緊急關頭，竟然發現備份的文件無法恢復或者數據一致性和完整性沒有達到要求，如果我們定期的對備份文件進行恢復測試，這種悲劇可能就不會發生。

1 恢復時間及地點

每周進行一次恢復測試，主要在測試機上進行

2 恢復方式

模擬某個時間點主機數據全部丟失，要求恢復到丟失時間點的所有數據，先進行全備恢復，然后根據binlog恢復到最近時間點。