Question 20021 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL "data" folder keeps growing, even after DROP TABLE

I’m using MySQL (8.0.33-winx64, for Windows) with Python 3 and mysql.connector package.

Initially my mysql-8.0.33-winx64data folder was rather small: < 100 MB.

Then after a few tests of CREATE TABLE..., INSERT... and DROP TABLE..., I notice that even after I totally drop the tables, the data folder keeps growing:

  • #innodb_redo folder seems to stay at max 100 MB
  • #innodb_temp seems to be small
  • binlog.000001: this one seems to be the culprit: it keeps growing even if I drop tables!

How to clean this data store after I drop tables, to avoid unused disk space with MySQL?

Is it possible directly from Python3 mysql.connector API? Or from a SQL command to be execute (I already tried "OPTIMIZE" without success)? Or do I need to use an OS function manually (os.remove(...))?

Note: the config file seems to be in mysql-8.0.33-winx64dataauto.cnf in the portable Windows version (non-used as a service, but started with mysqld --console) (no default config file is created after a first run of the server, we can create it in mysql-8.0.33-winx64my.cnf)

Tags:

2

Answers


  1. 0

    The binlog is being used to store write changes. It only stores full transactions, so partial writes are not in it. If something happens accidentally, like your tables are being dropped, then the binlog will be useful so you will be able to recover them. It is also useful for replication purposes. See https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

    Now, if you are absolutely sure you no longer need the binlogs, then you can purge them, like

    FLUSH LOGS;
PURGE BINARY LOGS BEFORE NOW();

    see https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html

    Login or Signup to reply.
  2. 0

    You can disable the binary log, but only by setting disable_log_bin in your my.cnf file and restarting the MySQL Server. (See Disable MySQL binary logging with log_bin variable
    ) You can’t change binary logging dynamically. See https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin

    You can make the binary log automatically expire old logs as it rolls over to a new binlog file. This helps to limit the overall storage. See https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds

    You do need to understand what the binary log is used for before you decide to disable it. You might need it!

    The binary log is commonly used for three or four things:

    • Replication

    • Point-in-time recovery

    • Change Data Capture (CDC) tools

    • A poor form of change auditing. A real audit log is better, but some sites don’t have the audit log plugin installed.

    can you edit to clarify the difference between undo log vs. bin log?

    The binary log is for logging logical changes to your data. Nothing is written to the binary log until you COMMIT a transaction. It is not used for rollback, because by definition anything in the binary log has been committed. The binary log applies to all storage engines in MySQL.

    The undo log is only for the InnoDB storage engine. As you make changes to data during a transaction, the old version of the data is added to the undo log (this is also called the rollback segment in some documentation). So if you ROLLBACK, InnoDB can restore the original data. If you COMMIT, then the contents of the undo log for that transaction is discarded.

    Notes:

    • setting disable_log_bin in my.cf and restarting the MySQL server won’t delete old binlogs.

    • if you set disable_log_bin and restart the server first, and then do FLUSH LOGS; PURGE BINARY LOGS BEFORE NOW();, it won’t delete old binlogs

    • you have to do FLUSH LOGS; PURGE BINARY LOGS BEFORE NOW(); first, and only then edit the config mysql-8.0.33-winx64my.cnf to include:

      [mysqld]
disable_log_bin

      Then the old logs are deleted, and no new binlog will be created.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search