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

This article mainly tells you briefly how to ensure data consistency during mysqldump backup. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to ensure data consistency when mysqldump backup can bring you some practical help.

Mysqldump

Certain permissions are required to run mysqldump. For example, the minimum permission for a backup table is select, and the backup trigger requires show triggers permission.

(1) Command specification for backup result files: dbname_port_$ (date +% Y%m%d) .bak

(2) gzip,tar is a single-thread compression software, which can only use one cpu, and its efficiency is relatively low. Compression is not recommended during backup. If there is really not enough space, you can use pigz multithread compression software after the backup is completed.

(3) mysqldump works in a single thread, and its efficiency is relatively low. If mysqldump backup takes a long time, you can consider using mydumper to support multi-thread concurrent export.

Parameters for backup

Mysql > create table T1 (C1 int,c2 varchar (10)); mysql > insert into T1 values (1mementaaaa'), (2membbbb'), (3recorder ccc'); for example, a single library testdb: [root@Darren2 tmp] # mysqldump-uroot-p147258 testdb > / tmp/testdb1.bak [root@Darren2 tmp] # vim testdb1.bakSET @ MYSQLDUMP_TEMP_LOG_BIN = @ @ SESSION.SQL_LOG_BIN;SET @ @ SESSION.SQL_LOG_BIN= 0 -- binlog SET @ @ GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914log is not recorded during restore; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` (`c1` int (11) DEFAULT NULL, `c2` varchar (10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `t1` WRITE -when restoring the table, other conversations are not allowed to read and write the T1 table INSERT INTO `t1` VALUES (1mementaaaaa'), (2memoribbbb'), (3authoritative ccc'); UNLOCK TABLES;SET @ @ SESSION.SQL_LOG_BIN = @ MYSQLDUMP_TEMP_LOG_BIN; restore on this database: [root@Darren2 tmp] # mysql-uroot-p147258

< testdb1.bakERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.实质报错：root@localhost [testdb]>

SET @ @ GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914 investors error 1840 (HY000): @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty. If direct restore will cause an error, because I enabled gtid_mode, you can show master status to check that the executed_gtid_set parameter is not empty. You need to enter "SET @ @ GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914" in the backup file testdb1.bak; "comment out can continue to restore. If you want to be able to transfer to binlog, synchronize the restore to the slave library, you need to comment out SET @ @ SESSION.SQL_LOG_BIN= 0." Root@localhost [testdb] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-- -+ | mysql-bin.000004 | 6392 | 83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931 | +- -+-+ restore command: [root@Darren2 tmp] # mysql-uroot-p147258 testdb

< testdb1.bak#必须指定testdb库,否则报错找不到，如果还原testdb库之前被删除了，也会报错：[root@Darren2 tmp]# mysql -uroot -p147258 testdb testdb2.bak[root@Darren2 tmp]# vim testdb2.bakSET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;SET @@SESSION.SQL_LOG_BIN= 0;SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931';CREATE DATABASE `testdb` ;USE `testdb`;DROP TABLE IF EXISTS `t1`;CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `t1` WRITE;INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');UNLOCK TABLES;SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;还原：无需指定testdb库[root@Darren2 tmp]# mysql -uroot -p147258 < testdb2.bak#同时备份多个库：[root@Darren2 tmp]# mysqldump -uroot -p147258 -B testdb mysql >

Testdbmysql.bak restores multiple libraries: [root@Darren2 tmp] # mysql-uroot-p147258

< testdbmysql.bak例3：压缩备份[root@Darren2 logs]# mysqldump -uroot -p147258 testdb | gzip >

Testdbgzip.bak.gz restore: [root@Darren2 tmp] # gunzip

< testdbgzip.bak.gz | mysql -uroot -p147258例4：只备份库中的表格式：mysqldump -uroot -p147258 库名 表名1 表名2 ... >

Backup file name Note: do not add the-B parameter, otherwise you will backup multiple libraries # backup a single table: [root@Darren2 tmp] # mysqldump-uroot-p147258 testdbt1 > testdbt1.bak# backup multiple tables: [root@Darren2 tmp] # mysqldump-uroot-p147258 testdbt1t2 > testdbt1t2.bak example 5:--master-data, specify where to start the log file No need to cut binlog logs [root@www ~] # mysqldump-uroot-p147258-- master-data=1 testdbCHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107 [root@www opt] # mysqldump-uroot-p147258-- master-data=2 testdb-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107 -- when master-data=1, the change master in the backup file is a sql statement. When-master-data=2 is used in the master-slave synchronization, the change master in the backup file is commented out, and the restore does not have the function of sql statement. Example 6: other parameters-- single-transaction is suitable for innodb transaction database backup. The principle is to set this session level to repeatable read to ensure that other sessions that have been submitted will not be seen when this session is backed up. Ensure data consistency-A,-- all-databases Dump all the databases. This will be same as-- databases-F,-- flush-logs means refresh binlog

Summary:

Innodb engine: a general method for full use in production

[root@Darren2 tmp] # mysqldump-uroot-p147258-A-B-master-data=2-- events-- single-transaction > / tmp/all_$ (date +% Y%m%d) .bak

The whole process of backup

The following mysqldump backup process:

Root@localhost [testdb] > set global general_log=1 [root@Darren1 data] # cat / dev/null > general.log [root@Darren1 ~] # mysqldump-uroot-p147258-- single-transaction-- master-data=2 testdb > testdb1_$ (date +% Y%m%d) [root@Darren1 data] # cat general.log2016-12-21T15:31:00.474824Z 14 Connect root@localhost on using Socket2016-12-21T15:31:00.475031Z 14 Query / *! 40100 SET @ SQL_MODE='' * / 2016-12 -21T15:31:00.475194Z 14 Query / *! 40103 SET TIME_ZONE='+00:00' * / 2016-12-21T15:31:00.475282Z 14 Query FLUSH / *! 40101 LOCAL * / TABLES-- scan the meter In order to prevent DDL operations with tables, if there is a DDL operation of a table at the time of backup, flush tables waits until the end of the DDL action to execute flush tables2016-12-21T15:31:00.475598Z 14 Query FLUSH TABLES WITH READ LOCK-- global table lock. No DML and DDL operations can be performed on tables in all sessions. Keep data in consistent state 2016-12-21T15:31:00.475661Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ-- set isolation class to RR2016-12-21T15:31:00.475728Z 14 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /-- start things 2016-12-21T15:31:00.475805Z 14 Query SHOW VARIABLES LIKE 'gtid\ _ mode'-- View GTID status 2016-12- 21T15:31:00.478393Z 14 Query SHOW MASTER STATUS-View master2016-12-21T15:31:00.478487Z 14 Query UNLOCK TABLES-- unlock 2016-12-21T15:31:00.478625Z 14 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 ('testdb') GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME2016-12-21T15:31:00.480360Z 14 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 (' testdb')) ORDER BY TABLESPACE_NAME LOGFILE_GROUP_NAME2016-12-21T15:31:00.481072Z 14 Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'2016-12-21T15:31:00.483114Z 14 Init DB testdb2016-12-21T15:31:00.483193Z 14 Query SAVEPOINT sp-create a rollback point for things If something goes wrong below, you can roll back to the state before the rollback point. 2016-12-21T15:31:00.483262Z 14 Query show tables2016-12-21T15:31:00.483459Z 14 Query show table status like 't12016-12-21T15:31:00.483711Z 14 Query SET SQL_QUOTE_SHOW_CREATE=12016-12-21T15:31:00.483782Z 14 Query SET SESSION character_set_results = 'binary'2016-12-21T15:31:00.483844Z 14 Query show create table `t1`2016 -12-21T15:31:00.483927Z 14 Query SET SESSION character_set_results = 'utf8'2016-12-21T15:31:00.483998Z 14 Query show fields from `t1`2016-12-21T15:31:00.484307Z 14 Query show fields from `t1` 2016-12-21T15:31:00.484551Z 14 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t1`2016-12-21T15:31:00.484758Z 14 Query SET SESSION character_set_results = 'binary'2016-12-21T15:31:00.484828Z 14 Query use `testdb`2016-12-21T15:31:00.484888Z 14 Query select @ @ collation_database2016-12-21T15:31:00.484962Z 14 Query SHOW TRIGGERS LIKE 't12016-12-21T15:31:00.485199Z 14 Query SET SESSION character_set_results =' utf8'2016-12-21T15:31:00.485255Z 14 Query ROLLBACK TO SAVEPOINT sp-back to rollback point 2016-12-21T15:31:00.485315Z 14 Query show table status like 't22016-12-21T15:31:00.485464Z 14 Query SET SQL_QUOTE_SHOW_CREATE=12016-12-21T15:31:00.485515Z 14 Query SET SESSION character_set_results = 'binary'2016-12-21T15:31:00.485567Z 14 Query show create table `t2`2016-12 -21T15:31:00.485635Z 14 Query SET SESSION character_set_results = 'utf8'2016-12-21T15:31:00.485744Z 14 Query show fields from `t2`2016-12-21T15:31:00.485968Z 14 Query show fields from `t2`2016-12-21T15:31:00.486185Z 14 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t2`2016-12-21T15:31:00.486298Z 14 Query SET SESSION character_set_results = 'binary'2016-12-21T15:31:00.486351Z 14 Query use `testdb`2016-12-21T15:31:00.486407Z 14 Query select @ @ collation_database2016-12-21T15:31:00.486468Z 14 Query SHOW TRIGGERS LIKE 't22016-12-21T15:31:00.486792Z 14 Query SET SESSION character_set_results =' utf8'2016-12-21T15:31:00.486887Z 14 Query ROLLBACK TO SAVEPOINT sp-return to rollback point 2016-12-21T15:31:00.486943Z 14 Query RELEASE SAVEPOINT sp-release rollback point 2016-12-21T15:31:00.513639Z 14 Quit-exit

Summary: the process of mysqldump:

Flush tables;flush table with read lock;set tx_isolation='repeatable-read';start transaction;GTID_MODE;show master stauts;unlock tables;SAVEPOINT spshow create table `t1`Select / *! 40001 SQL_NO_CACHE * / * FROM `t1`show TRIGGERS LIKE 't1'ROLLBACK TO SAVEPOINT spRELEASE SAVEPOINT sp

Note:

When backing up a table with mysqldump, if you DDL the backed up table, the backup may fail, because DDL is not in the framework of the transaction, and mysql8.0 may later put the DDL in the framework of the transaction.

Mysqldump backup how to ensure the consistency of the data to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.