mysql server - how to reduce disk space by moving mysql-bin.00000

One fine day, the server disk usage report that, the one of the partitions is almost full. After some searching, the database directory at /var/db/mysql/ (FreeBSD) or /var/lib/mysql/ (Linux) is occupying most disk space. Listing the directory only to found out that mysql-bin.0000* are the ones that is occupying the disk space. What are these files? Can it be remove it to reduce disk usage?

These mysql-bin.0000* files are the binary logs for your database. It contains all the statements of updating your database. It another words, it is the archived of your database. Removing it will need some preparations, which is not covered in this post. But the below does suggest a way to move it to another partition or disk :

1. Refer to the "mysql-bin.index" for log files that are in used.

2. Use the

ls -l

to decide which file is to leave it in the database directory.

3. Copy the files to another partition or disk. Consider limit the speed so to NOT impact the system performance of the database server.

4. Stop the mysql server by running :

/usr/local/etc/rc.d/mysql-server stop

on FreeBSD or

/etc/init.d/mysql stop

on Linux

5. Edit the file "mysql-bin.index" and remove the entries of the mysql-bin.0000* that intended to be delete. e.g. mysql-bin.000001, mysql-bin.000002 & so on.

6. Start the mysql server by running :

/usr/local/etc/rc.d/mysql-server start

on FreeBSD or

/etc/init.d/mysql start

on Linux

7. Delete the mysql-bin.0000* files. If it is too many, consider using the regex approach to the "rm" command.

8. Check what binary log files is mysql server using :

• log into mysql server :
  

  mysql -u admin_username -p

• show the binary logs :
  

  show binary logs;

Voilla !!!