宜宾网站建设公司

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

点击(此处)折叠或打开

阜平网站建设公司成都创新互联,阜平网站设计制作，有大型网站制作公司丰富经验。已为阜平上千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的阜平做网站的公司定做！

  1. 1.查看MySQL数据库是否开启二进制日志log_bin的value值为ON为开启

  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)

  31. 2.查看时间

  33. mysql> select now();
  34. +---------------------+
  35. | now() |
  36. +---------------------+
  37. | 2017-10-20 19:26:55 |
  38. +---------------------+
  39. 1 row in set (0.00 sec)

  41. 3.查看bin日志文件

  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)

  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)

  57. mysql> insert into t(id,name)values (1,'a');
  58. Query OK, 1 row affected (0.00 sec)

  60. mysql> insert into t(id,name)values (1,'a');
  61. Query OK, 1 row affected (0.01 sec)

  63. mysql> insert into t(id,name)values (2,'b');
  64. Query OK, 1 row affected (0.00 sec)

  66. mysql> insert into t(id,name)values (2,'b');
  67. Query OK, 1 row affected (0.00 sec)

  69. mysql> insert into t(id,name)values (3,'c');
  70. Query OK, 1 row affected (0.00 sec)

  72. mysql> insert into t(id,name)values (3,'c');
  73. Query OK, 1 row affected (0.01 sec)

  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)

  88. mysql> insert into t select * from t;
  89. Query OK, 6 rows affected (0.00 sec)
  90. Records: 6 Duplicates: 0 Warnings: 0

  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)



  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)

  122. mysql> delete from t;
  123. Query OK, 12 rows affected (0.01 sec)


  126. mysql> flush logs;
  127. Query OK, 0 rows affected (0.02 sec)

  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)

  140. 刷新日志后会看到有三个二进制bin文件生成

  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


  150. 7.进行数据恢复

  152. mysql> source /root/binlog/t.sql;
  153. Query OK, 0 rows affected (0.00 sec)

  155. Query OK, 0 rows affected (0.00 sec)

  157. Query OK, 0 rows affected (0.00 sec)

  159. Query OK, 0 rows affected (0.00 sec)

  161. Query OK, 0 rows affected (0.00 sec)

  163. Query OK, 0 rows affected (0.00 sec)

  165. Query OK, 0 rows affected (0.00 sec)

  167. Query OK, 0 rows affected (0.00 sec)

  169. Query OK, 0 rows affected (0.00 sec)

  171. Query OK, 0 rows affected (0.00 sec)

  173. Query OK, 0 rows affected (0.00 sec)

  175. Charset changed
  176. Query OK, 0 rows affected (0.00 sec)

  178. Query OK, 0 rows affected (0.00 sec)

  180. Query OK, 0 rows affected (0.00 sec)

  182. Query OK, 0 rows affected, 1 warning (0.00 sec)

  184. Query OK, 0 rows affected (0.00 sec)

  186. Query OK, 0 rows affected (0.00 sec)

  188. Query OK, 0 rows affected (0.02 sec)

  190. Query OK, 0 rows affected (0.00 sec)

  192. Query OK, 0 rows affected (0.00 sec)

  194. Query OK, 0 rows affected (0.00 sec)

  196. Query OK, 0 rows affected (0.00 sec)

  198. Query OK, 0 rows affected (0.00 sec)

  200. Query OK, 0 rows affected (0.00 sec)

  202. Query OK, 0 rows affected (0.00 sec)

  204. Query OK, 0 rows affected (0.00 sec)

  206. Query OK, 0 rows affected (0.00 sec)

  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

  228. mysql> source /root/binlog/t.sql;
  229. Query OK, 0 rows affected (0.00 sec)

  231. Query OK, 0 rows affected (0.00 sec)

  233. Query OK, 0 rows affected (0.00 sec)

  235. Query OK, 0 rows affected (0.00 sec)

  237. Query OK, 0 rows affected (0.00 sec)

  239. Query OK, 0 rows affected (0.00 sec)

  241. Query OK, 0 rows affected (0.00 sec)

  243. Query OK, 0 rows affected (0.00 sec)

  245. Query OK, 0 rows affected (0.00 sec)

  247. Query OK, 0 rows affected (0.00 sec)

  249. Query OK, 0 rows affected (0.00 sec)

  251. Charset changed
  252. Query OK, 0 rows affected (0.00 sec)

  254. Query OK, 0 rows affected (0.00 sec)

  256. Query OK, 0 rows affected (0.00 sec)

  258. Query OK, 0 rows affected, 1 warning (0.00 sec)

  260. Query OK, 0 rows affected (0.00 sec)

  262. Query OK, 0 rows affected (0.00 sec)

  264. Query OK, 0 rows affected (0.00 sec)

  266. Query OK, 0 rows affected (0.00 sec)

  268. Query OK, 0 rows affected (0.00 sec)

  270. Query OK, 0 rows affected (0.00 sec)

  272. Query OK, 0 rows affected (0.00 sec)

  274. Query OK, 0 rows affected (0.00 sec)

  276. Query OK, 0 rows affected (0.00 sec)

  278. Query OK, 0 rows affected (0.00 sec)

  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)

  301. 此时数据已经全部恢复到数据删除之前！！


分享名称：mysql数据库mysqlbinlog二进制日志文件挖掘
文章转载：http://ybzwz.com/article/jecogi.html

其他资讯