• ADADADADAD

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

    mysql数据库 时间:2024-12-24 19:10:58

    作者:文/会员上传

    简介:

    点击(此处)折叠或打开1.查看mysql数据库是否开启二进制日志log_bin的value值为ON为开启

    mysql> show variables like 'log_%';
    +-------------------------------------

    以下为本文的正文内容,内容仅供参考!本站为公益性网站,复制本文以及下载DOC文档全部免费。


    点击(此处)折叠或打开

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

      mysql> show variables like 'log_%';
      +----------------------------------------+-------------------------------------+
      | Variable_name | Value |
      +----------------------------------------+-------------------------------------+
      | log_bin | ON |
      | log_bin_basename | /data/db/mysql/3306/mysql-bin |
      | log_bin_index | /data/db/mysql/3306/mysql-bin.index |
      | log_bin_trust_function_creators | OFF |
      | log_bin_use_v1_row_events | OFF |
      | log_builtin_as_identified_by_password | OFF |
      | log_error | /data/db/mysql/3306/mariadb.log |
      | log_error_verbosity | 3 |
      | log_output | FILE |
      | log_queries_not_using_indexes | OFF |
      | log_slave_updates | OFF |
      | log_slow_admin_statements | OFF |
      | log_slow_slave_statements | OFF |
      | log_statements_unsafe_for_binlog | ON |
      | log_syslog | OFF |
      | log_syslog_facility | daemon |
      | log_syslog_include_pid | ON |
      | log_syslog_tag | |
      | log_throttle_queries_not_using_indexes | 0 |
      | log_timestamps | UTC |
      | log_warnings | 2 |
      +----------------------------------------+-------------------------------------+
      21 rows in set (0.01 sec)

      2.查看时间

      mysql> select now();
      +---------------------+
      | now() |
      +---------------------+
      | 2017-10-20 19:26:55 |
      +---------------------+
      1 row in set (0.00 sec)

      3.查看bin日志文件

      mysql> show master logs;
      +------------------+-----------+
      | Log_name | File_size |
      +------------------+-----------+
      | mysql-bin.000044 | 870441798 |
      +------------------+-----------+
      1 rows in set (0.00 sec)

      4.创建测试表插入数据
      mysql> create table t(id int,name varchar(10));
      Query OK, 0 rows affected (0.26 sec)
      mysql> select * from t;
      Empty set (0.00 sec)

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

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

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

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

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

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

      mysql> select * from t;
      +------+------+
      | id | name |
      +------+------+
      | 1 | a |
      | 1 | a |
      | 2 | b |
      | 2 | b |
      | 3 | c |
      | 3 | c |
      +------+------+
      6 rows in set (0.00 sec)

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

      mysql> select * from t;
      +------+------+
      | id | name |
      +------+------+
      | 1 | a |
      | 1 | a |
      | 2 | b |
      | 2 | b |
      | 3 | c |
      | 3 | c |
      | 1 | a |
      | 1 | a |
      | 2 | b |
      | 2 | b |
      | 3 | c |
      | 3 | c |
      +------+------+
      12 rows in set (0.00 sec)



      5.删除数据
      mysql> select now();
      +---------------------+
      | now() |
      +---------------------+
      | 2017-10-20 19:27:46 |
      +---------------------+
      1 row in set (0.00 sec)

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


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

      mysql> show master logs;
      +------------------+-----------+
      | Log_name | File_size |
      +------------------+-----------+
      | mysql-bin.000044 | 870441798 |
      | mysql-bin.000045 | 154 |
      | mysql-bin.000046 | 2690 |
      | mysql-bin.000047 | 448 |
      +------------------+-----------+
      4 rows in set (0.00 sec)

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

      6.提取bin文件中的sql(基于时间的数据恢复)
      [root@msp binlog]# ls
      bak.sql bin.sqltest.sql
      [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
      [root@msp binlog]# ls
      bak.sql bin.sqltest.sqlt.sql


      7.进行数据恢复

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.02 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      [root@msp binlog]# rm -f t.sql
      [root@msp binlog]# ls
      bak.sql bin.sqltest.sql
      [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
      [root@msp binlog]# ls
      bak.sql bin.sqltest.sqlt.sql
      [root@msp binlog]# ll
      total 2715356
      -rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
      -rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
      -rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
      -rw-r--r--. 1 root root 2112 Oct 20 19:44 t.sql
      [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
      [root@msp binlog]# ll
      total 2715356
      -rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
      -rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
      -rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
      -rw-r--r--. 1 root root 2448 Oct 20 19:44 t.sql

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

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

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.00 sec)

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

      mysql> select * from t;
      +------+------+
      | id | name |
      +------+------+
      | 1 | a |
      | 1 | a |
      | 2 | b |
      | 2 | b |
      | 3 | c |
      | 3 | c |
      | 1 | a |
      | 1 | a |
      | 2 | b |
      | 2 | b |
      | 3 | c |
      | 3 | c |
      +------+------+
      12 rows in set (0.00 sec)

      此时数据已经全部恢复到数据删除之前!!

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

    将本文的Word文档下载到电脑

    推荐度:

    下载