MySQL安全删除(清理)Binlog日志 原创 数据库 2022年5月14日 19:21 夏至未至 716 当前内容 4752 字，在路上，马上到，马上到 ### 目录 [TOC] ### 单机清理分类 #### 自动清理 自动清理，即定时清理，配置 `Binlog` 日志过期时间，日志过期后，自动删除。在数据库配置文件增加如下配置即可： expire_logs_days = 15 单位是天，即日志保留天数，如上配置，自动删除15天前的日志，默认值为0，表示从不删除。 #### 手动清理 以下命令均需要连接数据库执行命令： 1. 删除所有binlog日志，新日志编号从头开始 RESET MASTER; 2. 删除 `mysql-bin.000011` 之前所有日志 PURGE MASTER LOGS TO 'mysql-bin.000011'; 3. 删除 2022-04-02 22:46:26 之前产生的所有日志 PURGE MASTER LOGS BEFORE '2022-04-02 22:46:26'; 最后想说，尽量不要去数据库目录，手动 `rm -rf` 直接删除 `binlog`，尽管能删除物理文件，但是对于数据库来说，逻辑上还是存在的，有记录，通过 `show binary logs;`还是能查询到已经被删除的日志，并且日志大小为0 ### 集群清理方法 如果是集群，例如主备集群，通过 `binlog` 日志在进行同步数据这种情况，删除日志需要慎重，一旦删除错误，会导致主备集群同步关系不正常，如下说如何删： 首先就是要在从节点上查询，正在从主节点往备节点上同步的是哪个` binlog`，如果有多个备节点，每个节点都要查询，命令如下： mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.18.16.22 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000320 Read_Master_Log_Pos: 555176471 Relay_Log_File: mysqld-relay-bin.004136 Relay_Log_Pos: 502564 Relay_Master_Log_File: mysql-bin.010327 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: blog Replicate_Ignore_DB: Replicate_Do_Table: blog.archives Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 555176471 Relay_Log_Space: 3642164873 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1042 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1622 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 其中 `Master_Log_File` 表示当前I/O线程当前正在读取主服务器二进制日志文件的名称，上边是 `mysql-bin.000320`，假设有多个从，这个值可能是 `mysql-bin.000318`，那么在主节点上清理日志的时候，就得清理 `mysql-bin.000318` 之前的日志，如下命令： PURGE MASTER LOGS TO 'mysql-bin.000318'; ### 关于命令 更多关于 PURGE, 可以看其帮助信息： MySQL [(none)]> 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 https://dev.mysql.com/doc/refman/8.0/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. PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has no effect if the server was not started with the --log-bin option to enable binary logging. URL: https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'; MySQL [(none)]> MySQL [(none)]> MySQL [(none)]> help PURGE MASTER LOGS; Name: 'PURGE MASTER 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 https://dev.mysql.com/doc/refman/8.0/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. PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has no effect if the server was not started with the --log-bin option to enable binary logging. URL: https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'; MySQL [(none)]> 本文标题： MySQL安全删除(清理)Binlog日志 本文作者： 夏至未至 发布时间： 2022年5月14日 19:21 最近更新： 2022年5月14日 19:22 原文链接： 许可协议： 署名-非商业性-禁止演绎 4.0 国际(CC BY-NC-ND 4.0) 请按协议转载并保留原文链接及作者 MySQL(27) 上一个 Borland C++ Builder 6.0 企业集成版安装包下载 下一个 MySQL最有效关闭Binlog方式 当前文章评论暂未开放,请移步至留言处留言。
