Mysql5.7开启binlog

1
2
3
4
5
6

# 在配置文件里
[mysqld]
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
server-id=123454

1
2

2019-02-26T06:50:46.581796Z 0 [ERROR] unknown variable 'log_bin_basename=/var/lib/mysql/mysql-bin'
2019-02-26T06:50:46.581811Z 0 [ERROR] Aborting

1
2
3

[mysqld]
log-bin=mysql-bin
server-id=1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

1
2
3
4
5

root@mysql-0:/var/lib/mysql# ls -l
......
-rw-r----- 1 mysql mysql      154 May 30 11:09 mysql-bin.000001
-rw-r----- 1 mysql mysql      285 May 30 11:09 mysql-bin.index
......

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

[mysqld]
server-id=1
log-bin=mysql-bin
#设置日志格式，binlog的格式有三种：STATEMENT、ROW、MIXED。 STATEMENT是基于sql语句的复制、ROW基于行的复制、MIXED混合模式的复制
binlog_format = mixed

#sql_mode 模式配置
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#设置日志路径，注意路经需要mysql用户有权限写，注意此处和log-bin=mysql-bin二选一即可
#log-bin=mysql-bin会默认设置在/var/lib/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