As we know, temporary tables have the following characteristics:
1. Session level, once the session is broken, it is automatically dropped.
2. With the default engine. If the default engine is InnoDB, and you're crazy about using temporary tables. Well, your ibdata will be infinitely enlarged.
3. As with the disk table, the default write to Binlog, and passively joined the rollback program.
Imagine the following scenario:
The database is corrupted and there are no backups. Just binary log all intact, happy. Import binary logs to MySQL.
There are two ways of doing this:
1) mysqlbinlog ... *.log | MySQL dbname; This use of the pipeline to restore data directly, short time, high efficiency. But if there is a pile of temporary tables, and just in the middle of the log, then tragic, import failed. (Recall the features of the temporary table.) ）
2 mysqlbinlog ... *.log > Result.log;mysql dbname < Result.log; This kind of time is long, the efficiency is low, occupies the high disk space, wastes many times the system resources. But the import was successful.
If there is a large number of temporary table applications, then in the master-slave replication, the correct method should be taken: (this I said 08 years ago.) ）
When writing data, of course, this is only the host. Whether you are managing MySQL in your application or on your own client, you should do this:
Copy Code code as follows:
[SQL]
SET sql_log_bin=0;
Turn off replication at the session level.
Update tmp_t1, tmp_t2 set ...;
Drop TMP_T1;
Drop tmp_t2;
SET sql_log_bin=1;
Turns on replication at the session level.
