mysql中binlog是二进制文件了,下面我们就来为各位介绍mysql中安全删除binlog日志、使用binlog恢复数据了,希望例子可以帮助到各位朋友哦.

在数据库正常使用的时候,默认binlog会一直放在一个文件中(大约1.1G),因此导致一个文件非常大,因此在必要的时候可能需要删除一些binlog日志文件.

删除日志文件:

方式一:

[root@Master-Mysql data]# grep -E "expire_logs_days" /etc/my.cnf

expire_logs_days = 7 #删除7天前的binlog记录

方式二:

1. mysql> RESET MASTER; #重置binlog
2. mysql> PURGE MASTER LOGS TO 'mysql-bin.000003'; #删除mysql-bin.000003之前的日志
3. mysql> PURGE MASTER LOGS BEFORE '2014-07-16 15:07:00'; #删除2014-07-16 15:07:00之前的binlog日志
4. mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

恢复日志文件:

1. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e 'SHOW BINLOG EVENTS ＼G'
2. Enter password:
3. *************************** 1. row ***************************
4. [root@Master-Mysql ~]# /etc/init.d/mysqld restart
5. [root@Master-Mysql ~]# ll /usr/local/mysql/data/
6. -rw-rw----. 1 mysql mysql 120 Jul 18 21:01 mysql-bin.000316 #重新启动生成的binlog
7. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot
8. mysql> create database hahaha;
9. mysql> use hahaha;
10. mysql> create table test(id int auto_increment not null primary key,
11. -> val int,data varchar(20));
12. mysql> insert into test(val,data) values(10,'liang');
13. mysql> insert into test(val,data) values(20,'jia');
14. mysql> insert into test(val,data) values(30,'hui');
15. mysql> show tables;
16. +------------------+
17. | Tables_in_hahaha |
18. +------------------+
19. | test |
20. +------------------+
21. 1 row in set (0.00 sec)
22. mysql> select * from test;
23. +----+------+-------+
24. | id | val | data |
25. +----+------+-------+
26. | 1 | 10 | liang |
27. | 3 | 20 | jia |
28. | 5 | 30 | hui |
29. +----+------+-------+
30. mysql> flush logs; #mysql-bin.000317
31. [root@Master-Mysql ~]# ll /usr/local/mysql/data/
32. -rw-rw----. 1 mysql mysql 120 Jul 18 21:08 mysql-bin.000317 #刷新binlog新产生的日志，其他依次类推；
33. mysql> insert into test(val,data) values(40,'aaa');
34. mysql> insert into test(val,data) values(50,'bbb');
35. mysql> insert into test(val,data) values(60,'ccc');
36. mysql> select * from test;
37. +----+------+-------+
38. | id | val | data |
39. +----+------+-------+
40. | 1 | 10 | liang |
41. | 3 | 20 | jia |
42. | 5 | 30 | hui |
43. | 7 | 40 | aaa |
44. | 9 | 50 | bbb |
45. | 11 | 60 | ccc |
46. +----+------+-------+
47. mysql> delete from test where id between 4 and 5;
48. mysql> select * from test;
49. +----+------+-------+
50. | id | val | data |
51. +----+------+-------+
52. | 1 | 10 | liang |
53. | 3 | 20 | jia |
54. | 7 | 40 | aaa |
55. | 9 | 50 | bbb |
56. | 11 | 60 | ccc |
57. +----+------+-------+
58. mysql> insert into test(val,data) values(70,'ddd');
59. mysql> select * from test;
60. +----+------+-------+
61. | id | val | data |
62. +----+------+-------+
63. | 1 | 10 | liang |
64. | 3 | 20 | jia |
65. | 7 | 40 | aaa |
66. | 9 | 50 | bbb |
67. | 11 | 60 | ccc |
68. | 13 | 70 | ddd |
69. +----+------+-------+
70. mysql> flush logs; #mysql-bin.000318
71. mysql> insert into test(val,data) values(80,'dddd');
72. mysql> insert into test(val,data) values(90,'eeee');
73. mysql> flush logs; #mysql-bin.000319
74. mysql> drop table test;
75. mysql> flush logs; #mysql-bin.000320
76. mysql> drop database hahaha;
77. ####################以上为测试数据####################
78. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000317 | /usr/local/mysql/bin/mysql -uroot
79. mysql> show databases;
80. mysql> show databases;
81. +--------------------+
82. | Database |
83. +--------------------+
84. | hahaha |
85. 14 rows in set (0.00 sec)
86. mysql> select * from test;
87. +----+------+-------+
88. | id | val | data |
89. +----+------+-------+
90. | 1 | 10 | liang |
91. | 3 | 20 | jia |
92. | 5 | 30 | hui |
93. +----+------+-------+
94. 3 rows in set (0.00 sec)
95. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000318 -d hahaha| /usr/local/mysql/bin/mysql -uroot
96. mysql> select * from test;
97. +----+------+-------+
98. | id | val | data |
99. +----+------+-------+
100. | 1 | 10 | liang |
101. | 3 | 20 | jia |
102. | 7 | 40 | aaa |
103. | 9 | 50 | bbb |
104. | 11 | 60 | ccc |
105. | 13 | 70 | ddd |
106. +----+------+-------+
107. 6 rows in set (0.00 sec)
108. ########################################################
109. [root@Master-Mysql ~]# ll /usr/local/mysql/data/
110. -rw-rw----. 1 mysql mysql 1518 Jul 18 21:12 mysql-bin.000318
111. -rw-rw----. 1 mysql mysql 723 Jul 18 21:13 mysql-bin.000319
112. -rw-rw----. 1 mysql mysql 293 Jul 18 21:14 mysql-bin.000320
113. -rw-rw----. 1 mysql mysql 3697 Jul 18 21:26 mysql-bin.000321
114. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000318 | grep end_log_pos
115. #140718 21:12:23 server id 1 end_log_pos 120 CRC32 0x38d71bd6 Start: binlog v 4, server v 5.6.16-log created 140718 21:12:23
116. #140718 21:13:40 server id 1 end_log_pos 723 CRC32 0xcaa8ac7f Rotate to mysql-bin.000320 pos: 4
117. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=120 --stop-position=1518 /usr/local/mysql/data/mysql-bin.000319 -d hahaha|/usr/local/mysql/bin/mysql -uroot
118. mysql> select * from test;
119. +----+------+-------+
120. | id | val | data |
121. +----+------+-------+
122. | 1 | 10 | liang |
123. | 3 | 20 | jia |
124. | 7 | 40 | aaa |
125. | 9 | 50 | bbb |
126. | 11 | 60 | ccc |
127. | 13 | 70 | ddd |
128. | 15 | 80 | dddd |
129. | 17 | 90 | eeee |
130. +----+------+-------+
131. 8 rows in set (0.00 sec)
132. ########################################################
133. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog --start-datetime="2014-07-18 21:13:59" --stop-datetime="2014-07-18 21:14:03" /usr/local/mysql/data/mysql-bin.000320 -d hahaha|/usr/local/mysql/bin/mysql -uroot
134. mysql> show tables;
135. Empty set (0.00 sec)