mysqldump 使用規範

資料庫很重要，沒有備份，資料丟失只能跑路。所以還是做好備份吧！

目錄

• 一、工具介紹
• 二、工具特點
• 三、備份許可權
• 四、工具使用限制
• 五、已知BUG
• 六、備份前注意事項
  • 6.1 需要長時間備份或匯入時，請使用 screen 視窗工具
  • 6.2 檢查資料庫引數
  • 6.3 檢查資料庫角色
• 7、使用方法舉例
  • 7.1 全庫備份
    • 進行mysql.proc 單表匯出：
    • 進行mysql.proc 單表匯入：
  • 7.2 單庫備份
  • 7.3 單表備份
  • 7.4 生成單獨的資料檔案（-T）
    • 生成檔案示例：
  • 7.5 根據條件備份（-w）
• 八、資料匯入
  • 8.1 備份匯入前注意事項
    • (1) 在備份匯入前要檢查以下引數的配置
    • (2) 檢查SQL檔案中所要DROP 的表是否是自己預期內的
  • 8.2 使用PV工具監控檔案匯入過程
  • 8.3 mysql client+PV+會話引數匯入（推薦）
• 九、特殊問題規範
  • 9.1 場景1：
    • 涉及引數：
    • 注意問題：
  • 9.2 場景2：
    • 涉及引數：
    • 注意問題：
  • 因為有悔，所以披星戴月；因為有夢，所以奮不顧身！ 個人部落格首發：easydb.net 微信公眾號：easydb 關注我，不走丟！

一、工具介紹

mysqldump是mysql自帶的邏輯備份工具。

它的備份原理是，通過協議連線到mysql資料庫，將需要備份的資料查詢出來，將查詢出的資料轉換成對應的insert語句，當我們需要還原這些資料時，只要執行這些insert語句，即可將對應的資料還原。

二、工具特點

• 免費備份工具
• 支援遠端備份
• 支援生成CSV格式或XML格式的檔案
• 可以使用文字工具直接處理對應的備份資料，以更靈活便利的進行恢復工作
• 與儲存引擎無關，可以在多種儲存引擎下進行備份恢復，對innodb引擎支援熱備，對MyISAM引擎支援溫備（施加表鎖）
• 當資料為浮點型別時，會出現精度丟失
• 備份的過程是序列化的，不支援並行備份

三、備份許可權

備份物件	許可權
table	SELECT
view	SHOW VIEW
trigger	TRIGGER
event	EVENT
儲存過程、函式	SELECT mysql.proc
轉儲使用者	SELECT "mysql"系統庫

備份未使用--single-transaction選項時，還需要LOCK TABLES許可權

四、工具使用限制

1. mysqldump轉儲時預設不會備份INFORMATION_SCHEMA, performance_schema, sys，如有需求轉儲，需要再命令列上顯式的指定他們。

2. mysqldump不會轉儲InnoDB CREATE TABLESPACE語句。

3. mysqldump不會備份 NDB cluster ndbinfo資訊資料庫。

4. 在啟用了GTID的資料庫中使用mysqldump備份時需要注意，如果備份檔案中包含了GTID資訊，則無法恢復到沒有啟用GTID的資料庫中。

5. Windows通過PowerShell使用如下命令進行轉儲時，轉儲檔案預設使用UTF-16編碼，而MySQL不允許將UTF-16作為連線字符集，所以通過如下命令備份的轉儲檔案將無法正確載入到資料庫中

   mysqldump [options] > dump.sql
   

6. mysqldump是單執行緒，當資料量大時備份時間長，甚至有可能在備份過程中非事務表長期鎖表對業務造成影響（SQL形式的備份資料恢復時間也較長）。

7. 慎用 --compact 引數，此引數會去掉檔案頭與檔案尾的一些引數設定（比如時區，字符集...），導致隱患。

8. 引數 --lock-all-tables，--flush-privileges分別會在備份時進行 flush tables 和 flush privileges 操作，會產生GTID，備份從庫時請注意。

9. 在對資料庫進行完全備份前，需要收集資料庫相關資訊（儲存引擎、字符集等），確保備份內容完整，以下為收集語句：

   -- 檢視錶相關資訊
   select table_schema,table_name,table_collation,engine,table_rows
   from information_schema.tables
   where table_schema NOT IN ('information_schema' , 'sys', 'mysql', 'performance_schema');
     
   -- 檢視是否存在儲存過程、觸發器、排程事件等
   select count(*) from information_schema.events;
   select count(*) from information_schema.routines;
   select count(*) from information_schema.triggers;
      
   -- 檢視字符集資訊
   show variables like 'character%';
   

10. mysqldump的一些選項跟mysqlpump有些變化，在使用中需注意：

mysqldump	mysqlpump
--routines、--events的別名分別為-R、-E	--routines、--events不存在別名
存在master-data選項	不存在master-data選項，在進行構建主從需要通過master_auto_position來控制，不能夠直觀的通過指定binlog以及position來構建主從
-d的別名是--no-data	-d的別名是--skip-dump-rows
轉儲檔案預設帶DROP TABLE語句	轉儲檔案預設不帶DROP TABLE、DROP USER（在使用--users備份使用者時）語句，匯入時可能會因為使用者存在或者表存在而報錯
備份不指定資料庫或者-A會提示報錯	備份不指定資料庫或者-A，預設備份所有的資料。ps：除了INFORMATION_SCHEMA, performance_schema, ndbinfo, or sys

五、已知BUG

MySQL5.7.7至今（2020/11/02）的所有5.7的小版本，在使用mysqldump在執行--all-databases都會清空mysql.proc導致sys無法正常使用。

mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

在使用mysqldump在執行--all-databases不會備份mysql.proc下的系統自身的儲存過程，導致匯入到新機器後，部分sys下的檢視沒法正常使用；這是一個BUG，並且只存在於MySQL5.7。

bug連線：

• https://bugs.mysql.com/bug.php?id=86807

• https://bugs.mysql.com/bug.php?id=92631

• https://bugs.mysql.com/bug.php?id=83259

• https://github.com/mysql/mysql-server/commit/ded3155def2ba3356017c958c49ff58c2cae1830

如果因為使用--all-databases引數已經造成sys異常報錯，這樣做可以修復其異常：

mysql_upgrade --upgrade-system-tables --skip-verbose --force

-- 使用mysql_upgrade的時候要加上 --upgrade-system-tables。不然會掃描使用者庫表，期間加鎖且速度一般。

六、備份前注意事項

6.1 需要長時間備份或匯入時，請使用 screen 視窗工具

如果你正在做一個大表 DDL 或者批量執行一個 sql 檔案，執行一半遠端連線斷開了，你會不會想吐血？如果剛好執行批量 sql 時沒有開啟事務，想想後果會怎樣？

使用 screen 時，即使當前遠端終端被斷開連線，也不會中斷正在執行的任務：

## 安裝 screen
yum install -y screen
  
## 開啟一個 screen
screen
  
## 檢視所有開啟的 screen
screen -ls
  
## 重新連線到指定的 screen
screen -r xxx

6.2 檢查資料庫引數

引數	原因
lock_wait_timeout	預設值為1年，如果沒有修改預設設定，或者當前值是個挺長的時間，當mysqldump遇到長事務並在獲取lock超時時，mysqldump不會退出，會等待獲取MDL鎖。此時介面無任何輸出，使用者不進入資料庫檢視，不會感知備份停滯等待，影響備份工作進度

6.3 檢查資料庫角色

檢查mysqldump備份角色，注意如果在從庫使用下面mysqldump命令時，不要將--master-data和--flush-logs一起使用，有可能造成資料庫死鎖影響備份與複製。

7、使用方法舉例

7.1 全庫備份

注意不僅需要備份資料，還需要備份儲存過程、觸發器、事件：

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > /tmp/backup/all_db_with_data.sql

注意：如果是MySQL5.7版本有BUG（詳情請看五、已知BUG）需要多新增一步操作：

進行mysql.proc 單表匯出：

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --triggers --routines --events --tables mysql proc > /tmp/backup/mysql_proc.sql

以上匯出的SQL，必須在開啟SET @@SESSION.SQL_LOG_BIN= 0;的情況下，利用mysql> source mysql_proc.sql的方式匯入，否則會造成異常情況影響主從建立，如果不是為了建立主從，可忽略此匯入方式。

進行mysql.proc 單表匯入：

#不搭建主從複製的情況下：
    多種匯入方式都可
#要搭建主從複製的情況下：
    mysql> use mysql
    mysql> SET @@SESSION.SQL_LOG_BIN= 0;
    mysql> source /tmp/backup/mysql_proc.sql

7.2 單庫備份

## 備份表結構和資料
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --databases employees > /tmp/backup/employees_all.sql
 
## 只備份表結構
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-data --databases employees > /tmp/backup/employees_schema.sql
  
## 只備份資料
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-create-info --databases employees > /tmp/backup/employees_only_data.sql

7.3 單表備份

## 只備份employees資料庫中的salaries表
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --tables employees salaries > /tmp/backup/employees_salaries.sql
  
## 排除指定資料庫的指定表
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --ignore-table=employees.departments --ignore-table=employees.employees employees > /tmp/backup/employees_exclude_departments.sql

注意

如果備份時要排除某個資料庫中多個表，要使用多個--ignore-table語句，不能在後面加逗號做分割。

7.4 生成單獨的資料檔案（-T）

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs -T /tmp/salaries --databases employees --tables salaries > employees_salaries.sql

注意事項

• 該選項值指定的是生成表備份檔案的目錄。所以要如上所示新增--database --tables。該選項與--database、--all-databases選項互斥，不能一起使用
• 只有當mysqldump與mysqld伺服器在同一臺機器上執行時，才能使用該選項
  因為在生成備份檔案時只能使用本地目錄，並且執行備份的MySQL賬戶還必須具有FILE許可權。所以， secure_file_priv系統引數必須設定為空串（因為是呼叫 SELECT ...INTO OUTFILE語句)，以使得啟動mysqld程式的系統登入賬戶可以將這些備份檔案生成到指定的檔案系統目錄下
• （注意，使用該選項時指定的目錄必須是備 份伺服器上的目錄，不能遠端連線備份，因為SELECT ... INTO OUTIFLE 只能寫到伺服器本地目錄下且啟動 mysqld 程式的使用者需要具有訪問許可權

生成檔案示例：

less salaries/salaries.txt

10001   60117   1986-06-26      1987-06-26
10001   62102   1987-06-26      1988-06-25
10001   66074   1988-06-25      1989-06-25
10001   66596   1989-06-25      1990-06-25
10001   66961   1990-06-25      1991-06-25

7.5 根據條件備份（-w）

只轉儲由給定的WHERE條件選擇的行

###按照where條件，備份指定庫下的指定表的資料
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 -w 'id > 10 and id < 20' --databases testdb --tables sbtest1 > /tmp/backup/sbtest1.sql
  
###按照where條件，備份指定庫下的所有表的資料
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 -w 'id > 10 and id < 20' --databases testdb > /tmp/backup/sbtest1.sql

注意事項

1. 只用--database不用--table的情況下需注意：
   同一個庫要所有的表都要有where條件列並且列資料型別一致。經測試同一個庫下如果有一個表不具有where條件列，此時mysqldump會報錯；經測試同一個庫下如果有表，具有同名where條件列，但資料型別不同，此時mysqldump不會報錯，但備份不出其資料，只會備份符合where條件的表的資料。
2. 當只指定--tables而不指定--databases選項，或者在命令列選項中沒有給出表示庫名的引數時，mysqldump會把緊跟在--tables之後的命令列引數中的第一個當作庫名來解析，從第二個引數開始全部被解析為表名。
3. 如果多次指定一個備份物件，則不會去重，而是執行多次備份.所以，在生產環境中不建議庫名與表名相同。

八、資料匯入

8.1 備份匯入前注意事項

(1) 在備份匯入前要檢查以下引數的配置

引數	建議	原因	命令
autocommit	建議開啟	如果關閉，可能會造成匯入語句無法自動提交，可能會導致事務卡住、事務回滾、binlog大事務，甚至資料庫關閉。	set session autocommit = 1;
wait_timeout \ interactive_timeout	建議調大	設定過小，且匯入時間長，會導致還沒匯入完，會話超時斷開連線，導致任務失敗。	set session wait_timeout=28800; \ set session interactive_timeout=28800;

(2) 檢查SQL檔案中所要DROP 的表是否是自己預期內的

less all_db_with_data.sql | grep -E "^DROP TABLE IF EXISTS|^USE"

8.2 使用PV工具監控檔案匯入過程

在一般的備份檔案匯入中，只能憑經驗去估測一下備份檔案實際匯入的時間，這裡可以使用PV工具，較為精確的算出導數剩餘量及完成時間，所以建議使用此種方式，進行資料匯入

#引數說明：
#-W：在需要密碼輸入時有用，可等待密碼輸出完成，再開啟監控進度條
#-L:限流，將傳輸限制在每秒最大位元組的範圍內（大小可自定義，單位可變）
shell> pv -W -L 2M all_db_with_data.sql | /data/mysql/base/5.7.25/bin/mysql -uops -p -S/data/mysql/data/3333/mysqld.sock
Enter password: 
 588MiB 0:04:54 [   2MiB/s] [======================================================>] 100%

8.3 mysql client+PV+會話引數匯入（推薦）

#引數說明：
#reset master：在匯入機器有自身的GTID時，會匯入失敗，所以可根據需要在echo裡面新增"reset master"命令，清空GTID purge。
  
shell> (echo 'set @@autocommit=1; set @@interactive_timeout=28800; reset master;'; pv -W -L 5M all_db_with_data.sql) | mysql -h127.0.0.1 -P3333 -uops -p
 
shell> (echo 'set @@autocommit=1; set @@interactive_timeout=28800;'; pv -W -L 5M employees_all.sql) | mysql -h127.0.0.1 -P3333 -uops -p employees

九、特殊問題規範

9.1 場景1：

防止SQL匯入中產生過大的binlog，使主從延遲增加，想在匯入時把日誌格式改為statement，減小binlog

涉及引數：

transaction_isolation

binlog_format

注意問題：

如果表是InnoDB表，並且事務隔離級別是READ COMMITTED或READ UNCOMMITTED，那麼只能使用基於行的日誌記錄。可以將日誌格式改為statement，但在執行時這樣做會導致錯誤，因為InnoDB表不能再執行insert語句。

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

mysql> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

mysql> create table test(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(1, 'zhou'),(2, 'wei');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | zhou |
|    2 | wei  |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into test values(3,'bing');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

在執行備份匯入時有將binlog_format臨時設定為statement需求時，需要檢查transaction_isolation是否為RR。在RC級別下，binlog_format設定為statement會導致不能進行insert相關操作。所以不是RR級別，請不要將binlog_format改為statement。

9.2 場景2：

備份或匯入SQL包過大，超過max_allowed_packet，備份或匯入失敗

涉及引數：

max_allowed_packet

注意問題：

備份或匯入時確定資料庫表中的字元型別：如果有BLOB列或長字串等字元型別建議合理增加這個值。

#  mysqldump -h10.100.143.62 -umeslogin -P3306 -p --default-character-set=utf8mb4 --master-data=2 --flush-logs --single-transaction --set-gtid-purged=off --hex-blob --databases yfmes_metal_sys > yfmes_metal_sys_all.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `operlog` at row: 264227

匯入時也相似的原理。

可以在mysql、mysqldump等client命令後臨時修改此引數：

mysqldump [xxxx] --max_allowed_packet=256M > dump.sql
mysql [xxxx] --max_allowed_packet=256M < dump.sql

因為有悔，所以披星戴月；因為有夢，所以奮不顧身！ 個人部落格首發：easydb.net 微信公眾號：easydb 關注我，不走丟！