What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log:


[Warning] Unsafe statement written to the binary log using statement
format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
because it uses a LIMIT clause. This is unsafe because the set of rows
included cannot be predicted.

I want to switch the replication format to MIXED.

But according to the MySQL document:


Switching the replication format at runtime is not recommended when
any temporary tables exist, because temporary tables are logged only
when using statement-based replication, whereas with row-based
replication they are not logged.

So, the question is how can I identify if there is any temporary tables exist to switch the binary log format safely?

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

StackExchange Database Administrators Q#6150, answer score: 37