MySQL – Large Files in WAMP\bin\mysql\mysql5.5.24\data Directory

logsMySQLmysql-5.5windows

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server.

They are consuming about 22 GB on a 40 GB drive.

enter image description here

I am using a LMS called moodle, that may be kicking off the creation of them, I am not sure.

I am trying to determine:

  1. what is creating these files?

  2. can I delete them to reclaim drive space?

Best Answer

1 - what is creating these files?

Those files are called binary logs. They contain a list of all completed SQL statements you have executed. Your mysqld process created them and autorotates at 1GB. Any restart of mysqld or issuing FLUSH LOGS; will close one binary log and open another

They got created because there is a line in my.ini that looks like this:

log-bin=mysql-bin

Note the timestamps on each file

  • mysql-bin.000068 has 10/31/2012 2:13 PM
  • mysql-bin.000069 has 11/1/2012 11:13 AM
  • mysql-bin.000070 has 11/5/2012 11:51 AM
  • mysql-bin.000071 has 11/9/2012 11:50 AM
  • mysql-bin.000072 has 11/13/2012 11:13 AM
  • mysql-bin.000073 has 11/16/2012 12:05 AM

Here is what has been going

  • On 10/31/2012 at 2:13 PM
    • mysql-bin.000068 surpassed 1GB (1048576 KB)
    • mysql-bin.000068 closed
    • mysql-bin.000069 opened
  • On 11/1/2012 at 11:13 AM
    • mysql-bin.000069 closed due to mysql restart or FLUSH LOGS;
    • mysql-bin.000070 opened
  • On 11/5/2012 at 11:51 AM
    • mysql-bin.000070 closed due to mysql restart or FLUSH LOGS;
    • mysql-bin.000071 opened
  • On 11/9/2012 at 11:50 AM
    • mysql-bin.000071 surpassed 1GB (1048576 KB)
    • mysql-bin.000071 closed
    • mysql-bin.000072 opened
  • On 11/13/2012 at 11:13 AM
    • mysql-bin.000072 closed due to mysql restart or FLUSH LOGS;
    • mysql-bin.000073 opened
  • On 11/16/2012 at 12:05 AM
    • mysql-bin.000073 surpassed 1GB (1048576 KB)
    • mysql-bin.000073 closed
    • mysql-bin.000074 opened

By the way, the reason it autorotates at 1GB? The default setting max-binlog-size is 1GB.

2 - can I delete them to reclaim drive space?

As long as you do not have mysql replication installed, then yes.

First login to mysql and run

mysql> RESET MASTER;

This will erase all binary logs and leave you with mysql-bin.000001 with 107 bytes.

Next, if you comment it out like this

#log-bin=mysql-bin

and restart the mysqld process

C:\> net stop mysql
C:\> net start mysql

The logs will no longer be written.

As a final step, you can then delete mysql-bin.000001 from Windows Explorer.

Give it a Try !!!

Related Question