188.8.131.52 Replication and Temporary Tables
The discussion in the following paragraphs does not apply when
binlog_format=ROW because, in that case, temporary tables are not replicated; this means that there are never any temporary tables on the replica to be lost in the event of an unplanned shutdown by the replica. The remainder of this section applies only when using statement-based or mixed-format replication. Loss of replicated temporary tables on the replica can be an issue, whenever
MIXED, for statements involving temporary tables that can be logged safely using statement-based format. For more information about row-based replication and temporary tables, see Row-based logging of temporary tables.
Safe replica shutdown when using temporary tables. Temporary tables are replicated except in the case where you stop the replica server (not just the replication threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the replica. If you stop the replica server, the temporary tables needed by those updates are no longer available when the replica is restarted. To avoid this problem, do not shut down the replica while it has temporary tables open. Instead, use the following procedure:
STOP SLAVE SQL_THREADstatement.
If the value is not 0, restart the replication SQL thread with
START SLAVE SQL_THREADand repeat the procedure later.
When the value is 0, issue a mysqladmin shutdown command to stop the replica.
Temporary tables and replication options. By default, all temporary tables are replicated; this happens whether or not there are any matching
--replicate-wild-do-table options in effect. However, the
--replicate-wild-ignore-table options are honored for temporary tables. The exception is that to enable correct removal of temporary tables at the end of a session, a replica always replicates a
DROP TEMPORARY TABLE IF EXISTS statement, regardless of any exclusion rules that would normally apply for the specified table.
A recommended practice when using statement-based or mixed-format replication is to designate a prefix for exclusive use in naming temporary tables that you do not want replicated, then employ a
--replicate-wild-ignore-table option to match that prefix. For example, you might give all such tables names beginning with
norep (such as
norepyourtable, and so on), then use
--replicate-wild-ignore-table=norep% to prevent them from being replicated.