MySQL的備份-mysqldump命令的使用

數(shù)據(jù)備份的意義[運(yùn)維的工作]
>1. 保護(hù)公司的數(shù)據(jù)
>2. 網(wǎng)站7*24小時(shí)提供服務(wù)

利用mysqldump命令備份數(shù)據(jù)的過(guò)程，實(shí)際上就是把數(shù)據(jù)從mysql庫(kù)里以邏輯的sql語(yǔ)句的形式直接輸出或者生成備份的文件的過(guò)程。

備份數(shù)據(jù)庫(kù)的參數(shù)

mysqldump參數(shù)
-A(--all-databases) ：備份所有庫(kù)
-B(--databases) ：連接多個(gè)數(shù)據(jù)庫(kù),備份的數(shù)據(jù)中增加 建庫(kù)和use庫(kù)語(yǔ)句 (方便導(dǎo)入數(shù)據(jù))
-d(--no-data) ：只導(dǎo)出表結(jié)構(gòu)
-t(--no-create-info) ：只導(dǎo)出表數(shù)據(jù)
-x(--lock-all-tables) ：鎖表(無(wú)法對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作，影響大)
-l(--lock-tables) : 只讀鎖表
-F(--flush-logs) : 刷新binlog日志(備份數(shù)據(jù)時(shí)可能會(huì)用上)也可以在mysql客戶(hù)端執(zhí)行reset master;
--master-date=1/2 ：增加binlog日志文件名以及對(duì)應(yīng)的位置點(diǎn)。
gzip ：指定gzip進(jìn)行備份sql壓縮
--single-transaction ：適合InnoDB事務(wù)數(shù)據(jù)庫(kù)備份（InnoDB表在備份時(shí),通常啟用該參數(shù)來(lái)保證備份的一致性，他的工作原理是設(shè)定本次回話(huà)的隔離級(jí)別為: repeatable read,以確保本次回話(huà)dump時(shí)，不會(huì)看到其他會(huì)話(huà)已經(jīng)提交了的數(shù)據(jù)）
--compact ：參數(shù)優(yōu)化備份文件大小減少輸出注釋(debug調(diào)試時(shí)使用)
--default-chatacter-set=utf8 ：指定默認(rèn)字符集

mysql數(shù)據(jù)庫(kù)自帶一個(gè)很好用的備份命令mysqldump，它的語(yǔ)法為:
mysqldump -u 用戶(hù)名 -p 密碼 數(shù)據(jù)庫(kù)名 [表名] > 備份的文件名

Myisam表常規(guī)備份(參數(shù))

mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --triggers --routines --events --hex-blob --master-data=1 -x|gzip > ~/all.sql.gz

InnoDB表常規(guī)備份(推薦使用的存儲(chǔ)引擎)

mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --master-data=1 --triggers --routines --events --hex-blob --single-transaction|gzip > ~/all.sql.gz

普通方式備份數(shù)據(jù)庫(kù)

# 20160108新增一個(gè)數(shù)據(jù)庫(kù)備份語(yǔ)句(運(yùn)維推薦這個(gè)參數(shù) 理由:保證數(shù)據(jù)同步的同時(shí)還原操作可控)
mysqldum -uroot -paaaaaa --opt DbName >~/dbname.sql

# 多實(shí)例指定sock文件
mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock database > ~/database.sql

多實(shí)例指定sock文件備份數(shù)據(jù)庫(kù)test并使用gzip壓縮

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock test|gzip > ~/test.gz.sql

使用egrep查看備份的文件的sql內(nèi)容

egrep -v "#|\*|--|^$" ~/test.sql

-B參數(shù) 備份多個(gè)庫(kù)

[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock -B test mysql|gzip >~/mutil_db.sql.gz

備份庫(kù)下的表

備份庫(kù)下的某個(gè)表

[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbNAME tableName>~/dbName.sql

備份庫(kù)下的多個(gè)表

[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbName tableName tableName ..>~/dbName.sql

-d參數(shù) 備份表結(jié)構(gòu)

[root@localhost ~]# mysqldump -uroot -paaaaaa -d -S /data/3306/mysql.sock dbName tableName

-t參數(shù) 備份表數(shù)據(jù)

[root@localhost ~]# mysqldump -uroot -paaaaaa -t -S /data/3306/mysql.sock dbName tableName

-F參數(shù) 刷新二進(jìn)制日志

--master-data參數(shù) 備份記錄點(diǎn)

實(shí)例

多實(shí)例分庫(kù)備份

思路: 使用for循環(huán)查詢(xún)出數(shù)據(jù)庫(kù)中存在的庫(kù),然后分別使用mysqldump進(jìn)行備份

命令行實(shí)現(xiàn)備份

# 多實(shí)例情況(單實(shí)例把sock去掉即可)
[root@localhost /test]# mysql -uroot -p'aaaaaa' -S /data/3306/mysql.sock -e 'show databases;'|grep -Evi 'database|information_schema|mysql'|sed -r 's#^([a-z].+$)#mysqldump -uroot -p"aaaaaa" -S /data/3306/mysql.sock --events -B \1|gzip>/test/\1.sql.gz #g'|/bin/bash

腳本實(shí)現(xiàn)(多實(shí)例情況)

[root@localhost ~] # vim mysql_bakdb.sql

#!/bin/bash
#filename mysql_bakdb.sh

MYUSER=root # 數(shù)據(jù)庫(kù)用戶(hù)
MYPASS=aaaaaa # 用戶(hù)密碼
SOCKET=/data/3306/mysql.sock # 多實(shí)例sock文件地址
BAKDIR=~/mysqldatabak/`date +"%Y%m%d"`/ # 保存的文件地址
MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysql連接
MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysqldump連接

mkdir -p $BAKDIR # 創(chuàng)建文件夾

for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'`
do
${MYDUMP} ${database}|gzip > ${BAKDIR}${database}.sql.gz
done

分庫(kù)備份的意義
有時(shí)候一個(gè)數(shù)據(jù)庫(kù)實(shí)例中會(huì)有多個(gè)庫(kù),例如(blog,bbs..)但是出問(wèn)題的時(shí)候可能是其中的某一個(gè)庫(kù)，如果在備份時(shí)，把所有的庫(kù)都備份成一個(gè)數(shù)據(jù)文件的話(huà)，恢復(fù)某一個(gè)數(shù)據(jù)庫(kù)就比較麻煩

多實(shí)例分表備份

典型備份語(yǔ)句(備份制定庫(kù)下的表)

[root@curder.localhost ~]
# mysqldump -uroot -paaaaaa rose student  > ~/student.sql

思路：在分庫(kù)備份下再循環(huán)庫(kù)下show tables from dbName找到所有表對(duì)表進(jìn)行備份(無(wú)-B參數(shù)) 使用mysqldump拼接庫(kù)、表進(jìn)行備份

[root@localhost ~] # vim mysql_baktable.sh

#!/bin/bash
#filename mysql_baktable.sh

MYUSER=root
MYPASS=aaaaaa
SOCKET=/data/3306/mysql.sock
BAKDIR=~/`date +"%Y%m%d"`
MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}"
MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}"

for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'`
do
	mkdir -p ${BAKDIR}/${database}
	
	for table in `${MYCMD} -e "show tables from ${database};"|sed '1d'` # 循環(huán)獲取當(dāng)前庫(kù)下的所有表
	do
	${MYDUMP} ${database} ${table}|gzip > ${BAKDIR}/${database}/${table}.sql.gz
	done
done

分表備份缺點(diǎn):文件多，碎。

1. 備份一個(gè)完整全備，再做一個(gè)分庫(kù)分表備份。

2. 腳本批量恢復(fù)多個(gè)SQL文件。