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
purgeconditions.
In addition to this, there is another way to find the binary logs’ location and their size.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Remove binary logs which are before a certain point of time. PURGE BINARY LOGS BEFORE '2020-04-02 20:40'; -- Remove binary logs which are before today. PURGE BINARY LOGS BEFORE DATE(NOW()); -- Remove binary logs which are before 4 hours ago. PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 4 HOUR); -- Remove all binary logs. PURGE BINARY LOGS; |
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.
|
1 2 3 4 5 |
use mysql; CREATE EVENT Event_purge_binlog ON SCHEDULE EVERY 4 HOUR STARTS '2020-04-02 04:00:00' DO PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 4 HOUR); |
Of course, you have to make sure the event_scheduler had been enabled.
|
1 |
SELECT @@event_scheduler; |
If it hadn’t, then you should turn on the event_scheduler.
|
1 |
SET GLOBAL event_scheduler = ON; |
After then you could run SHOW EVENTS to list all of the events in a specific database.
|
1 |
SHOW EVENTS FROM mysql; |
|
1 2 3 4 5 |
+-------+--------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-------+--------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | mysql | Event_purge_binlog | root@localhost | SYSTEM | RECURRING | NULL | 4 | HOUR | 2020-04-02 04:00:00 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-------+--------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ |
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.
|
1 2 |
/* Set the binary log expiration period as 8 hours. */ SET GLOBAL binlog_expire_logs_seconds = (60 * 60 * 8) |
If the current binary log size is larger than max_binlog_size, MySQL will open a new binary log to store new logs.
|
1 2 |
/* Set the maximum size of each binary log. */ SET GLOBAL max_binlog_size = 512M; |
Pingback:Wordpress DB went too large - BrilliantCode.net
Pingback:MySQL #01 binary logs 設定與屬性 - BrilliantCode.net