mysqldump工具在MySQL資料庫備份過程的作用
釋出時間：2020-05-07 10:57:16
來源：億速雲
閱讀：176
作者：三月
下文主要給大家帶來mysqldump工具在MySQL資料庫備份過程的作用，希望這些文字能夠帶給大家實際用處，這也是我編輯mysqldump工具在MySQL資料庫備份過程的作用這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。
mysqldump工具是MySQL資料庫備份時, 經常用到的一個工具. 可以指定資料表, 某些資料庫, 所有資料庫級别的備份, 在指令行上敲入mysqldump回車, 可看到該三種方式的示例.
[email protected]:~$ mysqldump
Usage: mysqldump[OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS]DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases[OPTIONS]
For moreoptions, use mysqldump --help
通過mysqldump --help, 可看到有非常多的選項(OPTIONS), 也見其功能的豐富性. 這些選項按作用, 可劃分成若幹組, 接下來就看下每組中重點選項的含義.
HelpOptions 組
1.--version
該組除了--help選項, 還有一個--version, 用于檢視mysqldump的版本.
[email protected]:~$ mysqldump --version
mysqldump Ver 10.13 Distrib 5.6.24, for Linux (x86_64)
另外, 為了下面的說明示範, 用到的資料庫和資料表如下所示:
([email protected])[(none)]> SHOW DATABASES;
+--------------------+
|Database |
+--------------------+
|information_schema |
|mysql |
|performance_schema |
|stage |
|test |
+--------------------+
5 rows inset (0.00 sec)
([email protected])[(none)]> SHOW TABLES FROM stage;
+-----------------+
|Tables_in_stage |
+-----------------+
|st1 |
|st2 |
+-----------------+
2 rows inset (0.00 sec)
([email protected])[(none)]> SHOW TABLES FROM test;
+----------------+
|Tables_in_test |
+----------------+
|tb1_bak |
|tb2 |
|v_tb1 |
|v_tb2 |
+----------------+
4 rows inset (0.00 sec)
ConnectionOptions 組
1.--bind-address=ip_address
若資料庫雲伺服器有多個網絡接口, 使用該選項設定其隻監聽在某個接口上, 且隻能通過該接口通路資料庫.
2.--login-path=name
登入路徑, 其是host, password, port, socket, user選項的集合. 後面的示範, 使用該登入方式, 相關細節已在"MySQL資料庫登入小貼士"中介紹過.
Option-FileOptions 組
1.--defaults-file=file_name
mysqldump隻讀取該選項指定的配置檔案中, client和mysqldump标簽下的參數. 若同時還指定了--login-path選項, 其還會讀取檔案.mylogin.cnf.
2.--no-defaults
mysqldump不讀取任何配置檔案, 一般用于讀到錯誤參數時的調試. 若同時還指定了--login-path選項, 其還會讀取檔案.mylogin.cnf.
3.--print-defaults
mysqldump會按此順序/etc/my.cnf, /etc/mysql/my.cnf,/data/3316/conf-DMYSQL_DATADIR=/data/3316/my.cnf, ~/.my.cnf 讀取這些配置檔案中, client和mysqldump标簽下的參數, 然後列印該參數, 即使有重複的. 對于重複參數後面的生效, 和mysql指令該選項含義是一樣的.
DDLOptions 組
1.--add-drop-database
在備份的SQL檔案中, 每個CREATE DATABASE語句前都明确寫入DROP DATABASE語句, SQL檔案中顯示如下:
;
CREATEDATABASE `stage` ;
該選項一般和--all-databases或—databases一塊使用, 其預設是關閉的.
2.--add-drop-table
在CREATE TABLE語句前面, 寫入DROP TABLE語句, SQL檔案中顯示如下:
DROPTABLE IF EXISTS `st1`;
CREATETABLE `st1` ...
其預設是開啟的.
3. --add-drop-trigger
在CREATE TRIGGER語句前, 寫入DROP TRIGGER語句.
其預設是關閉的.
4.--no-create-db, -n
和--databases或--all-databases一塊使用時, SQL檔案中不寫入CREATE DATABASE語句.
其預設是關閉的.
5.--no-create-info, -t
SQL檔案中不寫入CREATE TABLE語句, 即不導出資料表結構.
其預設是關閉的.
DebugOptions 組
1. --force,-f
mysqldump執行過程中, 若有SQL錯誤, 該過程仍會繼續, 同時列印錯誤日志到終端.
如有視圖v_tb1, 其依賴的表tb1改名為tb1_bak, 備份資料庫test時, 會有如下報錯, 且遇到錯誤, 備份過程就退出了.
[email protected]:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--result-file=test.sql --single-transaction --databases test
mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
若上述指令行加上--force選項, 備份過程即使有錯誤, 其也會執行完.
2.--log-error=file_name
警告和錯誤日志輸會出到指定的檔案中.
參考在--force選項中給出的例子, 如下所示, 報錯輸出到檔案error.txt中.
[email protected]:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --log-error=error.txt --result-file=test.sql --single-transaction--databases test
[email protected]:~/dbbackup$ cat error.txt
mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
InternationalizationOptions 組
1.--default-character-set=charset_name
指明mysqldump備份使用的字元集, 一般要和SHOW GLOBAL VARIABLES LIKE'character_set_server'顯示的一樣. 若資料庫字元集是utf8mb4的, 備份時未指明字元集, 其預設會使用utf8字元集, 如此恢複資料時, 就有可能出現亂碼.
2.--set-charset
在備份的SQL檔案中, 頭部加上SET NAMES語句, SQL檔案中顯示如下:
;
ReplicationOptions 組
mysqldump經常用于在主從複制環境中, 搭建一個從資料庫節點, 以下是兩個相關參數.
1.--master-data[=value]
mysqldump通過在主庫擷取GLOBAL READ LOCK, 取得從庫複制開始的坐标, 即主庫上binlog日志的名稱和位置. 該過程在general log中顯示如下:
1457271Query FLUSH TABLES
1457271Query FLUSH TABLES WITH READ LOCK
1457271Query SHOW MASTER STATUS
1457271Query UNLOCK TABLES
上面說的binlog日志名稱和位置, 會寫入CHANGE MASTER TO語句中, SQL檔案中顯示如下:
-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=345;
該選項有2和1兩個值, 前者代表CHANGE MASTER TO語句是被注釋的, 後者沒注釋, 一般使用前者.
2.--dump-slave[=value]
該選項的含義, 和--master-data類似, 隻不過是在從庫上執行的. 其通過停止從庫的SQL線程, 來擷取對應主庫上binlog日志的名稱和位置. 該過程在general log中顯示如下:
1770998Query SHOW SLAVE STATUS
1770998Query STOP SLAVE SQL_THREAD
1770998Query SHOW SLAVE STATUS
1770998Query UNLOCK TABLES
FormatOptions 組
1.--complete-insert, -c
INSERT語句中包含資料表字段列名, SQL檔案中顯示如下:
INSERTINTO `st1` (`a`, `b`, `c`) VALUES (1,'aa',2);
2.--create-options
在CREATE TABLE語句中, 包含MySQL資料表的特定資訊, 如字段的AUTO_INCREMENT屬性, 存儲引擎, 字元集等資訊.
3.--hex-blob
對于BINARY, VARBINARY, BLOB和BIT二進制資料類型的資料, 以十六進制方式導出. 建議以該方式導出二進制資料, 這樣會避免一些由字元集轉換帶來的錯誤.
4.--result-file=file_name, -r file_name
将備份資料輸出到指定的檔案中, 使用該選項時, 不能邊備份邊壓縮, 對磁盤空間要求多些.
FilteringOptions 組
1. --all-databases,-A
備份除information_schema和performance_schema兩個資料庫之外的全部資料庫.
2.--databases, -B
備份指定的資料庫, 多個庫名間用空格分隔.
3. --events, -E
備份事件.
4.--ignore-table=db_name.tbl_name
指定不導出某資料庫中某資料表的資料, 若有多張這樣的資料表, 需多次指定. 一般用于忽略視圖.
5.--no-data, -d
不導出資料表中的資料.
6.--routines, -R
導出存儲過程.
7.--triggers
導出觸發器.
PerformanceOptions 組
1.--disable-keys
在備份的SQL檔案中, 每個資料表的INSERT語句前後添加ALTER TABLE … DISABLE| ENABLE KEYS語句, SQL檔案中顯示如下:
;
INSERTINTO `st1` ...
;
資料導入完成後, 再建立索引, 該導入效率會高些, 但此選項僅對MyISAM表的非唯一索引起作用.
2.--extended-insert, -e
使用Multiple-Row文法導出資料, 即VALUES關鍵字後帶有多組值清單, 這樣産生的備份檔案較小, 導入速斷也較快. SQL檔案中顯示如下:
INSERTINTO `st1` VALUES (1,'aa',2),(2,'bb',4),(3,'cc',6),(4,'dd',8);
3. --opt
該選項代表一個選項組, 其包含的選項如下所示, 選項含義在前後都介紹到了.
--add-drop-table,--add-locks, --create-options, --disable-keys, --extended-insert,--lock-tables, --quick, --set-charset
其預設是開啟的.
4.--quick, -q
該選項訓示mysqldump直接将備份資料寫入SQL檔案, 而不是先緩存到記憶體中, 再寫入檔案.
其預設是開啟的.
TransactionalOptions 組
1.--add-locks
在備份的SQL檔案中, 每個資料表的INSERT語句前後, 添加LOCK|UNLOCK TABLES語句, SQL檔案中顯示如下:
LOCKTABLES `st1` WRITE;
INSERTINTO `st1` ...
UNLOCKTABLES;
這樣導入時, 可以提升SQL檔案中INSERT語句的插入速度.
其預設是開啟的.
2.--flush-privileges
備份完mysql(系統庫)資料庫後, 加入FLUSH PRIVILEGES語句, 在SQL檔案中顯示如下:
-- FlushGrant Tables
;
3.--lock-all-tables, -x
通過該參數, 擷取GLOBAL READ LOCK, 保證備份資料的一緻性.
該選項和--single-transaction同時使用, 會有如下報錯:
mysqldump:You can't use --single-transaction and --lock-all-tables at the same time.
4.--lock-tables, -l
在備份每個資料庫之前, 會将其所有資料表鎖住, 在general log日志中顯示如下:
1459882Query LOCK TABLES `st1` READ ,`st2` READ
1459882Query UNLOCK TABLES
由于是分别鎖定的每個資料庫, 備份資料可能會不一緻.
5.--single-transaction
備份時, 将隔離級别設定為REPEATABLE, 然後START TRANSACTION開啟一個事物, 利用InnoDB存儲引擎的MVCC特性, 進行資料一緻性的備份.
注意, --single-transaction和--lock-all-tables選項是互斥的; --lock-tables和前面任何一個選項連用, 其都會被關閉.
下面看幾個實際使用到的mysqldump的執行個體
1. 導出資料庫stage的表結構.
[email protected]:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --no-data --quick --result-file=stage_struc.sql --single-transactionstage
2. 備份資料庫stage中的資料表st2.
[email protected]:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --hex-blob --quick --result-file=stage_st2.sql --single-transactionstage st2
3. 備份資料庫stage和test, 在日志error.txt可看到報錯資訊.
[email protected]:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --log-error=error.txt --hex-blob --quick --result-file=stage_test.sql--single-transaction --databases stage test
[email protected]:~/dbbackup$ cat error.txt
mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
4. 在從資料庫進行備份, 用于搭建另一個從資料庫節點.
[[email protected]]$ mysqldump --login-path=mytest --add-drop-database --add-drop-trigger--default-character-set=utf8mb4 --dump-slave=2 --events --flush-privileges--force --log-error=error.txt --hex-blob --quick --routines--single-transaction --triggers --all-databases | gzip > all.sql.gz
[[email protected]]$ gzip -d all.sql.gz
[[email protected]]$ grep '\-\- CHANGE MASTER TO' all.sql
-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000041', MASTER_LOG_POS=3068;
對于以上關于mysqldump工具在MySQL資料庫備份過程的作用，大家是不是覺得非常有幫助。如果需要了解更多内容，請繼續關注我們的行業資訊，相信你會喜歡上這些内容的。