Mysql備份系列（2）--mysqldump備份（全量+增量）方案操作記錄

在日常運維工作中，對mysql資料庫的備份是萬分重要的，以防在資料庫表丢失或損壞情況出現，可以及時恢複資料。

線上資料庫備份場景：

每周日執行一次全量備份，然後每天下午1點執行MySQLdump增量備份.

下面對這種備份方案詳細說明下：

1.MySQLdump增量備份配置

執行增量備份的前提條件是MySQL打開binlog日志功能，在my.cnf中加入

log-bin=/opt/Data/MySQL-bin

“log-bin=”後的字元串為日志記載目錄，一般建議放在不同于MySQL資料目錄的磁盤上。

1

2

3

4

5

6

7

8

9

<code>-----------------------------------------------------------------------------------</code>

<code>mysqldump &gt;       導出資料</code>

<code>mysql &lt;           導入資料  （或者使用</code><code>source</code><code>指令導入資料，導入前要先切換到對應庫下）</code>

<code>注意一個細節：</code>

<code>若是mysqldump導出一個庫的資料，導出檔案為a.sql，然後mysql導入這個資料到新的空庫下。</code>

<code>如果新庫名和老庫名不一緻，那麼需要将a.sql檔案裡的老庫名改為新庫名，</code>

<code>這樣才能順利使用mysql指令導入資料（如果使用</code><code>source</code><code>指令導入就不需要修改a.sql檔案了）。</code>

2.MySQLdump增量備份

假定星期日下午1點執行全量備份，适用于MyISAM存儲引擎。

[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test &gt; backup_sunday_1_PM.sql

對于InnoDB将--lock-all-tables替換為--single-transaction

--flush-logs為結束目前日志，生成新日志檔案；

--master-data=2 選項将會在輸出SQL中記錄下完全備份後新日志檔案的名稱，

用于日後恢複時參考，例如輸出的備份SQL檔案中含有：

CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;

3.MySQLdump增量備份其他說明：

如果MySQLdump加上–delete-master-logs 則清除以前的日志，以釋放空間。但是如果伺服器配置為鏡像的複制主伺服器，用MySQLdump –delete-master-logs删掉MySQL二進制日志很危險，因為從伺服器可能還沒有完全處理該二進制日志的内容。在這種情況下，使用 PURGE MASTER LOGS更為安全。

每日定時使用 MySQLadmin flush-logs來建立新日志，并結束前一日志寫入過程。并把前一日志備份，例如上例中開始儲存資料目錄下的日志檔案 MySQL-bin.000002 , ...

1.恢複完全備份

mysql -u root -p &lt; backup_sunday_1_PM.sql

2.恢複增量備份

mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢複過程亦會寫入日志檔案，如果資料量很大，建議先關閉日志功能

--compatible=name

它告訴 MySQLdump，導出的資料将和哪種資料庫或哪個舊版本的 MySQL 伺服器相相容。值可以為 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用幾個值，用逗号将它們隔開。當然了，它并不保證能完全相容，而是盡量相容。

--complete-insert，-c

導出的資料采用包含字段名的完整 INSERT 方式，也就是把所有的值都寫在一行。這麼做能提高插入效率，但是可能會受到 max_allowed_packet 參數的影響而導緻插入失敗。是以，需要謹慎使用該參數，至少我不推薦。

--default-character-set=charset

指定導出資料時采用何種字元集，如果資料表不是采用預設的 latin1 字元集的話，那麼導出時必須指定該選項，否則再次導入資料後将産生亂碼問題。

--disable-keys

告訴 MySQLdump 在 INSERT 語句的開頭和結尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 語句，這能大大提高插入語句的速度，因為它是在插入完所有資料後才重建索引的。該選項隻适合 MyISAM 表。

--extended-insert = true|false

預設情況下，MySQLdump 開啟 --complete-insert 模式，是以不想用它的的話，就使用本選項，設定它的值為 false 即可。

--hex-blob

使用十六進制格式導出二進制字元串字段。如果有二進制資料就必須使用本選項。影響到的字段類型有 BINARY、VARBINARY、BLOB。

--lock-all-tables，-x

在開始導出之前，送出請求鎖定所有資料庫中的所有表，以保證資料的一緻性。這是一個全局讀鎖，并且自動關閉 --single-transaction 和 --lock-tables 選項。

--lock-tables

它和 --lock-all-tables 類似，不過是鎖定目前導出的資料表，而不是一下子鎖定全部庫下的表。本選項隻适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 選項。

--no-create-info，-t

隻導出資料，而不添加 CREATE TABLE 語句。

--no-data，-d

不導出任何資料，隻導出資料庫表結構。

mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 &gt; test.dump

将隻備份表結構。--databases訓示主機上要備份的資料庫。

--opt

這隻是一個快捷選項，等同于同時添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 選項。本選項能讓 MySQLdump 很快的導出資料，并且導出的資料能很快導回。該選項預設開啟，但可以用 --skip-opt 禁用。注意，如果運作 MySQLdump 沒有指定 --quick 或 --opt 選項，則會将整個結果集放在記憶體中。如果導出大資料庫的話可能會出現問題。

--quick，-q

該選項在導出大表時很有用，它強制 MySQLdump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後将它們緩存到記憶體中。

--routines，-R

導出存儲過程以及自定義函數。

--single-transaction

該選項在導出資料之前送出一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證導出時資料庫的一緻性狀态。它隻适用于事務表，例如 InnoDB 和 BDB。本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何挂起的事務隐含送出。要想導出大表的話，應結合使用 --quick 選項。

--triggers

同時導出觸發器。該選項預設啟用，用 --skip-triggers 禁用它。

跨主機備份

使用下面的指令可以将host1上的sourceDb複制到host2的targetDb，前提是host2主機上已經建立targetDb資料庫：

-C 訓示主機間的資料傳輸使用資料壓縮

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

結合Linux的cron指令實作定時備份

比如需要在每天淩晨1:30備份某個主機上的所有資料庫并壓縮dump檔案為gz格式

30 1 * * * mysqldump -u root -pPASSWORD --all-databases | gzip &gt; /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

一個完整的Shell腳本備份MySQL資料庫示例。比如備份資料庫opspc

[root@test-huanqiu ~]# vim /root/backup.sh

#!bin/bash

echo "Begin backup mysql database"

mysqldump -u root -ppassword opspc &gt; /home/backup/mysqlbackup-`date +%Y-%m-%d`.sql

echo "Your database backup successfully completed"

[root@test-huanqiu ~]# crontab -e

30 1 * * * /bin/bash -x /root/backup.sh &gt; /dev/null 2&gt;&amp;1

mysqldump全量備份+mysqlbinlog二進制日志增量備份

1）從mysqldump備份檔案恢複資料會丢失掉從備份點開始的更新資料，是以還需要結合mysqlbinlog二進制日志增量備份。

首先確定已開啟binlog日志功能。在my.cnf中包含下面的配置以啟用二進制日志：

[mysqld]

log-bin=mysql-bin

2）mysqldump指令必須帶上--flush-logs選項以生成新的二進制日志檔案：

mysqldump --single-transaction --flush-logs --master-data=2 &gt; backup.sql

其中參數--master-data=[0|1|2]

0: 不記錄

1：記錄為CHANGE MASTER語句

2：記錄為注釋的CHANGE MASTER語句

mysqldump全量+增量備份方案的具體操作可參考下面兩篇文檔：

<a href="http://www.cnblogs.com/kevingrace/p/5904800.html" target="_blank">資料庫誤删除後的資料恢複操作說明</a>

<a href="http://www.cnblogs.com/kevingrace/p/5907254.html" target="_blank">解說mysql之binlog日志以及利用binlog日志恢複資料</a>

--------------------------------------------------------------------------

下面分享一下自己用過的mysqldump全量和增量備份腳本

應用場景：

1）增量備份在周一到周六淩晨3點，會複制mysql-bin.00000*到指定目錄；

2）全量備份則使用mysqldump将所有的資料庫導出，每周日淩晨3點執行，并會删除上周留下的mysq-bin.00000*，然後對mysql的備份操作會保留在bak.log檔案中。

腳本實作：

1）全量備份腳本（假設mysql登入密碼為123456；注意腳本中的指令路徑）：

[root@test-huanqiu ~]# vim /root/Mysql-FullyBak.sh

#!/bin/bash

# Program

# use mysqldump to Fully backup mysql data per week!

# History

# Path

BakDir=/home/mysql/backup

LogFile=/home/mysql/backup/bak.log

Date=`date +%Y%m%d`

Begin=`date +"%Y年%m月%d日 %H:%M:%S"`

cd $BakDir

DumpFile=$Date.sql

GZDumpFile=$Date.sql.tgz

/usr/local/mysql/bin/mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction &gt; $DumpFile

/bin/tar -zvcf $GZDumpFile $DumpFile

/bin/rm $DumpFile

Last=`date +"%Y年%m月%d日 %H:%M:%S"`

echo 開始:$Begin 結束:$Last $GZDumpFile succ &gt;&gt; $LogFile

cd $BakDir/daily

/bin/rm -f *

2）增量備份腳本（腳本中mysql的資料存放路徑是/home/mysql/data，具體根據自己的實際情況進行調整）

[root@test-huanqiu ~]# vim /root/Mysql-DailyBak.sh

# use cp to backup mysql data everyday!

BakDir=/home/mysql/backup/daily                     //增量備份時複制mysql-bin.00000*的目标目錄，提前手動建立這個目錄

BinDir=/home/mysql/data                                   //mysql的資料目錄

BinFile=/home/mysql/data/mysql-bin.index           //mysql的index檔案路徑，放在資料目錄下的

/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs

#這個是用于産生新的mysql-bin.00000*檔案

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

NextNum=0

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

for file in `cat $BinFile`

do

    base=`basename $file`

    #basename用于截取mysql-bin.00000*檔案名，去掉./mysql-bin.000005前面的./

    NextNum=`expr $NextNum + 1`

    if [ $NextNum -eq $Counter ]

    then

        echo $base skip! &gt;&gt; $LogFile

    else

        dest=$BakDir/$base

        if(test -e $dest)

        #test -e用于檢測目标檔案是否存在，存在就寫exist!到$LogFile去

        then

            echo $base exist! &gt;&gt; $LogFile

        else

            cp $BinDir/$base $BakDir

            echo $base copying &gt;&gt; $LogFile

         fi

     fi

done

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

3）設定crontab任務，執行備份腳本。先執行的是增量備份腳本，然後執行的是全量備份腳本：

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

0 3 * * 0 /bin/bash -x /root/Mysql-FullyBak.sh &gt;/dev/null 2&gt;&amp;1

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

0 3 * * 1-6 /bin/bash -x /root/Mysql-DailyBak.sh &gt;/dev/null 2&gt;&amp;1

4）手動執行上面兩個腳本，測試下備份效果

[root@test-huanqiu backup]# pwd

/home/mysql/backup

[root@test-huanqiu backup]# mkdir daily

[root@test-huanqiu backup]# ll

total 4

drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily

[root@test-huanqiu backup]# ll daily/

total 0

先執行增量備份腳本

[root@test-huanqiu backup]# sh /root/Mysql-DailyBak.sh 

total 8

-rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log

-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030

-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031

[root@test-huanqiu backup]# cat bak.log 

mysql-binlog.000030 copying

mysql-binlog.000031 copying

mysql-binlog.000032 skip!

2016年11月29日 11:29:32 Bakup succ!

然後執行全量備份腳本

[root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh 

20161129.sql

total 152

-rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz

-rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log

drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily

開始:2016年11月29日 11:30:38 結束:2016年11月29日 11:30:38 20161129.sql.tgz succ

***************當你發現自己的才華撐不起野心時，就請安靜下來學習吧***************

本文轉自散盡浮華部落格園部落格，原文連結：http://www.cnblogs.com/kevingrace/p/6114979.html，如需轉載請自行聯系原作者