资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

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://www.cdkjz.cn/article/jecogi.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
成都画册设计广汉锦华手机微信网站成都广告制作不锈钢防护栏营山月子护理Android开发圣月冷冻库微信小程序成都微信开发

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图