mysqldump工具在MySQL數據庫備份過程的作用

下文主要給大家帶來mysqldump工具在MySQL數據庫備份過程的作用，希望這些文字能夠帶給大家實際用處，這也是我編輯mysqldump工具在MySQL數據庫備份過程的作用這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

mysqldump工具是MySQL數據庫備份時, 經常用到的一個工具. 可以指定數據表, 某些數據庫, 所有數據庫級別的備份, 在命令行上敲入mysqldump回車, 可看到該三種方式的示例.

mysql@db01:~$ 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的版本.

mysql@db01:~$ mysqldump --version

mysqldump  Ver 10.13 Distrib 5.6.24, for Linux (x86_64)

另外, 為了下面的說明演示, 用到的數據庫和數據表如下所示:

(root@localhost)[(none)]> SHOW DATABASES;

+--------------------+

|Database           |

+--------------------+

|information_schema |

|mysql              |

|performance_schema |

|stage              |

|test               |

+--------------------+

5 rows inset (0.00 sec)

(root@localhost)[(none)]> SHOW TABLES FROM stage;

+-----------------+

|Tables_in_stage |

+-----------------+

|st1             |

|st2             |

+-----------------+

2 rows inset (0.00 sec)

(root@localhost)[(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文件中顯示如下:

/*!40000DROP DATABASE IF EXISTS `stage`*/;

CREATEDATABASE /*!32312 IF NOT EXISTS*/ `stage` /*!40100 DEFAULT CHARACTER SETutf8mb4 */;

該選項一般和--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時, 會有如下報錯, 且遇到錯誤, 備份過程就退出了.

mysql@db01:~/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中.

mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --log-error=error.txt --result-file=test.sql --single-transaction--databases test

mysql@db01:~/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文件中顯示如下:

/*!40101SET NAMES utf8mb4 */;

ReplicationOptions 組

mysqldump經常用于在主從復制環境中, 搭建一個從數據庫節點, 以下是兩個相關參數.

1.--master-data[=value]

mysqldump通過在主庫獲取GLOBAL READ LOCK, 取得從庫復制開始的坐標, 即主庫上binlog日志的名稱和位置. 該過程在general log中顯示如下:

1457271Query   FLUSH /*!40101 LOCAL */ 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文件中顯示如下:

/*!40000ALTER TABLE `st1` DISABLE KEYS */;

INSERTINTO `st1` ...

/*!40000ALTER TABLE `st1` ENABLE KEYS */;

數據導入完成后, 再建立索引, 該導入效率會高些, 但此選項僅對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

/*! FLUSHPRIVILEGES */;

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 /*!32311LOCAL */,`st2` READ /*!32311 LOCAL */

1459882Query   UNLOCK TABLES

由于是分別鎖定的每個數據庫, 備份數據可能會不一致.

5.--single-transaction

備份時, 將隔離級別設置為REPEATABLE, 然后START TRANSACTION開啟一個事物, 利用InnoDB存儲引擎的MVCC特性, 進行數據一致性的備份.

注意, --single-transaction和--lock-all-tables選項是互斥的; --lock-tables和前面任何一個選項連用, 其都會被關閉.

下面看幾個實際使用到的mysqldump的實例

1. 導出數據庫stage的表結構.

mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --no-data --quick --result-file=stage_struc.sql --single-transactionstage

2. 備份數據庫stage中的數據表st2.

mysql@db01:~/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可看到報錯信息.

mysql@db01:~/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

mysql@db01:~/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. 在從數據庫進行備份, 用于搭建另一個從數據庫節點.

[mysql@db02dbbackup]$ 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

[mysql@db02dbbackup]$ gzip -d all.sql.gz

[mysql@db02dbbackup]$ grep '\-\- CHANGE MASTER TO' all.sql

-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000041', MASTER_LOG_POS=3068;

對于以上關于mysqldump工具在MySQL數據庫備份過程的作用，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。