快捷導(dǎo)航

MySQL數(shù)據(jù)備份之mysqldump的使用方法

更新時(shí)間：2021年11月23日 12:53:38 作者：MarkLogZhu

mysqldump常用于MySQL數(shù)據(jù)庫(kù)邏輯備份,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)備份之mysqldump使用的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

?一、mysqldump 簡(jiǎn)介

mysqldump 是 MySQL 自帶的邏輯備份工具。MySQLdump是一個(gè)數(shù)據(jù)庫(kù)邏輯備份程序，可以使用對(duì)一個(gè)或者多個(gè)mysql數(shù)據(jù)庫(kù)進(jìn)行備份或者將數(shù)據(jù)傳輸?shù)狡渌鹠ysql服務(wù)器。執(zhí)行mysqldump時(shí)需要賬戶(hù)擁有select權(quán)限才可以進(jìn)行備份數(shù)據(jù)表，show view權(quán)限用于備份視圖，trigger權(quán)限用于備份觸發(fā)器等。

mysqldump不是大數(shù)據(jù)備份的解決方案，因?yàn)閙ysqldump需要通過(guò)重建sql語(yǔ)句來(lái)實(shí)現(xiàn)備份功能，對(duì)于數(shù)據(jù)量比較大的數(shù)據(jù)庫(kù)備份與還原操作，速度比較慢，打開(kāi)mysqldump備份會(huì)發(fā)現(xiàn)里面其實(shí)就是數(shù)據(jù)庫(kù)sql語(yǔ)句的重現(xiàn)。

它的備份原理是通過(guò)協(xié)議連接到 MySQL 數(shù)據(jù)庫(kù)，將需要備份的數(shù)據(jù)查詢(xún)出來(lái)，將查詢(xún)出的數(shù)據(jù)轉(zhuǎn)換成對(duì)應(yīng)的insert 語(yǔ)句，當(dāng)我們需要還原這些數(shù)據(jù)時(shí)，只要執(zhí)行這些 insert 語(yǔ)句，即可將對(duì)應(yīng)的數(shù)據(jù)還原。

二、備份命令

2.1 命令格式

mysqldump [選項(xiàng)] 數(shù)據(jù)庫(kù)名 [表名] > 腳本名

或

mysqldump [選項(xiàng)] --數(shù)據(jù)庫(kù)名 [選項(xiàng) 表名] > 腳本名

或

mysqldump [選項(xiàng)] --all-databases [選項(xiàng)]? > 腳本名

2.2 選項(xiàng)說(shuō)明

參數(shù)名	縮寫(xiě)	含義
--host	-h	服務(wù)器IP地址
--port	-P	服務(wù)器端口號(hào)
--user	-u	MySQL 用戶(hù)名
--pasword	-p	MySQL 密碼
--databases		指定要備份的數(shù)據(jù)庫(kù)
--all-databases		備份mysql服務(wù)器上的所有數(shù)據(jù)庫(kù)
--compact		壓縮模式，產(chǎn)生更少的輸出
--comments		添加注釋信息
--complete-insert		輸出完成的插入語(yǔ)句
--lock-tables		備份前，鎖定所有數(shù)據(jù)庫(kù)表
--no-create-db/--no-create-info		禁止生成創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句
--force		當(dāng)出現(xiàn)錯(cuò)誤時(shí)仍然繼續(xù)備份操作
--default-character-set		指定默認(rèn)字符集
--add-locks		備份數(shù)據(jù)庫(kù)表時(shí)鎖定數(shù)據(jù)庫(kù)表

2.3 實(shí)例

備份所有數(shù)據(jù)庫(kù)：

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

備份指定數(shù)據(jù)庫(kù)：

mysqldump -uroot -p test > /backup/mysqldump/test.db

備份指定數(shù)據(jù)庫(kù)指定表(多個(gè)表以空格間隔)

mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.db

備份指定數(shù)據(jù)庫(kù)排除某些表

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

三、還原命令

3.1 系統(tǒng)行命令

mysqladmin -uroot -p create db_name 

mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注：在導(dǎo)入備份數(shù)據(jù)庫(kù)前，db_name如果沒(méi)有，是需要?jiǎng)?chuàng)建的；而且與db_name.db中數(shù)據(jù)庫(kù)名是一樣的才可以導(dǎo)入。

3.2 soure 方法

mysql > use db_name

mysql > source /backup/mysqldump/db_name.db

附：常用命令

mysqldump -u root -p --all-databases > D:/mysql.sql  #備份所有數(shù)據(jù)庫(kù)

mysqldump -uroot -p123456 --databases db1 db2 db3 > D:/mysql.sql  #備份多個(gè)數(shù)據(jù)庫(kù)

mysqldump -hhostname -Pport -uroot -p"123456" --databases dbname > D:/mysql.sql   #遠(yuǎn)程備份(遠(yuǎn)程時(shí)，需要多加入-h：主機(jī)名，-P：端口號(hào))



#帶條件導(dǎo)出指定表的部分?jǐn)?shù)據(jù),注意mysqldump導(dǎo)出時(shí)時(shí)區(qū)默認(rèn)設(shè)置為+00:00,w后面的timestamp時(shí)間也會(huì)被認(rèn)為是+00:00時(shí)區(qū)的數(shù)據(jù),從而導(dǎo)致數(shù)據(jù)有問(wèn)題

mysqldump -h222.222.221.197 -uroot -proot DBname TABLEname -t --complete-insert --skip-tz-utc -w"sys_create > '2020-12-25 16:00:00'">export.sql   



#一次性導(dǎo)出導(dǎo)入數(shù)據(jù)庫(kù)(!!!慎用,兩個(gè)服務(wù)器寫(xiě)反了就完蛋了,因?yàn)閷?dǎo)出的sql中有drop table語(yǔ)句)

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2 



#導(dǎo)出為壓縮包,這樣會(huì)小很多,線上服務(wù)器盡量用這個(gè)，推薦?。?！

mysqldump --opt -uroot -p123456 -h127.0.0.1 --databases dbname --ignore-table=dbname.table1 | gzip>/db_back/dbname_`date +%F`.zip