The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

/var/lib/mysql/servername-bin files

Discussion in 'General Discussion' started by verdon, Sep 12, 2007.

  1. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    Hi,

    In my ongoing quest to see what's eating my hard-drive lately, I've discovered a bunch of files ranging in size from a couple hundred mb to near a gb each. These files are named along the lines of ...

    /var/lib/mysql/servername-bin.000001
    /var/lib/mysql/servername-bin.000004
    /var/lib/mysql/servername-bin.000002
    /var/lib/mysql/servername-bin.000003
    /var/lib/mysql/servername-bin.000006
    /var/lib/mysql/servername-bin.000007
    /var/lib/mysql/servername-bin.000008

    ... and so on. They all seem to have been modified today. Are these normal files? Are they also being backed up by cpbackup? I have looked in here before, but to be honest, I don't remember these files one way or the other.

    Thanks,
     
  2. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    Actually, I was wrong about the mod dates... they have been last modified from any time between the 10th of July 07 and today. There are 23 of them totaling nearly 17 GB.
     
  3. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    OK, I've done some more googling around and although most of the hits I found were in languages I couldn't read, it's looking like these are likely binary log files for some databases that are innodb instead of myisam. I've found hints that the behaviour of these may be controllable in my.conf, but no concrete information I could understand. I've taken a quick look through the dbs on the server and as far as I can tell there is only one using innodb storage, and that is for an openads installation. I'm going to try some looking around there.

    In the meantime, if anyone has any hints as to whether or not it is safe to delete some of these, or any other tips in dealing with them, it would be much appreciated.
     
  4. AndyReed

    AndyReed Well-Known Member
    PartnerNOC

    Joined:
    May 29, 2004
    Messages:
    2,222
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Minneapolis, MN
    This might help: http://servertune.com/kbase/entry/78/
     
  5. kev1nk

    kev1nk Member

    Joined:
    Sep 11, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Re: bin files

    It seems that you have binary logging enabled in your binary configuration. If I am right then your users or applications use many InnoDB tables and make changes to them.
    If so then you can create snapshots of your innodb tables and if you do not have any issues with them , then you can simply delete the binary logs :)
     
  6. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    Andy, Kevin,

    Thanks for the pointers. You've got me on the right track. Binary logging is enabled. It was done a while back by a 3rd party I had doing a few performance tweaks on my server (I think). The other changes made in my.cnf at that time make sense to me, but not this one.

    I'm wondering...
    1) I do not have any other servers connecting to mysql on this server, so I know I don't need binary logging for that purpose
    2) I only have one db using innodb tables on this server, does that mean I MUST have binary logging enabled
    3) Is there any other reason why binary logging would be enabled? Performance benefits or some such thing?

    Thanks again,
    verdon
     
  7. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    In case anyone else is reading this... I haven't answered all my own questions, but I have found a few things out. Although I could safely shut off binary logging (I think) in mysql 4.1 and may actually realize a small (1%) performance gain, it looks as if binary logging is the standard in mysql > 5.x and moving ahead.

    The manual at mysql.com suggests a number of strategies for dealing with the large log files created this way (apparently rotation and deletion of old logs is not a default). There were also a number of user-contributed scripts, perl and php, for doing so.

    I'm surprised that there is nothing (AFAIK) built-in to cpanel or my OS for dealing with this, as these files start to chew up a lot of disc space. Maybe there is and it's just not working for me.

    At any rate, in the short term, I'm going to try adding a expire_logs_days directive in my.cnf and then delete the older files on my server, then monitor it for a while.

    If anyone reading this has better advice, or can see that I am passing on misinformation, please correct me :)
     
  8. kev1nk

    kev1nk Member

    Joined:
    Sep 11, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Re: binary

    Hello,

    The binary logging will give you the opportunity to restore your DB data easily. However you do not need that if you have regular SQL , mysqldump based backups.
    If you do not have any changes to your InnoDB tables then you do not need binary logging.
    Regarding the performance - the InnoDB performance is great but the logging is one IO operation - that means resourse usage....

    Best Regards
    Kevin
     
  9. verdon

    verdon Well-Known Member

    Joined:
    Nov 1, 2003
    Messages:
    836
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Northern Ontario, Canada
    cPanel Access Level:
    Root Administrator
    Thanks Kevin,

    Also found some interesting reading at the site in your signature. I'm going to try and learn more before I do much more, specifically into log rotation and retention IF binary logs are being used.
     
Loading...

Share This Page