代码改变世界

MySQL 5.7开启二进制日志注意事项

2018-07-11 15:56  潇湘隐者  阅读(...)  评论(...编辑  收藏

最近才开始将部分MySQL 5.6升级到MySQL 5.7, 在开启MySQL的二进制日志时,发现MySQL 5.7 MySQL 5.6已有细微区别。如果在my.cnf配置文件中,只设置了全局系统变量log_bin,没有设置全局系统变量server_id的话,那么MySQL启动不了,而且错误日志没有任何错误信息。

 

查了一下官方文档16.1.6.4 Binary Logging Options and Variables如下所示, 如果没有设置全局系统变量server_id,那么MySQL就不允许启动:

 

 

Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the base name. The binary log file base name and any specified path are available as the log_bin_basename system variable.

If you specify the --log-bin option without also specifying a --server-id, the server is not allowed to start. (Bug #11763963, Bug #56739)

 

 

       官方文档Changes in MySQL 5.7.3 (2013-12-03, Milestone 13)详细介绍如下:

 

 

Important Change; Replication: It was possible to start the server with binary logging enabled but no server ID specified; in such cases, the server would set server_idto 1 (rather than 0) while slaves remained unable to connect.

Now --server-id must be used when starting the server with binary logging enabled, otherwise the server is unable to start. If --server-id=0 is used, this value is no longer changed by the server; in this case, updates are written to the binary log, but slaves are unable to connect. Using --server-id without specifying a value has the same effect as setting it explicitly to 0. (Bug #11763963, Bug #56739)

 

 

也就是说,从MySQL 5.7.3以后版本必须配置server-id,否则无法启用MySQL二进制日志,虽然这个知识点没啥技术含量,但是如果不了解的话,就会犯懵:因为MySQL服务启动失败,但是在错误日志里面没有错误信息输出。看不到错误信息,但是启动又失败,你说晕不晕!

 

 

service mysqld restart

Redirecting to /bin/systemctl restart  mysqld.service

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

 

 

参考资料

 

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_bin

 

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html