删除日志
1. RESET MASTER;
删除所有binlog日志,新日志编号从头开始, 之前的日志文件在操作系统上也删除掉
- mysql> reset master;
(1)查看linux
- [root@MYSQL03 mysql]# ll
- total 78004
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 4025 Aug 25 03:09 MYSQL03-slow.log
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000010
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000011
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000012
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000013
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000014
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000015
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000016
- -rw-rw----. 1 mysql mysql 107 Aug 25 03:09 mysql-bin.000017
- -rw-rw----. 1 mysql mysql 232 Aug 25 03:12 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
- mysql> reset master;
- Query OK, 0 rows affected (0.03 sec)
-
- mysql> show master status ;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 107 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- [root@MYSQL03 mysql]# ll
- total 77976
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 4025 Aug 25 03:09 MYSQL03-slow.log
- -rw-rw----. 1 mysql mysql 107 Aug 25 03:15 mysql-bin.000001
- -rw-rw----. 1 mysql mysql 29 Aug 25 03:15 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
2. PURGE MASTER LOGS TO 'mysql-bin.******';
删除mysql-bin.******之前所有日志测试步骤:
(1)执行多次flush logs
- mysql> flush logs;
- Query OK, 0 rows affected (0.03 sec)
- [root@MYSQL03 mysql]# ll
- total 78020
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000001
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000002
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000003
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000004
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000005
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000006
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000007
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
- -rw-rw----. 1 mysql mysql 107 Aug 25 03:41 mysql-bin.000011
- -rw-rw----. 1 mysql mysql 319 Aug 25 03:41 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
- mysql> show master status ;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000011 | 107 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- mysql> purge master logs to 'mysql-bin.000008';
- Query OK, 0 rows affected (0.00 sec)
日志文件 mysql-bin.000008 之前的日志已经删除掉了
- [root@MYSQL03 mysql]# ll
- total 77992
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
- -rw-rw----. 1 mysql mysql 107 Aug 25 03:41 mysql-bin.000011
- -rw-rw----. 1 mysql mysql 116 Aug 25 03:45 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
3. PURGE MASTER LOGS BEFORE '2015-08-25 05:09:21'
删除 2015-08-25 0 5:09:21之前产生的所有日志实验步骤
(1)查看数据库日志文件
- [root@MYSQL03 mysql]# ll
- total 77992
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 05:09 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
- -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
- -rw-rw----. 1 mysql mysql 665 Aug 25 05:09 mysql-bin.000011
- -rw-rw----. 1 mysql mysql 116 Aug 25 03:45 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
- mysql> insert into t1 values(6,'d');
- Query OK, 1 row affected (0.00 sec)
-
- mysql> select sysdate();
- +---------------------+
- | sysdate() |
- +---------------------+
- | 2015-08-25 05:02:10 |
- +---------------------+
- 1 row in set (0.00 sec)
-
- mysql> insert into t1 values(7,'e');
- Query OK, 1 row affected (0.00 sec)
-
- mysql> select sysdate();
- +---------------------+
- | sysdate() |
- +---------------------+
- | 2015-08-25 05:09:10 |
- +---------------------+
- 1 row in set (0.00 sec)
-
- mysql> insert into t1 values(8,'f');
- Query OK, 1 row affected (0.00 sec)
-
- mysql> select sysdate();
- +---------------------+
- | sysdate() |
- +---------------------+
- | 2015-08-25 05:09:23 |
- +---------------------+
- 1 row in set (0.00 sec)
-
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000011 | 665 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- [root@MYSQL03 mysql]# mysqlbinlog mysql-bin.000011
- /*!40019 SET @@session.max_insert_delayed_threads=0*/;
- /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
- DELIMITER /*!*/;
- # at 4
- #150825 3:41:18 server id 10 end_log_pos 107 Start: binlog v 4, server v 5.5.23-log created 150825 3:41:18
- # Warning: this binlog is either in use or was not closed properly.
- BINLOG '
- XnPbVQ8KAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
- AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
- '/*!*/;
- # at 107
- #150825 5:01:57 server id 10 end_log_pos 175 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450117/*!*/;
- SET @@session.pseudo_thread_id=1/*!*/;
- SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
- SET @@session.sql_mode=0/*!*/;
- SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
- /*!\C utf8 *//*!*/;
- SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
- SET @@session.lc_time_names=0/*!*/;
- SET @@session.collation_database=DEFAULT/*!*/;
- BEGIN
- /*!*/;
- # at 175
- #150825 5:01:57 server id 10 end_log_pos 266 Query thread_id=1 exec_time=0 error_code=0
- use test/*!*/;
- SET TIMESTAMP=1440450117/*!*/;
- insert into t1 values(6,'d')
- /*!*/;
- # at 266
- #150825 5:01:57 server id 10 end_log_pos 293 Xid = 48
- COMMIT/*!*/;
- # at 293
- #150825 5:09:09 server id 10 end_log_pos 361 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450549/*!*/;
- BEGIN
- /*!*/;
- # at 361
- #150825 5:09:09 server id 10 end_log_pos 452 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450549/*!*/;
- insert into t1 values(7,'e')
- /*!*/;
- # at 452
- #150825 5:09:09 server id 10 end_log_pos 479 Xid = 50
- COMMIT/*!*/;
- # at 479
- #150825 5:09:21 server id 10 end_log_pos 547 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450561/*!*/;
- BEGIN
- /*!*/;
- # at 547
- #150825 5:09:21 server id 10 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450561/*!*/;
- insert into t1 values(8,'f')
- /*!*/;
- # at 638
- #150825 5:09:21 server id 10 end_log_pos 665 Xid = 52
- COMMIT/*!*/;
- DELIMITER ;
- # End of log file
- ROLLBACK /* added by mysqlbinlog */;
- /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
- mysql> purge master logs before '2015-08-25 05:09:10';
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000011 | 665 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
日志文件 mysql-bin.000011 之前的日志文件已经被删除
- [root@MYSQL03 mysql]# ll
- total 77980
- -rw-r--r--. 1 mysql mysql 69206016 Aug 25 05:09 ibdata1
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile0
- -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile1
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
- -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
- -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
- -rw-rw----. 1 mysql mysql 126 Aug 25 01:24 MYSQL03-relay-bin.000001
- -rw-rw----. 1 mysql mysql 27 Aug 25 01:24 MYSQL03-relay-bin.index
- -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
- -rw-rw----. 1 mysql mysql 665 Aug 25 05:09 mysql-bin.000011
- -rw-rw----. 1 mysql mysql 29 Aug 25 05:19 mysql-bin.index
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
- drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
发现 PURGE MASTER LOGS BEFORE 只是删除了指定之间所在日志文件之前的日志文件,及 mysql-bin.000011 之前的日志文件, 对 mysql-bin.000011 并没有进行截断处理
- [root@MYSQL03 mysql]# mysqlbinlog mysql-bin.000011
- /*!40019 SET @@session.max_insert_delayed_threads=0*/;
- /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
- DELIMITER /*!*/;
- # at 4
- #150825 3:41:18 server id 10 end_log_pos 107 Start: binlog v 4, server v 5.5.23-log created 150825 3:41:18
- # Warning: this binlog is either in use or was not closed properly.
- BINLOG '
- XnPbVQ8KAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
- AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
- '/*!*/;
- # at 107
- #150825 5:01:57 server id 10 end_log_pos 175 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450117/*!*/;
- SET @@session.pseudo_thread_id=1/*!*/;
- SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
- SET @@session.sql_mode=0/*!*/;
- SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
- /*!\C utf8 *//*!*/;
- SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
- SET @@session.lc_time_names=0/*!*/;
- SET @@session.collation_database=DEFAULT/*!*/;
- BEGIN
- /*!*/;
- # at 175
- #150825 5:01:57 server id 10 end_log_pos 266 Query thread_id=1 exec_time=0 error_code=0
- use test/*!*/;
- SET TIMESTAMP=1440450117/*!*/;
- insert into t1 values(6,'d')
- /*!*/;
- # at 266
- #150825 5:01:57 server id 10 end_log_pos 293 Xid = 48
- COMMIT/*!*/;
- # at 293
- #150825 5:09:09 server id 10 end_log_pos 361 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450549/*!*/;
- BEGIN
- /*!*/;
- # at 361
- #150825 5:09:09 server id 10 end_log_pos 452 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450549/*!*/;
- insert into t1 values(7,'e')
- /*!*/;
- # at 452
- #150825 5:09:09 server id 10 end_log_pos 479 Xid = 50
- COMMIT/*!*/;
- # at 479
- #150825 5:09:21 server id 10 end_log_pos 547 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450561/*!*/;
- BEGIN
- /*!*/;
- # at 547
- #150825 5:09:21 server id 10 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1440450561/*!*/;
- insert into t1 values(8,'f')
- /*!*/;
- # at 638
- #150825 5:09:21 server id 10 end_log_pos 665 Xid = 52
- COMMIT/*!*/;
- DELIMITER ;
- # End of log file
- ROLLBACK /* added by mysqlbinlog */;
- /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
4 . 参数 expire_logs_days
通过设置参数 expire_logs_days = 7 指定删除7天以前的日志文件附录:
- mysql> help purge
- 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';
-
- mysql> purge master logs before '2015-08-25 05:09:10';
- Query OK, 0 rows affected (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1782076/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30150152/viewspace-1782076/