mysql /data directory...

[thoraddict]

Hi,

I am using mysql 4.0.2 on Solaris and I have a question about some files in the mysql /data directory.  When I inherited this web server the first thing I noticed is there wasn't much disk space so I began looking for large directories and sure enough this /data directly was off the charts. 

 

I have no idea what this sequentially named file is, when I open it I see mostly garbage text. 

 

Is there anyway to find what is writing to this directory?  Each file tops out at 1gb, it has the name ****-bin.038, ****-bin.039...etc  I thought there might be a mysql scheduled task someone could help me find or maybe there is a backup job?

 

Thanks,

[trq]

The mysql data directory contains the data belonging to your mysql databases.

[thoraddict]

So its just general data about all the DB's?  It seemed like a little more...ie, where is it set to only create files no larger then 1gb?  Why does it write a file every so many days...?  Is there more details I can find out?

 

Thanks,

[dsmythe]

The *.bin files are the MySQL binary log used for Replication. It records the queries executed on the server which change the data, and generally creates a new file every time the server is restarted.

 

In a replication environment, these logs would be transmitted to a separate MySQL server, which would replay the queries on it's own database, thereby re-creating an exact replica of the original database.

 

For data recovery, suppose you made a backup at 1:00am and the server crashed at 7:00pm that evening. Without the bin logs you would only be able to restore your data to 1:00am. With the bin logs,  you would be able to restore the database to 1:00am, and then replay the queries executed between 1:00am and 7:00pm to bring the database back to the state it was in before the crash.

 

If you don't replicate, and you don't care about potentially losing data after a crash, then by all means delete all but the most current *.bin file. This is also most likely set in your configuration file (usually /etc/my.cnf) with a flag like "log-bin" that you can comment or remove to stop it.

 

I wouldn't do it - but then again I wouldn't want to lose any data. If my data didn't change often I probably wouldn't care too much.

 

It's also fairly safe to delete the old *.bin files after you make a backup, so long as you're sure you have a valid backup. You won't need to replay queries that happened before you made a backup, because they would already be in your backup.

 

-Dan-