mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled.

I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the same happen when there are any temporary tables created? We have temporary tables created in our app, and do not have any control over that.

First, I'll explain why DDL cannot execute during backup. The reason is that MySQL stores the definitions of relations (tables, indexes, etc) to MyISAM tables located at mysql subdirectory in version 5.6 or earlier (, therefore DDL should be prevented to execute during backup).

Next, I'll answer your question. I assume that you use only innodb tables. (If you use any MyISAM tables, --single-transaction option does not act to your wish.) In this case, the answer of your question is "Yes, you can create temporary tables during backup", because the definition of temporary tables does not need to store the mysql subdirectory, and creating temporary tables is a normal operation under transaction processing.

In other words, there is no problem because definitions and contents of temporary tables are not backed up.

StackExchange Database Administrators Q#134260, answer score: 3