如何启用 MySQL 二进制日志记录? [英] How do I enable MySQL binary logging?

查看：65
本文介绍了如何启用 MySQL 二进制日志记录?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我尝试使用 的简单示例 mysql-events 包 但是当我尝试使用它时，出现此错误:

<块引用>

错误:ER_NO_BINARY_LOGGING:您没有使用二进制日志

所以我改变了my.cnf:

<预><代码>## MySQL 数据库服务器配置文件.## 您可以将其复制到以下之一:# - "/etc/mysql/my.cnf" 设置全局选项，# - "~/.my.cnf" 设置用户特定的选项.## 可以使用程序支持的所有长选项.# 使用 --help 运行程序以获取可用选项列表，并使用# --print-defaults 以查看它实际理解和使用的内容.## 解释见# http://dev.mysql.com/doc/mysql/en/server-system-variables.html## * 重要提示:可以覆盖此文件中的设置的其他设置！# 文件必须以'.cnf'结尾，否则会被忽略.##我添加的内容: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 社区服务器已加载:已加载(/lib/systemd/system/mysql.service；已启用；供应商预设:已启用)活动:自 2016 年 11 月 18 日星期五 20:16:48 IRST 开始激活(开始发布)(结果:退出代码)；3 秒前进程:8838 ExecStart=/usr/sbin/mysqld(代码=退出，状态=1/FAILURE)进程:8831 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)主PID:8838(代码=退出，状态=1/FAILURE)；控制PID:8839(mysql-systemd-s)任务:2(限制:4915)C组:/system.slice/mysql.service└─控制├─8839/bin/bash/usr/share/mysql/mysql-systemd-start post└─8850睡眠111 月 18 日 20:16:48 erfan-m systemd[1]:启动 MySQL 社区服务器...11 月 18 日 20:16:48 erfan-m mysql-systemd-start[8831]: my_print_defaults: [错误] 在第 19 行的配置文件/etc/mysql/my.cnf 中找到没有前面组的选项！11 月 18 日 20:16:48 erfan-m mysql-systemd-start [8831]: my_print_defaults: [ERROR] 默认处理中的致命错误.程序中止！Nov 18 20:16:48 erfan-m systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

我现在应该怎么做，我的问题是什么?

解决方案

for UBUNTU

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

!includedir/etc/mysql/conf.d/!includedir/etc/mysql/mysql.conf.d/[mysqld] #grouping 配置选项很重要# 必须是 1-2^32 之间的唯一整数服务器 ID = 1# 纵集需要的行格式binlog_format = 行# 目录必须存在.此路径适用于 Linux.其他操作系统可能需要# 不同的路径.log_bin =/var/log/mysql/mysql-bin.log

最后用 $sudo service mysql restart

重启它

用于 CentOS

我必须在代码中将 socketPath : '/var/lib/mysql/mysql.sock' 添加到 dsn 变量并更正 /etc/my.cnf 如下:

[mysqld]# 必须是 1-2^32 之间的唯一整数服务器 ID = 1# 纵集需要的行格式binlog_format = 行# 目录必须存在.此路径适用于 Linux.其他操作系统可能需要# 不同的路径.log_bin =/var/log/mariadb/mariadb-bin.log数据目录=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# 建议禁用符号链接以防止各种安全风险符号链接=0# 使用 systemd 时忽略设置用户和组.# 如果你需要在不同的用户或组下运行mysqld，# 为 mariadb 自定义你的 systemd 单元文件# http://fedoraproject.org/wiki/Systemd 中的说明[mysqld_safe]日志错误=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## 包含配置目录中的所有文件#!includedir/etc/my.cnf.d

最后用 $systemctl restart mariadb

重启它

注意:CentOS 7 已经用 MariaDB 替换了 MySQL.所以UBUNTU和CentOS的log_bin路径是有区别的.

I tried to use simple example of mysql-events package but when i tried to use it , i got this error:

Error: ER_NO_BINARY_LOGGING: You are not using binary logging

so i changed 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/

but when i tried to restart mysql (using $ sudo service mysql restart) this error has happened:

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

and this is 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

What should i do now and what is my problem?

解决方案

for UBUNTU

i have to add socketPath : '/var/run/mysqld/mysqld.sock' to dsn variable in code and also correcting /etc/mysql/my.cnf as below :

!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

and at last restart it with $sudo service mysql restart

for CentOS

i have to add socketPath : '/var/lib/mysql/mysql.sock' to dsn variable in code and also correcting /etc/my.cnf as below :

[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

and at last restart it with $systemctl restart mariadb

NOTE : CentOS 7 has replaced MySQL with MariaDB. So there is deference between log_bin path of UBUNTU and CentOS .

这篇关于如何启用 MySQL 二进制日志记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆