知り合いのとこのwordpressサーバが稼働していなかったので調査したところディスクフルが発生していた
状況を確認すると、 /var/lib/mysqlが15GBと大変な状態になっていた
binlogがたまりまくっていた
というか、4/23以降、一日1GB以上出力されたりしてるのなんでだ？？
[root@xxxxxxxxxxx mysql]# ls -ltr /var/lib/mysql/binlog*
-rw-r-----. 1 mysql mysql 1076027128 Apr 11 12:15 /var/lib/mysql/binlog.000401
-rw-r-----. 1 mysql mysql 895282121 Apr 23 06:48 /var/lib/mysql/binlog.000402
-rw-r-----. 1 mysql mysql 1076428654 Apr 23 19:12 /var/lib/mysql/binlog.000403
-rw-r-----. 1 mysql mysql 596357576 Apr 24 03:25 /var/lib/mysql/binlog.000404
-rw-r-----. 1 mysql mysql 1074553571 Apr 24 22:57 /var/lib/mysql/binlog.000405
-rw-r-----. 1 mysql mysql 1075609896 Apr 26 11:28 /var/lib/mysql/binlog.000406
-rw-r-----. 1 mysql mysql 1075981972 Apr 27 18:37 /var/lib/mysql/binlog.000407
-rw-r-----. 1 mysql mysql 1075913505 Apr 28 00:47 /var/lib/mysql/binlog.000408
-rw-r-----. 1 mysql mysql 1075838660 Apr 28 05:05 /var/lib/mysql/binlog.000409
-rw-r-----. 1 mysql mysql 1075205513 Apr 28 08:48 /var/lib/mysql/binlog.000410
-rw-r-----. 1 mysql mysql 5651277 Apr 29 23:47 /var/lib/mysql/binlog.000411
-rw-r-----. 1 mysql mysql 2052256 Apr 29 23:56 /var/lib/mysql/binlog.000412
-rw-r-----. 1 mysql mysql 101787 Apr 30 00:01 /var/lib/mysql/binlog.000413
-rw-r-----. 1 mysql mysql 3171581 Apr 30 03:19 /var/lib/mysql/binlog.000414
-rw-r-----. 1 mysql mysql 1074621728 May 3 02:47 /var/lib/mysql/binlog.000415
-rw-r-----. 1 mysql mysql 1074747087 May 5 18:27 /var/lib/mysql/binlog.000416
-rw-r-----. 1 mysql mysql 1074122275 May 6 01:02 /var/lib/mysql/binlog.000417
-rw-r-----. 1 mysql mysql 1076183303 May 6 16:09 /var/lib/mysql/binlog.000418
-rw-r-----. 1 mysql mysql 1074003040 May 7 01:08 /var/lib/mysql/binlog.000419
-rw-r-----. 1 mysql mysql 320 May 7 01:08 /var/lib/mysql/binlog.index
-rw-r-----. 1 mysql mysql 286409863 May 7 16:07 /var/lib/mysql/binlog.000420
[root@xxxxxxxxxxx mysql]#
とりあえず、binlog関連の設定を「show variables like ‘binlog%’;」を実行して確認してみる
[root@xxxxxxxxxxx mysql]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50827
Server version: 8.0.45 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'binlog%';
+------------------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+------------------------------------------------+--------------+
24 rows in set (0.00 sec)
mysql>
17.1.6.4 バイナリロギングのオプションと変数を参照するとbinlog_expire_logs_secondsが関連する設定で、これが”binlog_expire_logs_seconds=2592000″ なので 720時間(30日)の保存となっている
「show binary logs;」を実行して、mysql内部で認識されているbinlog一覧を確認。まあ、/var/lib/mysql以下にあるファイルと一致してますね
mysql> show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000401 | 1076027128 | No |
| binlog.000402 | 895282121 | No |
| binlog.000403 | 1076428654 | No |
| binlog.000404 | 596357576 | No |
| binlog.000405 | 1074553571 | No |
| binlog.000406 | 1075609896 | No |
| binlog.000407 | 1075981972 | No |
| binlog.000408 | 1075913505 | No |
| binlog.000409 | 1075838660 | No |
| binlog.000410 | 1075205513 | No |
| binlog.000411 | 5651277 | No |
| binlog.000412 | 2052256 | No |
| binlog.000413 | 101787 | No |
| binlog.000414 | 3171581 | No |
| binlog.000415 | 1074621728 | No |
| binlog.000416 | 1074747087 | No |
| binlog.000417 | 1074122275 | No |
| binlog.000418 | 1076183303 | No |
| binlog.000419 | 1074003040 | No |
| binlog.000420 | 319677238 | No |
+---------------+------------+-----------+
20 rows in set (0.02 sec)
mysql>
まず最初として、「set PERSIST binlog_expire_logs_seconds=604800;」を実行してbinlog_expire_logs_secondsを7日間に短縮する
mysql> set PERSIST binlog_expire_logs_seconds=604800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog%';
+------------------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 604800 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+------------------------------------------------+--------------+
24 rows in set (0.00 sec)
mysql>
ただ、設定を変更しただけでは反映されないようで、10分程度待機しただけでは show binary logs;の結果に変化はなかった
mysql> show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000401 | 1076027128 | No |
| binlog.000402 | 895282121 | No |
| binlog.000403 | 1076428654 | No |
| binlog.000404 | 596357576 | No |
| binlog.000405 | 1074553571 | No |
| binlog.000406 | 1075609896 | No |
| binlog.000407 | 1075981972 | No |
| binlog.000408 | 1075913505 | No |
| binlog.000409 | 1075838660 | No |
| binlog.000410 | 1075205513 | No |
| binlog.000411 | 5651277 | No |
| binlog.000412 | 2052256 | No |
| binlog.000413 | 101787 | No |
| binlog.000414 | 3171581 | No |
| binlog.000415 | 1074621728 | No |
| binlog.000416 | 1074747087 | No |
| binlog.000417 | 1074122275 | No |
| binlog.000418 | 1076183303 | No |
| binlog.000419 | 1074003040 | No |
| binlog.000420 | 358509579 | No |
+---------------+------------+-----------+
20 rows in set (0.00 sec)
mysql>
13.4.1.1 PURGE BINARY LOGS ステートメント にあるように手動で「PURGE BINARY LOGS BEFORE ‘2026-05-01 00:00:00’;」と実行すれば2026/05/01以前のbinlogが削除されるのだと思うんだけど・・・ということで実行
mysql> PURGE BINARY LOGS BEFORE '2026-05-01 00:00:00';
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000415 | 1074621728 | No |
| binlog.000416 | 1074747087 | No |
| binlog.000417 | 1074122275 | No |
| binlog.000418 | 1076183303 | No |
| binlog.000419 | 1074003040 | No |
| binlog.000420 | 377894939 | No |
+---------------+------------+-----------+
6 rows in set (0.00 sec)
mysql>
ファイルは・・・削除されていますね
[root@xxxxxxxxxxx mysql]# ls -ltr /var/lib/mysql/binlog*
-rw-r-----. 1 mysql mysql 1074621728 May 3 02:47 /var/lib/mysql/binlog.000415
-rw-r-----. 1 mysql mysql 1074747087 May 5 18:27 /var/lib/mysql/binlog.000416
-rw-r-----. 1 mysql mysql 1074122275 May 6 01:02 /var/lib/mysql/binlog.000417
-rw-r-----. 1 mysql mysql 1076183303 May 6 16:09 /var/lib/mysql/binlog.000418
-rw-r-----. 1 mysql mysql 1074003040 May 7 01:08 /var/lib/mysql/binlog.000419
-rw-r-----. 1 mysql mysql 96 May 7 16:25 /var/lib/mysql/binlog.index
-rw-r-----. 1 mysql mysql 383435241 May 7 16:25 /var/lib/mysql/binlog.000420
[root@xxxxxxxxxxx mysql]#
とりあえずしのいだので、次はなぜ4/23以降でbinlogが肥大化したのか調査ですね・・・
