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