关于mysqldump使用中--skip-opt的一些坑

字数统计: 774阅读时长: 3 min

 2021/07/06   Share

前言

我们在备份线上数据的过程中, 经常使用mysqldump这个工具.而这个工具在备份过程中默认是开启锁表的--opt默认开启的, 这导致在备份大数据的时候会影响到线上业务.
为了防止这个,一般我们会在备份中使用--skip-opt 取消这个默认选项来备份.

mysqldump选项之skip-opt

实际上如果使用了这个选项, 默认也会关闭如下功能

1
2
3

--add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys

选项 --create-option 看起来比较不起眼：

1 2	-a, --create-options Include all MySQL specific create options.

如果关闭了它, 备份出来的表结构就会少了

1 2	AUTO_INCREMENT --PK字段的AUTO_INCREMENT属性以及数据表的AUTO_INCREMENT属性都会丢掉 ENGINE=InnoDB DEFAULT CHARSET=utf8 --数据表的引擎、字符集属性

并且数据结构也会变成每个数据单独一个insert插入的结构
如:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
    ...
    ...
    ...
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (5981,'',0,'3232',0,0,0,'',0,0,0,0,0,0,0,0,0,'');
INSERT INTO `users` VALUES (5987,'',0,'3232',0,0,0,'',0,0,0,0,0,0,0,0,0,'');
INSERT INTO `users` VALUES (5988,'',0,'3232',0,0,0,'',0,0,0,0,0,0,0,0,0,'');
INSERT INTO `users` VALUES (5989,'',0,'3232',0,0,0,'',0,0,0,0,0,0,0,0,0,'');
INSERT INTO `users` VALUES (5990,'',0,'3232',0,0,0,'',0,0,0,0,0,0,0,0,0,'');
...

大坑

这样备份的数据没有问题, 但是表结构却被完完全全的被破坏掉了。如果只是拿insert去还原原本数据库的数据倒是还好。

如果把之前表结构删除,或者重新迁移新的数据库, 因为主键的AUTO_INCREMENT 丢失, 后续插入的sql如果不带insert就会报错 Duplicate entry '0' for key 'PRIMARY'

备份数据库

去重新查了文档, 正确的操作应该是

使用 –single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

To dump large tables, combine the --single-transaction option with the --quick option.