How to manage binlog in MySQL?

Question

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

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

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

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.