Preface
We`re used to get a logical backup set(whole instance) by simply specifying “–single-transaction” and “–master-data” options.For innodb engine tables,we can get a consistent backup.But the non-innodb(not supporting transaction) tables may be not backed up consistently in MySQL 5.7.
Procedure
Check and open general log(if have not enabled it yet).
1 (zlm@192.168.1.101 3306)[(none)]>show variables like `general_log`; 2 +---------------+-------+ 3 | Variable_name | Value | 4 +---------------+-------+ 5 | general_log | OFF | 6 +---------------+-------+ 7 1 row in set (0.00 sec) 8 9 (zlm@192.168.1.101 3306)[(none)]>set @@global.general_log=on; 10 Query OK, 0 rows affected (0.01 sec) 11 12 (zlm@192.168.1.101 3306)[(none)]>show variables like `general_log`; 13 +---------------+-------+ 14 | Variable_name | Value | 15 +---------------+-------+ 16 | general_log | ON | 17 +---------------+-------+ 18 1 row in set (0.00 sec)
Clear the content of general log and tail it.
1 [root@zlm2 09:35:07 /data/mysql/mysql3306/data] 2 #echo ``> zlm2.log 3 4 [root@zlm2 09:35:13 /data/mysql/mysql3306/data] 5 #tail -f zlm2.log
Create test tables.
1 (zlm@192.168.1.101 3306)[(none)]>create database zlm; 2 Query OK, 1 row affected (0.00 sec) 3 4 (zlm@192.168.1.101 3306)[(none)]>use zlm; 5 Database changed 6 7 (zlm@192.168.1.101 3306)[(none)]>create table test_trx( 8 -> id int, 9 -> name varchar(10) 10 -> ) engine=innodb; 11 Query OK, 0 rows affected (0.01 sec) 12 13 (zlm@192.168.1.101 3306)[zlm]>insert into test_trx values(1,`zlm`); 14 Query OK, 1 row affected (0.01 sec) 15 16 (zlm@192.168.1.101 3306)[zlm]>create table test_notrx like test_trx; 17 Query OK, 0 rows affected (0.01 sec) 18 19 (zlm@192.168.1.101 3306)[zlm]>insert into test_notrx values(2,`aaron8219`); 20 Query OK, 1 row affected (0.00 sec) 21 22 (zlm@192.168.1.101 3306)[zlm]>alter table test_notrx engine=MyISAM; 23 Query OK, 1 row affected (0.01 sec) 24 Records: 1 Duplicates: 0 Warnings: 0 25 26 (zlm@192.168.1.101 3306)[zlm]>show create table test_trxG 27 *************************** 1. row *************************** 28 Table: test_trx 29 Create Table: CREATE TABLE `test_trx` ( 30 `id` int(11) DEFAULT NULL, 31 `name` varchar(10) DEFAULT NULL 32 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 33 1 row in set (0.00 sec) 34 35 (zlm@192.168.1.101 3306)[zlm]>show create table test_notrxG 36 *************************** 1. row *************************** 37 Table: test_notrx 38 Create Table: CREATE TABLE `test_notrx` ( 39 `id` int(11) DEFAULT NULL, 40 `name` varchar(10) DEFAULT NULL 41 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 42 1 row in set (0.00 sec) 43 44 (zlm@192.168.1.101 3306)[zlm]>select * from test_trx; 45 +------+------+ 46 | id | name | 47 +------+------+ 48 | 1 | zlm | 49 +------+------+ 50 1 row in set (0.00 sec) 51 52 (zlm@192.168.1.101 3306)[zlm]>select * from test_notrx; 53 +------+-----------+ 54 | id | name | 55 +------+-----------+ 56 | 2 | aaron8219 | 57 +------+-----------+ 58 1 row in set (0.00 sec)
Generate a backup with mysqldump specifying common option of “–single-transaction” together with “–master-data=2”.
1 [root@zlm2 09:36:23 ~] 2 #mysqldump --single-transaction --master-data=2 zlm > test1.sql 3 4 //General log output below. 5 2018-07-30T09:45:17.815021+01:00 4 Connect zlm@zlm2 on using TCP/IP 6 2018-07-30T09:45:17.815290+01:00 4 Query /*!40100 SET @@SQL_MODE=`` */ 7 2018-07-30T09:45:17.815445+01:00 4 Query /*!40103 SET TIME_ZONE=`+00:00` */ 8 2018-07-30T09:45:17.815862+01:00 4 Query FLUSH /*!40101 LOCAL */ TABLES 9 2018-07-30T09:45:17.823118+01:00 4 Query FLUSH TABLES WITH READ LOCK //Generate a FTWRL. 10 2018-07-30T09:45:17.823435+01:00 4 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 11 2018-07-30T09:45:17.823684+01:00 4 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 12 2018-07-30T09:45:17.823904+01:00 4 Query SHOW VARIABLES LIKE `gtid\_mode` 13 2018-07-30T09:45:17.826626+01:00 4 Query SELECT @@GLOBAL.GTID_EXECUTED 14 2018-07-30T09:45:17.827032+01:00 4 Query SHOW MASTER STATUS 15 2018-07-30T09:45:17.827278+01:00 4 Query UNLOCK TABLES //Release the locks on all tables in whole instance. 16 2018-07-30T09:45:17.827480+01:00 4 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 IN (`zlm`))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 17 2018-07-30T09:45:17.828912+01:00 4 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 IN (`zlm`)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 18 2018-07-30T09:45:17.830942+01:00 4 Query SHOW VARIABLES LIKE `ndbinfo\_version` 19 2018-07-30T09:45:17.833548+01:00 4 Init DB zlm 20 2018-07-30T09:45:17.833785+01:00 4 Query SAVEPOINT sp 21 2018-07-30T09:45:17.833963+01:00 4 Query show tables 22 2018-07-30T09:45:17.834252+01:00 4 Query show table status like `test\_notrx` 23 2018-07-30T09:45:17.834449+01:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1 24 2018-07-30T09:45:17.834582+01:00 4 Query SET SESSION character_set_results = `binary` 25 2018-07-30T09:45:17.834781+01:00 4 Query show create table `test_notrx` 26 2018-07-30T09:45:17.835621+01:00 4 Query SET SESSION character_set_results = `utf8` 27 2018-07-30T09:45:17.835851+01:00 4 Query show fields from `test_notrx` 28 2018-07-30T09:45:17.836362+01:00 4 Query show fields from `test_notrx` 29 2018-07-30T09:45:17.836843+01:00 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx` 30 2018-07-30T09:45:17.837327+01:00 4 Query SET SESSION character_set_results = `binary` 31 2018-07-30T09:45:17.838289+01:00 4 Query use `zlm` 32 2018-07-30T09:45:17.838723+01:00 4 Query select @@collation_database 33 2018-07-30T09:45:17.838908+01:00 4 Query SHOW TRIGGERS LIKE `test\_notrx` 34 2018-07-30T09:45:17.839457+01:00 4 Query SET SESSION character_set_results = `utf8` 35 2018-07-30T09:45:17.839678+01:00 4 Query ROLLBACK TO SAVEPOINT sp 36 2018-07-30T09:45:17.839821+01:00 4 Query show table status like `test\_trx` 37 2018-07-30T09:45:17.840112+01:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1 38 2018-07-30T09:45:17.840248+01:00 4 Query SET SESSION character_set_results = `binary` 39 2018-07-30T09:45:17.840364+01:00 4 Query show create table `test_trx` 40 2018-07-30T09:45:17.840675+01:00 4 Query SET SESSION character_set_results = `utf8` 41 2018-07-30T09:45:17.840828+01:00 4 Query show fields from `test_trx` 42 2018-07-30T09:45:17.841258+01:00 4 Query show fields from `test_trx` 43 2018-07-30T09:45:17.841774+01:00 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx` 44 2018-07-30T09:45:17.842205+01:00 4 Query SET SESSION character_set_results = `binary` 45 2018-07-30T09:45:17.842370+01:00 4 Query use `zlm` 46 2018-07-30T09:45:17.842646+01:00 4 Query select @@collation_database 47 2018-07-30T09:45:17.842814+01:00 4 Query SHOW TRIGGERS LIKE `test\_trx` 48 2018-07-30T09:45:17.844200+01:00 4 Query SET SESSION character_set_results = `utf8` 49 2018-07-30T09:45:17.844540+01:00 4 Query ROLLBACK TO SAVEPOINT sp 50 2018-07-30T09:45:17.844803+01:00 4 Query RELEASE SAVEPOINT sp 51 2018-07-30T09:45:17.847871+01:00 4 Quit 52 53 //The "unlock tables" operation was executed very soon after a short period of time of getting gtid information. 54 //After the releasing lock operation,the consistency of transaction table can be guaranteed while the non-transaction table cannot be.
This time merely use “–master-data=2”.
1 [root@zlm2 09:45:17 ~] 2 #mysqldump --master-data=2 zlm > test2.sql 3 4 //General log output below. 5 2018-07-30T10:06:41.766819+01:00 5 Connect zlm@zlm2 on using TCP/IP 6 2018-07-30T10:06:41.767265+01:00 5 Query /*!40100 SET @@SQL_MODE=`` */ 7 2018-07-30T10:06:41.767495+01:00 5 Query /*!40103 SET TIME_ZONE=`+00:00` */ 8 2018-07-30T10:06:41.767852+01:00 5 Query FLUSH /*!40101 LOCAL */ TABLES 9 2018-07-30T10:06:41.768067+01:00 5 Query FLUSH TABLES WITH READ LOCK //It still generate a FTWRL. 10 2018-07-30T10:06:41.768237+01:00 5 Query SHOW VARIABLES LIKE `gtid\_mode` 11 2018-07-30T10:06:41.770245+01:00 5 Query SELECT @@GLOBAL.GTID_EXECUTED 12 2018-07-30T10:06:41.770659+01:00 5 Query SHOW MASTER STATUS //No releasing lock operation here. 13 2018-07-30T10:06:41.770955+01:00 5 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 IN (`zlm`))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 14 2018-07-30T10:06:41.773632+01:00 5 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 IN (`zlm`)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 15 2018-07-30T10:06:41.775734+01:00 5 Query SHOW VARIABLES LIKE `ndbinfo\_version` 16 2018-07-30T10:06:41.777953+01:00 5 Init DB zlm 17 2018-07-30T10:06:41.778223+01:00 5 Query show tables 18 2018-07-30T10:06:41.778529+01:00 5 Query show table status like `test\_notrx` 19 2018-07-30T10:06:41.778898+01:00 5 Query SET SQL_QUOTE_SHOW_CREATE=1 20 2018-07-30T10:06:41.779149+01:00 5 Query SET SESSION character_set_results = `binary` 21 2018-07-30T10:06:41.780362+01:00 5 Query show create table `test_notrx` 22 2018-07-30T10:06:41.780722+01:00 5 Query SET SESSION character_set_results = `utf8` 23 2018-07-30T10:06:41.780907+01:00 5 Query show fields from `test_notrx` 24 2018-07-30T10:06:41.781665+01:00 5 Query show fields from `test_notrx` 25 2018-07-30T10:06:41.782447+01:00 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx` 26 2018-07-30T10:06:41.782788+01:00 5 Query SET SESSION character_set_results = `binary` 27 2018-07-30T10:06:41.782945+01:00 5 Query use `zlm` 28 2018-07-30T10:06:41.783298+01:00 5 Query select @@collation_database 29 2018-07-30T10:06:41.783425+01:00 5 Query SHOW TRIGGERS LIKE `test\_notrx` 30 2018-07-30T10:06:41.784086+01:00 5 Query SET SESSION character_set_results = `utf8` 31 2018-07-30T10:06:41.790618+01:00 5 Query show table status like `test\_trx` 32 2018-07-30T10:06:41.791015+01:00 5 Query SET SQL_QUOTE_SHOW_CREATE=1 33 2018-07-30T10:06:41.791094+01:00 5 Query SET SESSION character_set_results = `binary` 34 2018-07-30T10:06:41.791192+01:00 5 Query show create table `test_trx` 35 2018-07-30T10:06:41.791285+01:00 5 Query SET SESSION character_set_results = `utf8` 36 2018-07-30T10:06:41.791360+01:00 5 Query show fields from `test_trx` 37 2018-07-30T10:06:41.792328+01:00 5 Query show fields from `test_trx` 38 2018-07-30T10:06:41.792837+01:00 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx` 39 2018-07-30T10:06:41.793036+01:00 5 Query SET SESSION character_set_results = `binary` 40 2018-07-30T10:06:41.793147+01:00 5 Query use `zlm` 41 2018-07-30T10:06:41.793277+01:00 5 Query select @@collation_database 42 2018-07-30T10:06:41.793381+01:00 5 Query SHOW TRIGGERS LIKE `test\_trx` 43 2018-07-30T10:06:41.794223+01:00 5 Query SET SESSION character_set_results = `utf8` 44 2018-07-30T10:06:41.799961+01:00 5 Quit 45 46 //No RR isolation level and consistent snapshot this time what means the consistency of transaction table cannot be guaranteed,too. 47 //As for the non-transaction table,it will hold the FTWRL until the backup finishes.That means it`s consistent in the course. 48 //The bad influence is that the locking time will be extremly long.Because option of "--master-data" will enbale "--lock-all-tables" or "-x" option.
This time execute mysqldump without any option.
1 [root@zlm2 10:16:20 ~] 2 #mysqldump zlm > test3.sql 3 4 //General log output below. 5 2018-07-30T10:24:04.371650+01:00 6 Connect zlm@zlm2 on using TCP/IP 6 2018-07-30T10:24:04.371651+01:00 6 Query /*!40100 SET @@SQL_MODE=`` */ 7 2018-07-30T10:24:04.371885+01:00 6 Query /*!40103 SET TIME_ZONE=`+00:00` */ 8 2018-07-30T10:24:04.372048+01:00 6 Query SHOW VARIABLES LIKE `gtid\_mode` 9 2018-07-30T10:24:04.374903+01:00 6 Query SELECT @@GLOBAL.GTID_EXECUTED 10 2018-07-30T10:24:04.376278+01:00 6 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 IN (`zlm`))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 11 2018-07-30T10:24:04.378718+01:00 6 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 IN (`zlm`)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 12 2018-07-30T10:24:04.380381+01:00 6 Query SHOW VARIABLES LIKE `ndbinfo\_version` 13 2018-07-30T10:24:04.383136+01:00 6 Init DB zlm 14 2018-07-30T10:24:04.383512+01:00 6 Query show tables 15 2018-07-30T10:24:04.383922+01:00 6 Query LOCK TABLES `test_notrx` READ /*!32311 LOCAL */,`test_trx` READ /*!32311 LOCAL */ //There`re two individual locks here. 16 2018-07-30T10:24:04.384267+01:00 6 Query show table status like `test\_notrx` 17 2018-07-30T10:24:04.384870+01:00 6 Query SET SQL_QUOTE_SHOW_CREATE=1 18 2018-07-30T10:24:04.385115+01:00 6 Query SET SESSION character_set_results = `binary` 19 2018-07-30T10:24:04.385241+01:00 6 Query show create table `test_notrx` 20 2018-07-30T10:24:04.385479+01:00 6 Query SET SESSION character_set_results = `utf8` 21 2018-07-30T10:24:04.385651+01:00 6 Query show fields from `test_notrx` 22 2018-07-30T10:24:04.386440+01:00 6 Query show fields from `test_notrx` 23 2018-07-30T10:24:04.387011+01:00 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx` 24 2018-07-30T10:24:04.387314+01:00 6 Query SET SESSION character_set_results = `binary` 25 2018-07-30T10:24:04.387446+01:00 6 Query use `zlm` 26 2018-07-30T10:24:04.387704+01:00 6 Query select @@collation_database 27 2018-07-30T10:24:04.388009+01:00 6 Query SHOW TRIGGERS LIKE `test\_notrx` 28 2018-07-30T10:24:04.388492+01:00 6 Query SET SESSION character_set_results = `utf8` 29 2018-07-30T10:24:04.388576+01:00 6 Query show table status like `test\_trx` 30 2018-07-30T10:24:04.389393+01:00 6 Query SET SQL_QUOTE_SHOW_CREATE=1 31 2018-07-30T10:24:04.389691+01:00 6 Query SET SESSION character_set_results = `binary` 32 2018-07-30T10:24:04.390024+01:00 6 Query show create table `test_trx` 33 2018-07-30T10:24:04.390330+01:00 6 Query SET SESSION character_set_results = `utf8` 34 2018-07-30T10:24:04.392180+01:00 6 Query show fields from `test_trx` 35 2018-07-30T10:24:04.393918+01:00 6 Query show fields from `test_trx` 36 2018-07-30T10:24:04.394497+01:00 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx` 37 2018-07-30T10:24:04.394847+01:00 6 Query SET SESSION character_set_results = `binary` 38 2018-07-30T10:24:04.395108+01:00 6 Query use `zlm` 39 2018-07-30T10:24:04.395955+01:00 6 Query select @@collation_database 40 2018-07-30T10:24:04.396372+01:00 6 Query SHOW TRIGGERS LIKE `test\_trx` 41 2018-07-30T10:24:04.397040+01:00 6 Query SET SESSION character_set_results = `utf8` 42 2018-07-30T10:24:04.397295+01:00 6 Query UNLOCK TABLES //The lock was released after finished backup. 43 2018-07-30T10:24:04.401336+01:00 6 Quit 44 45 //If you don not specify any option of mysqldump,default option "--lock-tables" or "-l" will be enable. 46 //The "--lock-tables" option will add a read-only lock by executing "lock table xxx read;" individually on which table you want to backup. 47 //There`s neither RR isolation leve nor consistent snapshot.Therefore,only the non-transaction table was guaranteed a consistent backup.
Summary
- We cannot get a consistent backup of non-transaction table with simply using option of “–singel-transaction” and “–master-data” simultaneously.
- If you merely specify the option “–master-data”.It can guarantee a consistent backup of non-transaction table since it will enable a FTWRL.That is,it will enable the option of “–lock-all-tables”(short option is “-x”).The global read lock on table will sustain until to the end of backup.
- If you do not specify any option of mysqldump,the default option of “–lock-table”(short option is “-l”) will be enabled.That will also guarantee a consisten backup of non-transaction table.
- Notice the above situations when backup your non-transaction tables when using mysqldump otherwise you`ll get an inconsistent backup.That`s really an awful thing.