2

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

Initially my mysql-8.0.33-winx64\data 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-winx64\data\auto.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-winx64\my.cnf)

Basj
  • 41,386
  • 99
  • 383
  • 673
  • 2
    I suggest you do a bit of research on what the binlog files are used for! It will come in handy when you go live definitely. It may encourage you to do a backup now and again if nothing else – RiggsFolly Apr 19 '23 at 16:46
  • https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds – ysth Apr 19 '23 at 16:47
  • @ysth Can we disable this, or limit it to, say 500 MB, directly from SQL commands `cursor.execute("BINLOG disable;")`? – Basj Apr 19 '23 at 16:50
  • As I said above ___First find out what they are used for___ Remember MySQL is a pretty well fully funcitonal DBMS complete with crash recovery !!!! **BIG F'in HINT** – RiggsFolly Apr 19 '23 at 16:50
  • @RiggsFolly Is there a way to disable it directly from SQL commands syntax? Or is it mandatory to modify ini files? Note: During my tests, I don't need them, I don't need any backup, rollback, etc.: I often restart from zero, and don't want to fill GB for unused data. (No crash recovery needed either). – Basj Apr 19 '23 at 16:51
  • https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html – RiggsFolly Apr 19 '23 at 16:58
  • Also used for ROLLBACK – RiggsFolly Apr 19 '23 at 16:59
  • @RiggsFolly, You're thinking of the undo log. Binary log only stores committed changes. – Bill Karwin Apr 19 '23 at 17:26
  • Always get those confused @BillKarwin keep having to associate things to the good old days on a Univac where things were called the "quick look" quick before looks and "after look" commited upidates files :) And often get mixed up – RiggsFolly Apr 19 '23 at 17:28
  • The `auto.cnf` is not the same as the `my.cnf`! Do not edit or delete the `auto.cnf`. Read https://www.thegeekdiary.com/understanding-the-different-configuration-files-used-for-mysql-server/ – Bill Karwin Apr 19 '23 at 17:50
  • @BillKarwin Ok I will remove my edit about `auto.cnf`! I didn't find any `my.ini` or `my.cnf` at all in my whole computer (in any folder). I'm not using `mysql` as a Windows service, so I cannot use the main answer from https://stackoverflow.com/questions/1712646/i-can-not-find-my-cnf-on-my-windows-computer, but rather I'm running it from cmdline `mysqld --console`. – Basj Apr 19 '23 at 18:11
  • 1
    There is no `my.ini` or `my.cnf` created on a new installation. You create this file with a text editor. There are some default locations MySQL Server looks for the file, or you can optionally specify a file with an argument to mysqld given that you are running it explicitly. Read more here: https://dev.mysql.com/doc/refman/8.0/en/option-files.html – Bill Karwin Apr 19 '23 at 18:16

2 Answers2

3

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-winx64\my.cnf to include:

    [mysqld]
disable_log_bin

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

Basj
  • 41,386
  • 99
  • 383
  • 673
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you for your answer @BillKarwin! Can you edit to clarify the difference between "undo log" vs. "bin log"? (see your comment *"You're thinking of the undo log. Binary log only stores committed changes"*) It seems easy to confuse them, see another comment *"Always get those confused..."*. Thanks! – Basj Apr 19 '23 at 17:31
  • PS: `my.cnf` is not present in Windows, is it another name? (just for completeness since the question is with tag windows). – Basj Apr 19 '23 at 17:33
  • @Basj please refer to this question: https://stackoverflow.com/questions/1712646/i-can-not-find-my-cnf-on-my-windows-computer where the asker also used Windows and had trouble finding my.cnf. – Lajos Arpad Apr 19 '23 at 17:35
  • Added the SO link to the question so Windows users will also find the setting. Upvoted the answer as well, of course. – Lajos Arpad Apr 19 '23 at 17:37
  • The `my.cnf` may also be called `my.ini`. It is common that the file is not present on new installations, which means all options are at their default values. Read about it here: https://dev.mysql.com/doc/refman/8.0/en/option-files.html – Bill Karwin Apr 19 '23 at 17:39
  • I removed the edit about the my.cnf file from my answer, because I think it's tangential to the original question. – Bill Karwin Apr 19 '23 at 17:40
  • No worries, I understand your reasons. – Lajos Arpad Apr 19 '23 at 17:41
  • Thanks @BillKarwin. Can we disable both `binlogs` and `undologs`? – Basj Apr 19 '23 at 17:45
  • You can't disable undo logs if you use InnoDB tables. But there are options to manage undo log tablespaces. It's all in the manual: https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html – Bill Karwin Apr 19 '23 at 17:48
  • @BillKarwin I added a paragraph about how to remove old binlogs, is it ok for you? – Basj Apr 19 '23 at 18:22
  • Oops @BillKarwin `[mysqld] log_bin=OFF` doesn't work. It only creates `OFF.000001` files instead of `binlog.0000001` :) See also https://forums.mysql.com/read.php?10,592679,592685. How to disable them? – Basj Apr 19 '23 at 18:48
  • My apologies. The usage changed a lot since MySQL 5.x, so I got confused. I'm glad you found the correct configuration option. – Bill Karwin Apr 19 '23 at 19:07
  • 1
    Regarding removing old binary logs: another solution is that after you disable and restart, you can simply remove the old binary logs in the file explorer, since they are no longer used anyway. – Bill Karwin Apr 19 '23 at 19:09
2

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • 1
    Added FLUSH LOGS to cause a new binlog file to be started. PURGE BINARY LOGS won't remove the log file MySQL is currently writing to. – Bill Karwin Apr 19 '23 at 17:25
  • @LajosArpad `The binlog is being used to store write changes. It only stores full transactions`: is the data in this binlog permanently needed? Or is it no longer needed after transaction is done if we don't need any rollback or backups? I see 2 big files: `data/database_name/my_table.ibd` and `binlog.000001`. Does the former contain 100% of the data, or is the latter also needed? This is not 100% clear :) Thanks! – Basj Apr 19 '23 at 17:37
  • @BillKarwin thanks for the edit, indeed, `FLUSH LOGS;` is definitely an improvement. – Lajos Arpad Apr 19 '23 at 17:38
  • @Basj it is up to you to decide whether you need binlogs (I advise you to use them) or not. If you do not intend to use them, then Bill Karwin's answer will probably guide you towards your goals. If you want to only temporarily keep the binlogs (which seems to be a good idea), then it would probably make a lot of sense to periodically backup your database and remove all logs before your backup. The latter is very easy, just replace `NOW()` from my answer with the timestamp of your preference. – Lajos Arpad Apr 19 '23 at 17:42