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

admin

show global variables like 'expire_logs_days';
: \1 G4 L+ [# E6 u+------------------+-------+
| Variable_name    | Value |
. I6 D$ e+ j6 l/ X; |, d+------------------+-------+
| expire_logs_days | 0     |
修改bin.log日志清理日期：
set global expire_logs_days=3;
mysql>  show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
$ \0 b; Y7 ^" V' z+------------------+-------+
| expire_logs_days | 3     |
9 c4 g( B+ y  D: w2 p0 U+------------------+-------+
1 row in set (0.00 sec)
5 ?1 a( \: |7 Y+ Y9 X+------------------+-------+
1 row in set (0.01 sec)
) Y- u) i. ?) X2 ]: [
! ?$ [8 W( ?" v, k6 h
手动清理bin日志
4 S" W7 p$ y% P; q2 o8 P* R9 T* T我们执行flush logs;
mysql> flush logs;
) q0 D) @4 R: w& G) |, T% Q6 DQuery OK, 0 rows affected, 64 warnings (0.16 sec
) A' I  |) q1 ^2 m如果binlog非常多，不要轻易设置改参数，有可能导致io争用，这时候可以使用purge命令予以清除:
. U/ }2 t! q) q: ?" r) d
将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';
将指定时间之前的binlog清掉:
, c) V0 l( S9 \3 w, ^* ~, P
9 O! t& X( I4 q- n5 vmysql>purge binary logs before '2017-05-01 13:09:51';
9 N! v* y; v  I) Q
. M; y' V' f" k0 a. r/ l
5 s* ~' ^  H6 d& U+ E9 i' DMariaDB [(none)]>  show global variables like 'expire_logs_days';
4 H+ I4 ?, u5 W' a6 y+------------------+-------+
| Variable_name    | Value |
9 D; n( h3 d; _% b- ?2 |' h" L+------------------+-------+
6 C! Y# r5 W, H3 [( q| expire_logs_days | 0     |
. I  x7 O1 z1 O6 j! B9 h; d+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global expire_logs_days=2;
8 z  t# |8 h  G8 RQuery OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>  show global variables like 'expire_logs_days';
0 g% W' x$ [2 o! f2 m+------------------+-------+
| Variable_name    | Value |
. v3 n2 p1 v- {9 o' m3 i+------------------+-------+
| expire_logs_days | 2     |
  C) C" |% R8 P+------------------+-------+
$ h/ }1 u* g+ A, I8 x0 S. E1 row in set (0.01 sec)
MariaDB [(none)]> set global expire_logs_days=3;
Query OK, 0 rows affected (0.00 sec)
5 O- y1 q  o' x4 ~1 XMariaDB [(none)]>  show global variables like 'expire_logs_days';
+ G7 T$ I' E0 b/ D9 \+------------------+-------+
: d+ L0 [/ D. c| Variable_name    | Value |
- @2 g' Q8 u  B$ {; ?+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)
7 `' u4 x$ \& W* `4 j' u, hMariaDB [(none)]> purge binary logs before '2021-11-18 17:00:00';
Query 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)
3 [8 N- D# x9 O4 x
  d$ y; h, |$ ^4 u0 S/ @
  E& i1 W8 J$ Z4 H5 `( u. v

admin

[mysqld]
port = 3306
; \5 ~" \( p5 f9 @: X$ P! Fsocket=/tmp/mysql.sock
  c& m) L1 P1 }- G2 uuser=mysql
#log-bin=mysql-bin
: ?1 e2 ^/ G* W+ B" ylog-bin=log-bin.log   设置binlog日志
#skip-grant-tables
( [- m+ a0 x7 f3 u* v# j; ~expire_logs_days=3   设置清理binlog日志时间
service mysqld restart
1 }6 I6 I; X0 s: oShutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

' x  B  @6 n* _& l

admin

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
, N* W; _  Z( q) P( \% s7 U: `| expire_logs_days | 3     |
! x" N6 l  X( A7 K4 i+------------------+-------+
1 row in set (0.00 sec)
5 q$ y9 Q1 A. U; S! l
mysql> flush logs;
; z1 C9 Q4 s8 {( C5 e5 }0 |Query OK, 0 rows affected (0.01 sec)
3 K8 o* ~- n! |1 V5 j
, d  A0 T  F6 G  T, m/ umysql> show binary logs;
+----------------+-----------+
2 B+ u" Y$ ~3 o) g6 y7 V/ x| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
| log-bin.000003 |      9780 |
# ?7 z, a. K- \5 @  s+----------------+-----------+
3 rows in set (0.00 sec)
7 q: Y1 y/ _/ Z- b) F: H( W
/ X$ a, R) H, |+ A, P# ^4 x% Lmysql> flush logs;
+ e; H, x6 H: m, fQuery OK, 0 rows affected (0.00 sec)
* U" U- z0 d) F' f: \4 E
" {3 L: T3 a. E; _3 m" R5 l( H: Lmysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
" x$ e$ r1 j- k" m+----------------+-----------+
5 g" t) N" a' _& v) Z, v) o| log-bin.000001 |     56932 |
| log-bin.000002 |    116186 |
6 ~; c8 E5 ]) A6 f& w3 ~| log-bin.000003 |     21390 |
9 R6 W+ g1 n' F, x: E3 ?; O| log-bin.000004 |      1406 |
+----------------+-----------+
4 rows in set (0.00 sec)
0 Z# G- d; H9 }" l( n9 y" Z" K3 ?
; M( Y0 `8 {1 x  s) f. `mysql>