还想删库跑路？看我怎么破你

1、进入数据库，查看binlog状态
mysql> show variables like '%log-bin%' ;
Empty set (0.00 sec)


2、修改MySQL配置文件
[root@localhost bin]# vi /etc/my.cnf

// 找到mysqld，在下面添加如下数据
server-id = 1   # 单个节点id
log-bin=/var/lib/mysql/mysql-bin   # binlog日志文件保存地址
expire_logs_days = 10   # 日志过期时间

保存并退出


3. 重启MySQL
service mysqld restart 


4. 再次查看binlog状态
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

可以看到，binlog已经开启

5. Binlog日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件
可以查看所有binlog日志文件列表
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)


6. 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)


7. 执行Flush logs ,   刷新日志，此刻开始产生一个新编号的binlog文件
mysql> Flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志

8. 可以通过mysqlbinlog查看binlog日志，（cat命令无法查看）
[root@localhost ~]# whereis mysqlbinlog
mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz
[root@localhost mysql]# /usr/bin/mysqlbinlog  mysql-bin.000001
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

// 此处报错是因为mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令
//有两个解决方案：
1.是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
2.用mysqlbinlog --no-defaults mysql-bin.000001 命令打开

[root@localhost mysql]# /usr/bin/mysqlbinlog  --no-defaults mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200715 19:58:14 server id 1  end_log_pos 123 CRC32 0x1c66870e  Start: binlog v 4, server v 5.7.27-log created 200715 19:58:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Vu8OXw8BAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABW7w5fEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQ6HZhw=
'/*!*/;
# at 123
#200715 19:58:14 server id 1  end_log_pos 154 CRC32 0x59a19e6a  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


9. 也可以在mysql中以show binlog events in 'mysql-bin.000001'; 查看
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)


10. 执行下面SQL
create database hello ;
create table student(id int primary key auto_increment , name varchar(20) , age int );
insert into 
    student 
        (name , age ) 
    values 
         ('张三',18),
         ('李四',20),
         ('王五',19);



11. 将hello数据库备份到本地
[root@localhost ~]# mysqldump -u root -p  hello > /root/hello.sql
Enter password: 


12. 执行flush logs 生成新的binlog日志
mysql> flush  logs ;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       896 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



14. 往student表中新增两条数据
mysql> insert into student (name , age ) values ('赵六',28),('周七',25);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from student ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
|  2 | 李四   |   20 |
|  3 | 王五   |   19 |
|  4 | 赵六   |   28 |
|  5 | 周七   |   25 |
+----+--------+------+
5 rows in set (0.00 sec)



15.模拟误删操作（当然也可能是删库跑路那种...），删除hello数据库
mysql> drop database hello ;
Query OK, 1 row affected (0.01 sec)


16. 新建hello数据库，执行之前备份的SQL文件，数据已经恢复到备份那一刻
mysql> create database hello ;
Query OK, 1 row affected (0.00 sec)

mysql> use hello ;
Database changed
mysql> source /root/hello.sql
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
|  2 | 李四   |   20 |
|  3 | 王五   |   19 |
+----+--------+------+
3 rows in set (0.00 sec)



17. 执行flush logs 生成新的binlog日志，方便操作之前的binlog日志
mysql> flush logs
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       896 |
| mysql-bin.000003 |      1347 |
| mysql-bin.000004 |      154 |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



18. 查看日志4：show binlog events  in 'mysql-bin.000003' ;
mysql> show binlog events  in 'mysql-bin.000003' ;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000003 | 219 | Query          |         1 |         292 | BEGIN                                 |
| mysql-bin.000003 | 292 | Table_map      |         1 |         347 | table_id: 113 (hello.student)         |
| mysql-bin.000003 | 347 | Write_rows     |         1 |         414 | table_id: 113 flags: STMT_END_F       |
| mysql-bin.000003 | 414 | Xid            |         1 |         445 | COMMIT /* xid=242 */                  |
| mysql-bin.000003| 445 | Anonymous_Gtid |         1 |         510 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000003| 510 | Query          |         1 |         605 | drop database hello                   |
| mysql-bin.000003 | 605 | Anonymous_Gtid |         1 |         670 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000003| 670 | Query          |         1 |         767 | create database hello                 |
| mysql-bin.000003| 767 | Rotate         |         1 |         814 | mysql-bin.000007;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+


可以看出，在292开启事务，414开始写入内容，445提交事务

19. 在mysql外部执行数据恢复指令
[root@localhost mysql]# /usr/bin/mysqlbinlog  --no-defaults --start-position=292 --stop-position=445 --database=hello  /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -pitheima -v hello
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

--------------
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
--------------

--------------
BINLOG '
YP0OXw8BAAAAdwAAAHsAAAAAAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Af0bDWo=
'
--------------

--------------
BINLOG '
gP0OXxMBAAAANwAAAFsBAAAAAHEAAAAAAAEABWhlbGxvAAdzdHVkZW50AAMDDwMCPAAGrW1P0A==
gP0OXx4BAAAAQwAAAJ4BAAAAAHEAAAAAAAEAAgAD//gGAAAABui1teWFrRwAAAD4BwAAAAblkajk
uIMZAAAAZ7sssw==
'
--------------

--------------
COMMIT
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

--------------
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
--------------

--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
--------------

[root@localhost mysql]# 



20.mysql查看student表数据，搞定！
mysql> select * from student ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
|  2 | 李四   |   20 |
|  3 | 王五   |   19 |
|  6 | 赵六   |   28 |
|  7 | 周七   |   25 |
+----+--------+------+
5 rows in set (0.00 sec)