分享點(diǎn)自己近年來接觸MySQL數(shù)據(jù)備份這一塊的小經(jīng)驗(yàn)。數(shù)據(jù)是一個(gè)互聯(lián)網(wǎng)公司的命脈，數(shù)據(jù)庫的安全以及備案的完整性是至關(guān)重要的，所以我們需要在工作中要很熟練的掌握數(shù)據(jù)的備份與恢復(fù),這也是一個(gè)合格的運(yùn)維DBA必須具有的職業(yè)技能。

我這里簡(jiǎn)單總結(jié)MySQL的備份分為3種：分為冷備份，邏輯備份，熱備份。

1、冷備份：

一般主要用于非核心業(yè)務(wù)，這類業(yè)務(wù)一般都是允許業(yè)務(wù)中斷的，冷備份的特點(diǎn)就是數(shù)度快，恢復(fù)時(shí)也最為簡(jiǎn)單。通常直接復(fù)物理文件來實(shí)現(xiàn)冷備份。

1.1備份過程：

***關(guān)閉mysql服務(wù)

第二步就是把datas數(shù)據(jù)目錄（包含ibdata1）和日志目錄(包含ib_logfile0,ib_logfile1,ib_logfile2)復(fù)制到磁盤，或者本地的另一塊磁盤里

1.2恢復(fù)過程：

***使用復(fù)制的數(shù)據(jù)目錄和日志目錄替換原有的目錄

第二就是啟動(dòng)mysql

2、邏輯備份MySQLdump

生產(chǎn)場(chǎng)景備份的命令：

生產(chǎn)場(chǎng)景不同引擎mysqldump備份命令

myisam引擎企業(yè)生產(chǎn)備份命令（適合所有引擎或混合引擎）:

mysqldump -uroot -p123456 -A -B -F -R --master-data=2 -x --events|gzip >/opt/all.sql.gz 

提示：-F也可以不用，與--master-data有些重復(fù)。

innodb引擎企業(yè)生產(chǎn)備份命令:推薦使用的

mysqldump -uroot -p123456 -A -B -F -R --master-data=2  --default-character-set=utf8  --events --single-transaction |gzip >/opt/all.sql.gz 

提示：-F也可以不用。與--master-data有些重復(fù)。

--default-character-set=utf8 指定備份出的sql數(shù)據(jù)的字符集為utf8，當(dāng)然，這個(gè)要提前知道線上的mysql庫全部采用的字符集都是utf8，否則在恢復(fù)到線上的庫會(huì)出現(xiàn)字符集不一致的問題出現(xiàn)亂碼

提示：邏輯備份一般是數(shù)據(jù)遷移或者數(shù)據(jù)量很小時(shí)采用，邏輯備份采用的是數(shù)據(jù)導(dǎo)出的方式

2.1mysqldump庫表等備份說明：

全備：

Mysqldump –uroot –p  -q –single-transaction –A >>all.sql 

導(dǎo)出多個(gè)數(shù)據(jù)庫：

Mysqldump –uroot –p  -q –single-transaction –B  test01 wjw01 test02 >test01_wjw01_test02.sql 

導(dǎo)出一個(gè)test庫的某張wjw01表：

Mysqldump –uroot –p  -q –single-transaction –b  test  wjw01>>test_wjw01.sql 

只導(dǎo)出表結(jié)構(gòu)：

Mysqldump –uroot –q –d  --skip-triggers 

只需要導(dǎo)出儲(chǔ)存過程時(shí)：

Mysqldump –uroot –q -Rtdn  --skip-triggers 

只需要導(dǎo)出觸發(fā)器：

Mysqldump –uroot –p –q –tdn –triggers 

只需要導(dǎo)出事件：

Mysqldump –uroot –p –q –Etdn –skip-triggers

只需要導(dǎo)出數(shù)據(jù)：

Mysqldump –uroot –p –q  --skip-transaction --skip-triggers –t 

要想在線上建立一臺(tái)新的slave，則在master上執(zhí)行如下命令：

Mysqldump –uroot –p –q –single-transaction –master-data=2 -A >>all.sql 

提示：在mysql5.5里增加了一個(gè)新的參數(shù)：--dump-slave,使用該參數(shù)可在slave端dump數(shù)據(jù)，建立新的slave，其目的是為了防止對(duì)主庫造成過大的壓力。

在slave上執(zhí)行以下命令：

Mysqldump  -uroot –p  -A –dump-slave=2 –q –single-transaction >/tmp/all.sql 

查看alls.sql 里面會(huì)記錄slave上的那個(gè)點(diǎn)。

注意：--dump-slave用于在slave上dump數(shù)據(jù)，建立新的slave

2.2Mysqldump優(yōu)缺點(diǎn)以及使用場(chǎng)景

優(yōu)點(diǎn)：

1. 恢復(fù)簡(jiǎn)單，可以使用管道將他們輸入到mysql
2. 與存儲(chǔ)引擎無關(guān)，因?yàn)槭菑腗ySQL服務(wù)器中提取數(shù)據(jù)而生成的，所以消除了底層數(shù)據(jù)存儲(chǔ)的不同
3. 有助于避免數(shù)據(jù)損壞。若磁盤驅(qū)動(dòng)器有故障而要復(fù)制原始文件時(shí)，此時(shí)將得到一個(gè)損壞的備份

缺點(diǎn)：

1. 必須有數(shù)據(jù)庫服務(wù)器完成邏輯工作，需要更多地cpu周期
2.  邏輯備份還原速度慢：需要MySQL加載和解釋語句、轉(zhuǎn)化存儲(chǔ)格式、重建引擎

使用場(chǎng)景：對(duì)于MySQL數(shù)據(jù)庫數(shù)據(jù)量不是很大的場(chǎng)景，建議使用。因?yàn)閭浞莘奖?，?jiǎn)單靈活，易操作

當(dāng)數(shù)據(jù)庫特別的時(shí)候，比如30G以上時(shí)，備份MySQL數(shù)據(jù)時(shí)，也就建議不要采用mysqldump了，因?yàn)閭浞莸臅r(shí)間以及恢復(fù)數(shù)據(jù)庫的時(shí)間太長(zhǎng)了，會(huì)對(duì)庫表造成鎖，對(duì)線上的業(yè)務(wù)影響還是比較大的

2.3簡(jiǎn)單介紹下MySQL全量備份+增量備份binlog

注意：生產(chǎn)上強(qiáng)烈建議MySQL開啟Row格式來記錄binlog,盡管這樣對(duì)磁盤的IO以及磁盤的空間的消耗是比較大，但是和數(shù)據(jù)的安全性、完整性相比，磁盤資源那都是小事

全量備份腳本內(nèi)容：

#!/bin/bash  
#mysql全量備份腳本建議在slave從庫上運(yùn)行，從庫slave上建議開啟參數(shù)log_slave_updates=1  
mkdir /backup  
cd /backup  
dateDIR=$(date +"%Y-%m-%d")  
mkdir -p $dateDIR/data  
path=/data/mysql/data  
for n in `mysql -uroot -p123456 -e "show databases"|grep -v "Database"`  
do  
mysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B $n|gzip >/backup/$dateDIR/data/${n}_$dateDIR.sql.gz  
done  
binlog_rm=$(tail -n 1 $path/mysql-bin.index|sed 's/.\///')  
mysql -uroot -p123456 -e "purge binary logs to '$binlog_rm'" 

說明:這個(gè)全量備份腳本，會(huì)在導(dǎo)出的時(shí)候鎖住全局表，并且此時(shí)刷新產(chǎn)生一個(gè)新的bin-log,期間會(huì)產(chǎn)生寫操作的等待，直到導(dǎo)出結(jié)束后才會(huì)寫入新產(chǎn)生的bin-log文件，然后舊的bin-log 文件會(huì)被刪除刪除掉，一般在晚上2:00業(yè)務(wù)低峰期執(zhí)行操作

進(jìn)行本地bin-log增量備份

在執(zhí)行完全量備份腳本后，就可以執(zhí)行增量備份腳本了。

增量備份腳本思路很簡(jiǎn)單：

先mysqladmin -uroot -p123456 flush-logs 刷新新的binlog文件，此時(shí)mysql寫入到新的binlog文件中。

然后把當(dāng)前mysql數(shù)據(jù)庫存放binlog的目錄中拋去剛才***生成的binlog文件，其余舊的binlog文件全部cp到本地服務(wù)器/backup/binlog  binlog的備份目錄下。

***在登錄MySQL清除當(dāng)前的binlog文件數(shù)減一

mysql -uroot -p123456 -e "purge binary logs to 'mysql-bin.(n-1)'" 

3.熱備份與恢復(fù)

熱備份的方式也是直接復(fù)制數(shù)據(jù)物理文件，和冷備份一樣，但是熱備份可以不停機(jī)直接復(fù)制，一般用于7*24小時(shí)不間斷的重要核心業(yè)務(wù)。Mysql的社區(qū)版本熱備份工具InnoDB Hot Backup是付費(fèi)的，只能試用30天，商業(yè)版才可以***的使用，

Percona公司發(fā)布一個(gè)xtrabackup熱備工具，和付費(fèi)的工具一樣，支持在線熱備（備份時(shí)不影響數(shù)據(jù)的讀寫）是商業(yè)工具InnoDB Hot Backup的一個(gè)很好的替代。

xtrabackup熱備工具是一個(gè)開源的工具，他可以非?？焖俚膫浞莺突謴?fù)mysql數(shù)據(jù)庫。由于Xtrabackup支持備份innodb表，實(shí)際生產(chǎn)環(huán)境中我們使用的工具是innobackupex，它是對(duì)xtrabackup的一層封裝。innobackupex腳本用來備份非 InnoDB 表，同時(shí)會(huì)調(diào)用 xtrabackup命令來備份 InnoDB 表，innobackupex的基本流程如下：

1. 開啟redo日志拷貝線程，從***的檢查點(diǎn)開始順序拷貝redo日志；
2. 開啟idb文件拷貝線程，拷貝innodb表的數(shù)據(jù)
3. idb文件拷貝結(jié)束，通知調(diào)用FTWRL，獲取一致性位點(diǎn)
4. 備份非innodb表(系統(tǒng)表)和frm文件
5. 由于此時(shí)沒有新事務(wù)提交，等待redo日志拷貝完成
6. ***的redo日志拷貝完成后，相當(dāng)于此時(shí)的innodb表和非innodb表數(shù)據(jù)都是***的
7. 獲取binlog位點(diǎn)，此時(shí)數(shù)據(jù)庫的狀態(tài)是一致的。
8. 釋放鎖，備份結(jié)束。

具體使用請(qǐng)參考：https://www.linuxidc.com/Linux/2018-08/153635.htm

關(guān)于MySQL數(shù)據(jù)庫的備份方案到此處就介紹完了，如有不對(duì)，請(qǐng)及時(shí)指出。歡迎一起交流學(xué)習(xí)。

Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx

本文***更新鏈接地址：https://www.linuxidc.com/Linux/2018-08/153634.htm