Safe shutdown of slaves 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 used 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 the
Slave_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.
A recommended practice when using replication is to designate a
prefix for exclusive use in naming temporary tables that you do
not want replicated, then employ a matching
--replicate-wild-ignore-table
option. 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 the replication of these tables.
User Comments
Add your own comment.