mysql5.6 binlog设置详细步骤

自成E家

已于 2022-12-13 11:22:40 修改

阅读量948

点赞数

文章标签： mysql 数据库

于 2022-12-13 09:12:49 首次发布

本文链接：https://blog.csdn.net/rjbcxhc/article/details/128296341

版权

检查一下是否开启binlog

mysql> show master status;
Empty set (0.00 sec)

显示是空的，说明mysql没有开启binlog。

再检查一下参数配置

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_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)

看到log_bin是OFF。

一、开启binlog

编辑etc/my.cnf文件，在文件末尾添加如下配置

server-id =1
log-bin=mysql-bin

修改后重启mysql服务

service mysqld restart

再次查看：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    21864 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.05 sec)

表示binlog已开启。

二、配置binlog的模式

mysql> show global variables like "%binlog_format%";
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.05 sec)

通过以上命令查看binlog模式，可以看到是默认的statement.

方法一、修改my.cnf，需要重启mysql服务，这种方式可以永久生效

binlog_format="ROW"
#binlog_format="STATEMENT"
#binlog_format="MIXED"

然后重启mysql服务

方法二、不需要重启mysql服务，但是下次重启，状态会被还原。

SET global binlog_format="STATEMENT";

再次查看模式设置情况：

mysql> show global variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.39 sec)

下面是关于mysql binlog三种模式的补充介绍。

1、statement模式：（（基于SQL语句的复制(statement-based replication, SBR)）每一条修改数据的sql语句会记录在binlog中，这时在默认的binlog模式。

优点：

- 历史悠久，技术成熟
- 不需要记录每一行的变化，减少了binlog日志量，文件较小
- binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
- binlog可以用于实时的还原，而不仅仅用于复制
- 主从版本可以不一样，从服务器版本可以比主服务器版本高

缺点：

- 使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、SYSDATE()(除非启动时启用了 --sysdate-is-now 选项)

- INSERT … SELECT 会产生比 RBR 更多的行级锁
- 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
- 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
- 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
- 执行复杂语句如果出错的话，会消耗更多资源
- 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

2、ROW模式：（基于行的复制(row-based replication, RBR)）不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。就是你插入5条数据，RBR会记录五条数据，而SBR只会记录一条插入数据的sql

RBR 的优点：

- 任何情况都可以被复制，这对复制来说是最安全可靠的。（比如：不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题）
- 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
- 复制以下几种语句时的行锁更少：INSERT … SELECT、包含 AUTO_INCREMENT 字段的INSERT、没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句执行 INSERT/UPDATE/DELETE 语句时锁更少
- 从服务器上采用多线程来执行复制成为可能

RBR 的缺点：