记录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/*!*/;
<