关于mysqldump备份非事务表的注意事项

 

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.

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:关于mysqldump备份非事务表的注意事项

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。