Articles

Lean local MySQL: turn off binary logs

By Wunderer

People at conference

On your local development machine where you have lots of databases with a lot of in/out dumping going on but no critical data that would need to be recovered in case of a rare DB inconsistency issue, you can free up Gigabytes of disk space by turning off “binary logging” in MySQL (only do this if you don’t run a master-slave-configuration locally, which would be the exception). The steps to turn off binary logs and clean up existing binary log files are easy:

  • On your shell locate my.cnf to, well, locate your MySQL configuration file (/private/etc/my.cnf on Mac).
  • Find and comment out all lines starting with log-bin or expire_logs_days to turn off binary logging.
  • Either…
    • purge your existing binary logs via the MySQL shell tool (type mysql and add your user/password): PURGE BINARY LOGS TO ‘mysql-bin.0076’ (replace -0076 with the highest running number you’ll find via a locate mysql-bin shell command)
    • restart MySQL
  • ​Or
    • stop MySQL
    • manually delete the mysql-bin binary log files
    • restart MySQL
      (Note: When stopping MySQL, be sure no mysql demon is left running, via a quick ps aux | grep mysql or via your system task/activity monitor tool, so you don’t get PID not found problems on startup).

Et voilà, easily dozens of GB space freed up on your local system drive (in my case a fast but rather small SSD where file size still matters).

References:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html