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

部落客QQ：819594300

有什麼疑問的朋友可以聯系部落客，部落客會幫你們解答，謝謝支援！一、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.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

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

# 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

wjLNWBMBAAAAMAAAAFMBAAAAAGwAAAAAAAEABGJkcW4AAml0AAIDDwI8AALyyJ5K

wjLNWCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wBAAAACHpoYW5nc2FuqD2KLg==

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

### @1=1

### @2='zhangsan'

# at 388

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

③開始使用全庫備份後的增量備份的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 protected]

#定義備份日志檔案

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

#寫日志檔案

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

cat $emailfile >> $logfile

#發送郵件通知

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

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

#Name:mysqlDailyBackup.sh

mysqldir=/usr/local/mysql

datadir=$mysqldir/data

#定義備份目錄、每日備份檔案備份到$databackupdir/daily

dailybackupdir=$databackupdir/daily

#定義日志檔案

#重新整理日志，使資料庫使用新的二進制日志檔案

$mysqldir/bin/mysqladmin -u$user -p$userpwd --flush-logs

cd $datadir

#得到二進制日志清單

filelist=`cat mysql-bin.index`

icounter=0

for file in $filelist

icounter=`exper$icounter + 1`

done

nextnum=0

ifile=0

binlogname=`basename $file`

nextnum=`expr$nextnum + 1`

#跳過最後一個二進制日志（資料庫目前使用的二進制日志檔案）

if [ $nextnum -eq $icounter ]; then

echo "Skiplastest!" > /dev/null

dest=$dailybackupdir/$binlogname

#跳過已經備份的二進制日志檔案

if [ -e $dest ]; then

echo "Skipexist $binlogname!" > /dev/null

#備份日志檔案到備份目錄

cp $binlogname $dailybackupdir

ifile=`expr $ifile + 1`

echo "$binlogname backup success!" >>$emailfile

if [ $ifile -eq 0 ]; then

echo "NoBinlog Backup!" >> $emailfile

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

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

echo"-----------------------------------------" >> $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

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并安裝

至此就完成了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

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

本文轉自Mr大表哥部落格，原文連結： http://blog.51cto.com/zpf666/1913451 如需轉載請自行聯系原作者

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

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述