mysql的binlog日志开启

mysql的binlog日志主要记录了，日志的操作记录，我们可以根据binlog的日志记录，能够看到mysql的增删该查操作，主从复制就是根据mysql的binlog日志进行数据同步的，所以我们在安装mysql不管是集群还是单机mysql，都要开启mysql的binlog日志，恢复日志的时候能够事半功倍。

这里有一个经历过的小例子，一个同事把线上的mysql管理员权限给测试同事了，测试同事误操作删除了一个库，删除的时候也不知道是删除库了，就这样等线上出现事故，才知道有人误操作删除库的事件，我们解决方法，首先是看mysql的binlog日志，如果没有开启binlog日志，这个删除操作就无法查找了。下面看一下binlog的日志开启方法：

[root@www.wulaoer.org ~]# vim /etc/my.cnf 
...........
[mysqld]
............
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10  
early-plugin-load = ""
..............

上面设置的含义可以参考

#设置日志格式
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写
log-bin = /data/mysql/logs/mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

我已经定义了datadir = /usr/local/mysql/var所以这里不需要定义log-bin路径，验证一下binlog日志是否开启

[root@www.wulaoer.org mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------------+
| Variable_name                          | Value                                |
+----------------------------------------+--------------------------------------+
| log_bin                                | ON                                   |
| log_bin_basename                       | /usr/local/mysql/var/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/var/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                  |
| log_bin_use_v1_row_events              | OFF                                  |
| log_builtin_as_identified_by_password  | OFF                                  |
| log_error                              | ./www.wulaoer.org .err                        |
| log_error_verbosity                    | 3                                    |
| log_output                             | FILE                                 |
| log_queries_not_using_indexes          | OFF                                  |
| log_slave_updates                      | OFF                                  |
| log_slow_admin_statements              | OFF                                  |
| log_slow_slave_statements              | OFF                                  |
| log_statements_unsafe_for_binlog       | ON                                   |
| log_syslog                             | OFF                                  |
| log_syslog_facility                    | daemon                               |
| log_syslog_include_pid                 | ON                                   |
| log_syslog_tag                         |                                      |
| log_throttle_queries_not_using_indexes | 0                                    |
| log_timestamps                         | UTC                                  |
| log_warnings                           | 2                                    |
+----------------------------------------+--------------------------------------+
21 rows in set (0.01 sec)

关闭状态的binlog日志应该是log_bin  OFF,开启状态是log_bin  ON上面的已经开启了。存储路径在/usr/local/mysql/var/路径下。如果关闭binlog只需要把下面的注释掉即可

[root@www.wulaoer.org ~]# vim /etc/my.cnf 
...........
[mysqld]
............
#log-bin=mysql-bin
#binlog_format=mixed
#server-id   = 1
#expire_logs_days = 10
#early-plugin-load = ""
..............

注意，修改后my.cnf文件必须重启mysql，配置文件才会生效。