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?