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.
Here is the problem. The OS has two modes to print things
The --silent only affects stdout. How do you nail stderr?
Try one of the following, and see if it works:
mysql --user=myusername --password=mypassword --silent --force -b 2> nul
mysql --user=myusername --password=mypassword --silent --force -b --tee=nul
Give it a Try !!!
CAVEAT : I have dealt with something this before when answering a question about mysqldump : How to log verbose output from mysqldump?
Best Answer
This means that your binary log format
binlog_formatis STATEMENT, which is the default binary log format.STATEMENT-based format logs the statement issued, whileROW-based format logs how individual tables were changed. The problem withSTATEMENTlogging is certain statements cannot guarantee that the same data written on the Master will end up on the slave.An example:
You can get a good idea of different types of these non-deterministic statements by looking at how the
MIXEDformat handles switching toROW-based format.ROW-based format writes a lot more data to the binary log, but is much safer.