MySQL二进制日志操作记录

验证是否创建了二进制日志

mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------------------------------------+
| Variable_name                   | Value                                                       |
+---------------------------------+-------------------------------------------------------------+
| log_bin                         | ON                                                          |
| log_bin_basename                | D:\MySQL\MySQL Server 8.0.12\data\bin       |
| log_bin_index                   | D:\MySQL\MySQL Server 8.0.12\data\bin.index |
| log_bin_trust_function_creators | OFF                                                         |
| log_bin_use_v1_row_events       | OFF                                                         |
+---------------------------------+-------------------------------------------------------------+
5 rows in set, 0 warning (0.01 sec)

显示服务器所有二进制日志

mysql> show master logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| bin.000001 |      2711 |
+----------------------------+-----------+
1 row in set (0.00 sec)

mysql> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
|bin.000001 |      2711 |
+----------------------------+-----------+
1 row in set (0.00 sec)

获取当前二进制日志位置

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

禁用和启动当前会话二进制日志

#禁用
mysql> set sql_log_bin =0;
Query OK, 0 rows affected (0.00 sec)
#启用
mysql> set sql_log_bin =1;
Query OK, 0 rows affected (0.00 sec)

设置日志的到期时间

#同时设置，效果叠加。一下设置二进制日志23.5天清理一次
mysql> set@@global.expire_logs_days=23 and @@binlog_expire_logs_seconds =43200;
Query OK, 0 rows affected, 1 warning (0.00 sec)

手动清除日志

执行 purge binary logs to '<file_name>'，那么除该文件外的前面所有的二进制日志均会删除；
删除所有二进制日志并在此从头开始，执行 reset master ;

二进制日志的格式

1. statement：记录实际的SQL语句
2. row：记录每行的更改
3. mixed：当需要时，MySQL会从statement切换到row

mysql> set global binlog_format ='statement;
Query OK, 0 rows affected (0.00 sec)

在Linux上查看二进制日志文件

#直接查看会报错编码格式不正确，windows没有这个问题
root@localhost mysql]# mysqlbinlog  binlog.000025 
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

需要添加--no-default参数表示不读取任何选项文件；
不然需要修改MySQL的配置文件，在/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务；

[root@localhost mysql]# mysqlbinlog --no-defaults  binlog.000025 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190917 15:28:39 server id 1  end_log_pos 124 CRC32 0x5588426c 	Start: binlog v 4, server v 8.0.12 created 190917 15:28:39 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
J4uAXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAni4BdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgFsQohV
'/*!*/;
# at 124
#190917 15:28:39 server id 1  end_log_pos 155 CRC32 0xb8467156 	Previous-GTIDs
# [empty]
# at 155
#190917 16:43:52 server id 1  end_log_pos 230 CRC32 0x9aac7b0e 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1568709832902104	immediate_commit_timestamp=1568709832902104	transaction_length=40065
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1568709832902104 (2019-09-17 16:43:52.902104 CST)
# immediate_commit_timestamp=1568709832902104 (2019-09-17 16:43:52.902104 CST)
/*!80001 SET @@session.original_commit_timestamp=1568709832902104*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 230
#190917 16:43:52 server id 1  end_log_pos 317 CRC32 0x717d41a6 	Query	thread_id=12	exec_time=0	error_code=0
SET TIMESTAMP=1568709832/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 317
#190917 16:43:52 server id 1  end_log_pos 437 CRC32 0x93c29b80 	Table_map: `gateway`.`t_target_service` mapped to number 70
# at 437
#190917 16:43:52 server id 1  end_log_pos 8348 CRC32 0x98d35538 	Update_rows: table id 70
# at 8348
#190917 16:43:52 server id 1  end_log_pos 16178 CRC32 0xff4cea34 	Update_rows: table id 70
# at 16178
#190917 16:43:52 server id 1  end_log_pos 24334 CRC32 0x639f2bca 	Update_rows: table id 70
# at 24334
#190917 16:43:52 server id 1  end_log_pos 32464 CRC32 0x2789fb69 	Update_rows: table id 70
# at 32464
#190917 16:43:52 server id 1  end_log_pos 40189 CRC32 0x4070e457 	Update_rows: table id 70 flags: STMT_END_F

BINLOG '
yJyAXRMBAAAAeAAAALUBAAAAAEYAAAAAAAEAB2dhdGV3YXkAEHRfdGFyZ2V0X3NlcnZpY2UAFAgP
Eg8SEg8PDw8SDw8PD/wPDw8IH5YAAJYAAADgLocAwAA8AACHANwFLAGHAAL9AnAXcBf48QYBAQAC
ASGAm8KT
yJyAXR8BAAAA5x4AAJwgAAAAAEYAAAAAAAAAAgAUICAAAQAAAAAAAAAEdGVzdJmfDvo3
CGJ1c2luZXNzmZ/e4NMMAOa1i+ivleacjeWKoQRwb3N0BHRlc3QEc29hcJmfDvo3CVRFUk1JTkFU
'/*!*/;
# at 40189
#190917 16:43:52 server id 1  end_log_pos 40220 CRC32 0x01481c05 	Xid = 36
COMMIT/*!*/;
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*/;

截取部分

# at 230
#190917 16:43:52 server id 1  end_log_pos 317 CRC32 0x717d41a6 	Query	thread_id=12	exec_time=0	error_code=0

#at后的数字 表示二进制日志文件中事件的起始位置（文件偏移量），这里是 230
下一行包含了语句在服务器上被启用的时间戳
s

1. erver id：产生该事件服务器的server_id 这里是1
2. end_log_pos：下一个事件的开始位置 这里是314
3. thread_id：指示哪个线程执行了该事件 这里是12
4. exec_time：在主服务器上，它代表执行事件的的时间；
   在从服务器上，它代表从服务器的最终执行时间与主服务器的开始执行时间的差值，值可用作备份相对于主服务器滞后多少的指标 error_code：代表执行事件的结果。零表示无错误

显示二进制日志文件中的事件

mysql> show binlog events in 'binlog.000025';
+---------------+-------+----------------+-----------+-------------+-----------------------------------------+
| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info                                    |
+---------------+-------+----------------+-----------+-------------+-----------------------------------------+
| binlog.000025 |     4 | Format_desc    |         1 |         124 | Server ver: 8.0.12, Binlog ver: 4       |
| binlog.000025 |   124 | Previous_gtids |         1 |         155 |                                         |
| binlog.000025 |   155 | Anonymous_Gtid |         1 |         230 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| binlog.000025 |   230 | Query          |         1 |         317 | BEGIN                                   |
| binlog.000025 |   317 | Table_map      |         1 |         437 | table_id: 70 (gateway.t_target_service) |
| binlog.000025 |   437 | Update_rows    |         1 |        8348 | table_id: 70                            |
| binlog.000025 |  8348 | Update_rows    |         1 |       16178 | table_id: 70                            |
| binlog.000025 | 16178 | Update_rows    |         1 |       24334 | table_id: 70                            |
| binlog.000025 | 24334 | Update_rows    |         1 |       32464 | table_id: 70                            |
| binlog.000025 | 32464 | Update_rows    |         1 |       40189 | table_id: 70 flags: STMT_END_F          |
| binlog.000025 | 40189 | Xid            |         1 |       40220 | COMMIT /* xid=36 */                     |
| binlog.000025 | 40220 | Anonymous_Gtid |         1 |       40293 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| binlog.000025 | 40293 | Query          |         1 |       40390 | use `gateway`; flush privileges         |
| binlog.000025 | 40390 | Anonymous_Gtid |         1 |       40470 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| binlog.000025 | 40470 | Query          |         1 |       40567 | use `gateway`; flush privileges         |
+---------------+-------+----------------+-----------+-------------+-----------------------------------------+
15 rows in set (0.04 sec)

移至下一个日志

mysql>  show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000025 |     41629 |
+---------------+-----------+
1 row in set (0.08 sec)

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

mysql>  show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000025 |     41673 |
| binlog.000026 |       155 |
+---------------+-----------+
2 rows in set (0.00 sec)

还原报错

[root@localhost mysql]# mysql -u root -p < binlog.000025 
Enter password: 
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '�bin'��]'.

应该是

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position=124  --disable-log-bin  /var/lib/mysql/binlog.000025  |mysql --binary-mode=1  -u root -p  -f
Enter password: 

最后发现如果删除了部分数据，自己没有备份数据库或者二进制日志没有记录数据添加的语句，那么是无法还原的；
二进制日志只能记录你的操作语句和数据变化（update，delete，drop），并不能记录数据库存的数据。
比如你在打开二进制日志记录前已经创建好数据库并写入了数据，那么之后你删除数据是无法恢复的，除非用已经备份的文档恢复数据库后，再用二进制日志文档还原操作。