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

admin

show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
修改bin.log日志清理日期：
set global expire_logs_days=3;
% u6 ?  j" M. h7 l1 z- y8 Y* `mysql>  show global variables like 'expire_logs_days';
+------------------+-------+
& t' k0 ~- T- k' _4 v6 e| Variable_name    | Value |
' `* t8 ?! X& |5 P+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
+------------------+-------+
( ]; j6 d  N/ @. u" F0 \- y1 row in set (0.01 sec)


& Z7 c1 p' ?2 a) f, ?手动清理bin日志
我们执行flush logs;
& U0 a+ s' Z' ^5 {, d4 X( fmysql> flush logs;
7 U8 K6 b7 b$ `' h2 vQuery OK, 0 rows affected, 64 warnings (0.16 sec
如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:

; Q8 ^0 W* V: w- D, m! r% }) V将bin.000055之前的binlog清掉:
- W6 p9 ]$ W+ }' B! o' Fmysql>purge binary logs to 'bin.000055';
$ T% ~9 D% h, z, f0 X* ~* K将指定时间之前的binlog清掉:

mysql>purge binary logs before '2017-05-01 13:09:51';
4 U6 \2 t; c  B# z/ f- e5 }
; E! |9 x+ L& K; e' S2 ]
MariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
8 P8 U" X3 N" O7 k1 l3 O& I| Variable_name    | Value |
0 o* P# |' ]2 z$ n5 j+------------------+-------+
| expire_logs_days | 0     |
# N- v6 C, c9 w/ `, w3 I) v+------------------+-------+
1 row in set (0.00 sec)
2 z5 K: z0 [0 j$ M1 N# M2 L( ?MariaDB [(none)]> set global expire_logs_days=2;
Query OK, 0 rows affected (0.00 sec)
( @, H* k9 f9 AMariaDB [(none)]>  show global variables like 'expire_logs_days';
7 q1 G5 N* I  Y/ k* p8 g  z+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 2     |
+------------------+-------+
  O! |& D8 H6 v2 Y. t0 h. K6 _1 row in set (0.01 sec)
0 {% K2 r- v7 X& H' B3 B4 T. ^MariaDB [(none)]> set global expire_logs_days=3;
: H6 N8 }' u5 G; R. wQuery OK, 0 rows affected (0.00 sec)
: ?3 N  q/ o, m/ E7 a% L% `& }  rMariaDB [(none)]>  show global variables like 'expire_logs_days';
5 Y0 Z$ J, O" \+------------------+-------+
| Variable_name    | Value |
4 A' f. b) @# y! N1 B+------------------+-------+
  y  T9 m; ?" `( I3 Y) o; A8 ]( i| expire_logs_days | 3     |
  g- M3 F2 D1 v3 B" ?+------------------+-------+
1 row in set (0.00 sec)
8 ^+ v& W6 q$ j& F) y4 q- C5 yMariaDB [(none)]> flush logs;
" e7 m: ]& W7 }$ E9 XQuery OK, 0 rows affected (0.02 sec)
- d: |- Z4 R# K& u; dMariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
Query OK, 0 rows affected (0.00 sec)
6 W* z9 j  p: I+ @8 jMariaDB [(none)]> purge binary logs before '2021-11-18 19:00:00';
6 m$ q& W2 G( v3 V, n) x! i/ r: G& wQuery OK, 0 rows affected (0.01 sec)
+ u! ?' w5 ^( W

& E/ U5 i& W% D

admin

[mysqld]
$ d) R) Y4 ]+ r$ qport = 3306
socket=/tmp/mysql.sock
5 O1 @" P* `8 B, Y7 J" Z  K' v! N1 }user=mysql
0 ~0 f+ n4 X3 U- }* G#log-bin=mysql-bin
, O9 S7 Q9 g1 v: Mlog-bin=log-bin.log   设置binlog日志
% Q& i! z' {2 y2 u1 X#skip-grant-tables
5 c1 k6 m) l* u. u& Kexpire_logs_days=3   设置清理binlog日志时间
service mysqld restart
Shutting down MySQL.. SUCCESS!
5 L9 s. Q! ]9 X: oStarting MySQL. SUCCESS!
6 m" U) E% E. i+ @8 A- i; N
1 c2 j5 h/ L7 X0 B7 R" X

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
4 K& \* S: B/ g& R
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
3 v7 \  B* a) G$ H5 l! S+----------------+-----------+
' V4 U4 a( `1 {0 N5 @! L6 g3 y| log-bin.000001 |     56932 |
+ Y0 U  _& y- V| log-bin.000002 |    116186 |
| log-bin.000003 |      9780 |
+----------------+-----------+
3 rows in set (0.00 sec)

% o: P6 R0 d1 E* O, t3 Omysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
8 y1 h. w0 t9 Z' H
mysql> show binary logs;
1 Y8 S2 x# k0 M5 D; V7 D( [* r+----------------+-----------+
) @+ a6 I  o5 F: u/ N: ]| Log_name       | File_size |
$ g. ?! t" C: F. w9 S+----------------+-----------+
| log-bin.000001 |     56932 |
& Z) Z- d0 v# z' y$ e: Q| log-bin.000002 |    116186 |
8 Y, ^/ [# [# E/ T2 O| log-bin.000003 |     21390 |
4 N  ~1 s! g/ }/ E, g- }# b  m% ~| log-bin.000004 |      1406 |
+----------------+-----------+
4 rows in set (0.00 sec)
6 k5 ^. ?! r: N* e8 W
1 j2 a3 @0 O, {* O5 Imysql>