aLzsIJ.jpg

业务需求,需要开启binlog。原来是在网上直接搜的文档教程 发现实际操作报错。此文档做一下记录

开启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

重启mysql后报错提示

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

重启后正常,查看binlog文件

 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

参考链接

官方文档