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)
