mysqldump备份时如何保证数据的一致

本文主要给大家简单讲讲mysqldump备份时如何保证数据的一致，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望mysqldump备份时如何保证数据的一致这篇文章可以给大家带来一些实际帮助。

mysqldump

运行mysqldump需一定的权限。如，备份表的最低权限为select，备份触发器需show triggers权限。

（1）备份结果文件命令规范：dbname_port_$(date +%Y%m%d).bak

（2）gzip,tar是单线程压缩软件，只能用到一个cpu，效率比较低，备份的时候不建议压缩，如果空间实在不足，可以考录备份完成之后使用pigz多线程压缩软件；

（3）mysqldump是单线程工作，效率比较低，如果mysqldump备份需要很长时间，可以考虑使用mydumper支持多线程并发导出；

备份的参数

mysql>create table t1(c1 int,c2 varchar(10));mysql>insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');例1：#指定备份单个库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-10914';     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;                               --还原表的时候不允许其他会话读写t1表INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');UNLOCK TABLES;SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;在本数据库上还原：[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';ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.如果直接还原会报错，因为我开启了gtid_mode,此时可以show master status看一下executed_gtid_set参数不为空，需要在备份文件testdb1.bak中把"SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914';"注释掉能继续还原如果希望能够传写入binlog,把还原也同步到从库，需要注释掉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 |+------------------+----------+--------------+------------------+----------------------------------------------+还原命令：[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还原多个库：[root@Darren2 tmp]# mysql -uroot -p147258 < testdbmysql.bak例3：压缩备份[root@Darren2 logs]# mysqldump -uroot -p147258 testdb | gzip > testdbgzip.bak.gz还原：[root@Darren2 tmp]# gunzip < testdbgzip.bak.gz | mysql -uroot -p147258例4：只备份库中的表格式：mysqldump -uroot -p147258 库名 表名1 表名2 ... >备份文件名注意：不能加-B 参数，否则就是备份多个库了#备份单个表：[root@Darren2 tmp]# mysqldump -uroot -p147258 testdb t1 > testdbt1.bak#备份多个表：[root@Darren2 tmp]# mysqldump -uroot -p147258 testdb t1 t2 > testdbt1t2.bak例5：--master-data，指定日志文件从哪个位置开始，不用切割binlog日志了[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;--master-data=1时，备份文件中change master 是sql语句，在主从同步时用到--master-data=2时，备份文件中--change master被注释掉，还原时不具有sql语句作用例6：其它参数--single-transaction  适合innodb事物数据库备份，原理是设定本次会话级别是repeatable read,保证本次会话备份时，不会看到其他已经提交的会话，保证数据一致性-A, --all-databases Dump all the databases. This will be same as --databases-F, --flush-logs   即刷新binlog

总结：

innodb引擎 :一般生产全备使用的方法

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

备份的全过程

下面mysqldump备份的过程：

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 --刷表，为了防止有表的DDL操作，如果备份的时候有表的DDL操作，flush tables一直处于等待状态，直到DDL动作结束，才执行flush tables2016-12-21T15:31:00.475598Z        14 Query     FLUSH TABLES WITH READ LOCK       --全局锁表，所有会话不能对表进行任何DML和DDL操作，让数据处于一致性状态2016-12-21T15:31:00.475661Z        14 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ   --设置隔离界别为RR2016-12-21T15:31:00.475728Z        14 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */  --开始事物2016-12-21T15:31:00.475805Z        14 Query     SHOW VARIABLES LIKE 'gtid\_mode'  --查看GTID状态2016-12-21T15:31:00.478393Z        14 Query     SHOW MASTER STATUS  --查看master2016-12-21T15:31:00.478487Z        14 Query     UNLOCK TABLES  --解锁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    --创建事物的回滚点，如果下面一旦出错，可以回滚到回滚点之前的状态；2016-12-21T15:31:00.483262Z        14 Query     show tables2016-12-21T15:31:00.483459Z        14 Query     show table status like 't1'2016-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 't1'2016-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   --回到回滚点2016-12-21T15:31:00.485315Z        14 Query     show table status like 't2'2016-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 't2'2016-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  --回到回滚点2016-12-21T15:31:00.486943Z        14 Query     RELEASE SAVEPOINT sp  --释放回滚点2016-12-21T15:31:00.513639Z        14 Quit  --退出

总结：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

注意事项：

在使用mysqldump备份表的时候，如果对备份的表进行DDL操作可能使备份失败，因为DDL不在事物的框架中，mysql8.0以后可能会把DDL放在事物框架中；

mysqldump备份时如何保证数据的一致就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。