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 slave to be lost in
the event of an unplanned shutdown by the slave. The remainder
of this section applies only when using statement-based or
mixed-format replication. Loss of replicated temporary tables on
the slave can be an issue, whenever
binlog_format
is
STATEMENT
or 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 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:
Issue a
STOP SLAVE SQL_THREAD
statement.Use
SHOW STATUS
to check the value of theSlave_open_temp_tables
variable.If the value is not 0, restart the slave SQL thread with
START SLAVE SQL_THREAD
and 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, all temporary tables are replicated; this happens
whether or not there are any matching
--replicate-do-db
,
--replicate-do-table
, or
--replicate-wild-do-table
options in effect. However, the
--replicate-ignore-table
and
--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 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 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 norepmytable
,
norepyourtable
, and so on), then use
--replicate-wild-ignore-table=norep%
to prevent them from being replicated.