使用mysqldump對MySQL的數據進行備份的操作教程

MySQL 自身的 mysqldump 工具支持單線程工作，依次一個個導出多個表，沒有一個并行的機，這就使得它無法迅速的備份數據。

mydumper 作為一個實用工具，能夠良好支持多線程工作，可以并行的多線程的從表中讀入數據并同時寫到不同的文件里，這使得它在處理速度方面快于傳統的 mysqldump 。其特征之一是在處理過程中需要對列表加以鎖定，因此如果我們需要在工作時段執行備份工作，那么會引起 DML 阻塞。但一般現在的 MySQL 都有主從，備份也大部分在從上進行，所以鎖的問題可以不用考慮。這樣， mydumper 能更好的完成備份任務。

mydumper 特性

多線程備份
因為是多線程邏輯備份，備份后會生成多個備份文件
備份時對 MyISAM 表施加 FTWRL (FLUSH TABLES WITH READ LOCK), 會阻塞 DML 語句
保證備份數據的一致性
支持文件壓縮
支持導出binlog
支持多線程恢復
支持以守護進程模式工作，定時快照和連續二進制日志
支持將備份文件切塊

mydumper 備份機制

mydumper 工作流程圖

使用mysqldump對MySQL的數據進行備份的操作教程

主要步驟概括

主線程 FLUSH TABLES WITH READ LOCK , 施加全局只讀鎖，以阻止 DML 語句寫入，保證數據的一致性
讀取當前時間點的二進制日志文件名和日志寫入的位置并記錄在 metadata 文件中，以供即使點恢復使用
N 個（線程數可以指定，默認是 4 ） dump 線程 START TRANSACTION WITH CONSISTENT SNAPSHOT ; 開啟讀一致的事物
dump non-InnoDB tables , 首先導出非事物引擎的表
主線程 UNLOCK TABLES 非事物引擎備份完后，釋放全局只讀鎖
dump InnoDB tables , 基于事物導出 InnoDB 表
事物結束
備份所生成的文件

所有的備份文件在一個目錄中，目錄可以自己指定
目錄中包含一個 metadata 文件
記錄了備份數據庫在備份時間點的二進制日志文件名，日志的寫入位置，

如果是在從庫進行備份，還會記錄備份時同步至主庫的二進制日志文件及寫入位置

每個表有兩個備份文件：

database.table-schema.sql 表結構文件
database.table.sql 表數據文件

如果對表文件分片，將生成多個備份數據文件，可以指定行數或指定大小分片

安裝使用實例

假設現有2臺DB服務器，分別用于A業務與B業務，其中A業務比較重要，需要對A業務的1個DB（TaeOss）進行熱備，大概有40G的數據，并用業務B的DB服務器作為備機，服務器分布如下：
10.137.143.151 A業務
10.137.143.152 B業務

假設要達到的要求是：
在導出A業務的DB（TaeOss）時，不能對A業務有影響。同時在B業務的DB服務器上進行恢復時，也不能有較大影響，盡量控制在1分鐘以內。

采取的方案：
1、mysqldump：屬于邏輯備份，會存在鎖表，但考慮到數據量比較大，鎖表的時間會比較長，業務不允許，pass掉；
2、xtrabackup：屬于物理備份，不存在鎖表，但考慮到2臺DB使用的都是共享表空間，同時在業務B的數據庫進行恢復時，一是時間比較長，二是數據肯定不正確，pass掉（測試過）；
3、mydumper：屬于邏輯備份，是一個多線程、高性能的數據邏輯備份、恢復的工具，且鎖表的時間很短（40G數據，10分鐘以內），同時會記錄binlog file和pos，業務可以接受。

mydumper主要有如下特性：
(1)、任務速度要比mysqldump快6倍以上；
(2)、事務性和非事務性表一致的快照（適用于0.2.2以上版本）；
(3)、快速的文件壓縮；
(4)、支持導出binlog；
(5)、多線程恢復（適用于0.2.1以上版本）；
(6)、以守護進程的工作方式，定時快照和連續二進制日志（適用于0.5.0以上版本）。

mydumper安裝：
https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz

# yum install glib2-devel mysql-devel zlib-devel pcre-devel# tar zxvf mydumper-0.6.2.tar.gz# cd mydumper-0.6.2# cmake .# make# make install

參數如下：

使用mysqldump對MySQL的數據進行備份的操作教程

由于DB是部署在比較老的SuSE Linux 10服務器上，安裝mydumper時依賴的庫比較多，會比較繁瑣，同時采用本地備份的話，也會占用大量的磁盤I/O，所以我們選擇在同網段的另一臺centos 6.4（10.137.143.156）服務器進行備份。

步驟如下：
1、在“10.137.143.151、10.137.143.152”上對“10.137.143.156”進行臨時授權

# mysql -uroot -e "grant all privileges on *.* to 'backup'@'10.137.143.156' identified by 'backup2015';"# mysql -uroot -e "flush privileges;"

2、在“10.137.143.156”上對“10.137.143.151”的DB（TaeOss）進行備份

# mydumper -h 10.137.143.151 -u backup -p backup2015 -B TaeOss -t 8 -o /data/rocketzhang

3、將備份數據恢復到“10.137.143.152”

# myloader -h 10.137.143.152 -u backup -p backup2015 -B TaeOss -t 8 -o -d /data/rocketzhang

4、主從關系建立：10.137.143.151（主）、10.137.143.152（從）
在“10.137.143.151”建立授權賬號：

# mysql -uroot -e "grant replication slave on *.* to 'repl'@'10.137.143.152' identified by 'repl123456';"# mysql -uroot -e "flush privileges;"

在“10.137.143.156”查看記錄下的binlog信息：

使用mysqldump對MySQL的數據進行備份的操作教程

在“10.137.143.152”如下操作：

# vim /etc/my.cnf……replicate-do-table = TaeOss.%replicate-wild-do-table = TaeOss.%…… # service mysqld reload # mysql -uroot -e "change master to master_host='10.137.143.151',master_user='repl',master_password='repl123456',master_log_file='mysql-bin.002205',master_log_pos=456584891;"# mysql -uroot -e "start slave;" # mysql -uroot -e "show slave status/G;"

出現如下信息：

使用mysqldump對MySQL的數據進行備份的操作教程

看來是存在主鍵沖突，導致主從復制失敗。

問題分析：
在主DB（10.137.143.151）上執行：

# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.002205 > mysql-bin.002205.txt# grep -C 8 529864938 mysql-bin.002205.txt

使用mysqldump對MySQL的數據進行備份的操作教程

大概的意思是，在主DB上存在對t_evil_detect_uin_blacklist表的insert操作時，發生了主鍵沖突，當在從端進行同步的時候，也出現了主鍵沖突，從而導致主從同步失敗。

臨時的解決辦法：
導出從端的表TaeOss.t_evil_detect_uin_blacklist

# mysqldump -uroot --opt TaeOss t_evil_detect_uin_blacklist > TaeOss.t_evil_detect_uin_blacklist.sql

去掉TaeOss.t_evil_detect_uin_blacklist.sql其中的主鍵語句：

使用mysqldump對MySQL的數據進行備份的操作教程

然后再導入：

# mysql -uroot TaeOss < TaeOss.t_evil_detect_uin_blacklist.sql # mysql -uroot -e "stop slave;"# mysql -uroot -e "start slave;"# mysql -uroot -e "show slave status/G;"

使用mysqldump對MySQL的數據進行備份的操作教程