MySQL #01 binary logs 設定與屬性
無意間忽略 MySQL Binary Log 相關設定可能會造成很嚴重的問題，而這包括資料庫增長速度異常(例如這篇文章)。所以在本文中將會介紹一些跟 Binary Log 相關的設定、以及如何建立可以自動刪除 binary log 的 MySQL Event。本文提供三種方法解決 Binary log 佔據過多空間的問題，各方法之間皆有不同的用途與特色，只有使用情境是否適合而已。
There is the English version of this post: MySQL #01 binary logs settings & variables
手動刪除 Binary log
purge 是一個 MySQL 的指令，可以用來刪除 binary log ，但前提是執行指令的使用者必須要擁有 BINLOG_ADMIN 的權限，下方有幾個使用情境提供給你參考。
下列的指令可以用來查看現在 Binary log 的大小、檔名，而你也可以透過這個指令查看不同的
purge情境使用差異。
除此之外，下列是查看 Binary log 檔案大小與檔名的另一種方式。路徑『/var/lib/mysql/』是 CentOS 的 MySQL 放置資料庫與相關檔案的目錄，而
binlog則是 binary log 預設檔名的開頭。
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 刪除某特定時間點之前的 binary log PURGE BINARY LOGS BEFORE '2020-04-02 20:40'; -- 刪除今天以前的 binary log PURGE BINARY LOGS BEFORE DATE(NOW()); -- 刪除4小時之前的 binary log PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 4 HOUR); -- 刪除所有的 binary log PURGE BINARY LOGS; |
建立 MySQL Event 清除 Binary log
我認為這是個用來定期清除 Binary log 的好方法之一。
你必須要使用同時具備 BINLOG_ADMIN 與 EVENT 權限的 MySQL 使用者來建立下列這個 Event。然後結合上一節內容中適合你的使用情境。
|
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); |
此外，你還需要確認 MySQL 的全域變數 event_schedular 是開啟的狀態。
|
1 |
SELECT @@event_scheduler; |
如果是關閉的狀態，可以再透過下列語法修改。
|
1 |
SET GLOBAL event_scheduler = ON; |
最後，你還可以使用這個語法來查看在特定資料庫中的所有 event。
|
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 | +-------+--------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ |
MySQL 8 設定
如果你需要的話，建議還是到 MySQL 官網提供的手冊查詢會更全面。在此，我認為關於 Binary log 相關的設定中，有兩個是一定調整的設定值 binlog_expire_logs_seconds 和 max_binlog_size。
binlog_expire_logs_seconds
這是用來設定 Binary log 過期時間的變數，數值單位是秒鐘。MySQL會自動將超過這個時間的 Binary log 刪除。
|
1 2 |
/* 設定 binary log 8 小時過期 */ SET GLOBAL binlog_expire_logs_seconds = (60 * 60 * 8) |
max_binlog_size
這是用來設定 Binary log 單檔最大 size ，單位可以是 K, M, G分別代表 KB, MB, GB。如果正在寫入的 Binary log 超過這個大小，MySQL 就會自動建一個新的檔案。
|
1 2 |
/* Set the maximum size of each binary log. */ SET GLOBAL max_binlog_size = 512M; |
Pingback:Wordpress 資料庫大小猛增 - BrilliantCode.net