mysqldump的基本用法

Mysqldump為邏輯備份工具，透過sql或文字方式匯出資料；

執行該命令的使用者對要匯出的物件應該有select/show view/trigger許可權以匯出相應表/檢視/觸發器，如果不指定—single-transaction還應具有lock tables許可權；

 

限制

不適合大規模資料備份：如果以InnoDB表為主，採用mysqlbackup進行物理備份；如果MyISAM為主，則採用mysqlhotcopy；

 

工作機制

可逐行抽取，也可快取整個表內容然後一次dump，但對於大表則比較消耗記憶體；

--quick採用row-by-row模式，也可使用—opt(包含--quick)；--skip-quick則使用快取機制；

Mysqldump可生成兩種檔案：文字檔案和sql檔案；

--tab=dirname選項為每個表生成兩個檔案sql和txt(select … into outfile，需要file許可權)

 

選項

 

複製相關

--dump-slave：生成change master語句

--apply-slave-statements：指定—dump-slave時，在change master to前後新增stop slave/start slave

--include-master-host-port：對生成的change master新增master_host/master_port選項

--master-data(1|2)：可用於建立slave，該選項會輸出change master to 幷包含binlog的具體位置，在匯入slave後就不必再手工配置change master to資訊；如果值為2則以註釋的方式生成；

此選項會自動禁止—lock-tables同時啟用—lock-all-tables(除非指定了—single-transaction)；

需要reload許可權，並且啟用了二進位制日誌；

 

Sql相關

--add-drop-table：每個create table前新增drop table

--complete-insert：生成完整的insert，包含列名

--no-data：不dump表內容

 

網路相關

--compress/-C：壓縮伺服器/客戶端之間的通訊資訊

--max_allowed_packet：

--net_buffer_length：TCP/IP的buffer大小，當使用—extended-insert生成insert … values時，每個insert受限於net_buffer_length，同時注意mysql伺服器中該變數的設定；

 

效能相關

--delayed-insert：生成insert delayed語句，5.6.6起 不再支援delayed insert

--extended-insert：生成insert … values，匯入時可批次執行，減少sql parse

--disable-keys：僅用於MyISAM表的非唯一索引，在匯入所有行後再建立索引；每個表的insert前後分別有

                 /*!40000 ALTER TABLE tbl_name DISABLE KEYS */

                  ……

                /*!40000 ALTER TABLE tbl_name ENABLE KEYS */

--opt：為多個選項集合--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

--quick：row-by-row匯出資料

 

事務相關

--flush-logs：在dump前重新整理日誌檔案，需要reload許可權

--add-locks：每個表前後生成lock tables/unlock tables

--lock-all-tables/-x：獲取global read lock鎖定所有表，與--single-transaction/--lock-tables不相容

--lock-tables/-l：對於每個即將dump的db，提前鎖定其下所有表；對MyISAM採用read local，InnoDB最好採用--single-transaction

--no-autocommit：將每個表的所有insert新增set autocommit=0/commit

--single-transaction：只用於事務表，不必鎖定要匯出的表；將事務隔離級別設定為repeatable read，在dump資料前執行start transaction；

注：5.5.3之前，使用該選項時相應表不能進行alter/create/drop/rename/truncate table操作，一致性讀不能隔離這些操作；而5.5.3引入metadata lock解決了這一問題；

 此選項在dump之前需要獲取global read lock

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

http://dev.mysql.com/doc/refman/5.6/en/backup-policy.html

 

 

用途

複製資料庫

Mysqldump db1 > dump.sql –不能指定—databases，否則檔案開頭會有use db1

Mysqladmin create db2

Mysql db2 < dump.sql

 

匯出儲存程式

--events：匯出scheduler event

--routines：儲存過程和函式

--triggers：觸發器，預設匯出

這些選項分別由對應的skip-***，

 

分別匯出表定義和內容

--no-data：只匯出表定義

--no-create-info：只匯出表資料

匯出所有的create語句

Mysqldump –no-data –routines –events test > dump defs.sql

 

驗證升級相容性

先匯出各種物件定義sql，檢驗新版本是否支援建立語法

然後匯出並同步表資料，進行測試查詢

 

 

相關工具

Mysqldump無法並行執行

mk-parallel-dump可以並行匯出表資料，但是不建議用於備份

 

mydumper也是並行匯出