ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > mysql日志删除

mysql日志删除

原创 MySQL 作者：warren20 时间：2015-08-25 13:54:46 0 删除 编辑

删除日志

1.  RESET MASTER;              

  删除所有binlog日志，新日志编号从头开始,  之前的日志文件在操作系统上也删除掉

  1. mysql> reset master;
测试步骤：
（1）查看linux
  1. [root@MYSQL03 mysql]# ll
  2. total 78004
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 4025 Aug 25 03:09 MYSQL03-slow.log
  10. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000010
  11. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000011
  12. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000012
  13. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000013
  14. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000014
  15. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000015
  16. -rw-rw----. 1 mysql mysql 150 Aug 25 03:09 mysql-bin.000016
  17. -rw-rw----. 1 mysql mysql 107 Aug 25 03:09 mysql-bin.000017
  18. -rw-rw----. 1 mysql mysql 232 Aug 25 03:12 mysql-bin.index
  19. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  20. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
（2）执行reset master
  1. mysql> reset master;
  2. Query OK, 0 rows affected (0.03 sec)

  4. mysql> show master status ;
  5. +------------------+----------+--------------+------------------+
  6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  7. +------------------+----------+--------------+------------------+
  8. | mysql-bin.000001 | 107      |              |                  |
  9. +------------------+----------+--------------+------------------+
  10. 1 row in set (0.00 sec)
（3）查看文件
  1. [root@MYSQL03 mysql]# ll
  2. total 77976
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 4025 Aug 25 03:09 MYSQL03-slow.log
  10. -rw-rw----. 1 mysql mysql 107 Aug 25 03:15 mysql-bin.000001
  11. -rw-rw----. 1 mysql mysql 29 Aug 25 03:15 mysql-bin.index
  12. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  13. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test

2. PURGE MASTER LOGS TO 'mysql-bin.******';

删除mysql-bin.******之前所有日志
测试步骤：
（1）执行多次flush logs
  1. mysql> flush logs;
  2. Query OK, 0 rows affected (0.03 sec)
（2）查看日志文件
  1. [root@MYSQL03 mysql]# ll
  2. total 78020
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
  10. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
  11. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000001
  12. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000002
  13. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000003
  14. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000004
  15. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000005
  16. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000006
  17. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000007
  18. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
  19. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
  20. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
  21. -rw-rw----. 1 mysql mysql 107 Aug 25 03:41 mysql-bin.000011
  22. -rw-rw----. 1 mysql mysql 319 Aug 25 03:41 mysql-bin.index
  23. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  24. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
（3）删除 mysql-bin.000008 之前的日志
  1. mysql> show master status ;
  2. +------------------+----------+--------------+------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000011 | 107      |              |                  |
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> purge master logs to 'mysql-bin.000008';
  10. Query OK, 0 rows affected (0.00 sec)
（4）查看操作系统文件
    日志文件 mysql-bin.000008 之前的日志已经删除掉了
  1. [root@MYSQL03 mysql]# ll
  2. total 77992
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 01:32 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 01:32 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
  10. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
  11. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
  12. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
  13. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
  14. -rw-rw----. 1 mysql mysql 107 Aug 25 03:41 mysql-bin.000011
  15. -rw-rw----. 1 mysql mysql 116 Aug 25 03:45 mysql-bin.index
  16. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  17. 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 05:09:21之前产生的所有日志
实验步骤
（1）查看数据库日志文件
  1. [root@MYSQL03 mysql]# ll
  2. total 77992
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 05:09 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
  10. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
  11. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000008
  12. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000009
  13. -rw-rw----. 1 mysql mysql 150 Aug 25 03:41 mysql-bin.000010
  14. -rw-rw----. 1 mysql mysql 665 Aug 25 05:09 mysql-bin.000011
  15. -rw-rw----. 1 mysql mysql 116 Aug 25 03:45 mysql-bin.index
  16. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  17. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
（2）根据时间插入相关的数据
  1. mysql> insert into t1 values(6,'d');
  2. Query OK, 1 row affected (0.00 sec)

  4. mysql> select sysdate();
  5. +---------------------+
  6. | sysdate() |
  7. +---------------------+
  8. | 2015-08-25 05:02:10 |
  9. +---------------------+
  10. 1 row in set (0.00 sec)

  12. mysql> insert into t1 values(7,'e');
  13. Query OK, 1 row affected (0.00 sec)

  15. mysql> select sysdate();
  16. +---------------------+
  17. | sysdate() |
  18. +---------------------+
  19. | 2015-08-25 05:09:10 |
  20. +---------------------+
  21. 1 row in set (0.00 sec)

  23. mysql> insert into t1 values(8,'f');
  24. Query OK, 1 row affected (0.00 sec)

  26. mysql> select sysdate();
  27. +---------------------+
  28. | sysdate() |
  29. +---------------------+
  30. | 2015-08-25 05:09:23 |
  31. +---------------------+
  32. 1 row in set (0.00 sec)

  34. mysql> show master status;
  35. +------------------+----------+--------------+------------------+
  36. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  37. +------------------+----------+--------------+------------------+
  38. | mysql-bin.000011 | 665      |              |                  |
  39. +------------------+----------+--------------+------------------+
  40. 1 row in set (0.00 sec)
（3）解析日志文件 mysql-bin.000011
  1. [root@MYSQL03 mysql]# mysqlbinlog mysql-bin.000011
  2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #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
  7. # Warning: this binlog is either in use or was not closed properly.
  8. BINLOG '
  9. XnPbVQ8KAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  10. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
  11. '/*!*/;
  12. # at 107
  13. #150825 5:01:57 server id 10 end_log_pos 175     Query    thread_id=1    exec_time=0    error_code=0
  14. SET TIMESTAMP=1440450117/*!*/;
  15. SET @@session.pseudo_thread_id=1/*!*/;
  16. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  17. SET @@session.sql_mode=0/*!*/;
  18. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  19. /*!\C utf8 *//*!*/;
  20. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  21. SET @@session.lc_time_names=0/*!*/;
  22. SET @@session.collation_database=DEFAULT/*!*/;
  23. BEGIN
  24. /*!*/;
  25. # at 175
  26. #150825 5:01:57 server id 10 end_log_pos 266     Query    thread_id=1    exec_time=0    error_code=0
  27. use test/*!*/;
  28. SET TIMESTAMP=1440450117/*!*/;
  29. insert into t1 values(6,'d')
  30. /*!*/;
  31. # at 266
  32. #150825 5:01:57 server id 10 end_log_pos 293     Xid = 48
  33. COMMIT/*!*/;
  34. # at 293
  35. #150825 5:09:09 server id 10 end_log_pos 361     Query    thread_id=1    exec_time=0    error_code=0
  36. SET TIMESTAMP=1440450549/*!*/;
  37. BEGIN
  38. /*!*/;
  39. # at 361
  40. #150825 5:09:09 server id 10 end_log_pos 452     Query    thread_id=1    exec_time=0    error_code=0
  41. SET TIMESTAMP=1440450549/*!*/;
  42. insert into t1 values(7,'e')
  43. /*!*/;
  44. # at 452
  45. #150825 5:09:09 server id 10 end_log_pos 479     Xid = 50
  46. COMMIT/*!*/;
  47. # at 479
  48. #150825 5:09:21 server id 10 end_log_pos 547     Query    thread_id=1    exec_time=0    error_code=0
  49. SET TIMESTAMP=1440450561/*!*/;
  50. BEGIN
  51. /*!*/;
  52. # at 547
  53. #150825 5:09:21 server id 10 end_log_pos 638     Query    thread_id=1    exec_time=0    error_code=0
  54. SET TIMESTAMP=1440450561/*!*/;
  55. insert into t1 values(8,'f')
  56. /*!*/;
  57. # at 638
  58. #150825 5:09:21 server id 10 end_log_pos 665     Xid = 52
  59. COMMIT/*!*/;
  60. DELIMITER ;
  61. # End of log file
  62. ROLLBACK /* added by mysqlbinlog */;
  63. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
（4）删除 2015-08-25 05:09:10 之前的日志，即保留到语句 insert into t1 values(8,'f') 之后的日志
  1. mysql> purge master logs before '2015-08-25 05:09:10';
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> show master status;
  5. +------------------+----------+--------------+------------------+
  6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  7. +------------------+----------+--------------+------------------+
  8. | mysql-bin.000011 | 665 | | |
  9. +------------------+----------+--------------+------------------+
  10. 1 row in set (0.00 sec)
（5）查看数据库日志文件
    日志文件 mysql-bin.000011 之前的日志文件已经被删除
  1. [root@MYSQL03 mysql]# ll
  2. total 77980
  3. -rw-r--r--. 1 mysql mysql 69206016 Aug 25 05:09 ibdata1
  4. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile0
  5. -rw-r--r--. 1 mysql mysql 5242880 Aug 25 05:09 ib_logfile1
  6. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql
  7. -rw-rw----. 1 mysql mysql 9772 Aug 25 03:11 MYSQL03.err
  8. -rw-rw----. 1 mysql mysql 5 Aug 25 01:32 MYSQL03.pid
  9. -rw-rw----. 1 mysql mysql 126 Aug 25 01:24 MYSQL03-relay-bin.000001
  10. -rw-rw----. 1 mysql mysql 27 Aug 25 01:24 MYSQL03-relay-bin.index
  11. -rw-rw----. 1 mysql mysql 5775 Aug 25 03:41 MYSQL03-slow.log
  12. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 mysql_bin
  13. -rw-rw----. 1 mysql mysql 665 Aug 25 05:09 mysql-bin.000011
  14. -rw-rw----. 1 mysql mysql 29 Aug 25 05:19 mysql-bin.index
  15. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:37 performance_schema
  16. drwxr-xr-x. 2 mysql mysql 4096 Aug 24 08:46 test
（5）再次解析日志文件 mysql-bin.000011
    发现 PURGE MASTER LOGS BEFORE 只是删除了指定之间所在日志文件之前的日志文件，及 mysql-bin.000011 之前的日志文件， 对 mysql-bin.000011 并没有进行截断处理
  1. [root@MYSQL03 mysql]# mysqlbinlog mysql-bin.000011
  2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #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
  7. # Warning: this binlog is either in use or was not closed properly.
  8. BINLOG '
  9. XnPbVQ8KAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  10. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
  11. '/*!*/;
  12. # at 107
  13. #150825 5:01:57 server id 10 end_log_pos 175     Query    thread_id=1    exec_time=0    error_code=0
  14. SET TIMESTAMP=1440450117/*!*/;
  15. SET @@session.pseudo_thread_id=1/*!*/;
  16. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  17. SET @@session.sql_mode=0/*!*/;
  18. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  19. /*!\C utf8 *//*!*/;
  20. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  21. SET @@session.lc_time_names=0/*!*/;
  22. SET @@session.collation_database=DEFAULT/*!*/;
  23. BEGIN
  24. /*!*/;
  25. # at 175
  26. #150825 5:01:57 server id 10 end_log_pos 266     Query    thread_id=1    exec_time=0    error_code=0
  27. use test/*!*/;
  28. SET TIMESTAMP=1440450117/*!*/;
  29. insert into t1 values(6,'d')
  30. /*!*/;
  31. # at 266
  32. #150825 5:01:57 server id 10 end_log_pos 293     Xid = 48
  33. COMMIT/*!*/;
  34. # at 293
  35. #150825 5:09:09 server id 10 end_log_pos 361     Query    thread_id=1    exec_time=0    error_code=0
  36. SET TIMESTAMP=1440450549/*!*/;
  37. BEGIN
  38. /*!*/;
  39. # at 361
  40. #150825 5:09:09 server id 10 end_log_pos 452     Query    thread_id=1    exec_time=0    error_code=0
  41. SET TIMESTAMP=1440450549/*!*/;
  42. insert into t1 values(7,'e')
  43. /*!*/;
  44. # at 452
  45. #150825 5:09:09 server id 10 end_log_pos 479     Xid = 50
  46. COMMIT/*!*/;
  47. # at 479
  48. #150825 5:09:21 server id 10 end_log_pos 547     Query    thread_id=1    exec_time=0    error_code=0
  49. SET TIMESTAMP=1440450561/*!*/;
  50. BEGIN
  51. /*!*/;
  52. # at 547
  53. #150825 5:09:21 server id 10 end_log_pos 638     Query    thread_id=1    exec_time=0    error_code=0
  54. SET TIMESTAMP=1440450561/*!*/;
  55. insert into t1 values(8,'f')
  56. /*!*/;
  57. # at 638
  58. #150825 5:09:21 server id 10 end_log_pos 665     Xid = 52
  59. COMMIT/*!*/;
  60. DELIMITER ;
  61. # End of log file
  62. ROLLBACK /* added by mysqlbinlog */;
  63. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

4 . 参数 expire_logs_days

通过设置参数 expire_logs_days = 7  指定删除7天以前的日志文件


附录：
  1. mysql> help purge
  2. Name: 'PURGE BINARY LOGS'
  3. Description:
  4. Syntax:
  5. PURGE { BINARY | MASTER } LOGS
  6.     { TO 'log_name' | BEFORE datetime_expr }

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

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

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

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

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

  28. mysql> purge master logs before '2015-08-25 05:09:10';
  29. Query OK, 0 rows affected (0.00 sec)



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30150152/viewspace-1782076/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： mysql常用参数
下一篇： 源码安装mysql错误
请登录后发表评论 登录
全部评论
warren20

注册时间：2015-03-03

  • 博文量
    56
  • 访问量
    376702

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593