MySQL數據備份之mysqldump的使用方法

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

mysqldump常用于MySQL數據庫邏輯備份,這篇文章主要給大家介紹了關于MySQL數據備份之mysqldump使用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

?一、mysqldump 簡介

mysqldump 是 MySQL 自帶的邏輯備份工具。MySQLdump是一個數據庫邏輯備份程序，可以使用對一個或者多個mysql數據庫進行備份或者將數據傳輸到其他mysql服務器。執(zhí)行mysqldump時需要賬戶擁有select權限才可以進行備份數據表，show view權限用于備份視圖，trigger權限用于備份觸發(fā)器等。

mysqldump不是大數據備份的解決方案，因為mysqldump需要通過重建sql語句來實現備份功能，對于數據量比較大的數據庫備份與還原操作，速度比較慢，打開mysqldump備份會發(fā)現里面其實就是數據庫sql語句的重現。

它的備份原理是通過協(xié)議連接到 MySQL 數據庫，將需要備份的數據查詢出來，將查詢出的數據轉換成對應的insert 語句，當我們需要還原這些數據時，只要執(zhí)行這些 insert 語句，即可將對應的數據還原。

二、備份命令

2.1 命令格式

mysqldump [選項] 數據庫名 [表名] > 腳本名

或

mysqldump [選項] --數據庫名 [選項表名] > 腳本名

或

mysqldump [選項] --all-databases [選項]? > 腳本名

2.2 選項說明

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

2.3 實例

備份所有數據庫：

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

備份指定數據庫：

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

備份指定數據庫指定表(多個表以空格間隔)

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

備份指定數據庫排除某些表

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

注：在導入備份數據庫前，db_name如果沒有，是需要創(chuàng)建的；而且與db_name.db中數據庫名是一樣的才可以導入。

3.2 soure 方法

mysql > use db_name

mysql > source /backup/mysqldump/db_name.db

附：常用命令

mysqldump -u root -p --all-databases > D:/mysql.sql  #備份所有數據庫

mysqldump -uroot -p123456 --databases db1 db2 db3 > D:/mysql.sql  #備份多個數據庫

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



#帶條件導出指定表的部分數據,注意mysqldump導出時時區(qū)默認設置為+00:00,w后面的timestamp時間也會被認為是+00:00時區(qū)的數據,從而導致數據有問題

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   



#一次性導出導入數據庫(!!!慎用,兩個服務器寫反了就完蛋了,因為導出的sql中有drop table語句)

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



#導出為壓縮包,這樣會小很多,線上服務器盡量用這個，推薦！?。?

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