Preface
The day before yesterday,there's a motif about the lock procedure when backing up MySQL using mysqldump or Xtrabackup in "Ask Ye" which is like a kind of Q&A originated by Mr. Ye.In my essay today,I'm gonna use the tool mysqlsump to do some tests then observe procedures of backing up MySQL database,here we go.
Introduction
mysqldump is a protogenic logical backup tool of MySQL.As soon as you've installed MySQL server,it can be used in command line directly.
mysqldump logically dumps data in the target database then gernerate a sql file which can be used to restore the the very database later.it's rather convenient to backup a single or multiple databases online(innodb only) but the side-effect is that it can hold lock when backing up.
Procedure
In order to see the intact procedure of backup,we open the general log by setting "general_log=on" in runtime first.
1 ###Open General Log###
2 (root@localhost mysql3306.sock)[performance_schema]09:29:17>show variables like '%gener%';
3 +------------------+-------------------------------------+
4 | Variable_name | Value |
5 +------------------+-------------------------------------+
6 | general_log | OFF |
7 | general_log_file | /data/mysql/mysql3306/data/zlm2.log |
8 +------------------+-------------------------------------+
9 2 rows in set (0.00 sec)
10
11 (root@localhost mysql3306.sock)[performance_schema]09:29:23>set general_log=on;
12 ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL
13 (root@localhost mysql3306.sock)[performance_schema]09:29:39>set global general_log=on;
14 Query OK, 0 rows affected (0.14 sec)
15
16 ###Create innodb table and myisam table in database zlm.###
17 (root@localhost mysql3306.sock)[zlm]09:36:56>create table test_innodb(id int primary key) engine=innodb;
18 Query OK, 0 rows affected (0.02 sec)
19
20 (root@localhost mysql3306.sock)[zlm]09:37:17>create table test_myisam(id int primary key) engine=myisam;
21 Query OK, 0 rows affected (0.01 sec)
22
23 (root@localhost mysql3306.sock)[zlm]09:37:42>show tables;
24 +---------------+
25 | Tables_in_zlm |
26 +---------------+
27 | test_innodb |
28 | test_myisam |
29 +---------------+
30 2 rows in set (0.00 sec)
31
32 (root@localhost mysql3306.sock)[zlm]09:37:45>
33
34 ###Gnerate First Backup(disable triggers)###
35 [root@zlm2 09:50:08 ~]
36 #mysqldump --triggers=false -B zlm > /data/backup/first_3306-`date +%Y%m%d`.sql
37 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
38
39 [root@zlm2 09:53:41 ~]
40 #ls -l /data/backup
41 total 8
42 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
43
44 ###Check General Log###
45 [root@zlm2 09:52:57 /data/mysql/mysql3306/data]
46 #cat zlm2.log
47
48 2018-06-15T07:53:41.345850Z 32 Connect root@localhost on using Socket
49 2018-06-15T07:53:41.346193Z 32 Query /*!40100 SET @@SQL_MODE='' */
50 2018-06-15T07:53:41.346252Z 32 Query /*!40103 SET TIME_ZONE='+00:00' */
51 2018-06-15T07:53:41.346332Z 32 Query SHOW VARIABLES LIKE 'gtid\_mode'
52 2018-06-15T07:53:41.348422Z 32 Query SELECT @@GLOBAL.GTID_EXECUTED
53 2018-06-15T07:53:41.350309Z 32 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
54 2018-06-15T07:53:41.351603Z 32 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
55 2018-06-15T07:53:41.352151Z 32 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
56 2018-06-15T07:53:41.354634Z 32 Init DB zlm
57 2018-06-15T07:53:41.354659Z 32 Query SHOW CREATE DATABASE IF NOT EXISTS `zlm`
58 2018-06-15T07:53:41.354660Z 32 Query show tables
59 2018-06-15T07:53:41.354747Z 32 Query LOCK TABLES `test_innodb` READ /*!32311 LOCAL */,`test_myisam` READ /*!32311 LOCAL */
60 2018-06-15T07:53:41.354815Z 32 Query show table status like 'test\_innodb'
61 2018-06-15T07:53:41.355067Z 32 Query SET SQL_QUOTE_SHOW_CREATE=1
62 2018-06-15T07:53:41.355110Z 32 Query SET SESSION character_set_results = 'binary'
63 2018-06-15T07:53:41.355144Z 32 Query show create table `test_innodb`
64 2018-06-15T07:53:41.355188Z 32 Query SET SESSION character_set_results = 'utf8'
65 2018-06-15T07:53:41.355227Z 32 Query show fields from `test_innodb`
66 2018-06-15T07:53:41.355412Z 32 Query show fields from `test_innodb`
67 2018-06-15T07:53:41.355631Z 32 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
68 2018-06-15T07:53:41.356449Z 32 Query show table status like 'test\_myisam'
69 2018-06-15T07:53:41.356723Z 32 Query SET SQL_QUOTE_SHOW_CREATE=1
70 2018-06-15T07:53:41.356759Z 32 Query SET SESSION character_set_results = 'binary'
71 2018-06-15T07:53:41.356819Z 32 Query show create table `test_myisam`
72 2018-06-15T07:53:41.356863Z 32 Query SET SESSION character_set_results = 'utf8'
73 2018-06-15T07:53:41.356900Z 32 Query show fields from `test_myisam`
74 2018-06-15T07:53:41.357109Z 32 Query show fields from `test_myisam`
75 2018-06-15T07:53:41.357349Z 32 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
76 2018-06-15T07:53:41.357420Z 32 Query UNLOCK TABLES --Release the lock only after all tables have finished backup.
77 2018-06-15T07:53:41.361654Z 32 Quit
I've make the output be simplest by disable triggers' backup.We can see from general log is that "LOCK TABLES test_innodb READ" then the "UNLOCK TABLES" statment.Let's see the difference with parameter "single-transaction".
1 ###Gnerate Sceond Backup(enable transaction consistent backup)###
2 [root@zlm2 10:00:41 ~]
3 #mysqldump --triggers=false --single-transaction -B zlm > /data/backup/second_3306-`date +%Y%m%d`.sql
4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
5
6 [root@zlm2 10:02:16 ~]
7 #ls -l /data/backup
8 total 8
9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11
12 ###Check General Log###
13 [root@zlm2 10:00:56 /data/mysql/mysql3306/data]
14 #cat zlm2.log
15
16 2018-06-15T08:00:52.911566Z 34 Connect root@localhost on using Socket
17 2018-06-15T08:00:52.911733Z 34 Query /*!40100 SET @@SQL_MODE='' */
18 2018-06-15T08:00:52.911848Z 34 Query /*!40103 SET TIME_ZONE='+00:00' */
19 2018-06-15T08:00:52.912749Z 34 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
20 2018-06-15T08:00:52.912839Z 34 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
21 2018-06-15T08:00:52.912919Z 34 Query SHOW VARIABLES LIKE 'gtid\_mode'
22 2018-06-15T08:00:52.915228Z 34 Query SELECT @@GLOBAL.GTID_EXECUTED
23 2018-06-15T08:00:52.915371Z 34 Query UNLOCK TABLES --Release lock here untill end,there're no more locks.
24 2018-06-15T08:00:52.915568Z 34 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
25 2018-06-15T08:00:52.916737Z 34 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
26 2018-06-15T08:00:52.918498Z 34 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
27 2018-06-15T08:00:52.920665Z 34 Init DB zlm
28 2018-06-15T08:00:52.920742Z 34 Query SHOW CREATE DATABASE IF NOT EXISTS `zlm`
29 2018-06-15T08:00:52.920787Z 34 Query SAVEPOINT sp --Notice,there's a save opoint here.
30 2018-06-15T08:00:52.920837Z 34 Query show tables
31 2018-06-15T08:00:52.921068Z 34 Query show table status like 'test\_innodb'
32 2018-06-15T08:00:52.921242Z 34 Query SET SQL_QUOTE_SHOW_CREATE=1
33 2018-06-15T08:00:52.921290Z 34 Query SET SESSION character_set_results = 'binary'
34 2018-06-15T08:00:52.921334Z 34 Query show create table `test_innodb`
35 2018-06-15T08:00:52.921397Z 34 Query SET SESSION character_set_results = 'utf8'
36 2018-06-15T08:00:52.921444Z 34 Query show fields from `test_innodb`
37 2018-06-15T08:00:52.921833Z 34 Query show fields from `test_innodb`
38 2018-06-15T08:00:52.922279Z 34 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
39 2018-06-15T08:00:52.922380Z 34 Query ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp
40 2018-06-15T08:00:52.922487Z 34 Query show table status like 'test\_myisam'
41 2018-06-15T08:00:52.922694Z 34 Query SET SQL_QUOTE_SHOW_CREATE=1
42 2018-06-15T08:00:52.922730Z 34 Query SET SESSION character_set_results = 'binary'
43 2018-06-15T08:00:52.922763Z 34 Query show create table `test_myisam`
44 2018-06-15T08:00:52.922872Z 34 Query SET SESSION character_set_results = 'utf8'
45 2018-06-15T08:00:52.922929Z 34 Query show fields from `test_myisam`
46 2018-06-15T08:00:52.923140Z 34 Query show fields from `test_myisam`
47 2018-06-15T08:00:52.923395Z 34 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
48 2018-06-15T08:00:52.923459Z 34 Query ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp.
49 2018-06-15T08:00:52.923487Z 34 Query RELEASE SAVEPOINT sp --Release it where backup finish.
50 2018-06-15T08:00:52.928411Z 34 Quit
There is only one piece of "UNLOCK TABLES" which is related with lock can be found this time.At the very beginning of the general log,it shows "START TRANSACTION WITH CONSISTENT SNAPSHOT".As soon as it gets the gtid_executed variable,then it will realse the lock by execute statment "UNLOCK TABLES".Obviously,it's a very short time.
After get the statment of creating database,there's a savepoint created.The rollback operation of the savepoint hapens after it gets the full table backup.Let's see another parameter "master-data" which can make something different,too.
1 ###Gnerate Third Backup(add replication information)###
2 [root@zlm2 10:37:55 ~]
3 #mysqldump --triggers=false --single-transaction --master-data=2 -B zlm > /data/backup/third_3306-`date +%Y%m%d`.sql
4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
5
6 [root@zlm2 10:37:29 ~]
7 #ls -l /data/backup
8 total 12
9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11 -rw-r--r-- 1 root root 2971 Jun 15 10:37 third_3306-20180615.sql
12
13 ###Check General Log###
14 [root@zlm2 10:37:39 /data/mysql/mysql3306/data]
15 #cat zlm2.log
16
17 2018-06-15T08:37:29.848849Z 35 Connect root@localhost on using Socket
18 2018-06-15T08:37:29.849052Z 35 Query /*!40100 SET @@SQL_MODE='' */
19 2018-06-15T08:37:29.849153Z 35 Query /*!40103 SET TIME_ZONE='+00:00' */
20 2018-06-15T08:37:29.849290Z 35 Query FLUSH /*!40101 LOCAL */ TABLES -- Difference 1.
21 2018-06-15T08:37:29.855139Z 35 Query FLUSH TABLES WITH READ LOCK -- Difference 2.
22 2018-06-15T08:37:29.855196Z 35 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
23 2018-06-15T08:37:29.855225Z 35 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
24 2018-06-15T08:37:29.855272Z 35 Query SHOW VARIABLES LIKE 'gtid\_mode'
25 2018-06-15T08:37:29.857074Z 35 Query SELECT @@GLOBAL.GTID_EXECUTED
26 2018-06-15T08:37:29.857406Z 35 Query SHOW MASTER STATUS
27 2018-06-15T08:37:29.857498Z 35 Query UNLOCK TABLES
28 2018-06-15T08:37:29.857622Z 35 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
29 2018-06-15T08:37:29.858662Z 35 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
30 2018-06-15T08:37:29.859309Z 35 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
31 2018-06-15T08:37:29.861396Z 35 Init DB zlm
32 2018-06-15T08:37:29.862152Z 35 Query SHOW CREATE DATABASE IF NOT EXISTS `zlm`
33 2018-06-15T08:37:29.862255Z 35 Query SAVEPOINT sp
34 2018-06-15T08:37:29.862322Z 35 Query show tables
35 2018-06-15T08:37:29.862485Z 35 Query show table status like 'test\_innodb'
36 2018-06-15T08:37:29.862665Z 35 Query SET SQL_QUOTE_SHOW_CREATE=1
37 2018-06-15T08:37:29.862724Z 35 Query SET SESSION character_set_results = 'binary'
38 2018-06-15T08:37:29.862777Z 35 Query show create table `test_innodb`
39 2018-06-15T08:37:29.862827Z 35 Query SET SESSION character_set_results = 'utf8'
40 2018-06-15T08:37:29.862880Z 35 Query show fields from `test_innodb`
41 2018-06-15T08:37:29.863198Z 35 Query show fields from `test_innodb`
42 2018-06-15T08:37:29.863476Z 35 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
43 2018-06-15T08:37:29.863597Z 35 Query ROLLBACK TO SAVEPOINT sp
44 2018-06-15T08:37:29.863668Z 35 Query show table status like 'test\_myisam'
45 2018-06-15T08:37:29.865590Z 35 Query SET SQL_QUOTE_SHOW_CREATE=1
46 2018-06-15T08:37:29.865833Z 35 Query SET SESSION character_set_results = 'binary'
47 2018-06-15T08:37:29.865853Z 35 Query show create table `test_myisam`
48 2018-06-15T08:37:29.865853Z 35 Query SET SESSION character_set_results = 'utf8'
49 2018-06-15T08:37:29.865854Z 35 Query show fields from `test_myisam`
50 2018-06-15T08:37:29.866059Z 35 Query show fields from `test_myisam`
51 2018-06-15T08:37:29.867277Z 35 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
52 2018-06-15T08:37:29.867367Z 35 Query ROLLBACK TO SAVEPOINT sp
53 2018-06-15T08:37:29.867398Z 35 Query RELEASE SAVEPOINT sp
54 2018-06-15T08:37:29.869643Z 35 Quit
There're two differences above,one is "FLUSH TABLES" another one is "FLUSH TABLE WITH READ LOCK".In case of other transactoins hold the table lock related with the table to be backed up,the backup operation will be blocked until those transactions release the lock or waiting until time out(according to value of "lock_wait_timeout") occurs.
As a matter of fact,we usually use "master-data" to backup our databases,we shoudn't make it happen in the case of there're massive dml operations continueously executed in rush hour.This will lead to failure of your backup.
Summary
- You've been clear about the whole procedure of backup using mysqldump now.But,there's still one thing make me confused is that why I used "master-data" in mysqldump but not got the change master statement.Is it due to my Group Replication environment?
- Look out,this tool doesnot support backing up in parallel mode.If your database is huge enough,do think twice or use another backup tool in stead of it.