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?


2 Answers 2


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:

SET GLOBAL binlog_format = 'MIXED';

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 !!!


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:

SET GLOBAL binlog_format = 'MIXED';

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.

  • 3
    One thing to keep in mind is that mysql replication settings are actually set on a per-client-session basis. Setting the global binlog_format merely changes the value for NEW sessions. Thus, if you're running it on a system with clients persistently connected, any changes you make to the settings will not be immediately applicable even if you do the flushing and locking as specified here -- they won't take effect until the clients reconnect (or set the value in their own session but in my experience the former is more likely). Mar 21, 2013 at 21:32
  • For those curious, you can also put this "binlog_format = 'MIXED';" into your my.cnf.
    – Christian
    Apr 6, 2013 at 0:27
  • 2
    FYI, this answer is not in agreement with an answer here: dba.stackexchange.com/questions/58539/…
    – HTTP500
    Feb 10, 2014 at 13:42
  • The manual states: This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (..snip..) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.
    – Halfgaar
    Sep 22, 2014 at 7:44
  • @Halfgaar Just this past week, I switched a slave from MIXED to STATEMENT three times with no ill effects. I was doing that because replication was breaking due to a race condition. The table became nonexistent on a slave before the query's execution. So, I switched to STATEMENT to stable like that situation. Of course, all writes were stopped while I did this. BTW I did the Master as well. Sep 22, 2014 at 11:29

To switch binlog_format at runtime you can do:

set global binlog_format = 'MIXED';

This will set all NEW sessions to be mixed binlog format. All existing sessions will be whatever was set previously until they end.

You can also do set session binlog_format = 'MIXED'; manually to solve any problems with session specifically.

  • I don't ask the way, I ask the safest way and how can I check if there is any temporary tables exist.
    – quanta
    Sep 27, 2011 at 14:42
  • 3
    Setting the global variable first and waiting for the remaining sessions to finish, is the safest way.
    – Jonathan
    Sep 27, 2011 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.