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

admin

show global variables like 'expire_logs_days';
4 R2 o* K1 b( |# X; d. @+------------------+-------+
| Variable_name    | Value |
+ H( d6 @! [9 i; J$ @% d5 q; D# y+------------------+-------+
9 L2 U9 C2 v7 @/ i7 P: C| expire_logs_days | 0     |
) n; o1 t- R2 ]* G  y# R* P7 [& q修改bin.log日志清理日期：
set global expire_logs_days=3;
; Q; V* e' x  q2 y' F( Amysql>  show global variables like 'expire_logs_days';
2 ?2 t: l( \( |+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
6 e$ `2 \6 q" U+ u8 r' A| expire_logs_days | 3     |
+------------------+-------+
! ^0 s, Z- r3 z8 R% m1 row in set (0.00 sec)
+------------------+-------+
1 h7 h; K8 Q# p# @" G5 S1 row in set (0.01 sec)
2 N; z9 u0 g1 r, B3 H# Q" J8 a, A' ~

# y# U7 F- M1 G# |手动清理bin日志
( y2 F$ @- [8 }我们执行flush logs;
; g/ {; r8 R/ S9 ymysql> flush logs;
Query OK, 0 rows affected, 64 warnings (0.16 sec
7 @, I( F- V/ i% U( i如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:
9 Y! E# E. B5 [1 C" P
' Y) ^. M6 ?+ O" F. u" r! q% ^0 |2 x将bin.000055之前的binlog清掉:
! o, a6 M$ r+ i2 w4 |5 [5 s# C" Kmysql>purge binary logs to 'bin.000055';
将指定时间之前的binlog清掉:

mysql>purge binary logs before '2017-05-01 13:09:51';


7 ~) u( s( a$ `9 }8 q( [. |! JMariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
2 k3 ~1 I$ x0 [; b6 Y| Variable_name    | Value |
. O# n( Z' `1 ?* ]  E+------------------+-------+
9 R) h( i4 H7 y2 k6 n7 ]| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global expire_logs_days=2;
, l" X$ p! [# z: N7 }! ]5 RQuery OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
- e2 G( x4 }! j1 P4 v- [; o+------------------+-------+
, Z5 n; p$ M! K5 H/ Z: U' d$ R| Variable_name    | Value |
3 ]  @0 I/ R/ {( g+------------------+-------+
| expire_logs_days | 2     |
+------------------+-------+
1 V% k* V1 d$ `. m1 row in set (0.01 sec)
" P: y: G" _: AMariaDB [(none)]> set global expire_logs_days=3;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
4 n" ^3 N0 ~$ r6 N. a: @( X3 K| Variable_name    | Value |
! C: A1 J; `, B/ v8 u/ k+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
" W% q  r+ X" J6 b. l# @1 row in set (0.00 sec)
MariaDB [(none)]> flush logs;
, p) L+ ]* e0 jQuery OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
( G- G1 v6 f5 ~( t. V. a* xQuery 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)

admin

[mysqld]
$ X" B# x3 a% m5 _+ {port = 3306
, J. H0 B. [$ j9 m4 @# o1 P  rsocket=/tmp/mysql.sock
user=mysql
- R, \+ g$ [& N& B#log-bin=mysql-bin
log-bin=log-bin.log   设置binlog日志
#skip-grant-tables
expire_logs_days=3   设置清理binlog日志时间
) Z9 t# \3 x& qservice mysqld restart
+ K1 ~( p! G# b7 x! Q+ ?* `Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

, }2 `, ?+ O* h1 W% O% P0 u4 T$ @

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
6 S8 |, g. N5 B4 S. S" ?4 e, H) K| Variable_name    | Value |
+------------------+-------+
* H2 P  g# f/ P# W4 R' R| expire_logs_days | 3     |
5 j4 \4 K7 ^: V7 r) }+------------------+-------+
( \! n8 X# b. U1 row in set (0.00 sec)
, B3 B  k) [4 C& f
6 q0 S. ?0 U. C) W" Rmysql> flush logs;
; ?( ~# R, v$ j4 R6 o( }  uQuery OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
7 ?; a! q7 C* E+----------------+-----------+
% `) ]5 \& n) ]6 _| log-bin.000001 |     56932 |
  {4 q- H1 i" W/ g| log-bin.000002 |    116186 |
2 _2 w) s% Z1 J5 X6 M0 y! P| log-bin.000003 |      9780 |
+----------------+-----------+
3 rows in set (0.00 sec)
1 h9 a7 i- _; }( I, s
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+----------------+-----------+
8 r- ]& ]6 A' Q! ]2 u9 y3 e! O| Log_name       | File_size |
+----------------+-----------+
/ J( Q/ N" p* r2 I+ V, u) a3 A| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
9 g) s% s6 Q" q/ i  q| log-bin.000003 |     21390 |
8 o  w- S) |; V5 |0 L2 B& A4 {| log-bin.000004 |      1406 |
+----------------+-----------+
4 rows in set (0.00 sec)
* X5 Q9 m: k: X
mysql>