如何启用MySQL二进制日志记录？

Question

我尝试使用简单示例 mysql-events包，但当我尝试使用它时，我收到了以下错误信息：

错误：ER_NO_BINARY_LOGGING：您未使用二进制日志记录

因此，我更改了my.cnf文件：

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

#what i added:
log_bin = "/home/erfan/salone-entezar/server/"


!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

但是当我尝试重新启动MySQL（使用$ sudo service mysql restart）时，发生了以下错误：

mysql.service的工作失败，因为控制进程以错误代码退出。有关详细信息，请参见“systemctl status mysql.service”和“journalctl -xe”。

这是systemctl status mysql.service的输出：

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Fri 2016-11-18 20:16:48 IRST; 3s ago
  Process: 8838 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 8831 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 8838 (code=exited, status=1/FAILURE); Control PID: 8839 (mysql-systemd-s)
    Tasks: 2 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─control
             ├─8839 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─8850 sleep 1

Nov 18 20:16:48 erfan-m systemd[1]: Starting MySQL Community Server...
Nov 18 20:16:48 erfan-m mysql-systemd-start[8831]: my_print_defaults: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 19!
Nov 18 20:16:48 erfan-m mysql-systemd-start[8831]: my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
Nov 18 20:16:48 erfan-m systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

现在我应该做什么？我遇到了什么问题？

- erfan mehraban

---

在配置文件 /etc/mysql/my.cnf 的第19行发现了没有先导组的选项！应该将其放在[mysqld]之下。 - Solarflare

thanks! @Solarflare - erfan mehraban

Answer 1

针对Ubuntu

我需要在代码中的dsn变量中添加socketPath: '/var/run/mysqld/mysqld.sock'，并将/etc/mysql/my.cnf更正如下：

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld] #grouping  config options is important
# Must be unique integer from 1-2^32
server-id        = 1
# Row format required for ZongJi
binlog_format    = row
# Directory must exist. This path works for Linux. Other OS may require
#   different path.
log_bin          = /var/log/mysql/mysql-bin.log

最后，使用$sudo service mysql restart重新启动它。

对于CentOS

我需要在代码中的dsn变量中添加socketPath : '/var/lib/mysql/mysql.sock'，并将/etc/my.cnf更正如下：

[mysqld]
# Must be unique integer from 1-2^32
server-id        = 1
# Row format required for ZongJi
binlog_format    = row
# Directory must exist. This path works for Linux. Other OS may require
#   different path.
log_bin          = /var/log/mariadb/mariadb-bin.log

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

最后用 $systemctl restart mariadb 重新启动它。

注意 ：CentOS 7 已经将 MySQL 替换为MariaDB，因此 Ubuntu 和 CentOS 的 log_bin 路径略有不同。

- erfan mehraban