Shulou(Shulou.com)06/01 Report--

This article mainly introduces the use of mysqldump to achieve mysql backup practice, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the use of mysqldump to achieve mysql backup practice.

Open query log

Function: easy to analyze the working principle of mysqldump

Method: mysql > set global genaral_log=ON

Practice sample table root@localhost:mysql3306.sock [db1] > show create table tb1\ gateway * 1. Row * * Table: tb1Create Table: CREATE TABLE `tb1` (`id` int (11) DEFAULT NULL `name` varchar (20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) root@localhost:mysql3306.sock [db1] > show create table tb2\ Graph * 1. Row * * Table: tb2Create Table: CREATE TABLE `tb2` (`id` int (11) DEFAULT NULL `name` varchar (20) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec) 1. Back up the table of the myisam engine without parameters

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p db1 tb2 > myisam_date +% Y%m%d.sql

Output log:

2018-05-14T15:05:30.582509Z 6 Connect root@localhost on using Socket2018-05-14T15:05:30.582877Z 6 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T15:05:30.583141Z 6 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T15:05:30.583461Z 6 Query SHOW VARIABLES LIKE 'gtid\ _ mode' 2018-05-14T15:05:30.585270Z 6 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T15:05:30.589611Z 6 Init DB db12018-05-14T15:05:30.589894Z 6 Query SHOW TABLES LIKE 'tb2'2018-05-14T15:05:30.590262Z 6 Query LOCK TABLES `tb2` READ / *! 32311 LOCAL * / 2018-05-14T15:05:30.590555Z 6 Query show Table status like 'tb2'2018-05-14T15:05:30.591038Z 6 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T15:05:30.591307Z 6 Query SET SESSION character_set_results =' binary'2018-05-14T15:05:30.591604Z 6 Query show create table `tb2`2018-05-14T15:05:30.591884Z 6 Query SET SESSION character_set_results = 'utf8'2018-05-14T15: 05Query show fields from 30.592215Z 6 Query show fields from `tb2`2018-05-14T15:05:30.592808Z 6 Query show fields from `tb2`2018-05-14T15:05:30.593334Z 6 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb2`2018-05-14T15:05:30.593655Z 6 Query SET SESSION character_set_results = 'binary'2018-05-14T15:05:30.593876Z 6 Query Use `db1`2018-05-14T15:05:30.594140Z 6 Query select @ @ collation_database2018-05-14T15:05:30.594413Z 6 Query SHOW TRIGGERS LIKE 'tb2'2018-05-14T15:05:30.594903Z 6 Query SET SESSION character_set_results =' utf8'2018-05-14T15:05:30.595076Z 6 Query UNLOCK TABLES2018-05-14T15:05:30.597973Z 6 Quit

Summary: you can see that the LOCK TABLES READ LOCAL lock is automatically added without any parameters, which does not prevent reading or new data insertion. So mysqldump without parameters is very unsafe.

2.-- lock tables

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- lock-tables db1 tb2 > myisam_date +% Y%m%d.sql

Output log:

2018-05-14T15:31:46.319694Z 9 Connect root@localhost on using Socket2018-05-14T15:31:46.320016Z 9 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T15:31:46.320281Z 9 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T15:31:46.320559Z 9 Query SHOW VARIABLES LIKE 'gtid\ _ mode' 2018-05-14T15:31:46.322433Z 9 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T15:31:46.326421Z 9 Init DB db12018-05-14T15:31:46.326694Z 9 Query SHOW TABLES LIKE 'tb2'2018-05-14T15:31:46.327091Z 9 Query LOCK TABLES `tb2` READ / *! 32311 LOCAL * / 2018-05-14T15:31:46.327369Z 9 Query show Table status like 'tb2'2018-05-14T15:31:46.327850Z 9 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T15:31:46.328099Z 9 Query SET SESSION character_set_results =' binary'2018-05-14T15:31:46.328335Z 9 Query show create table `tb2`2018-05-14T15:31:46.328589Z 9 Query SET SESSION character_set_results = 'utf8'2018-05-14T15: 31Query show fields from 46.328868Z 9 Query show fields from `tb2`2018-05-14T15:31:46.329402Z 9 Query show fields from `tb2`2018-05-14T15:31:46.329892Z 9 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb2`2018-05-14T15:31:46.330246Z 9 Query SET SESSION character_set_results = 'binary'2018-05-14T15:31:46.330484Z 9 Query Use `db1`2018-05-14T15:31:46.330669Z 9 Query select @ @ collation_database2018-05-14T15:31:46.331026Z 9 Query SHOW TRIGGERS LIKE 'tb2'2018-05-14T15:31:46.331545Z 9 Query SET SESSION character_set_results =' utf8'2018-05-14T15:31:46.331807Z 9 Query UNLOCK TABLES2018-05-14T15:31:47.492837Z 9 Quit

Summary: the default parameter is the same, automatically added LOCK TABLES READ LOCAL lock, will not prevent reading, will not prevent writing.

3.-- lock-all-tables

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- lock-all-tables db1 tb2 > myisam_date +% Y%m%d.sql

Output log:

2018-05-14T15:37:59.045469Z 10 Connect root@localhost on using Socket2018-05-14T15:37:59.045824Z 10 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T15:37:59.046086Z 10 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T15:37:59.046388Z 10 Query FLUSH TABLES2018-05-14T15:37:59.106269Z 10 Query FLUSH TABLES WITH READ LOCK2018-05-14T15:37:59.106572Z 10 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2018-05-14T15:37:59.108617Z 10 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T15:37:59.112768Z 10 Init DB db12018-05-14T15:37:59.113058Z 10 Query SHOW TABLES LIKE' tb2'2018-05-14T15:37:59.113474Z 10 Query Show table status like 'tb2'2018-05-14T15:37:59.113876Z 10 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T15:37:59.114136Z 10 Query SET SESSION character_set_results =' binary'2018-05-14T15:37:59.114369Z 10 Query show create table `tb2`2018-05-14T15:37:59.114619Z 10 Query SET SESSION character_set_results = 'utf8'2018-05-14T15:37 : 59.114849Z 10 Query show fields from `tb2`2018-05-14T15:37:59.115364Z 10 Query show fields from `tb2`2018-05-14T15:37:59.115843Z 10 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb2`2018-05-14T15:37:59.116209Z 10 Query SET SESSION character_set_results = 'binary'2018-05-14T15:37:59.116390Z 10 Query use `db1`2018-05- 14T15:37:59.116691Z 10 Query select @ @ collation_database2018-05-14T15:37:59.116929Z 10 Query SHOW TRIGGERS LIKE 'tb2'2018-05-14T15:37:59.117396Z 10 Query SET SESSION character_set_results =' utf8'2018-05-14T15:37:59.119640Z 10 Quit

Summary: you can find that flush tables (closing all open tables) has been executed, which requests that a global read lock (FLUSH TABLES WITH READ LOCK) be initiated to prevent writes to all tables, thus ensuring data consistency. When the backup is complete, the session is disconnected and automatically unlocked.

4.-lock-all-tables-master-data=2

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- lock-all-tables-- master-data=2 db1 tb2 > myisam_date +% Y%m%d.sql

Output log:

2018-05-14T15:45:58.822719Z 11 Connect root@localhost on using Socket2018-05-14T15:45:58.822835Z 11 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T15:45:58.822906Z 11 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T15:45:58.822991Z 11 Query FLUSH / *! 40101 LOCAL * / TABLES2018-05-14T15 : 45 Query SHOW VARIABLES LIKE 58.823137Z 11 Query FLUSH TABLES WITH READ LOCK2018-05-14T15:45:58.823206Z 11 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2018-05-14T15:45:58.825232Z 11 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T15:45:58.825364Z 11 Query SHOW MASTER STATUS2018-05-14T15:45:58.828646Z 11 Init DB db12018-05-14T15:45:58.828739Z 11 Query SHOW TABLES LIKE 'tb2'2018-05-14T15:45:58.828987Z 11 Query show table status like' tb2'2018-05-14T15:45:58.829176Z 11 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T15:45:58.829238Z 11 Query SET SESSION character_set_results= 'binary'2018-05-14T15:45:58.829293Z 11 Query show create table `tb2`2018-05-14T15:45:58. 829371Z 11 Query SET SESSION character_set_results= 'utf8'2018-05-14T15:45:58.829440Z 11 Query show fields from `tb2`2018-05-14T15:45:58.829775Z 11 Query show fields from `tb2`2018-05-14T15:45:58.830036Z 11 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb2`2018-05-14T15:45:58.830214Z 11 Query SET SESSION character_set_results=' binary '2018-05-14T15:45:58.830274Z 11 Query use `db1`2018-05-14T15:45:58.830331Z 11 Query select @ @ collation_database2018-05-14T15:45:58.830402Z 11 Query SHOW TRIGGERS LIKE 'tb2'2018-05-14T15:45:58.830691Z 11 Query SET SESSION character_set_results=' utf8'2018-05-14T15:45:58.833762Z 11 Quit

Summary: you can see that there is no change, except that SELECT @ @ GLOBAL.GTID_EXECUTED is executed, the former is used to record the GTID value of the transaction executed, and the latter is used to record the file name and location point information to which the binary log is executed. Master-data=2 is very useful when it comes to being the master and follower.

5.-lock-all-tables-master-data=2-flush-logs

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- lock-all-tables-- master-data=2-- flush-logs db1 tb2 > myisam_date +% Y%m%d.sql

Output log:

2018-05-14T15:58:12.896794Z 12 Connect root@localhost on using Socket2018-05-14T15:58:12.896917Z 12 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T15:58:12.896988Z 12 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T15:58:12.897076Z 12 Query FLUSH / *! 40101 LOCAL * / TABLES2018-05-14T15 : 58 Refresh/usr/local/mysql/bin/mysqld 12.897164Z 12 Query FLUSH TABLES WITH READ LOCK2018-05-14T15:58:12.897224Z 12 Version: 5.7.22-log (MySQL Community Server (GPL)) .started with:Tcp port: 3306 Unix socket: / tmp/mysql3306.sockTime Id Command Argument2018-05-14T15:58:14.310803Z 12 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2018-05-14T15:58:14.312359Z 12 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T15:58:14.312486Z 12 Query SHOW MASTER STATUS2018- 05-14T15:58:14.314876Z 12 Init DB db12018-05-14T15:58:14.314961Z 12 Query SHOW TABLES LIKE 'tb2'2018-05-14T15:58:14.315082Z 12 Query show table status like' tb2'2018-05-14T15:58:14.315218Z 12 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T15:58:14.315264Z 12 Query SET SESSION character_set_results= 'binary'2018- 05-14T15:58:14.315305Z 12 Query show create table `tb2`2018-05-14T15:58:14.315360Z 12 Query SET SESSION character_set_results= 'utf8'2018-05-14T15:58:14.315416Z 12 Query show fields from `tb 2`2018-05-14T15:58:14.315672Z 12 Query show fields from `tb2`2018-05-14T15:58:14.315914Z 12 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb2`2018-05-14T15:58:14.316027Z 12 Query SET SESSION character_set_results= 'binary'2018-05-14T15:58:14.316073Z 12 Query use `db1`2018-05-14T15:58:14.316134Z 12 Query select @ @ collation_database2018-05-14T15:58:14.316190Z 12 Query SHOW TRIGGERS LIKE' tb2'2018-05-14T15:58:14.316407Z 12 Query SET SESSION character_set_ Results= 'utf8'2018-05-14T15:58:14.318938Z 12 Quit

Summary: you can see that an extra parameter has been added-there is no significant change in the flush-logs log, but this parameter will refresh the binlog and generate a new binlog file.

6. Back up the innodb engine table using-- single-transaction

As we all know, innodb implements mvcc, that is, multi-version concurrency control.

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- single-transaction db1 tb1 > innodb_date +% Y%m%d.sql

Output log:

2018-05-14T16:16:34.757675Z 14 Connect root@localhost on using Socket2018-05-14T16:16:34.757788Z 14 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T16:16:34.757858Z 14 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T16:16:34.758005Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-05-14T16:16 34.758056Z 14 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * / 2018-05-14T16:16:34.758134Z 14 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2018-05-14T16:16:34.759847Z 14 Query SELECT @ @ GLOBAL.GTID_EXECUTED2018-05-14T16:16:34.760130Z 14 Query UNLOCK TABLES2018-05-14T16:16:34.763394Z 14 Init DB db12018-05-14T16:16:34 .763471Z 14 Query SHOW TABLES LIKE 'tb1'2018-05-14T16:16:34.763674Z 14 Query SAVEPOINT sp2018-05-14T16:16:34.763760Z 14 Query show table status like' tb1'2018-05-14T16:16:34.764038Z 14 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T16:16:34.764098Z 14 Query SET SESSION character_set_results = 'binary'2018-05-14T16:16 : 34.764175Z 14 Query show create table `tb1`2018-05-14T16:16:34.764273Z 14 Query SET SESSION character_set_results = 'utf8'2018-05-14T16:16:34.764365Z 14 Query show fields from `tb1`2018-05-14T16:16:34.764705Z 14 Query show fields from `tb1`2018-05-14T16:16:34.764973Z 14 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb1`2018- 05-14T16:16:34.765118Z 14 Query SET SESSION character_set_results = 'binary'2018-05-14T16:16:34.765175Z 14 Query use `db1`2018-05-14T16:16:34.765296Z 14 Query select @ @ collation_database2018-05-14T16:16:34.765377Z 14 Query SHOW TRIGGERS LIKE' tb1'2018-05-14T16:16:34.765658Z 14 Query SET SESSION character_set_results =' Utf8'2018-05-14T16:16:34.765715Z 14 Query ROLLBACK TO SAVEPOINT sp2018-05-14T16:16:34.765762Z 14 Query RELEASE SAVEPOINT sp2018-05-14T16:16:37.208932Z 14 Quit

Summary: when backing up the innodb table, the option-single-transaction is usually enabled to ensure the consistency of the backup. In fact, its working principle is to set the isolation level of this session to RR, and then start a snapshot to achieve consistent non-lock read.

7.-single-transaction-master-data=2

Backup command:

Mysqldump-S / tmp/mysql3306.sock-p-- single-transaction-- master-data=2 db1 tb1 > innodb_date +% Y%m%d.sql

Output log:

2018-05-14T16:28:28.118691Z 15 Connect root@localhost on using Socket2018-05-14T16:28:28.118805Z 15 Query / *! 40100 SET @ @ SQL_MODE='' * / 2018-05-14T16:28:28.118927Z 15 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2018-05-14T16:28:28.119040Z 15 Query FLUSH / *! 40101 LOCAL * / TABLES2018-05-14T16 : 28 Query FLUSH TABLES WITH READ LOCK2018 28.119135Z 15 Query FLUSH TABLES WITH READ LOCK2018-05-14T16:28:28.119189Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-05-14T16:28:28.119231Z 15 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * / 2018-05-14T16:28:28.119298Z 15 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2018-05-14T16:28:28.120900Z 15 Query SELECT @ @ GLOBAL. GTID_EXECUTED2018-05-14T16:28:28.121275Z 15 Query SHOW MASTER STATUS2018-05-14T16:28:28.121342Z 15 Query UNLOCK TABLES2018-05-14T16:28:28.124607Z 15 Init DB db12018-05-14T16:28:28.124689Z 15 Query SHOW TABLES LIKE 'tb1'2018-05-14T16:28:28.124829Z 15 Query SAVEPOINT sp2018-05-14T16:28:28.124901Z 15 Query Show table status like 'tb1'2018-05-14T16:28:28.125151Z 15 Query SET SQL_QUOTE_SHOW_CREATE=12018-05-14T16:28:28.125211Z 15 Query SET SESSION character_set_results =' binary'2018-05-14T16:28:28.125264Z 15 Query show create table `tb1`2018-05-14T16:28:28.125339Z 15 Query SET SESSION character_set_results = 'utf8'2018-05-14T16:28:28 .125408Z 15 Query show fields from `tb1`2018-05-14T16:28:28.125832Z 15 Query show fields from `tb1`2018-05-14T16:28:28.126198Z 15 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `tb1`2018-05-14T16:28:28.126337Z 15 Query SET SESSION character_set_results = 'binary'2018-05-14T16:28:28.126417Z 15 Query use `db1`2018-05-14T16: 28 Query select 28.126475Z 15 Query select @ @ collation_database2018-05-14T16:28:28.126546Z 15 Query SHOW TRIGGERS LIKE 'tb1'2018-05-14T16:28:28.126889Z 15 Query SET SESSION character_set_results =' utf8'2018-05-14T16:28:28.126949Z 15 Query ROLLBACK TO SAVEPOINT sp2018-05-14T16:28:28.127017Z 15 Query RELEASE SAVEPOINT sp2018-05-14T16:28:28.129902Z 15 Quit

Summary: due to the addition of the option-- master-data, you can see that a fast global read lock has been submitted in order to put the whole instance into a transient consistency state, so that-- single-transaction can get the consistency data of the entire instance and record the binary log offset (file name and location) in that state.

Summary of practice:

The parameters for backing up the myisam table are recommended as follows:

Mysqldump-- lock-all-tables-- master-data=2-- flush-logs db table > instence_port_db_table__ `date +% Y% m% d`.sql

The parameters for backing up the innodb table are recommended as follows:

Mysqldump-- single-transaction-- master-data=2-- flush-logs db table > instence_port_db_table_ `date +% Y% m% d`.sql

After reading the above about using mysqldump to achieve mysql backup practice, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.