mysqldump 参数--single-transaction

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

其实很简单，single-transaction可以让mysqldump 的时候不锁表。但是他有3个前提

1. innodb的引擎
2. 不能在执行的同时，有其他alter table ,drop table,rename table,truncate table的操作。
3. 隔离级别 必须是REPEATABLE READ ，很多公司都会修改这个隔离级别的，比如阿里云的rds ，默认隔离级别是READ-COMMITTED

下面附上 mysqldump --help的 内容，和mysql 官网上的内容:

--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

• --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 TABLEScauses 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, combine the --single-transaction option with the --quick option.