MySQL二进制日志操作记录

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

mysql> show variables like &＃39;log_bin%&＃39;;
&＃43;---------------------------------&＃43;-------------------------------------------------------------&＃43;
| Variable_name | Value |
&＃43;---------------------------------&＃43;-------------------------------------------------------------&＃43;
| 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 |
&＃43;---------------------------------&＃43;-------------------------------------------------------------&＃43;
5 rows in set, 0 warning (0.01 sec)


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

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


获取当前二进制日志位置

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


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

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


设置日志的到期时间

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


手动清除日志

执行 purge binary logs to &＃39;&＃39;&＃xff0c;那么除该文件外的前面所有的二进制日志均会删除&＃xff1b;
删除所有二进制日志并在此从头开始&＃xff0c;执行 reset master ;

二进制日志的格式

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

mysql> set global binlog_format &＃61;&＃39;statement;
Query OK, 0 rows affected (0.00 sec)


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

#直接查看会报错编码格式不正确&＃xff0c;windows没有这个问题
root&＃64;localhost mysql]# mysqlbinlog binlog.000025
mysqlbinlog: [ERROR] unknown variable &＃39;default-character-set&＃61;utf8&＃39;


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

[root&＃64;localhost mysql]# mysqlbinlog --no-defaults binlog.000025
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/;
/*!50003 SET &＃64;OLD_COMPLETION_TYPE&＃61;&＃64;&＃64;COMPLETION_TYPE,COMPLETION_TYPE&＃61;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 &＃39;
J4uAXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAni4BdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgFsQohV
&＃39;/*!*/;
# 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&＃61;0 sequence_number&＃61;1 rbr_only&＃61;yes original_committed_timestamp&＃61;1568709832902104 immediate_commit_timestamp&＃61;1568709832902104 transaction_length&＃61;40065
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp&＃61;1568709832902104 (2019-09-17 16:43:52.902104 CST)
# immediate_commit_timestamp&＃61;1568709832902104 (2019-09-17 16:43:52.902104 CST)
/*!80001 SET &＃64;&＃64;session.original_commit_timestamp&＃61;1568709832902104*//*!*/;
SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;ANONYMOUS&＃39;/*!*/;
# at 230
#190917 16:43:52 server id 1 end_log_pos 317 CRC32 0x717d41a6 Query thread_id&＃61;12 exec_time&＃61;0 error_code&＃61;0
SET TIMESTAMP&＃61;1568709832/*!*/;
SET &＃64;&＃64;session.pseudo_thread_id&＃61;12/*!*/;
SET &＃64;&＃64;session.foreign_key_checks&＃61;1, &＃64;&＃64;session.sql_auto_is_null&＃61;0, &＃64;&＃64;session.unique_checks&＃61;1, &＃64;&＃64;session.autocommit&＃61;1/*!*/;
SET &＃64;&＃64;session.sql_mode&＃61;1168113696/*!*/;
SET &＃64;&＃64;session.auto_increment_increment&＃61;1, &＃64;&＃64;session.auto_increment_offset&＃61;1/*!*/;
/*!\C utf8 *//*!*/;
SET &＃64;&＃64;session.character_set_client&＃61;33,&＃64;&＃64;session.collation_connection&＃61;33,&＃64;&＃64;session.collation_server&＃61;255/*!*/;
SET &＃64;&＃64;session.lc_time_names&＃61;0/*!*/;
SET &＃64;&＃64;session.collation_database&＃61;DEFAULT/*!*/;
/*!80005 SET &＃64;&＃64;session.default_collation_for_utf8mb4&＃61;255*//*!*/;
BEGIN
/*!*/;
# at 317
#190917 16:43:52 server id 1 end_log_pos 437 CRC32 0x93c29b80 Table_map: &＃96;gateway&＃96;.&＃96;t_target_service&＃96; 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_FBINLOG &＃39;
yJyAXRMBAAAAeAAAALUBAAAAAEYAAAAAAAEAB2dhdGV3YXkAEHRfdGFyZ2V0X3NlcnZpY2UAFAgP
Eg8SEg8PDw8SDw8PD/wPDw8IH5YAAJYAAADgLocAwAA8AACHANwFLAGHAAL9AnAXcBf48QYBAQAC
ASGAm8KT
yJyAXR8BAAAA5x4AAJwgAAAAAEYAAAAAAAAAAgAUICAAAQAAAAAAAAAEdGVzdJmfDvo3
CGJ1c2luZXNzmZ/e4NMMAOa1i&＃43;ivleacjeWKoQRwb3N0BHRlc3QEc29hcJmfDvo3CVRFUk1JTkFU
&＃39;/*!*/;
# at 40189
#190917 16:43:52 server id 1 end_log_pos 40220 CRC32 0x01481c05 Xid &＃61; 36
COMMIT/*!*/;
SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;AUTOMATIC&＃39; /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/;
/*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;


截取部分

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


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

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

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

mysql> show binlog events in &＃39;binlog.000025&＃39;;
&＃43;---------------&＃43;-------&＃43;----------------&＃43;-----------&＃43;-------------&＃43;-----------------------------------------&＃43;
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
&＃43;---------------&＃43;-------&＃43;----------------&＃43;-----------&＃43;-------------&＃43;-----------------------------------------&＃43;
| 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 &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;ANONYMOUS&＃39; |
| 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&＃61;36 */ |
| binlog.000025 | 40220 | Anonymous_Gtid | 1 | 40293 | SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;ANONYMOUS&＃39; |
| binlog.000025 | 40293 | Query | 1 | 40390 | use &＃96;gateway&＃96;; flush privileges |
| binlog.000025 | 40390 | Anonymous_Gtid | 1 | 40470 | SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;ANONYMOUS&＃39; |
| binlog.000025 | 40470 | Query | 1 | 40567 | use &＃96;gateway&＃96;; flush privileges |
&＃43;---------------&＃43;-------&＃43;----------------&＃43;-----------&＃43;-------------&＃43;-----------------------------------------&＃43;
15 rows in set (0.04 sec)


移至下一个日志

mysql> show binary logs;
&＃43;---------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;---------------&＃43;-----------&＃43;
| binlog.000025 | 41629 |
&＃43;---------------&＃43;-----------&＃43;
1 row in set (0.08 sec)mysql> flush logs;
Query OK, 0 rows affected (0.15 sec)mysql> show binary logs;
&＃43;---------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;---------------&＃43;-----------&＃43;
| binlog.000025 | 41673 |
| binlog.000026 | 155 |
&＃43;---------------&＃43;-----------&＃43;
2 rows in set (0.00 sec)


还原报错

[root&＃64;localhost mysql]# mysql -u root -p
Enter password:
ERROR: ASCII &＃39;\0&＃39; 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 &＃39;\0&＃39; is expected. Query: &＃39;&＃xfffd;bin&＃39;&＃xfffd;&＃xfffd;]&＃39;.


应该是

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


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