/var/lib/mysql/servername-bin files

[verdon]

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,

[verdon]

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.

[verdon]

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.

[AndyReed]

/var/lib/mysql/servername-bin.000001

... 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.

This might help: http://servertune.com/kbase/entry/78/

[kev1nk]

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

[verdon]

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

[verdon]

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

[kev1nk]

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

[verdon]

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.