MySQL #01 binary logs settings & variables

Omitted settings related with binary log of MySQL will cause many critical problems, and that includes database scale grows up abnormally fast(like this post.) So I would introduce some configurations and show how to create events to handle binary log automatically.

I provided 3 different way to handle the management of binary log of MySQL in this post, there is another thing I have to mention there is no solution would be the best. But you should find a way that is the most suitable for your situation.


Purge binary log manually.

It’s a statement to remove binary log, and it also require BINLOG_ADMIN privilege. I provided several use cases under below.

There is a statement will show how many binary logs and its size. And you could use this statement to compare the difference of results between those purge conditions.

In addition to this, there is another way to find the binary logs’ location and their size.

MySQL Event

Purge binary log by MySQL event.

I think it is a good solution to purge binary log automatically by using MySQL event.

You have to use a MySQL user who has BINLOG_ADMIN and EVENT privileges to create this event, and you could combine the PURGE statements I mentioned above for the event under below.


Of course, you have to make sure the event_scheduler had been enabled.


If it hadn’t, then you should turn on the event_scheduler.


After then you could run SHOW EVENTS to list all of the events in a specific database.



Variables that can help you stabilizing MySQL.

You could refer to manual of MySQL website for a more comprehensive introduction. But some important variables under below that you should adjust them.

This variable controls the expiration period of binary log. If binary logs after its expiration period end, they will be removed by MySQL automatically. The default expiration period is 30 days.


If the current binary log size is larger than max_binlog_size, MySQL will open a new binary log to store new logs.


  1. PURGE BINARY LOGS Statement – MySQL 8.0 Manual
  2. CREATE EVENT Statement – MySQL 8.0 Manual

Andy Wang

Non-stop learning and facing challenges.

2 thoughts on “MySQL #01 binary logs settings & variables

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.