Monday, March 11, 2013

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

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


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.

No comments:

Post a Comment