1、开启MySQL二进制日志
vi /etc/my.cnf [mysqld]组下设置
[mysqld]
log-bin
expire_logs_days = 10
max_binlog_size = 100M
# 关闭重启mysql服务,即可打开二进制日志
systemctl stop mysqld
systemctl start mysqld
# show variables 语句查询日志设置
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/node-a-bin |
| log_bin_index | /usr/local/mysql/data/node-a-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/error.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------+
21 rows in set (0.02 sec)
log_bin变量的值为ON 表示二进制日志已经打开
注意:
数据库文件与日志文件最好不要放在同一个磁盘,当数据库文件的所在磁盘发生故障时,可以使用日志文件恢复数据
2、查看二进制日志
二进制日志文件格式:
以 "filename"为名称,".index"为后缀的文件
以 "filename"为名称,".000001"为后缀的文件
MySQl服务重启时,".000001"后缀文件会增加一个,并且后缀名加1递增
当日志长度超过了max_binlog_size的上限(默认是1GB),则会创建一个新的日志文件
[mysqld]
login-bin
max_binlog_size=100M
# 通过show binary logs 查看二进制日志文件个数以及名称
show binary logs;
日志文件的个数与MySQL服务启动的次数相同,每启动一次MySQL服务,将会产生一个新的日志文件
# 使用mysqlbinlog 查看二进制日志
mysqlbinlog --no-defaults /usr/local/mysql/data/node-a-bin.000001