Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Quick warning. Watch out for this when going from RBR->SBR and using read-committed: bugs.mysql.com/bug.php?id=62493 –  Morgan Tocker Oct 13 '11 at 16:17

2 Answers 2

up vote 9 down vote accepted

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.

share|improve this answer
    
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). –  Austin Mills Mar 21 '13 at 21:32
    
For those curious, you can also put this "binlog_format = 'MIXED';" into your my.cnf. –  Christian Apr 6 '13 at 0:27
    
FYI, this answer is not in agreement with an answer here: dba.stackexchange.com/questions/58539/… –  HTTP500 Feb 10 at 13:42

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.

share|improve this answer
    
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 '11 at 14:42
2  
Setting the global variable first and waiting for the remaining sessions to finish, is the safest way. –  Jonathan Sep 27 '11 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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