MySQL Binary Logging Format - Disk Filling Up

Question

I have a MySQL server instance configured with binary logging enabled. I don't do any replication, but the binary logs are part of our recovery plan - to be able to replay all the transactions since the last full backup.

Not too long ago, it was noticed on a system that had been running for a few weeks that the MySQL error log had grown to the unruly size of over 5 GB. Looking into the log, almost every line written to it was a warning about an "Unsafe statement written to the binary log".

Now, I do not control the application that is using the database, so I am unable to try and make these statements "safe". So, as a "fix", I configured the binlog_format to MIXED, rather than STATEMENT. This tells MySQL to use STATEMENT logging where possible, but fall back to ROW logging with unsafe statements. Doing this has successfully kept the size of the error log to a manageable size.

HOWEVER, now the binary logs are growing much faster than they used to (I saw 3 GB of log files in just a few hours today), presumably because now the system is writing to the log for every row that is affected (for "unsafe" statements), and for statements that affect a large number of rows, well, you get the picture.

So, I find myself between a rock and a hard place. If I use the STATEMENT format, the binary logs are manageable, but I get an insane number of warnings in the error log. If I use the MIXED format, the error log is fine, but the binary logs grow fast enough to possibly fill the partition in even a single day.

Which brings me to my quesion: what exactly is the repercussion of these "unsafe" statements? Like I said, I don't have any replication going on, so I don't need to worry about one server being exactly the same as another. I simply need to ensure that in the case that we need to restore from a backup, all of the data is there. Will the logging of "unsafe" statements cause data loss, or will there just be a situation where certain rows are in different orders (and possibly with different primary key id's)? If it's not a huge deal, then I can disable warnings in the error log (though that does seem heavy-handed).

Otherwise, I may be forced to do away with the binary logging altogether and just rely on potentially out-of-date full backups for the recovery plan.

Any advice in this situation?

Answer 1

Row based replication format actually uses more disk space than statement based. That is simple because into the binlog you would have all data that was inserted/updated not just the statement. So if a statement says to insert 100 rows if binlog_format=STATEMENT will insert just one statement but if is ROW will actually contain all entries.

So in order to save disk space you have to revert to STATEMENT based. Into a mix mode mysql will try to write as a STATEMENT into the binlog but in case of a unsafe statements will revert to ROW based. In your case it looks like you have many unsafe statements so you end up with ROW based binlogs.

You can do a few things

• leave it as ROW and implement a clean up job that will purge the logs after a period of time, this you need to calculate what is appropriate for your system. Before you delete the logs you should copy them somewhere else so you don't loose it.

• implement replication via a second system and again have a clean up job on master (make sure the slave is in sync or you may loose data)

• have a good look at the statements that are potential unsafe, this may require collaboration with the developers of your application.