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

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

--single-transaction

This option sends aSTART TRANSACTIONSQL statement to the server before dumping data. It is useful only with transactional tables such asInnoDB, because then it dumps the consistent state of the database at the time whenBEGINwas issued without blocking any applications.

When using this option, you should keep in mind that onlyInnoDBtables are dumped in a consistent state. For example, anyMyISAMorMEMORYtables dumped while using this option may still change state.

While a--single-transactiondump 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 theSELECTthat is performed bymysqldumpto retrieve the table contents to obtain incorrect contents or fail.

The--single-transactionoption and the--lock-tablesoption are mutually exclusive becauseLOCK 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 theNDBCLUSTERstorage engine supports only theREAD_COMMITTEDtransaction isolation level. You should always useNDBbackup and restore instead.

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