mysql5.7版本手把手教你配置log_bin

1、mysql的配置my.cnf

#[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
#!includedir /etc/my.cnf.d
#l conf /etc/my.cnf
# Created by http://www.wdlinux.cn
# # Last Updated 2010.06.01
 [client]
port = 3307
socket = /tmp/mysql.sock
 [mysqld]
port = 3307
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data/
socket = /tmp/mysql.sock
expire_logs_days=7
binlog_format=row
log_slave_updates=ON
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
 myisam_sort_buffer_size = 8M
thread_cache_size = 8
 query_cache_size= 16M
 max_connections = 300
 wait_timeout = 30
 interactive_timeout = 30
 max_connect_errors = 9
 max_user_connections = 50
 long_query_time = 1
 tmp_table_size = 16M
#log-bin=mysql-bin
# #binlog_format=mixed
server-id = 1
log_bin=/usr/local/mysql/log/mysqlbin/binlog
log_bin_index=/usr/local/mysql/log/mysqlbin/binlog_index
[mysqldump]
 quick
 max_allowed_packet = 8M
 [mysql]
 no-auto-rehash
 [myisamchk]
 key_buffer_size = 12M
 sort_buffer_size = 1M
 read_buffer = 1M
 write_buffer = 1M
 [mysqlhotcopy]
#log_bin=/usr/local/mysql/log/mysqlbin/binlog
#    
#log-bin-index=/usr/local/mysql/log/mysqlbin/binlog_index

备注：红色部分为配置选项

2、注意事项

1、需要创建/usr/local/mysql/log/mysqlbin到这个目录等级，log或者mysqlbin需要新建目录

2、需要在[mysqld]标签下面，才能生效

3、遇到的问题：

解决方案：

需要对mysqlbin授权：

chown mysql:mysql mysqlbin

3、查询开启情况

1）-参数验证

show variables like '%log_bin%';

show variables like '%binlog_format%';

show variables like '%expire_logs_days%';

show variables like '%log_slave_updates%';   

show variables like '%binlog_expire_logs_seconds%';   ----mysql 8.0

注意：

1、MySQL配置必须开启binlog日志和binlog_format=row（行级别日志）

2、binlog需保留三天以上。

3、从库需开启log_slave_updates=ON