DBA必知的mysql備份與還原的幾大方法

博主QQ：819594300

博客地址：http://zpf666.blog.51cto.com/

有什么疑問的朋友可以聯系博主，博主會幫你們解答，謝謝支持！一、mysqldump備份結合binlog日志恢復

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

1、binlog介紹

1）該日志記錄著數據庫的所有增、刪、改的操作日志，還包括這些操作的執行時間。

Binlog功能默認是關閉的，沒有開啟。

查看binlog，用mysqlbinlog  -v  mysql-bin.000001

Binlog的用途：1：主從同步          2：恢復數據庫

開啟binary log功能：通過編輯my.cnf中的log-bin選項可以開啟二進制日志；形式如右：log-bin[=DIR/[filename]] ，注釋：每次重啟mysql服務或運行mysql> flush logs;都會生成一個新的二進制日志文件，這些日志文件的number會不斷地遞增，除了生成上述的文件外還會生成一個名為filename.index的文件。這個文件中存儲所有二進制日志文件的清單又稱為二進制文件的索引。

2）查看產生的binary log   注：查看binlog內容是為了恢復數據

說明：bin-log因為是二進制文件，不能通過文件內容查看命令直接打開查看，mysql提供兩種方式查看方式。

①在介紹之前，我們先對數據庫進行一下增刪改的操作，否則log里邊數據有點空。

②重新開始一個新的日志文件

③查看MySQL Server上的二進制日志

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

該命令還包含其他選項以便靈活查看：

總結：上述方式可以查看到服務器上存在的二進制日志文件及文件中的事件，但是想查看到文件中具體的內容并應于恢復場景還得借助mysqlbinlog這個工具。

語法格式：mysqlbinlog  [options]  log_file ...

輸出內容會因日志文件的格式以及mysqlbinlog工具使用的選項不同而略不同。

mysqlbinlog的可用選項可參考man手冊。

說明：無論是本地二進制日志文件還是遠程服務器上的二進制日志文件，無論是行模式、語句模式還是混合模式的二進制日志文件，被mysqlbinlog工具解析后都可直接應用與MySQL Server進行基于時間點、位置或數據庫的恢復。

下面我們就來演示如何使用binlog恢復之前刪除數據（id=2那條記錄）

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

①查看binlog文件，從中找出delete from bdqn.test where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog  -v mysql-bin.000002

顯示結果如下：

圖片看不清楚的可以看下面復制的日志：
# at 219

#170316 21:52:28 server id 1  end_log_pos 287 CRC32 0xff83a85b    Query   thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1489672348/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 287

#170316 21:52:28 server id 1  end_log_pos 337 CRC32 0x343e7343   Table_map: `bdqn`.`test` mapped to number 108

# at 337

#170316 21:52:28 server id 1  end_log_pos 382 CRC32 0xa3d1ce0d    Delete_rows: table id 108 flags: STMT_END_F

BINLOG '

nJjKWBMBAAAAMgAAAFEBAAAAAGwAAAAAAAEABGJkcW4ABHRlc3QAAgMPAjwAAkNzPjQ=

nJjKWCABAAAALQAAAH4BAAAAAGwAAAAAAAEAAgAC//wCAAAABGxpc2kNztGj

'/*!*/;

### DELETE FROM `bdqn`.`test`

### WHERE

###   @1=2

###   @2='lisi'

# at 382

#170316 21:52:28 server id 1  end_log_pos 413 CRC32 0x257e7073    Xid = 10

COMMIT/*!*/;

說明：可以從上圖可以看出來delete時間發生position是287，事件結束position是413。

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

由于之前沒有做過全庫備份，所以要使用所有binlog日志恢復，所以生產環境中需要很長時間恢復，導出相關binlog文件。

③刪除bdqn數據庫（刪除bdqn和恢復數據之前，要關閉binlog功能）

④利用binlog恢復數據

⑤恢復完成后，我們檢查下表的數據是否完整

2、mysqldump介紹

作用：mysqldump是mysql自帶的備份和數據轉移的工具。

特點：它只產生sql語句（即sql命令）封裝在文件，而不是真實的數據。

Mysqldump是邏輯備份，不是物理備份，備份的是SQL語句，而不是數據文件。

Mysqldump適用于小型數據庫，數據容量一般是在幾個G大小，當數據量很大的情況下，不建議使用mysqldump。

導出對象：可以針對單個表、多個表、單個數據庫、多個數據庫、所有數據庫。

格式：

#mysqldump   [選項]   庫名   [表名1]  [表名2]  … > /備份路徑/備份文件名

//導出指定數據庫的單個或多個表

#mysqldump   [選項]   --databases  庫名1 [庫名2]  … >  /備份路徑/備份文件名

//導出指定的數據庫或多個數據庫

#mysqldump   [選項]   --all-databases  >  /備份路徑/備份文件名

//導出所有的數據庫

#mysqldump   -uroot  -p123456 --flush-logs   bdqn  > /opt/bdqn.sql

//導出數據庫bdqn,其中“—flush-logs”這個選項是完整備份完畢后開啟一個新的binlog

#mysql -uroot  -p123456  bdqn <  /opt/bdqn.sql

//從備份文件導入數據庫bdqn

下面用一個具體的實驗說明用mysqldump實現全庫備份+binlog的數據恢復

1）開啟binlog功能并重啟服務

2）創建備份目錄

3）創建實驗數據

4）開始全庫備份（注意：全庫備份不會備份binlog日志文件）

5）備份mysqldump全庫備份之前的所有的binlog日志文件（注意：真是生產環境中可能不止一個binlog文件）

6）因為全庫備份之前的binlog已經備份了，現在就刪除它們（即新產生的binlog之前的所有的binlog刪除）

7）模擬誤操作，刪除了數據，并且新增加了新的數據

8）備份自mysqldump之后的binlog日志文件

9）使用mysqldump的全庫備份+binlog來恢復數據

①使用mysqldump的備份進行全庫恢復（即恢復到全部備份時候的所有數據）

②分析新開啟的binlog日志文件（我這里是mysql-bin.000002）里面誤操作的事件的起始位置和終止位置，只要跳過這一段事件即可

圖片看不清楚的可以看下面復制的日志：

# at 219

#170318 21:14:42 server id 1  end_log_pos 291 CRC32 0xddbf8eff    Query   thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1489842882/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 291

#170318 21:14:42 server id 1  end_log_pos 339 CRC32 0x4a9ec8f2   Table_map: `bdqn`.`it` mapped to number 108

# at 339

#170318 21:14:42 server id 1  end_log_pos 388 CRC32 0x2e8a3da8    Delete_rows: table id 108 flags: STMT_END_F

BINLOG '

wjLNWBMBAAAAMAAAAFMBAAAAAGwAAAAAAAEABGJkcW4AAml0AAIDDwI8AALyyJ5K

wjLNWCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wBAAAACHpoYW5nc2FuqD2KLg==

'/*!*/;

### DELETE FROM `bdqn`.`it`

### WHERE

###   @1=1

###  @2='zhangsan'

# at 388

#170318 21:14:42 server id 1  end_log_pos 419 CRC32 0xa1c06a4f    Xid = 43

COMMIT/*!*/;

③開始使用全庫備份后的增量備份的binlog日志文件備份文件進行對全庫恢復后的增量數據的恢復

10）查看恢復結果

總結：從上圖顯示可以看出數據恢復到正常狀態，實際生產環境中mysql數據庫的備份是周期性重復操作，所有通常是要編寫腳本實現，通過crond計劃任務周期性執行備份腳本。

通過crontad計劃任務周期性執行備份腳本

1）制定mysqldump備份方案

周日凌晨1點全庫備份；

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

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

2）編寫mysqlfullbackup.sh腳本（即mysql全庫備份腳本）

圖片看不清楚的可以看下面復制的腳本原文件：

#!/bin/bash

#Name:mysqlFullBackup.sh

#定義數據庫目錄

mysqlDir=/usr/local/mysql

#定義用于備份數據庫的用戶名和密碼

user=root

userpwd=123456

dbname=bdqn

#定義備份目錄

databackupdir=/opt/mysqlbackup

[ ! -d $databackupdir ] && mkdir $databackupdir

#定義郵件正文文件

emailfile=$databackupdir/email.txt

#定義郵件地址

email=root@localhost.localdomain

#定義備份日志文件

logfile=$databackupdir/mysqlbackup.log

DATE=`date -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 zcvf$gzdumpfile $dumpfile >> $emailfile 2>&1

   echo"BackupFileName:$gzdumpfile" >> $emailfile

   echo"DataBase Backup Success!" >> $emailfile

   rm -rf$dumpfile

else

   echo"DataBase Backup Fail!" >> $emailfile

fi

#寫日志文件

echo"-------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

#發送郵件通知

cat $emailfile | mail -s "MySQL Backup" $email

2）編寫mysqldailybackup.sh腳本（即mysql增量備份腳本）

圖片看不清楚的可以看下面復制的腳本原文件：

#!/bin/bash

#Name:mysqlDailyBackup.sh

#定義數據庫目錄和數據目錄

mysqldir=/usr/local/mysql

datadir=$mysqldir/data

#定義用于備份數據庫的用戶名和密碼

user=root

userpwd=123456

#定義備份目錄、每日備份文件備份到$databackupdir/daily

databackupdir=/opt/mysqlbackup

dailybackupdir=$databackupdir/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=`exper$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 "Skiplastest!" > /dev/null

else

 dest=$dailybackupdir/$binlogname

#跳過已經備份的二進制日志文件

if [ -e $dest ]; then

  echo "Skipexist $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 "NoBinlog Backup!" >> $emailfile

else

   echo"Backup $ifile File(s)." >> $emailfile

   echo"Backup MySQL Binlog OK!" >> $emailfile

fi

#發送郵件通知

cat $emailfile | mail -s "MySQL Backup" $email

#寫日志文件

echo"-----------------------------------------" >> $logfile

cat $emailfile >> $logfile

發送郵件測試：

安裝libmysqlclient.so.18

再次測試：

二、  使用xtrabackup進行MySQL數據庫備份

前面介紹mysqldump備份方式是采用邏輯備份，其最大的缺陷就是備份和恢復速度都慢，對于一個小于50G的數據庫而言，這個速度還是能接受的，但如果數據庫非常大，那再使用mysqldump備份就不太適合了。

這時就需要一種好用又高效的工具，xtrabackup就是其中一款，號稱免費版的InnoDB HotBackup。

Xtrabackup實現是物理備份，而且是物理熱備。

目前主流的有兩個工具可以實現物理熱備：ibbackup和xtrabackup；ibbackup是商業軟件，需要授權，非常昂貴。而xtrabackup功能比ibbackup還要強大，但卻是開源的。因此我們這里就來介紹xtrabackup的使用。

Xtrabackup提供了兩種命令行工具：

xtrabackup：專用于備份InnoDB和XtraDB引擎的數據；

innobackupex：這是一個perl腳本，在執行過程中會調用xtrabackup命令，這樣用該命令即可以實現備份InnoDB，也可以備份MyISAM引擎的對象。

Xtrabackup是由percona提供的mysql數據庫備份工具，特點：

(1)備份過程快速、可靠；

(2)備份過程不會打斷正在執行的事務；

(3)能夠基于壓縮等功能節約磁盤空間和流量；

(4)自動實現備份檢驗；

(5)還原速度快。

官方鏈接地址：http://www.percona.com/software/percona-xtrabackup；可以下載源碼編譯安裝，也可以下載適合的RPM包或使用yum進行安裝或者下載二進制源碼包。

安裝xtrabackup

1）下載xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2）解壓

3)進入解壓目錄

4)復制bin下的所有程序到/usr/bin

說明：Xtrabackup中主要包含兩個工具：

xtrabackup：是用于熱備份innodb,xtradb表中數據的工具，支持在線熱備份，可以在不加鎖的情況下備份Innodb數據表，不過此工具不能操作Myisam引擎表；

innobackupex：是將xtrabackup進行封裝的perl腳本，能同時處理Innodb和Myisam，但在處理Myisam時需要加一個讀鎖。

由于操作Myisam時需要加讀鎖，這會堵塞線上服務的寫操作，而Innodb沒有這樣的限制，所以數據庫中Innodb表類型所占的比例越大，則越有利。

5)安裝相關插件

6)下載percona-toolkit并安裝

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

至此就完成了xtrabackup的安裝，下面就可以啟動備份了。

方案：xtrabackup完全備份+binlog增量備份

1）開啟binlog功能并重啟mysqld服務

2）創建備份用的目錄（full：全備存放的目錄；inc：增量備份存放的目錄）

3）創建實驗用數據庫、表、以及添加實驗數據

4）開始完全備份

5）我們可以看一下備份后的文件

說明：1）在使用innobackupex進行備份時，還可以使用--no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。

2）還可以加—database選項指定要備份的數據庫，這里指定的數據庫只對MyISAM表有效，對于InnoDB數據來說都是全備（所有數據庫中的InnoDB數據都進行了備份，不是只備份指定的數據庫，恢復時也一樣）。

針對里面的各個文件的說明：

下面我們護體看一下這幾個文件：

說明：xtrabackup_binlog_pos_innodb和xtrabackup_binary在這個版本里面沒有了，因為版本較新，這兩個文件在新版給刪除了，只存在于老版本。

6）至此完全備份成功，然后我們開啟一個新的binlog日志文件，并向mysql某個庫插入幾條數據。

7）模擬誤操作，刪除一條數據，同時再插入兩條新數據

8）開始增量備份binlog日志文件

9）開始還原數據庫

①模擬數據庫損壞

我這里直接使用刪除數據目錄文件來模擬損壞。

②然后首先是還原完全備份，準備(prepare)一個完全備份

說明1：一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態?！皽蕚洹钡闹饕饔谜峭ㄟ^回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。

說明2：在準備（prepare）過程結束后，InnoDB表數據已經前滾到整個備份結束的點，而不是回滾到xtrabackup剛開始時的點。

innobakupex命令的--apply-log選項可用于實現上述功能。如下面的命令：

--apply-log指明是將日志應用到數據文件上，完成之后將備份文件中的數據恢復到數據庫中：

說明3：在實現“準備”的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的內存的大小，默認通常為100M。如果有足夠的內存可用，可以多劃分一些內存給prepare的過程，以提高其完成速度。

③正式開始還原完全備份的數據庫

說明1：innobackupex命令的--copy-back選項用于執行恢復操作，其通過復制所有數據相關的文件至mysql服務器DATADIR目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。

④修改data目錄的屬組和屬組為mysql:mysql，并重啟mysqld服務。

⑤驗證還原后的數據

⑥開始還原增量備份，但在此之前為了防止還原時產生大量的二進制日志，在還原時可臨時關閉二進制日志后再還原。

⑦有誤操作的，在開始還原增量備份之前，要去binlog備份文件把誤操作事件刪除

⑧正式開始還原增量備份

⑨重新啟動二進制日志并驗證還原數據

附：Xtrabackup的“流”及“備份壓縮”功能

作用：Xtrabackup對備份的數據文件支持“流”功能，即可以將備份的數據通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。

要使用此功能，僅需要使用--stream選項即可。如：

看不清截圖的可以看下面復制粘貼的命令：

# innobackupex --user=root --password="123456"--stream=tar /opt/mysqlbackup/full/ | gzip >/opt/mysqlbackup/full/full_`date+%F_%H%M%S`.tar.gz

（再補充一句，現實生產環境中，基本上都要用流與備份壓縮功能，因為這樣可以很大程度上節省空間）