mysql:管理mysql的二进制日志(mysql 8.0.19)

一，从命令行查看mysql的日志文件:

[root@d1 var]# ll
total 174188296
-rw-rw---- 1 mysql mysql 1074435802 Jul  6  2019 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1074418575 Jul  6  2019 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1073741960 Jul 26  2019 mysql-bin.000003
-rw-rw---- 1 mysql mysql  977965714 Aug  2  2019 mysql-bin.000004
-rw-rw---- 1 mysql mysql        126 Aug  2  2019 mysql-bin.000005
-rw-rw---- 1 mysql mysql 1073742455 Aug  7  2019 mysql-bin.000006
-rw-rw---- 1 mysql mysql 1073742104 Aug 13  2019 mysql-bin.000007
-rw-rw---- 1 mysql mysql 1073742124 Aug 19  2019 mysql-bin.000008
-rw-rw---- 1 mysql mysql 1073742327 Sep  7  2019 mysql-bin.000009
-rw-rw---- 1 mysql mysql  638874522 Sep 18  2019 mysql-bin.000010
-rw-rw---- 1 mysql mysql     949280 Sep 18  2019 mysql-bin.000011
-rw-rw---- 1 mysql mysql 1074508569 Sep 23  2019 mysql-bin.000012
-rw-rw---- 1 mysql mysql 1073741886 Sep 26  2019 mysql-bin.000013
-rw-rw---- 1 mysql mysql 1073742102 Oct 18  2019 mysql-bin.000014
-rw-rw---- 1 mysql mysql 1073741907 Nov  8  2019 mysql-bin.000015
-rw-rw---- 1 mysql mysql 1073742207 Nov 29  2019 mysql-bin.000016
-rw-rw---- 1 mysql mysql 1073741927 Dec 20  2019 mysql-bin.000017
-rw-rw---- 1 mysql mysql 1073742057 Jan 11  2019 mysql-bin.000018
-rw-rw---- 1 mysql mysql  473988798 Jan 18  2019 mysql-bin.000019
-rw-rw---- 1 mysql mysql    2055287 Jan 18  2019 mysql-bin.000020
-rw-rw---- 1 mysql mysql      29439 Jan 18  2019 mysql-bin.000021 
...

说明：

二进制日志文件的路径在mysql的配置文件中定义：

[dbop@d1 var]$ more /etc/my.cnf
...
# BINARY LOGGING #
log-bin                        = /data/mysql/var/mysql-bin

说明：刘宏缔的架构森林是一个专注架构的博客，地址：https://www.cnblogs.com/architectforest

         对应的源码可以访问这里获取： https://github.com/liuhongdi/

说明：作者:刘宏缔 邮箱: 371125307@qq.com

二，执行清理二进制日志的sql

1，登录到mysql

[root@d1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g. 

2,查看二进制日志

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1074435802 |
| mysql-bin.000002 | 1074418575 |
| mysql-bin.000003 | 1073741960 |
| mysql-bin.000004 |  977965714 |
| mysql-bin.000005 |        126 |
| mysql-bin.000006 | 1073742455 |
| mysql-bin.000007 | 1073742104 |
| mysql-bin.000008 | 1073742124 |
| mysql-bin.000009 | 1073742327 | 
...

3,清理二进制日志

删除mysql-bin.000020之前的日志，但不包含mysql-bin.000020本身

mysql> purge binary logs to 'mysql-bin.000020';
Query OK, 0 rows affected (32.63 sec) 

4，查看purge的帮助:

mysql> help purge binary logs
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.

This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

5,其他 ：

reset master 

会删除所有的二进制日志,

所以如果有主从复制时不要使用这个处理方法

6,如何自动清理?

#使二进制日志超过14天后自动过期

expire-logs-days               = 14

三，查看mysql的版本

mysql> SHOW VARIABLES LIKE 'version';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 8.0.19-log |
+---------------+-----------------+
1 row in set (0.00 sec)

四，查看linux的版本:

[dbop@d1 var]$ more /etc/redhat-release
CentOS release 8.1 (Final)