RDSforMySQLMysqldump常見問題和處理
RDS for MySQL Mysqldump 常見問題和處理
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’
7. RDS for MySQL 邏輯備份
- RDS for MySQL 支援例項和單庫級別的邏輯備份。
- 邏輯備份執行期間不會影響主例項的正常使用。
- 邏輯備份匯入許可權問題請參考:RDS for MySQL許可權問題(錯誤程式碼:1227,1725)
相關文章
- HTML + CSS處理常見問題HTMLCSS
- Zabbix 常見問題處理整理
- BREW常見問題解答(FAQ 5)-處理器和事件處理 (轉)事件
- GoldenGate常見問題及處理Go
- Redis 快取常見問題處理Redis快取
- Mac遊戲安裝常見問題處理Mac遊戲
- 常見問題--plsql處理ora-28000SQL
- iOS開發中兩個常見問題的處理iOS
- 常見的幾個Qt程式設計問題的處理QT程式設計
- 5種常見的 DNS 故障診斷及問題處理方法DNS
- SQL Server資料庫中處理空值時常見問題SQLServer資料庫
- GoldenGate常見異常處理Go
- docker安裝和常見問題Docker
- 修復 SSL Certificate Problem,如何定位及常見問題的處理策略
- .net異常處理的效能問題
- spring @Async處理非同步任務以及常見“非同步失效”問題Spring非同步
- 最新版cornerstone 3 0 3 使用教程及SVN常見問題處理
- 【Nginx】常見問題Nginx
- js常見問題JS
- CSS常見問題CSS
- Git 常見問題Git
- PHP 常見問題PHP
- swiper常見問題
- nginx 常見問題Nginx
- java 常見問題Java
- MyBatis常見問題MyBatis
- 前端常見問題前端
- Git常見問題Git
- SQLServer常見問題SQLServer
- css 常見問題CSS
- HTML常見問題HTML
- PyMongo 常見問題Go
- xhtml常見問題HTML
- UITableview 常見問題UIView
- mysql常見問題MySql
- MySQL 常見問題MySql
- BlockUI常見問題BloCUI
- Mysql:常見問題MySql