mysql 5.7 sql log_Linux下Mysql5.7开启binlog步骤及注意事项

主要步骤同上一篇windows的配置

Linux 下的配置文件为 my.cnf,windows下为my.ini

配置文件在目录 /etc/my.cnf 配置

log_bin=mysql_bin

binlog-format=Row

server-id=1

系统变量binlog_format 指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式。这个参数主要影响主从复制。

复制的模式有下面几种:基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

log_bin=mysql_bin

binlog-format=Row

server-id=1

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

collation-server = utf8_unicode_ci

init-connect='SET NAMES utf8'

character-set-server = utf8

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

如果没有对log-bin指定log文件，默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称

使用时则使用mysqlbinlog /var/lib/mysql|grep "*****"等来追踪database的操作。

修改默认mysql的编码

mysql> SHOW VARIABLES LIKE 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

然后vim /etc/my.cnf

在追后面添加

collation-server = utf8_unicode_ci

init-connect='SET NAMES utf8'

character-set-server = utf8

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

然后重启mysql

service mysqld restart

再进入mysql确认(SHOW VARIABLES LIKE 'character%';)