MIXED mode replication was as following :

There have been many changes in the replication implementation between 5.1 and 5.5.
Many changes have been implemented to address bugs that were discovered.

Yes, I would suggest changing the bin logging format from statement to mixed. 

 Mixed mode works as follows:
Perform binary logging in statement mode
If a statement appears to be unsafe, change to row mode
after finishing the current operation/event fall back to statement

The advantage is that the server can detect statements which can possibly cause
binlogging to fail or create inconsistencies and avoid them by using row based format.

The disadvantage is that as compared to statement based logging, row based logging
is slower and the logs would be bigger in size. But in case where statement based logging
may cause the replication to break, the performance degradation may be acceptable.

Please refer to the following link for further details.

http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html



After upgrading to 5.5 version you will see more messages mentioning that statement-based replication is unsafe for particular queries. As you upgrade to even later version, it may cause more messages as the replication issues with non-deterministic queries become larger in number. So the question is whether mixed mode replication will fix this issue and are there any concerns. This is exactly what mixed mode replication attempts to do for you - offer you the best of both types of replication. When a statement is able to be replicated easily, it will use this format, but if the statement is non-deterministic, that is, may cause a different result to occur in some way on the slave, then it will use row-based replication. To use the mixed mode, you can simply change the binary log format and restart the master for it to take affect.

If you have any concerns about how it works or possible catches, I would recommend the following document:

MySQL Server Variable: binlog_format (Doc ID 1507802.1)

 


When MIXED mode is enabled, then ROW based logging will automatically be done for any statements which would NOT be safe to replicate via statements.

> so I would say MIXED is the way to go for us after upgrading to 5.5

Yes, I would use MIXED or ROW. If you use ROW then you can also safely use these options:
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2


You can read more about these options here:
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode


These options will allow InnoDB to use far fewer locks -- locks necessary to ensure the stabilizability of statement based logs -- namely gap locks, insert intention locks, the table level AUTO_INC lock, etc.

Use the sql-log-bin session variable to turn off logging for the current session.

You can use the sql-log-bin session variable to turn off logging for the current session. Just simply enter:

SET sql_log_bin = 0;

and all queries on your current session will not be sent to the binary log. If you want to turn binary logging back on, run:

SET sql_log_bin = 1;

This is only for the currently running session you are in. All other queries from all other connections will still continued to be logged. Also, you must have SUPER privileges for this to work

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.

Relay logs can be 'deleted' but mysql should manage it automatically.

Relay logs can be 'deleted' but mysql should manage it automatically.
One way to do this is to check for the value of relay_log_purge.

It should be set to 1 if you want mysql to manage them:

set global relay_log_purge=1;

You would probably need to flush the logs:

flush logs;

This does not affect the binary logs.

The Binary log can provide valuable information

The Binary log can provide valuable information about the frequency of per table DML statements.

This simple one line Linux command can provide valuable output:

$ mysqlbinlog /path/to/mysql-bin.000999 | \
grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \
cut -c1-100 | tr '[A-Z]' '[a-z]' | \
sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | \
sort | uniq -c | sort -nr

33389 update e_acc
17680 insert into r_b
17680 insert into e_rec
14332 insert into rcv_c
13543 update e_rec
10805 update loc
3339 insert into r_att
2781 insert into o_att

relay-log-space-limit : IO_Thread and SQL_Thread

relay-log-space-limit

We don’t often see this option configured (default: unlimited) but it might be a good idea to set it. What it does is limit the amount of disk space the combined relay logs are allowed to take up.

A slave’s IO_Thread reads from the master and puts the events into the relay log;
the slave’s SQL_Thread reads from the relay log and executes the query.

If/when replication “breaks”, unless it’s connection related it tends to be during execution of a query. In that case the IO_Thread will keep running (receiving master events and storing in the relay log). Beyond some point, that doesn’t make sense.

The reason for having two separate replication threads (introduced in MySQL 4.0) is that long-running queries don’t delay receiving more data. That’s good. But receiving data is generally pretty fast, so as long as that basic issue is handled, it’s not necessary (for performance) to have the IO_Thread run ahead that far.
So you can set something like relay-log-space-limit=256M. This prevents slave disk space from getting gobbled up in some replication failure scenarios. The data will still be available in the logs on the master (provided of course the log expiration there isn’t too short – replication monitoring is still important!).

Conclusion: the relay log as a cache. Don’t leave it at “Unlimited”, that’s inefficient (and potentially problematic) use of resources. If you do run out of diskspace, the relay log can get corrupted – then you have to reposition, which will re-read the data from the master anyway.

Mass killing of MySQL Connections

Every so often I run into situation when I need to kill a lot of connections on MySQL server – for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3101;             |
| KILL 2946;             |
+------------------------+
2 rows in set (0.00 sec)

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)

mysql> source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)