MySQL Backup mysqldump 常用選項與主要用法

来源:https://www.cnblogs.com/dbabd/archive/2019/01/07/10232786.html
-Advertisement-
Play Games

正文 The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database ob ...


正文

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

mysqldump是MySQL官方自帶的邏輯備份工具。備份結果是生成一系列的文本SQL語句，可以很方便地用作數據備份，也可以用於數據遷移。平時邏輯備份時用mysqldump是最多的，但也沒好好進行學習總結，本文主要就mysqldump常用選項和主要用法作個學習總結。

關於mysqldump更詳細的信息可以參考官方文檔：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

本文使用的MySQL版本為官方社區版 5.7.24。

(root@localhost) [test] > select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

主要說明

# mysqldump --help
mysqldump  Ver 10.13 Distrib 5.7.24, for linux-glibc2.12 (x86_64)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

常用選項

連接選項(Connection Options)

  • --bind-address=ip_address
    當MySQL伺服器有多個網路介面，指定mysqldump連接MySQL伺服器使用的網路介面。

  • --compress, -C
    當服務端和客戶端都支持壓縮時，指定壓縮傳輸的數據。

  • --host=host_name, -h host_name
    指定需要dump數據的MySQL伺服器，預設是localhost。

  • --port=port_num, -P port_num
    指定連接MySQL伺服器的埠。

  • --user=user_name, -u user_name
    指定連接MySQL伺服器的用戶名。

  • --password[=password], -p[password]
    指定連接MySQL伺服器的用戶密碼。
    • 如果命令行使用 -p 選項指定密碼，則選項與密碼串之間不允許有空格，不過命令行中密碼串明文是不安全的；
    • 如果命令行選項後面沒有指定密碼串，則mysqldump在連接前會進行提示輸入。

  • --pipe, -W
    在Windows環境下，指定命名管道連接MySQL伺服器。此選項僅當伺服器支持命名管道連接才生效。

  • --socket=path, -S path
    在Unix系統下指定本地連接使用socket文件，類似於Windows環境下的命名管道。

參數文件選項(Option-File Options)

  • --defaults-file=file_name
    指定讀取的參數文件。如果這個文件不存存或是無法訪問則會報錯。

  • --no-defaults
    不讀取任何的參數文件。

  • --print-defaults
    顯示mysqldump讀取的參數文件。

DDL選項(DDL Options)

  • --add-drop-database
    在每行CREATE DATABASE語句前添加DROP DATABASE語句。這個選項通常與 --all-databases或 --databases選項一起使用，因為如果沒有指定這兩個選項之一也不會有CREATE DATABASE語句。

  • --add-drop-table
    在dump輸出中每行CREATE TABLE語句前添加DROP TABLE語句。

  • --add-drop-trigger
    在dump輸出中每行CREATE TRIGGER語句前添加DROP TRIGGER語句。

  • --no-create-db, -n
    在dump輸出中禁止生成CREATE DATABASE語句，即使指定了 --all-databases或 --databases選項。

  • --no-create-info, -t
    每張表dump的時候都不生成CREATE TABLE語句。

  • --no-tablespaces, -y
    在dump輸出中都禁止生成CREATE LOGFILE GROUP和CREATE TABLESPACE語句。

  • --replace
    在dump輸出中使用REPLACE語句代替INSERT語句。

調試選項(Debug Options)

  • --comments, -i
    在dump輸出中增加額外的信息，如mysqldump版本，MySQL版本，MySQL主機名等。預設是開啟狀態，如果要禁止，則可以指定選項 --skip-comments。

  • --debug-info
    在mysqldump退出時列印調試信息以及CPU和記憶體的使用統計信息。

  • --dump-date
    當啟用選項 --comments時，在dump輸出中的末尾添加結束時間。可以使用選項 --skip-dump-date禁止添加。

  • --force, -f
    忽略dump表過程當中的SQL錯誤。這個選項適用於當指定dump視圖，視圖所對應的表被清除而變成invaild狀態時，mysqldump會記錄視圖定義以及錯誤信息並繼續執行dump操作。如果未指定該選項，則mysqldump報錯並退出。

  • --log-error=file_name
    指定警告和錯誤信息記錄的文件，預設不記錄。

  • --verbose, -v
    詳細模式，列印mysqldump執行更詳細的信息。

國際化選項(Internationalization Options)

  • --character-sets-dir=dir_name
    指定字元集安裝所在的目錄。

  • --default-character-set=charset_name
    指定預設的字元集，如果沒指定，預設為utf8。

  • --set-charset
    添加SET NAMES default_character_set語句到dump文件輸出中。預設是開啟的，可以使用選項 --skip-set-charset禁止。

  • --no-set-names, -N
    關閉選項 --set-charset。

複製選項(Replication Options)

  • --apply-slave-statements
    指定選項 --dump-slave的從庫dump過程中，在CHANGE MASTER TO語句之前添加STOP SLAVE語句，在dump結束之後再添加START SLAVE語句。

  • --delete-master-logs
    在主伺服器上，在完成dump之後發送語句PURGE BINARY LOGS到主伺服器執行。此選項會自動開啟 --master-data。

  • --dump-slave[=value]
    這個選項類似於 --master-data，只不過作用於mysqldump在從庫生成的dump文件，即mysqldump在從庫進行備份。這個選項的作用是為了產生一個與備份從庫相同主庫的從庫，記錄新的從庫需要從主庫開啟複製讀取的binlog文件和binlog位置，生成新的CHANGE MASTER TO語句，binlog文件和binlog位置分別讀取來自於SHOW SLAVE STATUS狀態語句當中的Relay_Master_Log_File和 Exec_Master_Log_Pos值。該選項的取值與 --master-data一致。
    該選項會使mysqldump在開始dump之前停止SQL thread，而在dump結束之後再重新開啟。

  • --include-master-host-port
    指定選項 --dump-slave的從庫dump過程中，生成CHANGE MASTER TO語句添加MASTER_HOST和MASTER_PORT信息以便可以通過新dump文件創建新的從庫開啟主從複製。

  • --master-data[=value]
    在主伺服器完成mysqldump指定該選項導出備份文件可以用來搭建從庫。在dump文件中包含了CHANGE MASTER TO語句，該語句表示新搭建的從庫需要從主庫哪個binlog文件和位置開始進行同步。
    該選項需要RELOAD許可權和MySQL中開啟binlog，會關閉選項 --lock-tables並開啟選項 --lock-all-tables，除非指定了選項 --single-transaction。
    value取值如下：
    • 2：則CHANGE MASTER TO語句是以註釋的形式存在dump文件中，在dump文件導入的過程當中並不會生效；
    • 1：則CHANGE MASTER TO語句是以非註釋的形式存在dump文件中，在dump文件導入的過程中會生效；
    • 未指定：則預設值為1。
  • --set-gtid-purged=value
    指定是否在dump文件中寫入SET @@GLOBAL.gtid_purged語句，同時會導致dump文件在被重載的時候不會記錄binlog。
    value取值如下：
    • OFF：不添加SET @@GLOBAL.gtid_purged語句，同時不添加SET @@SESSION.SQL_LOG_BIN=0語句；
    • ON：添加SET @@GLOBAL.gtid_purged語句，如果沒開啟GTID則報錯，同時添加SET @@SESSION.SQL_LOG_BIN=0語句；
    • AUTO：如果開啟GITD則添加SET @@GLOBAL.gtid_purged語句，同時添加SET @@SESSION.SQL_LOG_BIN=0語句，預設選項。

格式化選項(Format Options)

  • --compact
    更加緊湊的輸出dump文件。該選項同時會開啟 --skip-add-drop-table，--skip-add-locks，--skip-comments，--skip-disable-keys和 --skip-set-charset選項。

  • --complete-insert, -c
    指定dump文件中的INSERT語句為包含所有欄位名稱的完整語句。

  • --create-options
    指定dump文件中CREATE TABLE語句包含所有MySQL表選項。

  • --hex-blob
    轉儲二進位列為十六進位格式。涉及的類型有BINARY、VARBINARY、BLOB和BIT。

  • --quote-names, -Q
    指定dump文件中資料庫、表和欄位的引用標識符為反引號 `，預設開啟，通過選項 --skip-quote-names來禁用。如果SQL_MODE當中有ANSI_QUOTES，則標識符為雙引號 "。

  • --result-file=file_name, -r file_name
    指定dump輸出到給定文件。

  • --tz-utc
    指定dump文件中表TIMESTAMP欄位導出格式以適應不同時區伺服器導入時值的準確性。預設是開啟，使用選項 --skip-tz-utc禁用。

過濾選項(Filtering Options)

  • --all-databases, -A
    指定dump出所有庫當中的所有表。

  • --databases, -B
    指定dump一個或多個資料庫。該選項會在dump每個資料庫之前添加CREATE DATABASE和USE語句，如果沒有指定該選項，則mysqldump將第一個參數當成是資料庫，第二個參數當成是表。通常用來備份INFORMATION_SCHEMA和performance_schema庫，預設情況下是不導出這兩個庫的。

  • --no-data, -d
    指定只dump表結構，而不dump表數據。

  • --tables
    指定dump一張或多張表。會覆蓋選項 --databases, -B，把該選項之後所有參數當成是表。

  • --ignore-table=db_name.tbl_name
    指定忽略dump某張表。格式必須是db.table，如果有多張表需要多次指定該選項，也適用於視圖。

  • --where='where_condition', -w 'where_condition'
    指定where條件dump數據。

  • --events, -E
    指定dump資料庫中的計劃事件。需要對庫有EVENT許可權。

  • --routines, -R
    指定dump資料庫中的函數和存儲過程。

  • --triggers
    指定dump表中的觸發器。需要對錶有TRIGGER許可權。

性能選項(Performance Options)

  • --disable-keys, -K
    指定dump文件里INSERT表時添加/*!40000 ALTER TABLE tbl_name DISABLE KEYS */語句，完成後再添加/*!40000 ALTER TABLE tbl_name ENABLE KEYS */語句，這樣可以加快表數據導入速度，但只對有非唯一索引的MyISAM表有效果。

  • --extended-insert, -e
    指定dump文件當中的INSERT語句為多行格式，降低dump文件大小。

  • --insert-ignore
    使用INSERT IGNORE代替INSERT語句。

  • --opt
    預設開啟，包含了一系列選項的集合，主要有 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset，如果要禁用，可以使用選項 --skip-opt。

  • --quick, -q
    對於大表的dump很有幫助。預設開啟。該選項使mysqldump直接從表中獲取數據輸出而不經過MySQL的緩存。

事務選項(Transactional Options)

  • --add-locks
    指定dump文件中每張表INSERT語句之前添加LOCK TABLES語句，INSERT語句之後添加UNLOCK TABLES語句。這樣可以提高導入效率。

  • --flush-logs, -F
    在dump開始之前切換日誌。該選項需要RELOAD許可權。如果與選項 --all-databases聯合使用的話會導致每dump一個庫之前都要進行日誌切換。如果有使用選項 --lock-all-tables、--master-data或者 --single-transaction，則在dump過程當中只進行一次日誌切換。如果希望dump與日誌的切換同時發生，則使用 --flush-logs與選項 --lock-all-tables、--master-data或者 --single-transaction一同使用。

  • --flush-privileges
    在dump完mysql資料庫之後添加FLUSH PRIVILEGES語句。

  • --lock-all-tables, -x
    在dump過程中鎖住所有資料庫中所有表。該選項會自動關閉 --single-transaction和 --lock-tables選項。

  • --lock-tables, -l
    在dump每個庫時鎖定庫中的表。這個選項只能保證庫中表的狀態一致，因為針對每個庫進行加鎖，所以無法保證所有庫都是一致狀態。

  • --no-autocommit
    指定dump文件中每條INSERT語句包含在SET autocommit = 0和COMMIT語句之間。

  • --order-by-primary
    在dump每張表的時候以表的主鍵或第一個唯一索引為序。對於導出是MyISAM表而導入是InnoDB表很有用。

  • --single-transaction
    在dump開始之前將會話事務隔離級別設置成REPEATABLE READ並執行語句START TRANSACTION，只適用於事務引擎的表如InnoDB。當指定該選項進行dump的過程中，應該避免其它會話連接執行如下語句：ALTER TABLE，CREATE TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE，因為一致性讀並不能隔離以上語句，所以當mysqldump執行SELECT語句進行dump表數據時，其它會話執行以上語句容易產生數據不一致或者報錯。
    該選項與 --lock-tables是互斥的，因為LOCK TABLES語句會導致在執行的事務隱式提交。

用法示例

  • 備份所有庫
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 -A > test_all.sql
  • 備份一個或多個庫
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 -B test1 test2 > test1_test2.sql
  • 備份一張表或多張表
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 t1 > test1_t1.sql
或
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 --tables t1 > test1_t1.sql
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 t1 t2 > test1_t1_t2.sql
或
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 --tables t1 t2 > test1_t1_t2.sql
  • 備份庫結構(同時會包含表結構)
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 --no-data -B test1 test2 > test1_test2.sql
  • 只備份表結構
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 --no-data test1 --tables t1 t2 > test1_t1_t2.sql
  • 只備份表數據(不包含建表語句)
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 --no-create-info test1 --tables t1 t2 > test1_t1_t2.sql
  • 忽略一張或多張表備份
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 --ignore-table=test1.t1 --ignore-table=test1.t2 > test1_ig_t1_t2.sql
  • 輸出文件通過管道傳到gzip命令進行壓縮
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 -A | gzip > test_all.sql.gz
  • 指定where條件備份表數據
# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 test1 --tables t1 --where='c1>1000000' > test1_t1_w.sql

不包含建表語句

# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 --no-create-info test1 --tables t1 --where='c1>1000000' > test1_t1_w.sql

註意事項

  • 預設情況下，mysqldump是不會導出INFORMATION_SCHEMA、performance_schema和sys的，如果需要導出，則在命令行中顯示指定選項 --databases, -B；
  • mysqldump不會導出InnoDB創建表空間CREATE TABLESPACE語句；
  • 不建議使用當前版本的mysqldump導出文件導入MySQL5.6.9(或更早)開啟GTID的版本；
  • 如果general_log和slow_query_log都是指定存存儲在表中，mysqldump導出文件包含general_log和slow_query_log表的創建語句，但並不包含表數據。

參考

☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL 基礎知識梳理學習（三）----InnoDB日誌相關的幾個要點
    1.REDO LOG 是用來做資料庫crash recovery的，是保證資料庫數據正確性、完整性及持久性的重要機制之一。在資料庫操作中，它保存了對InnoDB表中數據的修改記錄，所以也叫日誌文件。 2.日誌文件內部也是按照大小相等的頁面切割，頁面大小為512個位元組（回顧下數據頁是多大？ ）。考慮到 ...
  • oracle逐步學習總結之許可權和角色（基礎六）
    繼續上節的索引，這次主要總結oracle資料庫的許可權和角色問題！ ...
  • 讓MySQL查詢更加高效——對查詢進行重構
    在優化有問題的查詢時，目標應該是找到一個更優的方法獲得實際需要的結果，而不是一定總是要求從MySQL獲取一模一樣的結果集 ...
  • SQLServer之修改用戶自定義資料庫用戶
    修改用戶自定義資料庫用戶註意事項 預設架構將是伺服器為此資料庫用戶解析對象名時將搜索的第一個架構。 除非另外指定，否則預設架構將是此資料庫用戶創建的對象所屬的架構。 如果用戶具有預設架構，則將使用預設架構。 如果用戶不具有預設架構，但該用戶是具有預設架構的組的成員，則將使用該組的預設架構。 如果用戶 ...
  • 新增表前判斷同名錶是否存在（mysql）
    新增多個表時，如果有同名錶會報錯，導致其中一個表不能正確創建，此時可以用以下語句進行判斷 ...
  • redis測試實踐
    最近測試服務端的時候，接觸到了redis，之前也看過，但不系統，藉著這次實踐，記錄一下。 一、寫在前面 Redis是一個開源的使用ANSI C語言編寫、遵守BSD協議、支持網路、可基於記憶體亦可持久化的日誌型、Key-Value資料庫，並提供多種語言的API。 它通常被稱為數據結構伺服器，因為值（va ...
  • PostgreSQL10.1 linux安裝配置
    一 安裝準備 1.首先從官網下載PostgreSQL壓縮包（也可以使用yum安裝），我們這裡使用的是10.1的版本 2.將文件上傳到linux服務區目錄（我們這裡放在/root 中） 3.解壓縮 tar -vxf postgresql-10.1.tar 壓縮包格式不同請註意替換命令 4.由於我們這裡 ...
  • Linux中mysql中文亂碼
    問題描述： 當我們在linux系統上裝載mysql後操作資料庫的時候，會出現中文亂碼問題，比如做插入操作，發現添加到資料庫的數據中文出現亂碼，遠程連接資料庫數據顯示中文亂碼等。 下麵就將解決linux下mysql中文亂碼問題。 環境CentOS-6.5 1.打開Linux視窗，啟動mysql： my ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類