mysql - 在我开始进行 MYSQL 转储后，Web 应用程序出现故障

Question

尝试将进程推送到 Linux 上的后台，并尝试使用 -q 命令:mysqldump -q -u root -p root database_name > dumpName.sql 似乎没有用。

Answer 1

这很可能是因为 mysqldump 在备份期间锁定了表。

当您使用 InnoDB 表时，可以使用 --single-transaction 参数。请务必阅读manual :

• --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.

当你使用 MyISAM 表(不推荐)时，你就注定失败了。那么最好的选择是设置到另一台主机的复制。要进行备份，请停止从属服务器上的复制，并在 mysqldump 完成后再次启动从属服务器。 (google一下，这里就不详细解释了)。

另一个可能的解决方案是使用 Percona 的 xtrabackup。再次，谷歌并阅读整个手册!我怎么强调都不为过。