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

admin

show global variables like 'expire_logs_days';
+------------------+-------+
- h4 U! u% j! V6 ?| Variable_name    | Value |
( r9 s8 R) v9 ^& B7 ]" |) V" E+------------------+-------+
| expire_logs_days | 0     |
6 M2 I+ V/ c. H9 b! X. @修改bin.log日志清理日期：
- _, r2 K0 W. d4 E set global expire_logs_days=3;
  @2 q5 L- `0 D  Q$ j/ U* omysql>  show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
; F2 k8 b+ C, v( c| expire_logs_days | 3     |
+------------------+-------+
) G% A8 \4 k% a. L% R1 row in set (0.00 sec)
+------------------+-------+
& ^/ d- b# ~3 @7 t# |6 |! D& G' S1 row in set (0.01 sec)
/ M4 X7 i6 b4 P/ j5 N- }
$ y: ~; W8 s9 \/ m) p
手动清理bin日志
我们执行flush logs;
1 u8 s& L  t5 N8 k" v1 g0 I; Omysql> flush logs;
' b" y& A3 M, t. r3 ?Query OK, 0 rows affected, 64 warnings (0.16 sec
如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:
$ [/ P$ ~$ x& M6 t  Q
将bin.000055之前的binlog清掉:
9 C0 B/ d2 r4 _/ N8 [' n  Vmysql>purge binary logs to 'bin.000055';
将指定时间之前的binlog清掉:

# M+ |# V% m2 y. f8 o1 N# L7 g" i- kmysql>purge binary logs before '2017-05-01 13:09:51';
. Z+ N8 E* {7 }

( S, w: y; ]& e5 vMariaDB [(none)]>  show global variables like 'expire_logs_days';
0 M' r, `# ]" A% ]+ Z' E1 ]  d+------------------+-------+
| Variable_name    | Value |
+ R5 o0 }5 M$ P# R. N' X# R+------------------+-------+
! {) H8 D- _5 U9 A. ^0 e; T| expire_logs_days | 0     |
: M4 W$ L! B( V$ c+------------------+-------+
1 row in set (0.00 sec)
5 D9 ?8 Z4 @( d" l1 m' _1 d- uMariaDB [(none)]> set global expire_logs_days=2;
# j7 S& u: V7 R+ b* YQuery OK, 0 rows affected (0.00 sec)
  h& a* A, Q# b  \. V2 y( I1 KMariaDB [(none)]>  show global variables like 'expire_logs_days';
+ _7 r9 F5 G( d8 l2 ~% h& c' V" L+------------------+-------+
# C1 L9 r6 u6 P0 l' F| Variable_name    | Value |
+ [+ o6 g9 p. r3 V; }+------------------+-------+
| expire_logs_days | 2     |
' e, }( T/ `6 I! e$ t" p+------------------+-------+
1 row in set (0.01 sec)
MariaDB [(none)]> set global expire_logs_days=3;
3 e6 }$ ^  N- i& a( d! r+ b/ O! \Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
& C: o/ E/ r1 C" S1 p, M" ?Query OK, 0 rows affected (0.00 sec)
3 u8 p# L" E- x/ ?MariaDB [(none)]> purge binary logs before '2021-11-18 19:00:00';
Query OK, 0 rows affected (0.01 sec)
+ ?2 ~1 y' Z2 g9 T: d1 e+ w6 {
# z; V2 {$ F7 Z% C  l

admin

[mysqld]
5 d0 V0 p8 m3 v" n* R9 ?7 lport = 3306
3 j, g  R9 ?- \3 n5 d/ v! x' isocket=/tmp/mysql.sock
user=mysql
#log-bin=mysql-bin
2 x3 U; |5 x5 i/ ?. \: Jlog-bin=log-bin.log   设置binlog日志
#skip-grant-tables
expire_logs_days=3   设置清理binlog日志时间
service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

  @2 Z+ V* z; r9 e, w. z

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
* k, B2 v4 r- R. j2 E1 a| Variable_name    | Value |
( g% m' Q% L8 ~/ _+------------------+-------+
6 c; B. w0 @  R& Y% o  u. ]8 z3 l3 E| expire_logs_days | 3     |
+------------------+-------+
) K+ g" `5 ?" q1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
3 W% B1 i& W* {9 t9 y1 Z1 T, h| log-bin.000001 |     56932 |
% T- }- @( i7 o| log-bin.000002 |    116186 |
5 d- y+ @# e* K2 A# y| log-bin.000003 |      9780 |
+----------------+-----------+
3 rows in set (0.00 sec)
. S5 V3 b  N8 _4 u
mysql> flush logs;
0 ^9 z0 G- k4 R% B; ?' AQuery OK, 0 rows affected (0.00 sec)
0 P$ G! e( V! M& B. o. {0 x; _
9 X. h2 R! U; \2 y1 ~" A2 Nmysql> show binary logs;
+----------------+-----------+
" @, F8 [, ?+ k; ^$ I8 \| Log_name       | File_size |
0 t7 F9 W) `  f6 W% I! z+----------------+-----------+
7 r% I( s. T; B% L* y0 O4 z| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
' n  [0 s- N' [- d% i. O1 W| log-bin.000003 |     21390 |
4 C2 K- I' `& t9 k| log-bin.000004 |      1406 |
+ h/ `3 E" t  V+----------------+-----------+
- ]) C  A: ]+ W7 k( \5 T) ?+ u4 rows in set (0.00 sec)
, k4 q2 r* O8 S* Q+ R, U) G5 I
3 t% e  m4 ?2 f( umysql>