MySQL 自动清理binlog日志的方法

2024-03-10 21:59:02 69

说明：

开启MySQLbinlog日志的服务器，如果不设置自动清理日志，默认binlog日志一直保留着，时间一长，服务器磁盘空间被binlog日志占满，导致MySQL数据库出错。

使用下面方法可以安全清理binlog日志

一、没有主从同步的情况下清理日志

mysql-uroot-p123456-e'PURGEMASTERLOGSBEFOREDATE_SUB(NOW(),INTERVAL5DAY)';

#mysql定时清理5天前的binlog

mysql-uroot-p #进入mysql控制台

resetmaster; #重置binlog

二、MySQL主从同步下安全清理binlog日志

1、mysql -uroot-p #进入从服务器mysql控制台
showslavestatus\G; #检查从服务器正在读取哪个日志，有多个从服务器，选择时间最早的一个做为目标日志。
2、进入主服务器mysql控制台

showmasterlog; #获得主服务器上的一系列日志
PURGEMASTERLOGSTO'binlog.000058'; #删除binlog.000005之前的，不包括binlog.000058
PURGEMASTERLOGSBEFORE'2016-06-2213:00:00'; #清除2016-06-2213:00:00前binlog日志
PURGEMASTERLOGSBEFOREDATE_SUB(NOW(),INTERVAL3DAY); #清除3天前binlog日志

三、设置自动清理MySQLbinlog日志

vi /etc/my.cnf #编辑配置

expire_logs_days=15#自动删除15天前的日志。默认值为0，表示从不删除。
log-bin=mysql-bin#注释掉之后，会关闭binlog日志
binlog_format=mixed#注释掉之后，会关闭binlog日志

:wq! #保存退出

扩展阅读：

mysql>helppurge;

Name:'PURGEBINARYLOGS'

Description:

Syntax:

PURGE{BINARY|MASTER}LOGS

{TO'log_name'|BEFOREdatetime_expr}

Thebinarylogisasetoffilesthatcontaininformationaboutdata

modificationsmadebytheMySQLserver.Thelogconsistsofasetof

binarylogfiles,plusanindexfile(see

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html).

ThePURGEBINARYLOGSstatementdeletesallthebinarylogfileslisted

inthelogindexfilepriortothespecifiedlogfilenameordate.

BINARYandMASTERaresynonyms.Deletedlogfilesalsoareremovedfrom

thelistrecordedintheindexfile,sothatthegivenlogfilebecomes

thefirstinthelist.

Thisstatementhasnoeffectiftheserverwasnotstartedwiththe

--log-binoptiontoenablebinarylogging.

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

Examples:

PURGEBINARYLOGSTO'mysql-bin.010';

PURGEBINARYLOGSBEFORE'2008-04-0222:46:26';

下面是其它网友给出的方法，大家可以参考一下

MYSQL主从复制(replication)采用RBR模式后，binlog的格式为"ROW"，能解决很多原先出现的主键重复问题。
在一个繁忙的masterdbserver上，binlog日志文件增长速度很快，如果不定时清除，硬盘空间很快就会被充满。
设置自动清理mysqlbinlog日志，配置my.cnf：

expire_logs_days=10

在运行时修改：

showbinarylogs;
showvariableslike'%log%';
setglobalexpire_logs_days=10;

清除之前可以采用相应的备份策略。

手动删除10天前的MySQLbinlog日志：

PURGEMASTERLOGSBEFOREDATE_SUB(CURRENT_DATE,INTERVAL10DAY);
showmasterlogs;

MASTER和BINARY是同义词。

一般情况下，推荐使用MIXEDbinlog的复制。http://dev.mysql.com/doc/refman/5.1/en/open-bugs-general.html中的说明:Replicationusesquery-levellogging:Themasterwritestheexecutedqueriestothebinarylog.Thisisaveryfast,compact,andefficientloggingmethodthatworksperfectlyinmostcases.

附：关于MYSQL复制的几种模式

从MySQL5.1.12开始，可以用以下三种模式来实现：
–基于SQL语句的复制(statement-basedreplication,SBR)，
–基于行的复制(row-basedreplication,RBR)，
–混合模式复制(mixed-basedreplication,MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。MBR模式中，SBR模式是默认的。

在运行时可以动态改动binlog的格式，除了以下几种情况：
.存储流程或者触发器中间
.启用了NDB
.当前会话试用RBR模式，并且已打开了临时表

如果binlog采用了MIXED模式，那么在以下几种情况下会自动将binlog的模式由SBR模式改成RBR模式。
.当DML语句更新一个NDB表时
.当函数中包含UUID()时
.2个及以上包含AUTO_INCREMENT字段的表被更新时
.行任何INSERTDELAYED语句时
.用UDF时
.视图中必须要求运用RBR时，例如建立视图是运用了UUID()函数

设定主从复制模式：
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"

也可以在运行时动态修改binlog的格式。例如
mysql>SETSESSIONbinlog_format='STATEMENT';
mysql>SETSESSIONbinlog_format='ROW';
mysql>SETSESSIONbinlog_format='MIXED';
mysql>SETGLOBALbinlog_format='STATEMENT';
mysql>SETGLOBALbinlog_format='ROW';
mysql>SETGLOBALbinlog_format='MIXED';

两种模式各自的优缺点：

SBR的优点：
历史悠久，技能成熟
binlog文件较小
binlog中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况
binlog可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR的缺点：
不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。
调用具有不确定因素的UDF时复制也可能出疑问
运用以下函数的语句也不能被复制：
*LOAD_FILE()
*UUID()
*USER()
*FOUND_ROWS()
*SYSDATE()(除非启动时启用了–sysdate-is-now选项)
INSERT…SELECT会产生比RBR更多的行级锁
复制须要执行全表扫描(WHERE语句中没有运用到索引)的UPDATE时，须要比RBR请求更多的行级锁
对于有AUTO_INCREMENT字段的InnoDB表而言，INSERT语句会阻塞其他INSERT语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而RBR模式下，只会对那个发生变化的记录产生影响
存储函数(不是存储流程)在被调用的同时也会执行一次NOW()函数，这个可以说是坏事也可能是好事
确定了的UDF也须要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源

RBR的优点：
任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技能一样
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：
*INSERT…SELECT
*包含AUTO_INCREMENT字段的INSERT
*没有附带条件或者并没有修改很多记录的UPDATE或DELETE语句
执行INSERT，UPDATE，DELETE语句时锁更少
从服务器上采用多线程来执行复制成为可能
RBR的缺点：
binlog大了很多
复杂的回滚时binlog中会包含大量的数据
主服务器上执行UPDATE语句时，所有发生变化的记录都会写到binlog中，而SBR只会写一次，这会导致频繁发生binlog的并发写疑问
UDF产生的大BLOB值会导致复制变慢
不能从binlog中看到都复制了写什么语句(加密过的)
当在非事务表上执行一段堆积的SQL语句时，最好采用SBR模式，否则很容易导致主从服务器的数据不一致情况发生
另外，针对系统库mysql里面的表发生变化时的处理准则如下：
如果是采用INSERT，UPDATE，DELETE直接操作表的情况，则日志格式根据binlog_format的设定而记录
如果是采用GRANT，REVOKE，SETPASSWORD等管理语句来做的话，那么无论如何都采用SBR模式记录。
注：采用RBR模式后，能处理很多原先出现的主键重复问题。实例:
对于insertintodb_allot_idsselect*fromdb_allot_ids这个语句:
在BINLOG_FORMAT=STATEMENT模式下:
BINLOG日志信息为:
—————————————–
BEGIN
/*!*/;
#at173
#09061216:05:42serverid1end_log_pos288Querythread_id=4exec_time=0error_code=0
SETTIMESTAMP=1244793942/*!*/;
insertintodb_allot_idsselect*fromdb_allot_ids
/*!*/;
—————————————–

在BINLOG_FORMAT=ROW模式下:
BINLOG日志信息为:
—————————————–
BINLOG'
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=
'/*!*/;
—————————————–

3. 查询结果(现在只有一条记录了.)

mysql>showbinlogevents\G
***************************1.row***************************
Log_name:ablelee.000003
Pos:4
Event_type:Format_desc
Server_id:1
End_log_pos:106
Info:Serverver:5.1.26-rc-log,Binlogver:4
1rowinset(0.01sec)
(ablelee.000001和ablelee.000002已被删除)
mysql>showbinarylogs;
+----------------+-----------+
|Log_name |File_size|
+----------------+-----------+
|ablelee.000003| 106|
+----------------+-----------+
1rowinset(0.00sec)

(删除的其它格式运用!)
PURGE{MASTER|BINARY}LOGSTO'log_name'
PURGE{MASTER|BINARY}LOGSBEFORE'date'
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
例如：
PURGEMASTERLOGSTO'mysql-bin.010';
PURGEMASTERLOGSBEFORE'2008-06-2213:00:00';
清除3天前的binlog
PURGEMASTERLOGSBEFOREDATE_SUB(NOW(),INTERVAL3DAY);
BEFORE变量的date自变量可以为'YYYY-MM-DDhh:mm:ss'格式。MASTER和BINARY是同义词。
如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
要清理日志，需按照以下步骤：
1.在每个从属服务器上，使用SHOWSLAVESTATUS来检查它正在读取哪个日志。
2.使用SHOWMASTERLOGS获得主服务器上的一系列日志。
3.在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。

4.制作您将要删除的所有日志的备份。（这个步骤是自选的，但是建议采用。）

5.清理所有的日志，但是不包括目标日志

MySQL 自动清理binlog日志的方法

热门推荐

随机推荐