mysqldump导数据库数据不加锁（InnoDB）

最新推荐文章于 2025-05-31 23:56:38 发布

转载最新推荐文章于 2025-05-31 23:56:38 发布 · 3k 阅读

文章标签：

#数据库 #table #transactions #database #server #mysql

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文深入解析了MySQL mysqldump的单交易模式选项，详细阐述了其工作原理、适用场景及使用注意事项，特别是针对InnoDB表的一致性状态备份。同时，文章还介绍了如何结合快速模式来高效备份大型表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转载自：http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction

--single-transaction

This option 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 whenBEGIN 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.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

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