[数据库/MYSQL]MYSQL开启Bin-Log

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是为了实现事务的持久性而出现的产物
[数据库/MYSQL]MYSQL开启Bin-Log
  • undo log 事务日志
用来回滚行记录到某个版本。
事务未提交之前,Undo保存了未提交之前的版本数据,Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读。
undo log是为了实现事务的原子性而出现的产物,在Mysql innodb存储引擎中用来实现多版本并发控制
[数据库/MYSQL]MYSQL开启Bin-Log

写 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)
[数据库/MYSQL]MYSQL开启Bin-Log

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(如果是集群,则不能重复)】
[数据库/MYSQL]MYSQL开启Bin-Log

Binlog常见格式:

[数据库/MYSQL]MYSQL开启Bin-Log
  • step2.2 重启mysql服务
    保存文件,重启mysql服务
cmd(管理员权限)> sc stop mysql
cmd(管理员权限)> sc start mysql
cmd(管理员权限)> sc query mysql
[数据库/MYSQL]MYSQL开启Bin-Log

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)
[数据库/MYSQL]MYSQL开启Bin-Log

log-bin配置项提供的全路径,则: 生成的日志文件就在指定的路径下;若仅提供的1个文件名称(Eg: mysql-bin),则:生成的binlog日志的位置在data目录下(eg: D:\Program Files(x86)\MySQL\db-data)

[数据库/MYSQL]MYSQL开启Bin-Log

3 操作步骤(Linux系统下: my.cnf)

配置文件 : /etc/my.cnf

log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW 

server_id=1 

X 参考文献

[数据库/MYSQL]MYSQL开启Bin-Log

上一篇:Photoshop 图片高质量放大技巧


下一篇:84、给mysql数据库中的所有表批量添加字段