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.
OBSERVATION #1
You mentioned Ask developers put all the temporary tables into a separated database
If your developers are using CREATE TEMPORARY TABLE commands to create temporary tables, they need to use CREATE TABLE instead. Here is why:
With MySQL Replication processing a temporary table, this is what occurs
- 1) Master run
CREATE TEMPORARY TABLE
- 2) Command inserted into binary log
- 3) Replication copies this over to the Slave's Relay Logs via I/O Thread
- 4) Slave SQL Thread runs
CREATE TEMPORARY TABLE
- 5) Slave processes data with that temp table
Once in a while, someone may run STOP SLAVE;
to run a backup. If STOP SLAVE;
is issued just after step 4, the temp created disappears and so does its data. When you run START SLAVE;
Replication breaks instantly complaining the table does not exist. This is normal because when a DB Connections terminates deliberately or accidently, all temp tables opened using CREATE TEMPORARY TABLE
in the DB session are dropped. Running STOP SLAVE;
kill the SQL thread who was holding opening the temp table.
The only workaround for this is to create the table using CREATE TABLE
instead of CREATE TEMPORARY TABLE
. When run STOP SLAVE;
, the temp table you created normally does not disappear.
I have seen this happen maybe 10 times in my DBA career. Fixing it using the binary logs to find out the name of the temp tables, to create those tables using CREATE TABLE
, then starting replication up was the only maintenance possible without just brute force copying the master.
OBSERVATION #2
mk-table-sync
only works on tables with primary keys and/or unique keys. It works maybe 99% of the time. I have seen instances where the checksum of a table on the master and slave were different. I would run mk-table-sync
, there were still differences (Of course, I was doing mk-table-sync
in circular replication with 3 masters, which can be a little dangerous. Using it in Master/Slave is far more stable)
OBSERVATION #3
You mentioned There is some unsafe queries. Does it get some problems with MIXED based replication?
It depends. The most popular unsafe query is any UPDATE or DELETE that uses ORDER BY ... LIMIT
. With SBR, this could possibly cause MySQL to UPDATE or DELETE rows from a table on the Slave in a different order tham that of the Master. With RBR, I believe the exact changes in a row are more identifiable to UPDATE or DELETE on the Slave.
SOLUTION : Avoid using unsafe queries. Then, you will not worry !!!
OBSERVATION #4
I just read your second link. ROFL !!! I am familiar with the poster of the answer.
Best Answer
After a lot of playing with the data the answer turned out to be NOT to insert data within a trigger into a table with an auto_increment column. Once I have shifted the id to be generated via the UUID() function all warnings went away.