MySQL的備份-mysqldump命令的使用

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

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

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

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

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

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ī)備份(推薦使用的存儲引擎)

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

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

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

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

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

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

使用egrep查看備份的文件的sql內容

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

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

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

備份庫下的表

備份庫下的某個表

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

備份庫下的多個表

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

-d參數(shù) 備份表結構

[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ù) 刷新二進制日志

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

實例

多實例分庫備份

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

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

# 多實例情況(單實例把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

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

[root@localhost ~] # vim mysql_bakdb.sql

#!/bin/bash
#filename mysql_bakdb.sh

MYUSER=root # 數(shù)據(jù)庫用戶
MYPASS=aaaaaa # 用戶密碼
SOCKET=/data/3306/mysql.sock # 多實例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

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

多實例分表備份

典型備份語句(備份制定庫下的表)

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

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

[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)獲取當前庫下的所有表
	do
	${MYDUMP} ${database} ${table}|gzip > ${BAKDIR}/${database}/${table}.sql.gz
	done
done

分表備份缺點:文件多，碎。

1. 備份一個完整全備，再做一個分庫分表備份。

2. 腳本批量恢復多個SQL文件。