[数据库/MYSQL]MYSQL开启Bin-Log
时间：2021-05-07 19:00:32
收藏：0
阅读：28
1 概述: MYSQL数据库的二进制日志————bin log
什么是二进制日志(binlog)?
MySQL的二进制日志binlog，可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是【事务安全型】的。
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。
二进制日志包括两类文件：
二进制日志索引文件（文件名后缀为.index），用于记录所有的二进制文件
二进制日志文件（文件名后缀为.00000*），用于记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
什么是事务日志？
事务日志的目的：实例或者介质失败，事务日志文件就能作为备份数据而派上用场。
innodb事务日志包括:
redo log : 指事务开始之前， 在操作任何数据之前,首先将需操作的数据备份到一个地方
undo log : 指事务中操作的任何数据,将最新的数据备份到一个地方
- redo log 事务日志
不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。
具体的落盘策略可以进行配置 。
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
RedoLog是为了实现事务的持久性而出现的产物
- undo log 事务日志
用来回滚行记录到某个版本。
事务未提交之前，Undo保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读。
undo log是为了实现事务的原子性而出现的产物,在Mysql innodb存储引擎中用来实现多版本并发控制
写 Binlog 的时机？ sync_binlog
对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
- 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
- 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
- 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
sync_binlog的设置
如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。
在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。
【二进制日志处理事务】和【非事务性语句】的区别?
在事务性语句（update）执行过程中，服务器将会进行额外的处理，在服务器执行时多个事务是并行执行的，为了把他们的记录在一起，需要引入事务日志的概念。在事务完成被提交的时候一同刷新到二进制日志。
对于非事务性语句（insert，delete）的处理。遵循以下3条规则：
1）如果非事务性语句被标记为事务，那么: 将被写入重做日志(redo log)。
2）如果没有标记为事务，而且重做日志中没有，那么: 直接写入二进制日志(bin log)。
3）如果没有标记为事务，但是重做日志中有，那么: 写入重做日志(redo log)。
注意: 如果在一个事务中有非事务性语句，那么: 将会利用规则2，优先将该影响非事务表语句直接写入二进制日志。
Binlog日志的应用场景
- MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
- 数据恢复：通过使用 mysqlbinlog 工具来使恢复数据
Binlog日志的代价
一般来说，开启binlog日志大概会有1%的性能损耗
2 操作步骤(Windows系统下: my.ini)
step1 查看MYSQL是否开启 bin log
bin-log的默认配置: 关闭(OFF)
mysql> show variables like ‘log_bin‘
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set, 1 warning (0.10 sec)
step2 若为OFF，则需开启 bin log
mysql-version: 5.7.19
my.ini文件默认不允许修改，需要右键“管理员取得所有权”之后才能保存修改。
- step2.1 配置
bin log
在打开my.ini文件，在mysqld下面添加
# Binary Logging
log-bin=mysql-bin
# 【binlog 日志存放路径】若提供的全路径，则: 生成的日志文件就在指定的路径下；若仅提供的1个文件名称(Eg: mysql-bin)，则：生成的binlog日志的位置在data目录下(eg: D:\Program Files(x86)\MySQL\db-data)
binlog-format=ROW
# 【日志中会记录成每?一?行行数据被修改的形式】
# Server Id
server-id=1
# 【指定当前机器的服务 ID(如果是集群，则不能重复)】
Binlog常见格式:
- step2.2 重启mysql服务
保存文件，重启mysql服务
cmd(管理员权限)> sc stop mysql
cmd(管理员权限)> sc start mysql
cmd(管理员权限)> sc query mysql
step2.3 确认/查验 是否mysql开启成功了binlog
mysql> show variables like ‘%log_bin%‘;
+---------------------------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\Program Files(x86)\MySQL\db-data\mysql-bin |
| log_bin_index | D:\Program Files(x86)\MySQL\db-data\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
若
log-bin配置项提供的全路径，则: 生成的日志文件就在指定的路径下；若仅提供的1个文件名称(Eg: mysql-bin)，则：生成的binlog日志的位置在data目录下(eg: D:\Program Files(x86)\MySQL\db-data)
3 操作步骤(Linux系统下: my.cnf)
配置文件 :
/etc/my.cnf
log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW
server_id=1
X 参考文献
- Mysql的Binlog原理 - CSDN
- MySQL Binlog 介绍 - CSDN
- WINDOWS下MYSQL开启BINLOG - 博客园
- Windows下Mysql5.7开启binlog步骤及注意事项 - 博客园
- [Windows]CMD命令入门教程 与 Windows常见维护问题 - 博客园/千千寰宇
原文：https://www.cnblogs.com/johnnyzen/p/14738877.html
评论（0）