Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!

So what is the solution? you could either:
1. Not use “FLUSH LOGS” (nah that aint happenin)
2. Not use –error-log (that aint happenin either cos I need to use it for my specific setup)
3. Create an ugly hack script which saves the error log or renames it or whatever (Ugly Ugly)
4. Create a variable in MySQL which says: flush-log-ignore-error-log which if set “FLUSH LOGS” will know not to mess around with my error log. Hence … create a feature request (nicer and friendlier solution) and submit a tested patch too whilst at it.

If you think it is not idea that “FLUSH LOGS” recycles through error logs or and you have been bitten by the way it works like me, then feel free to put your vote into http://bugs.mysql.com/bug.php?id=56385&thanks=sub so that MySQL sees the importance of it.

Because Sharing is Caring!

VN:F [1.9.22_1171]
Rating: 5.5/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 7 votes)

Do you use MySQL replication? Do you use “FLUSH LOGS”? If yes you might want to read this., 5.5 out of 10 based on 2 ratings

  1. Jon says:

    Hi,

    This is already fixed in MySQL 5.5, which — as of version 5.5.3 — supports the syntax

    FLUSH [BINARY | ENGINE | ERROR | GENERAL | RELAY | SLOW] LOGS

    as explained at http://dev.mysql.com/doc/refman/5.5/en/flush.html

    cheers,

    Jon Stephens
    MySQL Docs @ Oracle
    Stockholm

  2. guillaume says:

    Well actually, you could use syslog, redirect to a specific facility and have your messages logged in a specific file. That would allow you better control of your error log files. Using the default file mechanism is really unreliable because of that behavior, I don’t think that a feature is really needed.

  3. That is great Jon well done! Your approach makes the most sense of all but I can’t really wait for 5.5 either, God knows when 5.5 will be GA (maybe Larry knows too) but I don’t :)

  4. I am currently running more than one instance on the machines in question using mysqld_multi and hence I can’t use syslog. Jon’s approach above makes most sense infact making it very flexible, pity it is only available on 5.5

  5. amorgan says:

    Just to give an update on the current (good) situation.

    For MySQL 5.1 flushing the logs no longer (from 5.1.51) renames the error log – if you want to rotate the error log then you need to rename it before flushing the logs. This was introduced under bug 29751.

    In MySQL 5.5 you now have the option of specifying which log(s) you want to flush and so you can flush the binary log independently of the error log.

    So for either release, the problem described in this post can be avoided.

    Regards, Andrew.

  6. Cheers Andrew, you are totally right and I should have updated the post myself. Thank you for your contribution to the community.

    regards,
    Darren

  7. Mannoj says:

    Darren, I use 5.0.77 but when I flush logs twice it doesn’t cycle my err log file. Is it the case only on master-master replication only . I use centOs 5 and RHEL 5.4 ?

*