mysql中安全删除binlog日志、使用binlog恢复数据

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>RESETMASTER;#重置binlog
2. mysql>PURGEMASTERLOGSTO'mysql-bin.000003';#删除mysql-bin.000003之前的日志
3. mysql>PURGEMASTERLOGSBEFORE'2014-07-1615:07:00';#删除2014-07-1615:07:00之前的binlog日志
4. mysql>PURGEMASTERLOGSBEFOREDATE_SUB(NOW(),INTERVAL3DAY);

恢复日志文件:

1. [root@Master-Mysql~]#/usr/local/mysql/bin/mysql-uroot-p''-e'SHOWBINLOGEVENTS＼G'
2. Enterpassword:
3. ***************************1.row***************************
4. [root@Master-Mysql~]#/etc/init.d/mysqldrestart
5. [root@Master-Mysql~]#ll/usr/local/mysql/data/
6. -rw-rw----.1mysqlmysql120Jul1821:01mysql-bin.000316#重新启动生成的binlog
7. [root@Master-Mysql~]#/usr/local/mysql/bin/mysql-uroot
8. mysql>createdatabasehahaha;
9. mysql>usehahaha;
10. mysql>createtabletest(idintauto_incrementnotnullprimarykey,
11. ->valint,datavarchar(20));
12. mysql>insertintotest(val,data)values(10,'liang');
13. mysql>insertintotest(val,data)values(20,'jia');
14. mysql>insertintotest(val,data)values(30,'hui');
15. mysql>showtables;
16. +------------------+
17. |Tables_in_hahaha|
18. +------------------+
19. |test|
20. +------------------+
21. 1rowinset(0.00sec)
22. mysql>select*fromtest;
23. +----+------+-------+
24. |id|val|data|
25. +----+------+-------+
26. |1|10|liang|
27. |3|20|jia|
28. |5|30|hui|
29. +----+------+-------+
30. mysql>flushlogs;#mysql-bin.000317
31. [root@Master-Mysql~]#ll/usr/local/mysql/data/
32. -rw-rw----.1mysqlmysql120Jul1821:08mysql-bin.000317#刷新binlog新产生的日志，其他依次类推；
33. mysql>insertintotest(val,data)values(40,'aaa');
34. mysql>insertintotest(val,data)values(50,'bbb');
35. mysql>insertintotest(val,data)values(60,'ccc');
36. mysql>select*fromtest;
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>deletefromtestwhereidbetween4and5;
48. mysql>select*fromtest;
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>insertintotest(val,data)values(70,'ddd');
59. mysql>select*fromtest;
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>flushlogs;#mysql-bin.000318
71. mysql>insertintotest(val,data)values(80,'dddd');
72. mysql>insertintotest(val,data)values(90,'eeee');
73. mysql>flushlogs;#mysql-bin.000319
74. mysql>droptabletest;
75. mysql>flushlogs;#mysql-bin.000320
76. mysql>dropdatabasehahaha;
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>showdatabases;
80. mysql>showdatabases;
81. +--------------------+
82. |Database|
83. +--------------------+
84. |hahaha|
85. 14rowsinset(0.00sec)
86. mysql>select*fromtest;
87. +----+------+-------+
88. |id|val|data|
89. +----+------+-------+
90. |1|10|liang|
91. |3|20|jia|
92. |5|30|hui|
93. +----+------+-------+
94. 3rowsinset(0.00sec)
95. [root@Master-Mysql~]#/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000318-dhahaha|/usr/local/mysql/bin/mysql-uroot
96. mysql>select*fromtest;
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. 6rowsinset(0.00sec)
108. ########################################################
109. [root@Master-Mysql~]#ll/usr/local/mysql/data/
110. -rw-rw----.1mysqlmysql1518Jul1821:12mysql-bin.000318
111. -rw-rw----.1mysqlmysql723Jul1821:13mysql-bin.000319
112. -rw-rw----.1mysqlmysql293Jul1821:14mysql-bin.000320
113. -rw-rw----.1mysqlmysql3697Jul1821:26mysql-bin.000321
114. [root@Master-Mysql~]#/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000318|grepend_log_pos
115. #14071821:12:23serverid1end_log_pos120CRC320x38d71bd6Start:binlogv4,serverv5.6.16-logcreated14071821:12:23
116. #14071821:13:40serverid1end_log_pos723CRC320xcaa8ac7fRotatetomysql-bin.000320pos:4
117. [root@Master-Mysql~]#/usr/local/mysql/bin/mysqlbinlog--start-position=120--stop-position=1518/usr/local/mysql/data/mysql-bin.000319-dhahaha|/usr/local/mysql/bin/mysql-uroot
118. mysql>select*fromtest;
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. 8rowsinset(0.00sec)
132. ########################################################
133. [root@Master-Mysql~]#/usr/local/mysql/bin/mysqlbinlog--start-datetime="2014-07-1821:13:59"--stop-datetime="2014-07-1821:14:03"/usr/local/mysql/data/mysql-bin.000320-dhahaha|/usr/local/mysql/bin/mysql-uroot
134. mysql>showtables;
135. Emptyset(0.00sec)