记录MysqL数据库真正执行更改的所有 *** 作（DML语句），不包含那些没有修改任何数据的语句，不会记录select和show这样的语句。
二进制日志的作用：
1、 可以完成主从复制的功能
2、 进行恢复 *** 作。
数据可以通过binglog日志,使用MysqLbinlog命令，实现基于时间点和位置的恢复 *** 作。
查看binglog参数信息：MysqL> show global variables like '%bin%';+-----------------------------------------+-------------------------------------------------+| Variable_name | Value |+-----------------------------------------+-------------------------------------------------+| bind_address | * || binlog_cache_size | 4194304 || binlog_checksum | CRC32 || binlog_direct_non_transactional_updates | OFF || binlog_error_action | ABORT_SERVER || binlog_format | ROW || binlog_group_commit_sync_delay | 0 || binlog_group_commit_sync_no_delay_count | 0 || binlog_gtID_simple_recovery | ON || binlog_max_flush_queue_time | 0 || binlog_order_commits | ON || binlog_row_image | FulL || binlog_rows_query_log_events | OFF || binlog_stmt_cache_size | 32768 || innodb_API_enable_binlog | OFF || innodb_locks_unsafe_for_binlog | OFF || log_bin | ON || log_bin_basename | /mydata/MysqL/MysqL3307/logs/MysqL-binlog || log_bin_index | /mydata/MysqL/MysqL3307/logs/MysqL-binlog.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || log_statements_unsafe_for_binlog | ON || max_binlog_cache_size | 1073741824 || max_binlog_size | 524288000 || max_binlog_stmt_cache_size | 18446744073709547520 || sync_binlog | 1 |+-----------------------------------------+-------------------------------------------------+26 rows in set (0.00 sec)
二进制文件用cat,head,或者tail命令是无法看的，需要通过MysqLbinglog 命令查看二进制日志，把日志转换格式后，输出到自定义的文件。查看binglog日志文件：
MysqLbinlog --no-defaults -v -v --base64-output=decode-rows /mydata/MysqL/MysqL3307/logs/MysqL-binlog.000021 /root/MysqLbinlog21.log
含义：-v代表可以查看到的具体执行信息。--base64-output 把二进制文件转换格式以下是在ROW模式下，记录的记忆行的变更情况，变更前以及变更后的内容。修改记录模式为：
set global binlog_format=statement;set global binlog_format=mixed;set global binlog_format=row;
查看生产文件内容/root/MysqLbinlog21.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @olD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DEliMITER /*!*/;# at 4#180521 1:09:56 server ID 3307101 end_log_pos 123 CRC32 0x79f0cd1c Start: binlog v 4,server v 5.7.20-log created 180521 1:09:56 at startup# Warning: this binlog is either in use or was not closed properly.RolLBACK/*!*/;# at 123#180521 1:09:56 server ID 3307101 end_log_pos 194 CRC32 0x96ca2d5f PrevIoUs-GTIDs# d6a9d222-5282-11e8-bac7-080027691c44:1-15# at 194#180521 9:47:53 server ID 3307101 end_log_pos 259 CRC32 0x737b7bad GTID last_committed=0 sequence_number=1 rbr_only=noSET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:16'/*!*/;# at 259#180521 9:47:53 server ID 3307101 end_log_pos 388 CRC32 0x9eba0300 query thread_ID=3 exec_time=0 error_code=0SET TIMESTAMP=1526867273/*!*/;SET @@session.pseudo_thread_ID=3/*!*/;SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET @@session.sql_mode=1436549152/*!*/;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=45/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAulT/*!*/;SET @@session.explicit_defaults_for_timestamp=1/*!*/;create table test.zs(a timestamp,b timestamp,c timestamp)/*!*/;# at 388#180521 9:49:28 server ID 3307101 end_log_pos 453 CRC32 0x8a902dce GTID last_committed=1 sequence_number=2 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:17'/*!*/;# at 453#180521 9:49:28 server ID 3307101 end_log_pos 521 CRC32 0xfe907eed query thread_ID=3 exec_time=0 error_code=0SET TIMESTAMP=1526867368/*!*/;BEGIN/*!*/;# at 521#180521 9:49:28 server ID 3307101 end_log_pos 571 CRC32 0x528b2287 table_map: `test`.`zs` mapped to number 219# at 571#180521 9:49:28 server ID 3307101 end_log_pos 607 CRC32 0x2bb11644 Write_rows: table ID 219 flags: STMT_END_F### INSERT INTO `test`.`zs`### SET### @1=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 607#180521 9:49:28 server ID 3307101 end_log_pos 638 CRC32 0xf1b37ef5 XID = 26COMMIT/*!*/;# at 638#180521 9:49:30 server ID 3307101 end_log_pos 703 CRC32 0x8d1451b6 GTID last_committed=2 sequence_number=3 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:18'/*!*/;# at 703#180521 9:49:30 server ID 3307101 end_log_pos 771 CRC32 0x17f458cd query thread_ID=3 exec_time=0 error_code=0SET TIMESTAMP=1526867370/*!*/;BEGIN/*!*/;# at 771#180521 9:49:30 server ID 3307101 end_log_pos 821 CRC32 0x8608f3f8 table_map: `test`.`zs` mapped to number 219# at 821#180521 9:49:30 server ID 3307101 end_log_pos 857 CRC32 0x61faa6a3 Write_rows: table ID 219 flags: STMT_END_F### INSERT INTO `test`.`zs`### SET### @1=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 857#180521 9:49:30 server ID 3307101 end_log_pos 888 CRC32 0xc555dada XID = 27COMMIT/*!*/;# at 888#180521 9:49:31 server ID 3307101 end_log_pos 953 CRC32 0x152275a2 GTID last_committed=3 sequence_number=4 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:19'/*!*/;# at 953#180521 9:49:31 server ID 3307101 end_log_pos 1021 CRC32 0x68fadf15 query thread_ID=3 exec_time=0 error_code=0SET TIMESTAMP=1526867371/*!*/;BEGIN/*!*/;# at 1021#180521 9:49:31 server ID 3307101 end_log_pos 1071 CRC32 0x999fd973 table_map: `test`.`zs` mapped to number 219# at 1071#180521 9:49:31 server ID 3307101 end_log_pos 1107 CRC32 0x0c1bb219 Write_rows: table ID 219 flags: STMT_END_F### INSERT INTO `test`.`zs`### SET### @1=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 1107#180521 9:49:31 server ID 3307101 end_log_pos 1138 CRC32 0x0e581441 XID = 28COMMIT/*!*/;# at 1138#180521 9:51:29 server ID 3307101 end_log_pos 1203 CRC32 0x6c5d54df GTID last_committed=4 sequence_number=5 rbr_only=noSET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:20'/*!*/;# at 1203#180521 9:51:29 server ID 3307101 end_log_pos 1290 CRC32 0x93e104d9 query thread_ID=4 exec_time=0 error_code=0SET TIMESTAMP=1526867489/*!*/;SET @@session.time_zone='SYstem'/*!*/;flush privileges/*!*/;# at 1290#180521 10:02:20 server ID 3307101 end_log_pos 1355 CRC32 0x07b66739 GTID last_committed=5 sequence_number=6 rbr_only=noSET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:21'/*!*/;# at 1355#180521 10:02:20 server ID 3307101 end_log_pos 1520 CRC32 0x48415c32 query thread_ID=7 exec_time=0 error_code=0use `test`/*!*/;SET TIMESTAMP=1526868140/*!*/;SET @@session.explicit_defaults_for_timestamp=0/*!*/;create table zs1(a timestamp,b timestamp null default null,c timestamp null default null)/*!*/;# at 1520#180521 10:05:36 server ID 3307101 end_log_pos 1585 CRC32 0x749f7319 GTID last_committed=6 sequence_number=7 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:22'/*!*/;# at 1585#180521 10:05:36 server ID 3307101 end_log_pos 1657 CRC32 0x381adad5 query thread_ID=7 exec_time=0 error_code=0SET TIMESTAMP=1526868336/*!*/;BEGIN/*!*/;# at 1657#180521 10:05:36 server ID 3307101 end_log_pos 1708 CRC32 0x8b75b8d0 table_map: `test`.`zs1` mapped to number 220# at 1708#180521 10:05:36 server ID 3307101 end_log_pos 1748 CRC32 0x6f7e879b Write_rows: table ID 220 flags: STMT_END_F### INSERT INTO `test`.`zs1`### SET### @1=1526868336 /* TIMESTAMP(0) Meta=0 nullable=0 is_null=0 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 1748#180521 10:05:36 server ID 3307101 end_log_pos 1779 CRC32 0x8f7b45df XID = 74COMMIT/*!*/;# at 1779#180521 10:06:01 server ID 3307101 end_log_pos 1844 CRC32 0x0857208f GTID last_committed=7 sequence_number=8 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:23'/*!*/;# at 1844#180521 10:06:01 server ID 3307101 end_log_pos 1916 CRC32 0xf8b57357 query thread_ID=7 exec_time=0 error_code=0SET TIMESTAMP=1526868361/*!*/;BEGIN/*!*/;# at 1916#180521 10:06:01 server ID 3307101 end_log_pos 1967 CRC32 0x71f69634 table_map: `test`.`zs1` mapped to number 220# at 1967#180521 10:06:01 server ID 3307101 end_log_pos 2007 CRC32 0x8398dbd6 Write_rows: table ID 220 flags: STMT_END_F### INSERT INTO `test`.`zs1`### SET### @1=1526868361 /* TIMESTAMP(0) Meta=0 nullable=0 is_null=0 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 2007#180521 10:06:01 server ID 3307101 end_log_pos 2038 CRC32 0xae8d3064 XID = 77COMMIT/*!*/;# at 2038#180521 10:06:02 server ID 3307101 end_log_pos 2103 CRC32 0x50fdabc1 GTID last_committed=8 sequence_number=9 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:24'/*!*/;# at 2103#180521 10:06:02 server ID 3307101 end_log_pos 2175 CRC32 0x38a21fb3 query thread_ID=7 exec_time=0 error_code=0SET TIMESTAMP=1526868362/*!*/;BEGIN/*!*/;# at 2175#180521 10:06:02 server ID 3307101 end_log_pos 2226 CRC32 0x4a1b7b27 table_map: `test`.`zs1` mapped to number 220# at 2226#180521 10:06:02 server ID 3307101 end_log_pos 2266 CRC32 0x397de1b3 Write_rows: table ID 220 flags: STMT_END_F### INSERT INTO `test`.`zs1`### SET### @1=1526868362 /* TIMESTAMP(0) Meta=0 nullable=0 is_null=0 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 2266#180521 10:06:02 server ID 3307101 end_log_pos 2297 CRC32 0xe2727b87 XID = 78COMMIT/*!*/;# at 2297#180521 10:06:03 server ID 3307101 end_log_pos 2362 CRC32 0x3cadd4ea GTID last_committed=9 sequence_number=10 rbr_only=yes/*!50718 SET TRANSACTION ISolATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:25'/*!*/;# at 2362#180521 10:06:03 server ID 3307101 end_log_pos 2434 CRC32 0x06a0be65 query thread_ID=7 exec_time=0 error_code=0SET TIMESTAMP=1526868363/*!*/;BEGIN/*!*/;# at 2434#180521 10:06:03 server ID 3307101 end_log_pos 2485 CRC32 0xea1f5733 table_map: `test`.`zs1` mapped to number 220# at 2485#180521 10:06:03 server ID 3307101 end_log_pos 2525 CRC32 0xba9bf9de Write_rows: table ID 220 flags: STMT_END_F### INSERT INTO `test`.`zs1`### SET### @1=1526868363 /* TIMESTAMP(0) Meta=0 nullable=0 is_null=0 */### @2=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */### @3=NulL /* TIMESTAMP(0) Meta=0 nullable=1 is_null=1 */# at 2525#180521 10:06:03 server ID 3307101 end_log_pos 2556 CRC32 0x1961d7b0 XID = 79COMMIT/*!*/;# at 2556#180521 10:09:00 server ID 3307101 end_log_pos 2621 CRC32 0xe802a496 GTID last_committed=10 sequence_number=11 rbr_only=noSET @@SESSION.GTID_NEXT= 'd6a9d222-5282-11e8-bac7-080027691c44:26'/*!*/;# at 2621#180521 10:09:00 server ID 3307101 end_log_pos 2712 CRC32 0x1b99ba5c query thread_ID=7 exec_time=0 error_code=0SET TIMESTAMP=1526868540/*!*/;flush privileges/*!*/;MysqLbinlog: file '/root/MysqLbinlog21.log' not found (Errcode: 2 - No such file or directory)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*/;
总结以上是内存溢出为你收集整理的MYSQL binlog 日志内容查看全部内容，希望文章能够帮你解决MYSQL binlog 日志内容查看所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）