mysql数据库mysqlbinlog二进制日志文件挖掘

点击(此处)折叠或打开

1. 1.查看mysql数据库是否开启二进制日志log_bin的value值为ON为开启
   
2. 
   
3. mysql> show variables like 'log_%';
   
4. +----------------------------------------+-------------------------------------+
   
5. | Variable_name | Value |
   
6. +----------------------------------------+-------------------------------------+
   
7. | log_bin | ON |
   
8. | log_bin_basename | /data/db/mysql/3306/mysql-bin |
   
9. | log_bin_index | /data/db/mysql/3306/mysql-bin.index |
   
10. | log_bin_trust_function_creators | OFF |
    
11. | log_bin_use_v1_row_events | OFF |
    
12. | log_builtin_as_identified_by_password | OFF |
    
13. | log_error | /data/db/mysql/3306/mariadb.log |
    
14. | log_error_verbosity | 3 |
    
15. | log_output | FILE |
    
16. | log_queries_not_using_indexes | OFF |
    
17. | log_slave_updates | OFF |
    
18. | log_slow_admin_statements | OFF |
    
19. | log_slow_slave_statements | OFF |
    
20. | log_statements_unsafe_for_binlog | ON |
    
21. | log_syslog | OFF |
    
22. | log_syslog_facility | daemon |
    
23. | log_syslog_include_pid | ON |
    
24. | log_syslog_tag | |
    
25. | log_throttle_queries_not_using_indexes | 0 |
    
26. | log_timestamps | UTC |
    
27. | log_warnings | 2 |
    
28. +----------------------------------------+-------------------------------------+
    
29. 21 rows in set (0.01 sec)
    
30. 
    
31. 2.查看时间
    
32. 
    
33. mysql> select now();
    
34. +---------------------+
    
35. | now() |
    
36. +---------------------+
    
37. | 2017-10-20 19:26:55 |
    
38. +---------------------+
    
39. 1 row in set (0.00 sec)
    
40. 
    
41. 3.查看bin日志文件
    
42. 
    
43. mysql> show master logs;
    
44. +------------------+-----------+
    
45. | Log_name | File_size |
    
46. +------------------+-----------+
    
47. | mysql-bin.000044 | 870441798 |
    
48. +------------------+-----------+
    
49. 1 rows in set (0.00 sec)
    
50. 
    
51. 4.创建测试表插入数据
    
52. mysql> create table t(id int,name varchar(10));
    
53. Query OK, 0 rows affected (0.26 sec)
    
54. mysql> select * from t;
    
55. Empty set (0.00 sec)
    
56. 
    
57. mysql> insert into t(id,name)values (1,'a');
    
58. Query OK, 1 row affected (0.00 sec)
    
59. 
    
60. mysql> insert into t(id,name)values (1,'a');
    
61. Query OK, 1 row affected (0.01 sec)
    
62. 
    
63. mysql> insert into t(id,name)values (2,'b');
    
64. Query OK, 1 row affected (0.00 sec)
    
65. 
    
66. mysql> insert into t(id,name)values (2,'b');
    
67. Query OK, 1 row affected (0.00 sec)
    
68. 
    
69. mysql> insert into t(id,name)values (3,'c');
    
70. Query OK, 1 row affected (0.00 sec)
    
71. 
    
72. mysql> insert into t(id,name)values (3,'c');
    
73. Query OK, 1 row affected (0.01 sec)
    
74. 
    
75. mysql> select * from t;
    
76. +------+------+
    
77. | id | name |
    
78. +------+------+
    
79. | 1 | a |
    
80. | 1 | a |
    
81. | 2 | b |
    
82. | 2 | b |
    
83. | 3 | c |
    
84. | 3 | c |
    
85. +------+------+
    
86. 6 rows in set (0.00 sec)
    
87. 
    
88. mysql> insert into t select * from t;
    
89. Query OK, 6 rows affected (0.00 sec)
    
90. Records: 6 Duplicates: 0 Warnings: 0
    
91. 
    
92. mysql> select * from t;
    
93. +------+------+
    
94. | id | name |
    
95. +------+------+
    
96. | 1 | a |
    
97. | 1 | a |
    
98. | 2 | b |
    
99. | 2 | b |
    
100. | 3 | c |
     
101. | 3 | c |
     
102. | 1 | a |
     
103. | 1 | a |
     
104. | 2 | b |
     
105. | 2 | b |
     
106. | 3 | c |
     
107. | 3 | c |
     
108. +------+------+
     
109. 12 rows in set (0.00 sec)
     
110. 
     
111. 
     
112. 
     
113. 5.删除数据
     
114. mysql> select now();
     
115. +---------------------+
     
116. | now() |
     
117. +---------------------+
     
118. | 2017-10-20 19:27:46 |
     
119. +---------------------+
     
120. 1 row in set (0.00 sec)
     
121. 
     
122. mysql> delete from t;
     
123. Query OK, 12 rows affected (0.01 sec)
     
124. 
     
125. 
     
126. mysql> flush logs;
     
127. Query OK, 0 rows affected (0.02 sec)
     
128. 
     
129. mysql> show master logs;
     
130. +------------------+-----------+
     
131. | Log_name | File_size |
     
132. +------------------+-----------+
     
133. | mysql-bin.000044 | 870441798 |
     
134. | mysql-bin.000045 | 154 |
     
135. | mysql-bin.000046 | 2690 |
     
136. | mysql-bin.000047 | 448 |
     
137. +------------------+-----------+
     
138. 4 rows in set (0.00 sec)
     
139. 
     
140. 刷新日志后会看到有三个二进制bin文件生成
     
141. 
     
142. 6.提取bin文件中的sql(基于时间的数据恢复)
     
143. [root@msp binlog]# ls
     
144. bak.sql bin.sql test.sql
     
145. [root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000045 >/root/binlog/t.sql
     
146. [root@msp binlog]# ls
     
147. bak.sql bin.sql test.sql t.sql
     
148. 
     
149. 
     
150. 7.进行数据恢复
     
151. 
     
152. mysql> source /root/binlog/t.sql;
     
153. Query OK, 0 rows affected (0.00 sec)
     
154. 
     
155. Query OK, 0 rows affected (0.00 sec)
     
156. 
     
157. Query OK, 0 rows affected (0.00 sec)
     
158. 
     
159. Query OK, 0 rows affected (0.00 sec)
     
160. 
     
161. Query OK, 0 rows affected (0.00 sec)
     
162. 
     
163. Query OK, 0 rows affected (0.00 sec)
     
164. 
     
165. Query OK, 0 rows affected (0.00 sec)
     
166. 
     
167. Query OK, 0 rows affected (0.00 sec)
     
168. 
     
169. Query OK, 0 rows affected (0.00 sec)
     
170. 
     
171. Query OK, 0 rows affected (0.00 sec)
     
172. 
     
173. Query OK, 0 rows affected (0.00 sec)
     
174. 
     
175. Charset changed
     
176. Query OK, 0 rows affected (0.00 sec)
     
177. 
     
178. Query OK, 0 rows affected (0.00 sec)
     
179. 
     
180. Query OK, 0 rows affected (0.00 sec)
     
181. 
     
182. Query OK, 0 rows affected, 1 warning (0.00 sec)
     
183. 
     
184. Query OK, 0 rows affected (0.00 sec)
     
185. 
     
186. Query OK, 0 rows affected (0.00 sec)
     
187. 
     
188. Query OK, 0 rows affected (0.02 sec)
     
189. 
     
190. Query OK, 0 rows affected (0.00 sec)
     
191. 
     
192. Query OK, 0 rows affected (0.00 sec)
     
193. 
     
194. Query OK, 0 rows affected (0.00 sec)
     
195. 
     
196. Query OK, 0 rows affected (0.00 sec)
     
197. 
     
198. Query OK, 0 rows affected (0.00 sec)
     
199. 
     
200. Query OK, 0 rows affected (0.00 sec)
     
201. 
     
202. Query OK, 0 rows affected (0.00 sec)
     
203. 
     
204. Query OK, 0 rows affected (0.00 sec)
     
205. 
     
206. Query OK, 0 rows affected (0.00 sec)
     
207. 
     
208. [root@msp binlog]# rm -f t.sql
     
209. [root@msp binlog]# ls
     
210. bak.sql bin.sql test.sql
     
211. [root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000046 >>/root/binlog/t.sql
     
212. [root@msp binlog]# ls
     
213. bak.sql bin.sql test.sql t.sql
     
214. [root@msp binlog]# ll
     
215. total 2715356
     
216. -rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
     
217. -rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
     
218. -rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
     
219. -rw-r--r--. 1 root root 2112 Oct 20 19:44 t.sql
     
220. [root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000047 >>/root/binlog/t.sql
     
221. [root@msp binlog]# ll
     
222. total 2715356
     
223. -rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
     
224. -rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
     
225. -rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
     
226. -rw-r--r--. 1 root root 2448 Oct 20 19:44 t.sql
     
227. 
     
228. mysql> source /root/binlog/t.sql;
     
229. Query OK, 0 rows affected (0.00 sec)
     
230. 
     
231. Query OK, 0 rows affected (0.00 sec)
     
232. 
     
233. Query OK, 0 rows affected (0.00 sec)
     
234. 
     
235. Query OK, 0 rows affected (0.00 sec)
     
236. 
     
237. Query OK, 0 rows affected (0.00 sec)
     
238. 
     
239. Query OK, 0 rows affected (0.00 sec)
     
240. 
     
241. Query OK, 0 rows affected (0.00 sec)
     
242. 
     
243. Query OK, 0 rows affected (0.00 sec)
     
244. 
     
245. Query OK, 0 rows affected (0.00 sec)
     
246. 
     
247. Query OK, 0 rows affected (0.00 sec)
     
248. 
     
249. Query OK, 0 rows affected (0.00 sec)
     
250. 
     
251. Charset changed
     
252. Query OK, 0 rows affected (0.00 sec)
     
253. 
     
254. Query OK, 0 rows affected (0.00 sec)
     
255. 
     
256. Query OK, 0 rows affected (0.00 sec)
     
257. 
     
258. Query OK, 0 rows affected, 1 warning (0.00 sec)
     
259. 
     
260. Query OK, 0 rows affected (0.00 sec)
     
261. 
     
262. Query OK, 0 rows affected (0.00 sec)
     
263. 
     
264. Query OK, 0 rows affected (0.00 sec)
     
265. 
     
266. Query OK, 0 rows affected (0.00 sec)
     
267. 
     
268. Query OK, 0 rows affected (0.00 sec)
     
269. 
     
270. Query OK, 0 rows affected (0.00 sec)
     
271. 
     
272. Query OK, 0 rows affected (0.00 sec)
     
273. 
     
274. Query OK, 0 rows affected (0.00 sec)
     
275. 
     
276. Query OK, 0 rows affected (0.00 sec)
     
277. 
     
278. Query OK, 0 rows affected (0.00 sec)
     
279. 
     
280. Query OK, 0 rows affected, 1 warning (0.00 sec)
     
281. 
     
282. mysql> select * from t;
     
283. +------+------+
     
284. | id | name |
     
285. +------+------+
     
286. | 1 | a |
     
287. | 1 | a |
     
288. | 2 | b |
     
289. | 2 | b |
     
290. | 3 | c |
     
291. | 3 | c |
     
292. | 1 | a |
     
293. | 1 | a |
     
294. | 2 | b |
     
295. | 2 | b |
     
296. | 3 | c |
     
297. | 3 | c |
     
298. +------+------+
     
299. 12 rows in set (0.00 sec)
     
300. 
     
301. 此时数据已经全部恢复到数据删除之前！！