2

I have over 500 files in my /var/lib/mysql folder named mysql-bin.000522 (or some variation thereof)...each is anywhere from a couple megabytes to 1.1GB. Overall, these files are taking up >400GB on our system. How are these being created? Are they safe to delete? If they're not necessary, how can we prevent them from being re-created?

womble
  • 96,255
  • 29
  • 175
  • 230
user_78361084
  • 137
  • 5

2 Answers2

4

It's all in the MySQL documentation that they are binary log files for data recovery and synchronizing.

Look at the documentation on how to get rid of them. But make sure that you don't need them in your current setup.

mailq
  • 17,023
  • 2
  • 37
  • 69
1

As already mentioned those files are for binary logging. If you're not using them, you can issue a RESET MASTER to remove them.

I would also then stop the server, and add a new configuration variable to your my.cnf. expire_log_days will tell the server to keep the binary logs for the amount of days you specify, to prevent further buildup in the future.

You could also turn off binary logging by making sure the bin_log option is not set, but this is not the recommended route. A better solution would be to develop a backup and restore strategy so that if you do have a database crash, you can recover from it.

One such example, on a low-write server, would be to take a full backup once weekly. Then you would have the binary logs for 7 days (14 to be safe) to replay any changes from the full backup.

Here is what MySQL has to say about backing up and restoring.

Derek Downey
  • 3,955
  • 4
  • 27
  • 29
  • when you say "take a full backup once weekly" what do you mean? Having binary logging on will automatically do the backups, no? – user_78361084 Aug 06 '11 at 01:01
  • @user70981 The binary logs only record the changes made, so you'd have to have every log file ever in order to restore the database, and you'd have to sit there while all hundreds (thousands?) of them were imported back into the DB. By making an actual backup of the database, you only need backup plus the log files since that backup to restore the data. – DerfK Aug 06 '11 at 01:35
  • ahhh, I see what you're saying. thx! – user_78361084 Aug 06 '11 at 01:36