I have a database with three big tables, It has the following specialties:

  1. Three tables with around 15M insertions per hour for each table.
  2. No update.
  3. Select query fast with the help of Indexing and Partition.
  4. Only keep data for 14 days, I use PARTITION. Each partition for a day. I create a new Partition at 23:50 (before the new day start), then delete the partition of 15 days ago. (Fast and accurate)
  5. I back up the dataset every day, for one day. (only yesterday's data)

I monitor my binary log for nine days, log size increased sharply. The problem is: at some point, the binlog takes all my disk space. My questions are:

  1. In my case, Am I need binlog? (If the benefit is only replication I do not need it now)
  2. What is the best way to manage binlog? (If I keep it)
  3. Is there any relationship between Partition and binlog? (If I disabled it)

The command that runs, table, and MySQL configuration is shown below.

du -ch /var/lib/mysql/binlog.* | tail -n 1
Date Total Size in GB
2022-11-22 289 GB
2022-11-23 300 GB
2022-11-24 311 GB
2022-11-25 322 GB
2022-11-26 334 GB
2022-11-27 364 GB
2022-11-28 378 GB
2022-11-29 417 GB
2022-11-30 437 GB
  • If you don't need the binary log, why did you enable it in the first place? In any case, to disable it do the reverse.
    – mustaccio
    Commented Nov 30, 2022 at 21:27
  • 1
    Does this answer your question? Disable MySQL binary logging with log_bin variable
    – mustaccio
    Commented Nov 30, 2022 at 21:28
  • My question is not about how to disable binlog. what I said is, besides replication is there any other cases that I need my binlog? or if there is any relation between PARTITION and binlog?
    – Niyaz
    Commented Nov 30, 2022 at 21:32
  • Your question title literally says "How to disable (or manage) my binlog in MySQL?". Is that inaccurate?
    – mustaccio
    Commented Dec 1, 2022 at 0:31
  • How are you deleting partitions? They should not cause the binlog to expand a lot, because normally dropping partitions is done with ALTER TABLE, a DDL statement, which is always written to the binlog in statement format. Thus it should be small, not the size of the data in your partition. But if you use a DML statement like DELETE, and use ROW format for the binlogs, then that would expand the binlog proportional to the size of rows you delete. Commented Dec 1, 2022 at 2:37

1 Answer 1


There are several uses of the binary log:

  • Replication, as you know.
  • Point in time recovery. If you need to restore from a recent backup, using the binary log of changes since that backup can bring your restore up to the latest change (provided you still have the binary logs). See https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-binlog.html
  • Feeding change-data-capture tools (one open source example is Debezium), so you can stream MySQL to a data warehouse or another destination.
  • Sometimes investigating suspicious changes to data. This is of limited use, because the binary log records timestamps but not user names. An audit log would be better, but I've often been in environments where the management doesn't think they need an audit log, but then something happens and they want some data change investigated.

If one or more of these uses applies in your case, then you should keep your binary log. You haven't provided enough information for me to know that.

The binary log accumulates and grows larger and larger unless you expire some or all of the files. You can do this manually with PURGE BINARY LOGS (this will not allow you to remove the last binary log, because that log file is currently being written to).

  • In MySQL 8.0, binary logs also automatically expire after 30 days by default, or you can set it to a different time period (see binlog_expire_logs_seconds).

  • In MySQL 5.x, you may optionally configure automatic expiration of binary logs, but the default is to retain all binary logs forever (see expire_logs_days).

Note that expiration means when the binary log rolls over to open a new file, it checks the oldest files and if they're older than the expiration time window, they are removed. Remember that this does not happen until the log opens a new file. When using MySQL 5.x, this can lead to situations where files don't expire while you are doing a huge data import/export, because the granularity of file age is in days. In other words, if your expiration is for logs older than 1 day, but you fill up 500GB of data in one day, you could accumulate 500 binlog files, and none of them are older than 1 day, so they don't expire yet. So I'm glad they changed the granularity to 1 second in MySQL 8.0.

There is no special relationship between partitioning and binary logs. The binary log records all DML and DDL changes (though it is possible for a given session to suppress writes to the binary log).

DDL is always written to the binary log in statement format. There is no way ALTER TABLE will take more space in the binary log depending on the size of the table. So if your binary log is increasing a lot, it's because of DML (INSERT/UPDATE/DELETE). Your daily DROP PARTITION is not responsible.

  • thanks, I'm not sure if I need a binlog or not. I'm in the development stage of a big dataset, and I want to follow the best practice. I updated my question to give you more detail about my situation.
    – Niyaz
    Commented Dec 1, 2022 at 17:42
  • 1
    Nothing you have added to your question so far requires a binary log. Commented Dec 1, 2022 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.