首页 > > 网络编程 > Mysql >

MySQL备份恢复之mysqldump

2018-06-18 01:43:48来源：未知阅读 ()

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.

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有