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

admin

show global variables like 'expire_logs_days';
+------------------+-------+
+ |- e* w& _, _. M, M| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
5 T% r5 G  ?% }7 j+ ]) g: U修改bin.log日志清理日期：
5 R6 w9 Q+ N  t4 `; x8 L; u4 i set global expire_logs_days=3;
+ J* r: [0 E) Q# h. s, Pmysql>  show global variables like 'expire_logs_days';
( b# R- v, B5 ~, y5 V+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
* D0 `  X5 ~% n& o3 r3 j) `; y1 row in set (0.00 sec)
' |2 p8 o7 X5 b, W+------------------+-------+
1 row in set (0.01 sec)

6 Z+ z" t* m" `- g8 t: U
手动清理bin日志
4 p+ l, G. }4 `5 v  A我们执行flush logs;
mysql> flush logs;
Query OK, 0 rows affected, 64 warnings (0.16 sec
) S0 \. u$ f: _5 J$ Z5 A& F如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:

+ r+ y9 Y& g/ p9 y; b- F将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';
2 _4 r- V1 I9 }+ I将指定时间之前的binlog清掉:

mysql>purge binary logs before '2017-05-01 13:09:51';
( P% `# U& _7 f

MariaDB [(none)]>  show global variables like 'expire_logs_days';
1 u+ F/ P: s/ @3 g+------------------+-------+
, l* N( t% s6 n8 i& l" {7 e0 P| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
! q% `) d+ @  h4 A+------------------+-------+
5 X1 J# X7 A1 x0 ?; T9 [8 u1 row in set (0.00 sec)
MariaDB [(none)]> set global expire_logs_days=2;
9 o. Y& {8 L- g( M' a# c: p: b  oQuery OK, 0 rows affected (0.00 sec)
% m6 i3 w4 \" c, y5 D! m9 x' g, lMariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
5 g8 Q# Y2 |* a" \4 |1 \| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 2     |
* |" O8 x! H3 q1 ?) ^  L+------------------+-------+
7 K8 |$ E$ C) U* M, T% R' Y/ _  G1 row in set (0.01 sec)
$ F" y6 j5 F/ U! B& \MariaDB [(none)]> set global expire_logs_days=3;
! b: W( P7 y+ `- |Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
! q; v. c2 Y% U2 B* X+------------------+-------+
: W+ }& U5 ^$ F6 K. R  \/ F| Variable_name    | Value |
+------------------+-------+
4 o; o; v' ]" J6 P. ?1 W0 _| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
3 K* |' i% t5 h: U; q/ h2 XMariaDB [(none)]> flush logs;
$ ~2 Q5 C# D: c& I  [6 L2 ]Query OK, 0 rows affected (0.02 sec)
; _0 t+ Q' u: `MariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
Query OK, 0 rows affected (0.00 sec)
2 o1 r& W: }( b* ^, A) iMariaDB [(none)]> purge binary logs before '2021-11-18 19:00:00';
/ B" ^1 p' |$ e) @6 kQuery OK, 0 rows affected (0.01 sec)
) ]8 d+ n, o: P# J  E

2 e2 w# _( N+ M8 {0 o) b

admin

[mysqld]
port = 3306
socket=/tmp/mysql.sock
user=mysql
) e9 v* d7 H$ F; Y* D#log-bin=mysql-bin
5 k, q* }  V: ^# G9 ^log-bin=log-bin.log   设置binlog日志
#skip-grant-tables
! T+ |5 A$ L6 I& L, ?- \expire_logs_days=3   设置清理binlog日志时间
service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
) K- \% H7 D3 o* s/ A8 E: i5 f
% o* `! O- n: V/ G) S

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
8 \8 x4 }# N4 X+ h$ l% k| Variable_name    | Value |
  ?! `7 f/ {( c; T6 Z# J6 A. Q+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
% L1 N" J' Y* \& V3 Z6 P4 X" ?1 row in set (0.00 sec)

( l  Q7 F/ w& z+ p$ Q. x- pmysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

0 w6 H& \( d9 h. X  Z# bmysql> show binary logs;
+----------------+-----------+
7 y& y- `( z1 i0 ~* @5 M| Log_name       | File_size |
+----------------+-----------+
. A2 d( V% ]1 v  c4 b& g& k$ H| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
! Q) A7 B3 B, s$ q& ^  B& J1 u| log-bin.000003 |      9780 |
+----------------+-----------+
. z2 J5 P( @0 {" B( C7 A3 rows in set (0.00 sec)
6 @& q! U/ V6 |4 Y. f% O$ T; e% M
, B9 U( R5 s% Wmysql> flush logs;
0 g7 k) F% X$ T9 Y, k; uQuery OK, 0 rows affected (0.00 sec)
7 X9 V; q6 {5 f8 j7 R: g
6 C& s- U. a; Jmysql> show binary logs;
' i5 l3 o( R4 C0 ^/ j9 X! g( W5 C+----------------+-----------+
" a/ J; a5 ]& `: r) g8 ~. Z. z( v: T) P| Log_name       | File_size |
; L- `* o7 N- T7 c) @; o$ m7 F+----------------+-----------+
| log-bin.000001 |     56932 |
: z2 c6 J% C1 P; n| log-bin.000002 |    116186 |
| log-bin.000003 |     21390 |
| log-bin.000004 |      1406 |
" U# B, E8 ~' `% J8 s- u5 ?+----------------+-----------+
4 rows in set (0.00 sec)
4 v+ L  i& w9 P# T2 O/ d/ e, s
mysql>