mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 126 | | mysql-bin.000002 | 150 | | mysql-bin.000003 | 150 | | mysql-bin.000004 | 150 | | mysql-bin.000005 | 107 | +------------------+-----------+ 5 rows in set (0.00 sec)
删除列于指定日志之前的所有日志，但不包括指定的日志：
mysql> purge binary logs to 'mysql-bin.000002'; Query OK, 0 rows affected (0.10 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 150 | | mysql-bin.000003 | 150 | | mysql-bin.000004 | 150 | | mysql-bin.000005 | 107 | +------------------+-----------+ 4 rows in set (0.00 sec)
查看binlog是否开启：
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec)
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 | 368 | test,test2 | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
删除2016－08－22 16:00:00时间点之前的日志：
mysql> purge binary logs before '2016-08-22 16:00:00'; Query OK, 0 rows affected (0.16 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000007 | 368 | +------------------+-----------+ 1 row in set (0.00 sec)
注意事项：
在删除binlog日志同时，也会清理MySQL-bin.index的文件记录，清理完后命令中指定的日志文件成为第一个。
[root@DB-S data]# cat mysql-bin.index ./mysql-bin.000007
主从架构下，如果复制正在进行中，执行该命令是安全的，例如slave正在读取我们要删除的log，该语句将什么也不会做，并返回一个错误；如果复制是停止的，我们删除了一个slave还未读取的日志，则复制重新建立连接时将会失败。
指定过期天数（expire_logs_days）:该参数为全局动态调整参数，默认值为0，即关闭，取值范围0－99；
mysql> SHOW VARIABLES LIKE 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ 1 row in set (0.00 sec)
这里我设置过期天数为：一周
mysql> set global expire_logs_days = 7; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 7 | +------------------+-------+ 1 row in set (0.01 sec)
注意：在双机复制环境下，应确保过期天数不应小于从机追赶主机binlog日志的时间。
触发过期删除的条件:
1重启MYSQL；
2BINLOG文件大小达到参数max_binlog_size限制；
3手动执行命令。
重置binlog（reset master）
mysql> reset master; Query OK, 0 rows affected (0.26 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | test,test2 | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
mysql> system cat mysql-bin.index ./mysql-bin.000001
该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日制文件，并删除，过去生成的二进制日志，如下：
mysql> system ls ceshi DB-S-relay-bin.000104 DB-S-relay-bin.000108 ib_logfile0 mysql-bin.000001 sx chenlin DB-S-relay-bin.000105 DB-S-relay-bin.000109 ib_logfile1 mysql-bin.index test DB-S.err DB-S-relay-bin.000106 DB-S-relay-bin.index master.info performance_schema test2 DB-S.pid DB-S-relay-bin.000107 ibdata1 mysql relay-log.info mysql> flush logs; Query OK, 0 rows affected (0.24 sec) mysql> system ls ceshi DB-S-relay-bin.000107 ib_logfile1 relay-log.info chenlin DB-S-relay-bin.000108 master.info sx DB-S.err DB-S-relay-bin.000109 mysql test DB-S.pid DB-S-relay-bin.000110 mysql-bin.000001 test2 DB-S-relay-bin.000104 DB-S-relay-bin.index mysql-bin.000002 DB-S-relay-bin.000105 ibdata1 mysql-bin.index DB-S-relay-bin.000106 ib_logfile0 performance_schema mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 150 | | mysql-bin.000002 | 107 | +------------------+-----------+ 2 rows in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.19 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> system ls ceshi DB-S-relay-bin.000106 ibdata1 mysql-bin.index chenlin DB-S-relay-bin.000107 ib_logfile0 performance_schema DB-S.err DB-S-relay-bin.000108 ib_logfile1 relay-log.info DB-S.pid DB-S-relay-bin.000109 master.info sx DB-S-relay-bin.000104 DB-S-relay-bin.000110 mysql test DB-S-relay-bin.000105 DB-S-relay-bin.index mysql-bin.000001 test2
该语法一般只用在主从环境下初次建立复制时。 在主从复制进行过程中，该语句是无效的。
主从环境下的配置步骤：
a. 启动master和slave，开启replication(即复制)
b. 在master上运行一些测试的语句，看数据是否能够复制到 slave上面
c. 当复制运行正常的话，就在从上stop slave 然后执行 reset slave,去掉不需要的数据
d. 在master上面执行reset master 清除测试产生的数据