MySQLDump在使用之前一定要想到的事情

dbqs8710發表於2015-01-13
MySql
MySQLDump經常用於遷移資料和備份.

下面建立實驗資料,兩個資料庫和若干表
create database db1 ;
use db1;
create table t1(id int primary key);
insert into t1 values(1),(2),(3);
create table t2(id int primary key);
insert into t2 values(1),(2),(3);

create database db2;
use db2;
create table t3(id int primary key);
insert into t3 values(1),(2),(3);
create table t4(id int primary key);
insert into t4 values(1),(2),(3);
commit;

mysqldump的常用引數如下
1.匯出指定資料庫(--databases)
mysqldump -uroot --databases db1 db2 > test.sql

2.匯出指定資料庫的結構（-d）
mysqldump -uroot --databases -d db1 db2 > test.sql

3.匯出之前重新整理日誌(-F)

4.設定字符集(--default-character-set)

5.設定擴充套件Insert(-e --skip-extended-insert禁用擴充套件Insert)

6.鎖表(--lock-tables)

7.鎖所有資料庫的所有表(--lock-all-tables)

8.一致性讀,僅針對InnoDB有效（--single-transaction）

9.獲取binlog位置(--master-data 1將binlog位置寫在正文 2將binlog位置寫入註釋)

常用用法
1.遷移資料
將db1 db2資料庫通過mysqldump匯出.然後通過管道匯入目標資料庫
mysqldump -uroot --single-transaction--databases db1 db2 | mysql -uroot -p123456 -h 172.16.1.25

2.匯出資料備份或者建立Slave
mysqldump -uroot --single-transaction --master-data --databases db1 db2 > test.sql

3.分別產生表結構和資料
select into outfile是針對單個表的.使用--tab選項可以匯出多個表
mysqldump -uroot --single-transaction  --tab=F:\  db1


重要引數解析(MySQL 5.6.14)
開啟MySQL general_log,然後使用mysqldump操作,檢視產生的日誌.

1.--lock-tables
執行命令
mysqldump -uroot --lock-tables --databases db1 db2 > test.sql
它在匯出db1的時候,會對db1所有的表上鎖,匯出結束之後釋放鎖.然後再同樣匯出db2.
也就是說在db1匯出的時候,db2的資料可能還在變化.


2.--lock-all-tables
mysqldump -uroot --lock-all-tables --databases db1 db2 > test.sql
它會在一開始就對所有的資料庫的所有表上鎖,請注意它會使用FLUSH TABLES


3.--single-transaction
mysqldump -uroot --single-transaction --databases db1 db2 > test.sql
可以看到它設定整個匯出的過程為一個事務.避免了鎖


4.--master-data
它對所有資料庫的所有表上了鎖,並且查詢binlog的位置。請注意它會使用FLUSH TABLES


5.--master-data + --single-transaction
mysqldump -uroot --master-data --single-transaction --databases db1 db2 > test.sql
這種組合,會先對所有資料庫的所有表上鎖,讀取binlog的資訊之後就立即釋放鎖,這個過程是十分短暫的。
然後整個匯出過程都在一個事務裡.
請注意它會使用FLUSH TABLES



MySQLDump在使用之前一定要想到的事情

如果mysqldump執行的過程中需要flush tables,而正在此時,有一個慢SQL正在執行,這時mysqldump會被阻塞（waiting for table flush）,
並且其他連線對這個表的所有操作（甚至查詢）都被阻塞.系統Hung了.

這個問題在XtraBackup備份的時候同樣存在.

如果是人工執行,一定要開啟另外一個連線,監控 show processlist，檢視是否阻塞.
如果是排程執行,拼人品了.

其實優化慢SQL才是正道.

另外在mysqldump匯出的過程中,不要有任何的DDL操作,否則同樣會引發metadata lock的連環阻塞.

參考:
http://blog.itpub.net/29254281/viewspace-1157701/(Waiting for table阻塞查詢的問題)
http://blog.itpub.net/29254281/viewspace-1383193/

http://imysql.com/2008_10_24_deep_into_mysqldump_options
http://hidba.org/?p=421

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1392757/，如需轉載，請註明出處，否則將追究法律責任。

相關文章