MySQL Bin Files Eating Lots of Disk Space

Q. I get a large amount of bin files in the MySQL data directory called “server-bin.n” or mysql-bin.00000n, where n is a number that increments. What is MySQL Binary Log? How do I stop these files being created?

A. Usually /var/lib/mysql stores the binary log files. The binary log contains all statements that update data or potentially could have updated it. For example, a DELETE or UPDATE which matched no rows. Statements are stored in the form of events that describe the modifications. The binary log also contains information about how long each statement took that updated data.

The purpose of MySQL Binary Log

The binary log has two important purposes:

  • Data Recovery : It may be used for data recovery operations. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
  • High availability / replication : The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

Disable MySQL binlogging

If you are not replicating, you can disable binlogging by changing your my.ini or my.cnf file. Open your my.ini or /etc/my.cnf (/etc/mysql/my.cnf), enter:
# vi /etc/my.cnf
Find a line that reads “log_bin” and remove or comment it as follows:

#log_bin                        = /var/log/mysql/mysql-bin.log

You also need to remove or comment following lines:

#expire_logs_days        = 10
#max_binlog_size         = 100M

Close and save the file. Finally, restart mysql server:
# service mysql restart

Purge Master Logs

If you ARE replicating, then you need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs as those files are necessary for the proper operation of replication. Use following command to purge master logs:

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"

OR

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"

Suggested readings:

MySQL Manual – The binary logs

🐧 If you liked this page, please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
14 comments… add one
  • Amr Hamdy Dec 17, 2008 @ 18:44

    If you are using
    expire_logs_days = 10
    max_binlog_size = 100M
    You will not need to do purge logs manually .. logs older than 10 days will be purged automatically by mysql server …

    Thanks

  • NiTRo Dec 24, 2008 @ 16:30

    Amr Hamdy,
    you’re right but if you disable log_bin, you must purge manually.

    Thanks for this post.

  • Johny Jun 26, 2009 @ 7:47

    Hi, thanks for the tips

    does it works on innodb too ?

  • Steve Stringer Apr 22, 2010 @ 9:33

    Awesome tip. My logs were taking up 40 Gigs on my dev server and I had no idea.

    Thanks!

  • Music01 May 6, 2010 @ 19:46

    #!/bin/bash
    CURRENT_LOGFILE=$(/usr/bin/mysql -u root -p -S -e "SHOW SLAVE STATUS\G" | awk '$1 == "Master_Log_File:" {print $2}')
    echo "Purging Master Logs before ${CURRENT_LOGFILE}"
    /usr/bin/mysql -uroot -p -h -P -e "PURGE MASTER LOGS TO '${CURRENT_LOGFILE}'"
    echo "Purging Slave Logs before Current Log'"
    /usr/bin/mysql -uroot -p -h -S -e "purge binary logs before now();"
    exit $?

  • Aaron Feb 23, 2011 @ 16:43

    Hi, just a small correction:
    About the High availability / replication section. It states that the Master sends the binary logs to the slave. However, it’s actually the slave that pulls the log/data from the master.

    “Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave”
    Source: http://dev.mysql.com/doc/refman/5.0/en/replication-implementation.html

  • cangshubao Oct 13, 2011 @ 10:46

    hi

    could you tell me how to perform the data recovery operations by these files
    thanks

  • Katie @ StylishandTrendy Jul 11, 2012 @ 12:08

    Alternatively, you can try this mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

    This deletes all logs older than 7 days.

  • Katie @ StylishandTrendy Jul 11, 2012 @ 12:09

    Alternatively we can simply use this command

    mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

    It deletes all old logs older than 7 days.

  • linux7802 Aug 7, 2012 @ 10:59

    nice information ….

  • Gopalji varshneya Aug 31, 2013 @ 11:57

    PURGE BINARY LOGS BEFORE now()

  • Ganeshbabu Mar 3, 2015 @ 6:44

    Hi Friends, is it possiable to setup a master, slave replication without binlog process… any idea pls… give me asap.

    • Alex Ostrem Aug 7, 2015 @ 18:35

      Set up replication…without the logs it uses for replication to happen? No. If the master doesn’t log, there is nothing for the slave to download and apply.

  • bitkahuna Dec 13, 2015 @ 18:19

    great article, thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.