2019獨角獸企業重金招聘Python工程師標準>>>

摘要：?RDS for MySQL Mysqldump 常見問題和處理 ? GTID 特性相關避免表級鎖等待設置導出字符集其他導出時需要注意的選項舉例 RDS for MySQL 不支持的選項 RDS for MySQL 邏輯備份 1. GTID 特性相關 MySQL 5.6 引入了 GTID 特性，因此隨?5.6 版本分發的 mysqldump 工具增加了 --set-gtid-purged 選項。

RDS for MySQL Mysqldump 常見問題和處理

GTID 特性相關
避免表級鎖等待
設置導出字符集
其他導出時需要注意的選項
舉例
RDS for MySQL 不支持的選項
RDS for MySQL 邏輯備份

1. GTID 特性相關

MySQL 5.6 引入了 GTID 特性，因此隨?5.6 版本分發的 mysqldump 工具增加了 --set-gtid-purged 選項。

#	選項名稱	默認值	可選值	作用
1	set-gtid-purged	AUTO	ON,?OFF,?AUTO	是否輸出?SET @@GLOBAL.GTID_PURGED 子句

ON：在 mysqldump 輸出中包含 SET?@@GLOBAL.GTID_PURGED 語句。
OFF：在 mysqldump 輸出中不包含 SET?@@GLOBAL.GTID_PURGED 語句。
AUTO：默認值；對于啟用 GTID 實例，會輸出?SET?@@GLOBAL.GTID_PURGED 語句；對于沒有啟動或者不支持 GTID 的實例，不輸出任何 GTID 相關信息。

因此對于使用 MySQL 5.6 及以上版本帶有的 mysqldump 工具進行 RDS for MySQL 實例數據導出時設置該選項為 OFF。

注：

如果 mysqldump 設置?set-gtid-purged=ON??從?RDS for MySQL 5.5 或 5.1?版本實例導出數據，mysqldump 會提示下面的錯誤：

Error: Server has GTIDs disabled.
或者
mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ <1193>

2. 避免表級鎖等待

mysqldump 默認會啟用 lock-tables 選項，對要導出的表加表級鎖，阻止表上的 DML 操作。

RDS for MySQL 實例默認支持的?InnoDB 和 TokuDB 引擎均支持事務，建議使用 ?single-transaction 選項進行導出，而不要設置 lock-all-tables 或 lock-tables 選項。

#	選項名稱	默認值	可選值	作用
1	lock-all-tables	FALSE	FALSE,TRUE	在數據導出期間放置 global read lock，所有庫下的所有表在導出期間為只讀。自動關閉 lock-tables 和 single-transaction 選項。RDS 不支持該選項。
2	lock-tables	TRUE	FALSE,TRUE	導出期間在導出表上放置表級鎖。默認開啟。可以通過指定 --skip-lock-tables 選項來關閉。
3	single-transaction	FALSE	FALSE,TRUE	導出操作被放置在一個事務中執行。自動關閉 lock-tables 選項。

關于表級鎖的情況，請參考：RDS for MySQL InnoDB表級鎖等待

3. 設置導出字符集

如果不指定，mysqldump 默認使用 UTF8 字符集進行導出。

#	選項名稱	默認值	可選值	作用
1	default-character-set	UTF8	實例支持的字符集	mysqldump 到 RDS 實例導出連接的字符集

4. 其他導出時需要注意的選項

#	選項名稱	默認值	可選值	作用
1	no-defaults	NA	NA	除了.mylogin.cnf，不讀取任何選項文件
2	defaults-file=file_name	NA	NA	讀取指定的選項文件
3	add-drop-database	FALSE	FALSE,TRUE	在 create database 語句前增加 drop database 語句
4	add-drop-table	TRUE	FALSE,TRUE	在 create table 語句前增加 drop table 語句，默認開啟，使用選項 --skip-add-drop-table 來關閉。
5	add-locks	TRUE	FALSE,TRUE	在表相關語句前后增加 lock tables tab_name write; 和 unlock tables; 語句。這樣在導入數據時可以加快數據導入。
6	compatible=name	NA	ansi,postgresql, oracle,mssql	增強與指定的數據庫類型的兼容性
7	compact	FALSE	FALSE,TRUE	啟用 --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, --skip-set-charset 選項
8	databases	TRUE	FALSE,TRUE	導出多個庫。默認 mysqldump 將第一個名字識別為庫，其后的名字識別為表。指定該選項后，mysqldump會將所有名稱識別為庫，并在每個庫前增加 create database 和 use database 語句。
9	disable-keys	TRUE	FALSE,TRUE	在插入數據前后增加 /!40000 ALTER TABLE tab_name DISABLE KEYS?/ 和 /!40000 ALTER TABLE tab_name ENABLE KEYS?/ 語句來加速插入。該選項僅對 MyISAM 引擎表的非唯一索引有效。
10	events	FALSE	FALSE,TRUE	導出數據庫內的計劃事件（定時任務）
11	extended-insert	TRUE	FALSE,TRUE	使用擴展的 Insert 語句，一條 Insert 語句插入多行。
12	hex-blob	FALSE	FALSE,TRUE	以16進制導出 Binary、VarBinary、BLOB 類型數據。如果跨版本遷移數據，建議增加該選項。
13	ignore-table=db.tab	TRUE	FALSE,TRUE	不導出某表或視圖。格式：庫名.表名（db.tab)。可以多次使用該選項來忽略多張表。
14	max-allowed-packet	24 MB	24 MB - 1 GB	mysqldump 和 RDS 實例通信緩存最大值。默認24 MB。最大 1 GB。
15	no-create-db	FALSE	FALSE,TRUE	輸出中不包含 create database 語句
16	no-create-info	FALSE	FALSE,TRUE	輸出中不包含 create table 語句
17	no-data	FALSE	FALSE,TRUE	不導出數據
18	opt	TRUE	FALSE,TRUE	啟用 ?--add-drop-table, --add-locks, --create-options --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset; 可以通過指定 skip-opt 選項關閉默認 opt 選項。
19	dump-date	TRUE	FALSE,TRUE	如果指定了 --comments 選項（默認開啟），在輸出的注釋中顯示導出日期時間。
20	routines	FALSE	FALSE,TRUE	導出存儲過程和函數（默認不導出）
21	result-file	TRUE	FALSE,TRUE	將輸出重定向到文件
22	set-charset	TRUE	FALSE,TRUE	在導出文件中加上 set names default_chararacter_set
23	triggers	TRUE	FALSE,TRUE	導出表上的 Trigger

5. 舉例

5.1 導出庫 jacky 下的 teacher 表，包括表上的觸發器，導出字符集是 utf8mb4

mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --default-character-set=utf8mb4 --hex-blob --single-transaction --result-file=jacky_teacher.sql jacky teacher
# -p 選項指定密碼，密碼和選項間不要有空格
# -P 選項指定實例的端口
# -h 選項指定 RDS 實例的 URL 地址
# -u 選項指定使用的數據庫用戶
# 也可以使用下面的方式進行導出
mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --default-character-set=utf8mb4 --hex-blob --single-transaction jacky teacher > jacky_teacher.sql

5.2?導出庫 jacky，包括存儲過程和函數，不含 lock tables 和 unlock tables 語句

mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --routines --skip-add-locks --result-file=jacky.sql jacky
# --routines — 導出庫涉及的存儲過程和函數
# --skip-add-locks — 輸出中不包括 lock tables table_name write; 和 unlock tables; 語句

5.3 導出庫 jacky，包括存儲過程、函數、觸發器、事件，不包括數據

mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --routines --events --no-data --result-file=jacky.sql jacky
# 觸發器選項 --triggers 默認開啟，因此不需要指定
# --events — 導出庫涉及的定時事件（計劃任務）
# --no-data — 不導出數據

5.4 導出庫 jacky，不包括庫、表創建語句，不包括 drop table 語句

mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --no-create-db --no-create-info --skip-add-drop-table --result-file=jacky.sql jacky
# --no-create-db — 輸出中不包括庫的創建語句
# --no-create-info — 輸出中不包括表的創建語句
# --skip-add-drop-table — 輸出中不包括表的刪除語句

5.5?導出庫 jacky，jerry，jason，不包括表 jacky.teacher, jason.orders, jerry.sales

mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --hex-blob --single-transaction --result-file=jacky_jerry_jason.sql --ignore-table=jacky.teacher --ignore-table=jason.orders --ignore-table=jerry.sales --databases jacky jerry jason
# --ignore-table — 指定不進行導出的表
# --databases — 指定要進行導出的數據庫名稱

5.6?導出庫 jacky，包括存儲過程和函數，盡量兼容 SQL SERVER 語法

mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --compatible=mssql --routines --hex-blob --single-transaction --result-file=jacky_mssql.sql jacky
# --compatible=mssql — 增加對 SQL SERVER 的語法兼容性

6. RDS for MySQL 不支持的選項

#	選項名稱	默認值	可選值	作用
1	all-databases	FALSE	FALSE, TRUE	導出所有數據庫，包括 mysql
2	flush-logs	FALSE	FALSE, TRUE	導出前在實例中執行 flush logs; 命令
3	flush-privileges	FALSE	FALSE, TRUE	導出 mysql 系統庫后，輸出中包含 flush privileges; 命令
4	lock-all-tables	FALSE	FALSE, TRUE	在數據導出期間放置 global read lock，所有庫下的所有表在導出期間為只讀。自動關閉 lock-tables 和 single-transaction 選項。
5	tab=dir_name	NA	NA	在指定的目錄下生成 tbl_name.sql 文件（包含表創建語句）和以 tab 作為分隔符的tbl_name.txt文本格式的數據文件。

--all-databases： RDS for MySQL 普通用戶對?mysql 庫中部分表沒有權限，因此不能導出全部庫表。

# 錯誤信息：
mysqldump: Couldn’t execute ‘show create table slow_log‘: SHOW command denied to user ‘xxx’@’xx.xx.xx.xx’ for table ‘slow_log’ (1142)

--flush-logs： RDS for MySQL 普通用戶沒有 Reload 權限，因此不能執行 flush logs; 命令。

# 錯誤信息
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

--flush-privileges：因為 RDS for MySQL 不支持 mysql 系統庫的導出，因此沒必要使用該選項。
--lock-all-tables：因為 RDS for MySQL 普通用戶沒有 Reload 權限，因此不能使用該選項。

# 錯誤信息
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

--tab=dir_name：該選項要求?mysqldump 和 RDS for MySQL 實例在同一物理機上，因此不支持。但該選項可以和 --no-data 選項搭配使用來獲取表的創建語句。

# 和 --no-data 選項搭配，獲取 jacky 庫下每個表的創建語句文件 tab_name.sql
mysqldump --no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 --set-gtid-purged=off --single-transaction --tab=/tmp --no-data jacky
# 不帶 --no-data 選項（希望導出數據）時候的錯誤信息：
mysqldump --no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 --set-gtid-purged=off --single-transaction --tab=/tmp jacky
mysqldump: Got error: 1045: Access denied for user ‘xxx’@’%’ (using password: YES) when executing ‘SELECT INTO OUTFILE’