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

admin

show global variables like 'expire_logs_days';
4 H9 ?& c, K1 d1 t/ G+------------------+-------+
! `. T8 C/ [- D+ ]- F* j| Variable_name    | Value |
5 F7 K' G8 @4 G# S$ B% N& {+------------------+-------+
| expire_logs_days | 0     |
6 a2 i% c- ~) E0 k, P( r( F修改bin.log日志清理日期：
6 @4 y1 A$ `9 v+ d* ^ set global expire_logs_days=3;
mysql>  show global variables like 'expire_logs_days';
2 Y6 K# E  V2 n% t2 z3 i9 I  w' a+------------------+-------+
# P( @& P9 e$ }7 k+ W| Variable_name    | Value |
* ^$ g# w8 c% m% v, M/ Y$ Z+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
9 W3 V% u" |8 W* w; W1 row in set (0.00 sec)
+------------------+-------+
) D9 Q( @: W- s: y1 a1 row in set (0.01 sec)
2 M3 I) W( I$ F

3 Y' D+ J6 T3 b+ x: m" |; k" n$ c手动清理bin日志
我们执行flush logs;
mysql> flush logs;
8 e2 E: k8 c' ZQuery OK, 0 rows affected, 64 warnings (0.16 sec
如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:

将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';
/ f0 b1 A% h2 e5 e0 G将指定时间之前的binlog清掉:

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

6 ]* m4 ~6 r) k, [( A
$ b% q$ c8 a( {MariaDB [(none)]>  show global variables like 'expire_logs_days';
  w- F# O$ b" X1 y' g+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
/ `! x% z( h/ U3 P3 P| expire_logs_days | 0     |
+ Z0 n. v0 C+ V( x( Z" x: a) K+------------------+-------+
1 row in set (0.00 sec)
) f0 |! N; U& e0 y7 oMariaDB [(none)]> set global expire_logs_days=2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
$ ?8 Z, S- J" l. j+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 2     |
+------------------+-------+
. A6 V2 H7 X1 |4 ?1 row in set (0.01 sec)
MariaDB [(none)]> set global expire_logs_days=3;
4 L1 A' j& |9 w8 D8 ^Query OK, 0 rows affected (0.00 sec)
$ x; I1 W% s  e. B6 ~6 T% g5 w, CMariaDB [(none)]>  show global variables like 'expire_logs_days';
+------------------+-------+
* I3 Q' h, L, I8 h7 G| Variable_name    | Value |
+------------------+-------+
' g# f& t) k! \8 ^| expire_logs_days | 3     |
5 u1 ~! o7 Q" T- @% w; D- f) |8 U# }+------------------+-------+
$ Y9 m( h) x0 O( Y1 row in set (0.00 sec)
$ ^8 ?1 I& F! L7 b0 Z$ U& _% OMariaDB [(none)]> flush logs;
2 n4 i! \: ?# |$ B- TQuery OK, 0 rows affected (0.02 sec)
7 g. p5 h5 G8 ^& i5 V" H4 A9 y1 vMariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
* E4 x% h- J. \$ e" eQuery 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)
1 w- M* c4 k' ~; Z- k' T
6 s1 h) L1 H) V1 X3 ?+ ^5 i, H

admin

[mysqld]
port = 3306
$ Y) E% l7 q5 p% C/ V# \, P2 U' Tsocket=/tmp/mysql.sock
. P# ~6 `  u2 b* Z" suser=mysql
#log-bin=mysql-bin
log-bin=log-bin.log   设置binlog日志
#skip-grant-tables
- Z7 J2 b6 S2 g- nexpire_logs_days=3   设置清理binlog日志时间
service mysqld restart
, S3 m; k. n4 m! F( z  `Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
9 E2 c8 C9 v, n+ z+------------------+-------+
5 y: y& ~8 s& H| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
8 z  ^8 J9 B' D- t: c
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

( F0 T+ |+ ~5 k( g, Emysql> show binary logs;
0 V$ L# }8 D( B9 V  {1 e+----------------+-----------+
8 f7 g8 w2 z6 N; R9 L| Log_name       | File_size |
+----------------+-----------+
& e# ^) W6 B5 U$ v| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
| log-bin.000003 |      9780 |
+----------------+-----------+
: p+ x' a  l+ O' y" T3 R0 ]6 h+ f3 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
  K+ e7 a- ^1 N2 a2 i
mysql> show binary logs;
* ]' Y4 M! M8 P# ~. O% V+----------------+-----------+
* v9 L+ h9 s. `* [: p| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     56932 |
) Y' }7 p4 [% K/ c" Q2 j. i8 l| log-bin.000002 |    116186 |
| log-bin.000003 |     21390 |
| log-bin.000004 |      1406 |
6 V' W0 E& n) L. e7 Q. K1 {+----------------+-----------+
( S. _' _" G$ [4 rows in set (0.00 sec)
) T8 s( T6 ~4 K& B0 g# u+ ?
mysql>