In MySQL 8.0, when
binlog_format is set
statements that exclusively use temporary tables are not logged
on the master, and therefore the temporary tables are not
replicated. Statements that involve a mix of temporary and
nontemporary tables are logged on the master only for the
operations on nontemporary tables, and the operations on
temporary tables are not logged. This means that there are never
any temporary tables on the slave to be lost in the event of an
unplanned shutdown by the slave. The exception is if the
creation of a temporary table was recorded in the binary log
using statement-based format. In this case, a
TEMPORARY TABLE IF EXISTS statement is logged on the
master when the temporary table is dropped. For more information
about row-based replication and temporary tables, see
Row-based logging of temporary tables.
binlog_format is set to
STATEMENT, operations on temporary tables are
logged on the master and replicated on the slave, provided that
the statements involving temporary tables can be logged safely
using statement-based format. In this situation, loss of
replicated temporary tables on the slave can be an issue.
Safe slave shutdown when using temporary tables. Temporary tables are replicated except in the case where you stop the slave server (not just the slave threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the slave. If you stop the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:
STOP SLAVE SQL_THREADstatement.
If the value is not 0, restart the slave SQL thread with
START SLAVE SQL_THREADand repeat the procedure later.
When the value is 0, issue a mysqladmin shutdown command to stop the slave.
Temporary tables and replication options.
By default, with statement-based replication, all temporary
tables are replicated; this happens whether or not there are
options in effect. However, the
options are honored for temporary tables. The exception is
that to enable correct removal of temporary tables at the end
of a session, a replication slave 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 replication is
to designate a prefix for exclusive use in naming temporary
tables that you do not want replicated, then employ a
option to match that prefix. For example, you might give all
such tables names beginning with
norepyourtable, and so on), then use
to prevent them from being replicated.