mysqldump 資料庫備份程式

mysqldump 資料庫備份程式
mysqldump客戶端工具執行邏輯備份，生成一組SQL語句可以用來執行重新建立原資料庫物件定義和表資料。它dumps一個或多個MySQL資料庫的備份或者傳輸到另外的伺服器。mysqldump命令也可以生成CSV或，其它文字或XML格式的輸出

mysqldump的優點是在還原之前它可以方便與靈活的檢視或者編輯輸出。可以克隆資料庫來給開發人員和DBA使用，或者提供測試環境。它並不是一種用來備份大量資料的快速或可擴充套件的方案。使用大資料大小，即使備份操作花費的時間合理，但還原資料可能非常緩慢因為重放SQL語句呼叫磁碟I/O來執行插入操作，索引建立等等。

對於大規模的備份和還原，物理備份更合適，可以以它們的原有格式來複制資料檔案來快速完成還原操作:
.如果你的表主要是InnoDB表，或者如果你有一種混合的InnoDB與MyISAM表，可以考慮MySQL企業級備份產品的mysqlbackup命令。它為InnoDB備份提供了最好的效能並且破壞性最小，它也可以從MyISAM和其它儲存引擎中備份資料表，並且它提供了許多不同的選項來適應不同的備份場景。

mysqldump可以逐行檢索和dump表的內容或者它可以在dump它之前從一個表與記憶體的快取中來檢索整個內容。如果dump一個大表從記憶體中的快取中檢索可能是一個問題。為了逐行dump表，使用--quick選項(或--opt,它啟用--quick)--opt選項(因此--quick)預設被啟用，因此啟用記憶體快取，使用--skip-quick選項.

如果使用一個最近版本的mysqldump來生成一個dump被載入到一箇舊版本的MySQL伺服器中，使用--skip-opt選項來代替--extended-insert選項。

有三種常用方式來使用mysqldump命令來完成對一個或多個表，一個或多個資料庫，或整個MySQL伺服器來進行dump:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

為了dump整個資料庫，在db_name後面不要接任何表名，或者使用--database或--all-databases選項。

mysqldump支援以下選項，它可以在命令列或在選項檔案中的[mysqldump]與[client]組中進行指定。

mysqldump選項:
--add-drop-database 在每個create database語句之前加上drop database語句

--add-drop-table 在每個create table語句之前加上drop table語句

--add-drop-trigger 在每個create trigger語句之前加上drop table語句

--add-locks 使用lock tables與unlock tables語句來包圍每個表的dump

--all-databases dump所有資料庫中的所有表

--allow-keywords 允許建立關鍵字列名

--apply-slave-statements 在change master語句之前包含stop slave，在結束輸出之前包含start slave

--bind-address 使用特定的網路介面來連線MySQL伺服器

--character-sets-dir 安裝字符集的目錄

--comments 新增註釋到dump檔案中

--compact 產生更緊湊的輸出

--compatible 產生的輸出將與其它的資料庫系統或者舊版本的MySQL伺服器更相容

--complete-insert 使用完整的insert語句包括列名

--compress 對在客戶端和伺服器之間傳送的所有資訊進行壓縮

--create-options 在create table語句中包含所有的MySQL特定的表選項

--databases 將所有的命名引數解釋為資料庫名

--debug 寫除錯日誌

--debug-check 當程式退出時列印除錯資訊

--debug-info 當程式退出時列印除錯資訊，記憶體與CPU統計資訊

--default-auth 要使用的身份認證外掛

--default-character-set 指定的預設字符集

--default-extra-file 除了常用的選項檔案外還要讀取的選項檔案

--default-file 只讀指定的選項檔案

--default-group-suffix 選項組字尾值

--delete-master-logs 在一個主複製伺服器上，在執行dump操作後刪除binary日誌檔案

--disable-keys 對於每個表，圍繞insert語句使用語句來禁用與啟用關鍵字

--dump-date 如果--comments被指定，包含dump日期作為"Dump completed on"註釋

--dump-slave 包含change master語句來列出slave's master相關的二進位制日誌

--enable-cleartext-plugin 使用cleartext身份認證外掛

--events 來自轉儲資料庫的轉儲事件

--extended-insert 使用多行插入語法

--fields-enclosed-by 這個選項與--tab選項一起使用並且與load data infile相關子句有相同的意思

--fields-escaped-by 這個選項與--tab選項一起使用並且與load data infile相關子句有相同的意思

--fields-optionally-enclosed-by 這個選項與--tab選項一起使用並且與load data infile相關子句有相同的意思。

--fields-terminated-by 這個選項與--tab選項一起使用並且與load data infile相關子句有相同的意思

--flush-logs 在開始dump之前清空MySQL伺服器的日誌檔案

--flush-privileges 在dump MySQL資料庫後釋出一個flush privileges語句。

--force 在一個表被dump時即使出現錯誤也會繼續

--help 顯示幫助資訊並退出

--hex-blob 使用十六進位制表示法來dump二進位制列

--host 要連線的主機(IP地址或主機名)

--ignore-error 忽略特定的錯誤資訊

--ignore-table 不dump指定的表

--include-master-host-port 在使用--dump-slave生成的change master語句中包含master_host/master_port選項

--insert-ignore 寫insert ignore而不是insert語句

--lines-terminated-by 這個選項與--tab選項一起使用並且有與load data infile相關子句相同的意思

--lock-all-tables 跨所有資料庫鎖定所有表

--lock-tables 在dump他們之前鎖定所有表

--log-error 將警告與錯誤追加到指定檔案

--master-data 輸出所寫入的二進位制日誌檔名與位置

--max_allowed_packet 傳送到到伺服器或從伺服器接收到的最大包長度

-net_buffer_length TCP/IP與socket通訊的緩衝大小

--no-autocommit 對於每個dump表的insert語句包含了set autocommit=0與commit語句

--no-create-db 不寫create database語句

--no-create-info 對於重建的每個dump表不寫create table語句

--no-data 不dump表的內容

--no-defaults 不讀取選項檔案

--no-set-names 與--skip-set-charset相同

--no-tablespaces 在輸出中不寫入任何create logfile group或create tablespace語句

--opt 對於--add-drop-table --add-locks --create-options --disable-kyes --extended-insert --lock-tables --quick --sset-charset的速記法

--order-by-primary 透過主鍵或它的第一個唯一索引排序來dump每個表的行記錄

--password 當連線伺服器時使用的密碼

--pipe 在Windows上，使用命名管道連線伺服器

--plugin-dir 外掛被安裝的目錄

--port 用於連線的TCP/IP埠號

--print-defaults 列印預設選項

--protocol 使用的連線協議

--quick 從伺服器中一次一行地檢索資料

--quote-names 引號內的識別符號

--replace 寫replace語句而不是insert語句

--result-file 指示輸出到指定檔案

--routines 從被dump的資料庫中dump儲存程式(過程與函式)

--secure-auth 不使用舊格式傳送密碼到伺服器

--set-charset 新增set names default_character_set到輸出中

--set-gtid-purged 是否向輸出新增set @@global.gtid_purged

--shared-memory-base-name 用於共享記憶體連線的共享記憶體名

--single-transaction 在從伺服器dump資料之前執行begin sql語句

--skip-add-drop-table 在每個create table語句之前不新增drop table語句

--skip-add-locks 不新增鎖

--skip-comments 不新增註釋到dump檔案

--skip-compact 不產生更多緊湊輸出

--skip-disable-keys 不禁用關鍵字

--skip-extended-insert 關閉擴充套件插入

--skip-opt 關閉--opt選項集

--skip-quick 不從伺服器一次一行地檢索表的行

--skip-quote-names 不要引用識別符號

--skip-set-charset 不寫set names語句

--skip-triggers 不dump觸發器

--skip-tz-utc 關閉tz-utc

--socket 對於本地連線，使用的Unix socket檔案

--ssl 啟用加密連線

--ssl-ca 包含可信SSL CA列表的檔案路徑

--ssl-capath 包含PEM格式可信SSL CA認證的目錄路徑

--ssl-cert 包含PEM格式X509認證的檔案路徑

--ssl-cipher 用於連線加密的允許密碼列表

--ssl-crl 包含證照撤銷列表的檔案路徑

--ssl-crlpath 包含證照撤銷列表檔案的目錄的路徑

--ssl-key 包含PEM格式的X509 鑰匙的檔案路徑

--ssl-mode 連線伺服器的安全狀態

--ssl-verify-server-cert 根據連線到伺服器時使用的主機名驗證伺服器證照公共名稱值

--tab 生成用製表符分隔的資料檔案

--tables 覆蓋--database或-B選項

--tls-version 對於加密連線允許的連線協議

--triggers 對於每個被dump的表dump觸發器

--tz-utc 新增set time_zone='+00:00'到dump檔案

--user 連線伺服器時所使用的使用者名稱

--verbose 詳細模式

--version 顯示版本資訊並退出

--where 透過指定where條件只dump所選擇的行

--xml 生成XML輸出

1、備份命令
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --database 資料庫名 > 檔名.sql

例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql

2、備份壓縮
匯出的資料有可能比較大，不好備份到遠端，這時候就需要進行壓縮
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --database 資料庫名 | gzip > 檔名.sql.gz

例如： mysqldump -h192.168.1.100 -p 3306 -uroot -ppassword --database cmdb | gzip > /data/backup/cmdb.sql.gz

3、備份同個庫多個表
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --database 資料庫名 表1 表2 .... > 檔名.sql

例如 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql

4、同時備份多個庫
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --databases 資料庫名1 資料庫名2 資料庫名3 > 檔名.sql

例如：mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql

5、備份例項上所有的資料庫
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --all-databases > 檔名.sql

例如：mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases > /data/backup/all_db.sql

6、備份資料出帶刪除資料庫或者表的sql備份
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --add-drop-table --add-drop-database 資料庫名 > 檔名.sql

例如：mysqldump -uroot -ppassword --add-drop-table --add-drop-database cmdb > /data/backup/all_db.sql

7、備份資料庫結構，不備份資料
格式：mysqldump -h主機名 -P埠 -u使用者名稱 -p密碼 --no-data 資料庫名1 資料庫名2 資料庫名3 > 檔名.sql

例如：mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql