mysqldump單表備份加--single-transaction --master-data=2引數執行過程的內部剖析
開啟general_log:
mysql(mdba@localhost:(none) 01:08:55)>set global general_log=on;
Query OK, 0 rows affected (0.02 sec)
mysql(mdba@localhost:(none) 01:09:07)>show variables like '%general%';
+------------------+-------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)
mysql(mdba@localhost:(none) 01:31:43)>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbb |
| credit |
| e_contract |
| ixinnuo_sjcj |
| ixinnuo_sjcj-_T |
| mysql |
| percona |
| performance_schema |
| sys |
| test |
| whpmap |
+--------------------+
12 rows in set (0.05 sec)
mysql(mdba@localhost:(none) 01:31:46)>use credit
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql(mdba@localhost:credit 01:32:02)>show tables;
+----------------------+
| Tables_in_credit |
+----------------------+
| f_audit |
| f_audit_bak_20170504 |
+----------------------+
2 rows in set (0.00 sec)
例如備份credit庫下面的f_audit表，使用--single-transaction --master-data=2引數:
[root@localhost tmp]# mysqldump -umdba -pdsf0723 --single-transaction --master-data=2 credit f_audit > /tmp/f_audit.sql
檢視general_log:
[root@localhost tmp]# cat /usr/local/mysql/data/localhost.log
/opt/app/mysql/5.7.18/bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2017-08-31T05:09:09.189375Z 19513 Query show variables like '%gen%'
2017-08-31T05:09:24.603322Z 19513 Query SELECT DATABASE()
2017-08-31T05:09:24.604041Z 19513 Init DB credit
2017-08-31T05:09:24.605173Z 19513 Query show databases
2017-08-31T05:09:24.605838Z 19513 Query show tables
2017-08-31T05:09:24.605976Z 19513 Field List f_audit
2017-08-31T05:09:24.606416Z 19513 Field List f_audit_bak_20170504
2017-08-31T05:09:26.575331Z 19513 Query show tables
2017-08-31T05:11:18.126829Z 19514 Connect mdba@localhost on using Socket
2017-08-31T05:11:18.126927Z 19514 Query /*!40100 SET @@SQL_MODE='' */
2017-08-31T05:11:18.130352Z 19514 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05:11:18.130450Z 19514 Query FLUSH /*!40101 LOCAL */ TABLES
2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK
2017-08-31T05:11:18.140318Z 19514 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-31T05:11:18.140355Z 19514 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-31T05:11:18.140417Z 19514 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-31T05:11:18.150840Z 19514 Query SHOW MASTER STATUS
2017-08-31T05:11:18.168898Z 19514 Query UNLOCK TABLES
2017-08-31T05:11:18.181199Z 19514 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017-08-31T05:11:18.210240Z 19514 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-08-31T05:11:18.220125Z 19514 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-08-31T05:11:18.221667Z 19514 Init DB credit
2017-08-31T05:11:18.221712Z 19514 Query SHOW TABLES LIKE 'f\_audit'
2017-08-31T05:11:18.221834Z 19514 Query SAVEPOINT sp
2017-08-31T05:11:18.221887Z 19514 Query show table status like 'f\_audit'
2017-08-31T05:11:18.222054Z 19514 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-31T05:11:18.222100Z 19514 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:11:18.222142Z 19514 Query show create table `f_audit`
2017-08-31T05:11:18.222231Z 19514 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:11:18.222287Z 19514 Query show fields from `f_audit`
2017-08-31T05:11:18.222614Z 19514 Query show fields from `f_audit`
2017-08-31T05:11:18.222940Z 19514 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:11:18.223074Z 19514 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:11:18.223116Z 19514 Query use `credit`
2017-08-31T05:11:18.223165Z 19514 Query select @@collation_database
2017-08-31T05:11:18.223224Z 19514 Query SHOW TRIGGERS LIKE 'f\_audit'
2017-08-31T05:11:18.223465Z 19514 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:11:18.223509Z 19514 Query ROLLBACK TO SAVEPOINT sp
2017-08-31T05:11:18.223547Z 19514 Query RELEASE SAVEPOINT sp
2017-08-31T05:11:18.281441Z 19514 Quit
發現其執行了FLUSH TABLES WITH READ LOCK:
2017-08-31T05:11:18.130450Z 19514 Query FLUSH /*!40101 LOCAL */ TABLES
2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK
透過進一步測試發現mysqldump備份的時候只使用--single-transaction不使用--master-data=2引數是不會進行鎖表的。
不加--master-data=2引數:
[root@localhost tmp]#mysqldump -umdba -pdsf0723 --single-transaction credit f_audit > f_audit.sql
檢視general_log:
2017-08-31T05:24:05.890881Z 19516 Connect mdba@localhost on using Socket
2017-08-31T05:24:05.890984Z 19516 Query /*!40100 SET @@SQL_MODE='' */
2017-08-31T05:24:05.891037Z 19516 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05:24:05.891478Z 19516 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-31T05:24:05.891520Z 19516 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-31T05:24:05.891589Z 19516 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-31T05:24:05.893983Z 19516 Query UNLOCK TABLES
2017-08-31T05:24:05.894099Z 19516 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017-08-31T05:24:05.911788Z 19516 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-08-31T05:24:05.916870Z 19516 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-08-31T05:24:05.918150Z 19516 Init DB credit
2017-08-31T05:24:05.918208Z 19516 Query SHOW TABLES LIKE 'f\_audit'
2017-08-31T05:24:05.918336Z 19516 Query SAVEPOINT sp
2017-08-31T05:24:05.918390Z 19516 Query show table status like 'f\_audit'
2017-08-31T05:24:05.918558Z 19516 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-31T05:24:05.918603Z 19516 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:24:05.918648Z 19516 Query show create table `f_audit`
2017-08-31T05:24:05.918730Z 19516 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:24:05.918786Z 19516 Query show fields from `f_audit`
2017-08-31T05:24:05.919114Z 19516 Query show fields from `f_audit`
2017-08-31T05:24:05.919417Z 19516 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:24:05.919547Z 19516 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:24:05.919592Z 19516 Query use `credit`
2017-08-31T05:24:05.919639Z 19516 Query select @@collation_database
2017-08-31T05:24:05.919698Z 19516 Query SHOW TRIGGERS LIKE 'f\_audit'
2017-08-31T05:24:05.919999Z 19516 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:24:05.920064Z 19516 Query ROLLBACK TO SAVEPOINT sp
2017-08-31T05:24:05.920107Z 19516 Query RELEASE SAVEPOINT sp
2017-08-31T05:24:05.971293Z 19516 Quit
發現並沒有執行FLUSH TABLES WITH READ LOCK.
結論:
只使用--master-data=2引數為了獲取show master status，會執行FLUSH TABLES WITH READ LOCK的全域性鎖，在業務低峰期，mysql獲取全域性鎖會很快,但在業務高峰期又有很多庫表的情況下是不建議執行全域性鎖的，
因此mysqldump備份儘量放在業務低峰期做，或者備份時直接去掉--master-data=2引數.
mysql(mdba@localhost:(none) 01:08:55)>set global general_log=on;
Query OK, 0 rows affected (0.02 sec)
mysql(mdba@localhost:(none) 01:09:07)>show variables like '%general%';
+------------------+-------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)
mysql(mdba@localhost:(none) 01:31:43)>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbb |
| credit |
| e_contract |
| ixinnuo_sjcj |
| ixinnuo_sjcj-_T |
| mysql |
| percona |
| performance_schema |
| sys |
| test |
| whpmap |
+--------------------+
12 rows in set (0.05 sec)
mysql(mdba@localhost:(none) 01:31:46)>use credit
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql(mdba@localhost:credit 01:32:02)>show tables;
+----------------------+
| Tables_in_credit |
+----------------------+
| f_audit |
| f_audit_bak_20170504 |
+----------------------+
2 rows in set (0.00 sec)
例如備份credit庫下面的f_audit表，使用--single-transaction --master-data=2引數:
[root@localhost tmp]# mysqldump -umdba -pdsf0723 --single-transaction --master-data=2 credit f_audit > /tmp/f_audit.sql
檢視general_log:
[root@localhost tmp]# cat /usr/local/mysql/data/localhost.log
/opt/app/mysql/5.7.18/bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2017-08-31T05:09:09.189375Z 19513 Query show variables like '%gen%'
2017-08-31T05:09:24.603322Z 19513 Query SELECT DATABASE()
2017-08-31T05:09:24.604041Z 19513 Init DB credit
2017-08-31T05:09:24.605173Z 19513 Query show databases
2017-08-31T05:09:24.605838Z 19513 Query show tables
2017-08-31T05:09:24.605976Z 19513 Field List f_audit
2017-08-31T05:09:24.606416Z 19513 Field List f_audit_bak_20170504
2017-08-31T05:09:26.575331Z 19513 Query show tables
2017-08-31T05:11:18.126829Z 19514 Connect mdba@localhost on using Socket
2017-08-31T05:11:18.126927Z 19514 Query /*!40100 SET @@SQL_MODE='' */
2017-08-31T05:11:18.130352Z 19514 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05:11:18.130450Z 19514 Query FLUSH /*!40101 LOCAL */ TABLES
2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK
2017-08-31T05:11:18.140318Z 19514 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-31T05:11:18.140355Z 19514 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-31T05:11:18.140417Z 19514 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-31T05:11:18.150840Z 19514 Query SHOW MASTER STATUS
2017-08-31T05:11:18.168898Z 19514 Query UNLOCK TABLES
2017-08-31T05:11:18.181199Z 19514 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017-08-31T05:11:18.210240Z 19514 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-08-31T05:11:18.220125Z 19514 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-08-31T05:11:18.221667Z 19514 Init DB credit
2017-08-31T05:11:18.221712Z 19514 Query SHOW TABLES LIKE 'f\_audit'
2017-08-31T05:11:18.221834Z 19514 Query SAVEPOINT sp
2017-08-31T05:11:18.221887Z 19514 Query show table status like 'f\_audit'
2017-08-31T05:11:18.222054Z 19514 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-31T05:11:18.222100Z 19514 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:11:18.222142Z 19514 Query show create table `f_audit`
2017-08-31T05:11:18.222231Z 19514 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:11:18.222287Z 19514 Query show fields from `f_audit`
2017-08-31T05:11:18.222614Z 19514 Query show fields from `f_audit`
2017-08-31T05:11:18.222940Z 19514 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:11:18.223074Z 19514 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:11:18.223116Z 19514 Query use `credit`
2017-08-31T05:11:18.223165Z 19514 Query select @@collation_database
2017-08-31T05:11:18.223224Z 19514 Query SHOW TRIGGERS LIKE 'f\_audit'
2017-08-31T05:11:18.223465Z 19514 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:11:18.223509Z 19514 Query ROLLBACK TO SAVEPOINT sp
2017-08-31T05:11:18.223547Z 19514 Query RELEASE SAVEPOINT sp
2017-08-31T05:11:18.281441Z 19514 Quit
發現其執行了FLUSH TABLES WITH READ LOCK:
2017-08-31T05:11:18.130450Z 19514 Query FLUSH /*!40101 LOCAL */ TABLES
2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK
透過進一步測試發現mysqldump備份的時候只使用--single-transaction不使用--master-data=2引數是不會進行鎖表的。
不加--master-data=2引數:
[root@localhost tmp]#mysqldump -umdba -pdsf0723 --single-transaction credit f_audit > f_audit.sql
檢視general_log:
2017-08-31T05:24:05.890881Z 19516 Connect mdba@localhost on using Socket
2017-08-31T05:24:05.890984Z 19516 Query /*!40100 SET @@SQL_MODE='' */
2017-08-31T05:24:05.891037Z 19516 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05:24:05.891478Z 19516 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-31T05:24:05.891520Z 19516 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-31T05:24:05.891589Z 19516 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-31T05:24:05.893983Z 19516 Query UNLOCK TABLES
2017-08-31T05:24:05.894099Z 19516 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017-08-31T05:24:05.911788Z 19516 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-08-31T05:24:05.916870Z 19516 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-08-31T05:24:05.918150Z 19516 Init DB credit
2017-08-31T05:24:05.918208Z 19516 Query SHOW TABLES LIKE 'f\_audit'
2017-08-31T05:24:05.918336Z 19516 Query SAVEPOINT sp
2017-08-31T05:24:05.918390Z 19516 Query show table status like 'f\_audit'
2017-08-31T05:24:05.918558Z 19516 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-31T05:24:05.918603Z 19516 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:24:05.918648Z 19516 Query show create table `f_audit`
2017-08-31T05:24:05.918730Z 19516 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:24:05.918786Z 19516 Query show fields from `f_audit`
2017-08-31T05:24:05.919114Z 19516 Query show fields from `f_audit`
2017-08-31T05:24:05.919417Z 19516 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:24:05.919547Z 19516 Query SET SESSION character_set_results = 'binary'
2017-08-31T05:24:05.919592Z 19516 Query use `credit`
2017-08-31T05:24:05.919639Z 19516 Query select @@collation_database
2017-08-31T05:24:05.919698Z 19516 Query SHOW TRIGGERS LIKE 'f\_audit'
2017-08-31T05:24:05.919999Z 19516 Query SET SESSION character_set_results = 'utf8'
2017-08-31T05:24:05.920064Z 19516 Query ROLLBACK TO SAVEPOINT sp
2017-08-31T05:24:05.920107Z 19516 Query RELEASE SAVEPOINT sp
2017-08-31T05:24:05.971293Z 19516 Quit
發現並沒有執行FLUSH TABLES WITH READ LOCK.
結論:
只使用--master-data=2引數為了獲取show master status，會執行FLUSH TABLES WITH READ LOCK的全域性鎖，在業務低峰期，mysql獲取全域性鎖會很快,但在業務高峰期又有很多庫表的情況下是不建議執行全域性鎖的，
因此mysqldump備份儘量放在業務低峰期做，或者備份時直接去掉--master-data=2引數.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2144368/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysqldump備份時加single-transaction會不會加鎖MySql
- mysqldump的single-transaction和master-dataMySqlAST
- mysqldump備份時使用single-transaction會不會加鎖？？？MySql
- [轉帖]mysqldump --master-data=2 --single-transactionMySqlAST
- mysqldump 備份單張表和恢復MySql
- 深入理解mysqldump原理 --single-transaction --lock-all-tables --master-dataMySqlAST
- mysqldump與innobackupex備份過程你知多少（一）MySql
- mysqldump與innobackupex備份過程你知多少（二）MySql
- mysqldump與innobackupex備份過程你知多少（三）MySql
- mysqldump --single-transaction引數的作用MySql
- 簡單的mysqldump備份（windows）MySqlWindows
- mysqldump --master-data=2引數的含義MySqlAST
- 剖析SQL語句的執行過程SQL
- mysqldump+mysqlbinlog執行備份與還原MySql
- mysqldump備份時的資料一致性問題--single-transactionMySql
- mysqldump與innobackupex備份過程你知多少（完結篇）MySql
- mysqldump 進行資料備份MySql
- mysqldump如何只備份表結構MySql
- MySQLDump的備份方法MySql
- Mysqldump的備份流程MySql
- 用mysqldump --master-data 建立slaveMySqlAST
- 一個Flask應用執行過程剖析Flask
- 10_SQL語句執行過程剖析SQL
- 【mysqldump】mysqldump及備份恢復示例MySql
- mysqldump的內部實現原理MySql
- mysqldump備份技巧分享MySql
- mysqldump 備份指令碼MySql指令碼
- mysqldump備份指令碼MySql指令碼
- 邏輯備份--mysqldumpMySql
- 揭秘計算機指令執行的神秘過程：CPU內部的絕密操作計算機
- PL/SQL在執行過程中，對其依賴的表，函式，過程都加lock和pin嗎？SQL函式
- 關於mysqldump備份非事務表的注意事項MySql
- oracle的內部啟動過程Oracle
- MySQL冷備份過程MySql
- 通過內部的hint來控制執行計劃
- windows mysqldump備份指令碼WindowsMySql指令碼
- mysqldump備份原理解析MySql
- mysql 邏輯備份 （mysqldump）MySql