溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

如何進(jìn)行mysqldump單表備份加--single-transaction --master-data=2參數(shù)執(zhí)行過程的內(nèi)部剖析

發(fā)布時間：2021-11-16 09:31:16 來源：億速云 閱讀：471 作者：柒染 欄目：MySQL數(shù)據(jù)庫

今天就跟大家聊聊有關(guān)如何進(jìn)行mysqldump單表備份加--single-transaction --master-data=2參數(shù)執(zhí)行過程的內(nèi)部剖析，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

開啟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參數(shù):

[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


發(fā)現(xiàn)其執(zhí)行了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


通過進(jìn)一步測試發(fā)現(xiàn)mysqldump備份的時候只使用--single-transaction不使用--master-data=2參數(shù)是不會進(jìn)行鎖表的。
不加--master-data=2參數(shù):
[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


發(fā)現(xiàn)并沒有執(zhí)行FLUSH TABLES WITH READ LOCK.

只使用--master-data=2參數(shù)為了獲取show master status，會執(zhí)行FLUSH TABLES WITH READ LOCK的全局鎖，在業(yè)務(wù)低峰期，mysql獲取全局鎖會很快,但在業(yè)務(wù)高峰期又有很多庫表的情況下是不建議執(zhí)行全局鎖的，
因此mysqldump備份盡量放在業(yè)務(wù)低峰期做，或者備份時直接去掉--master-data=2參數(shù).

看完上述內(nèi)容，你們對如何進(jìn)行mysqldump單表備份加--single-transaction --master-data=2參數(shù)執(zhí)行過程的內(nèi)部剖析有進(jìn)一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手