设置mysql（MariaDB ）数据库自动清理bin下log日志

admin

show global variables like 'expire_logs_days';
; M' @# g8 z. d, i4 @( u( B+------------------+-------+
| Variable_name    | Value |
0 F+ O2 v4 F& O% n5 ]8 `/ j5 }+------------------+-------+
$ i3 [" @# ?) y) E| expire_logs_days | 0     |
/ x; G9 \7 D! D# A修改bin.log日志清理日期：
! T% n0 `2 V) L+ @% P. i' z set global expire_logs_days=3;
& T- J% q7 z' K1 T6 ?mysql>  show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
& \( z% K2 [; X3 l| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
+------------------+-------+
/ v/ X$ k% @& S; h+ k1 row in set (0.01 sec)
; \: C+ ^3 k7 x: |# P) d

手动清理bin日志
我们执行flush logs;
mysql> flush logs;
! S# f9 s+ L; f1 RQuery OK, 0 rows affected, 64 warnings (0.16 sec
4 a3 e; T: W2 D如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:

6 o/ ]/ n6 p) ^! l将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';
$ \4 j7 u2 X0 Q$ J将指定时间之前的binlog清掉:
. {( h: n7 \+ s9 a+ X
5 c% M' v* _; t) V9 E% U* G9 t7 Gmysql>purge binary logs before '2017-05-01 13:09:51';
$ U% y) V* H$ x/ n( z
8 S( B9 i* ~) u9 t1 z# m7 D
MariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
: a/ z: J0 C% Y! ?+------------------+-------+
| expire_logs_days | 0     |
3 V% V6 }/ X% w2 W+ Q* _0 S+------------------+-------+
+ H# p6 T' {; ]) Y+ r0 W* R8 i1 row in set (0.00 sec)
MariaDB [(none)]> set global expire_logs_days=2;
4 j- ]# o4 X+ w' RQuery OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
- f- \' A- m7 Q| Variable_name    | Value |
" U( i; F  p- O0 p9 }; @+------------------+-------+
% @7 ^3 u) j& j' I) h| expire_logs_days | 2     |
+------------------+-------+
1 row in set (0.01 sec)
3 x- B0 r# ?' rMariaDB [(none)]> set global expire_logs_days=3;
$ V. w4 R! F8 z4 f; `- }6 VQuery OK, 0 rows affected (0.00 sec)
/ X  N) D& ^# M( S2 j) u/ nMariaDB [(none)]>  show global variables like 'expire_logs_days';
" r+ ]9 _% w: N+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
1 n% H% Z5 U4 R& i| expire_logs_days | 3     |
1 c; S0 q6 j/ ~& I. Y5 `+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)
) p" D6 v1 M1 r1 T( LMariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> purge binary logs before '2021-11-18 19:00:00';
Query OK, 0 rows affected (0.01 sec)
! ~& l7 b0 F- L6 d

admin

[mysqld]
port = 3306
( O; e' u4 U, e* u  U" d9 g. nsocket=/tmp/mysql.sock
user=mysql
% ]8 g- f8 ]# e; f5 l#log-bin=mysql-bin
; c- b! W/ z4 V3 C' h# ulog-bin=log-bin.log   设置binlog日志
#skip-grant-tables
expire_logs_days=3   设置清理binlog日志时间
! |( ], _; Y- qservice mysqld restart
+ Z- W" V$ Z/ K+ d% \. H; }, R4 cShutting down MySQL.. SUCCESS!
0 [2 L2 |, i" W4 ]# X( R8 ]4 m# z- kStarting MySQL. SUCCESS!

) |: G3 C# r8 q' j) W. R

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
2 Y& U) D# h% n+ X# }3 @, E| Variable_name    | Value |
+------------------+-------+
, l, z: P  Y! W( _6 @- ~2 v| expire_logs_days | 3     |
+------------------+-------+
0 e' i% ^# n/ D6 `1 row in set (0.00 sec)
6 Z! Z9 Z, n8 t, I
/ X3 l* J8 ^, ?+ ]) Q$ v* n5 Jmysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

* z' o8 e/ H- S+ L( s1 B( mmysql> show binary logs;
+----------------+-----------+
5 s8 b: [! |/ d  t" d) V. L/ R| Log_name       | File_size |
+ v. ?9 |/ ?' M( C: Y1 N% E+----------------+-----------+
| log-bin.000001 |     56932 |
. i3 q/ H9 |$ q/ i: {- e| log-bin.000002 |    116186 |
| log-bin.000003 |      9780 |
# i. x5 a2 s  k- H: ^+----------------+-----------+
3 rows in set (0.00 sec)
4 L: F& I4 b/ M9 I* w; V3 w* U
* d+ ?& o, b/ b! Cmysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
6 r, H7 d; t: K8 ~- O* ~) B2 i% N
+ G3 i. U9 w9 L7 e, mmysql> show binary logs;
+----------------+-----------+
3 {% l1 D0 l. d3 I8 }| Log_name       | File_size |
+----------------+-----------+
0 Q7 c& v3 w" l$ d) F. E| log-bin.000001 |     56932 |
+ |0 [) C3 s/ V: i| log-bin.000002 |    116186 |
) D7 p& y( R/ U3 I) y| log-bin.000003 |     21390 |
| log-bin.000004 |      1406 |
+----------------+-----------+
/ p& C, ^+ I, \3 N, x+ f# J" A4 rows in set (0.00 sec)
; u$ Z! c/ ^. m6 w4 c" N: X, ~( Z
7 m2 o2 O" {9 X) A- Umysql>