文章目录
1. 二进制日志
Binlog 在 MySQL 的 Server 层实现(引擎共用)
Binlog 为逻辑日志,记录的是一条语句的原始逻辑
Binlog 不限大小,追加写入,不会覆盖以前的日志
他和 redo log 的区别就在于,他是逻辑日志,而redo log 是物理日志
1.1 概述
二进制日志(BINLOG)记录了所有 DDL(数据定义语言)和 DML(数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL 的主从复制,就是通过该 binlog 实现的。
1.2 日志格式
1.2.1 STATEMENT
该日志格式在日志文件中记录的都是 SQL 语句(statement),每一条对数据进行修改的 SQL 都会记录在日志文件中,通过 MySQL 提供的 mysqlbinlog 工具,可以清晰的查看到每条语句的文本。主从复制的时候,从库(slave)会将日志文件解析为原文本,并在从库重新执行一次。
1.2.2 ROW
该日志格式在日志文件中记录的是 每一行的数据变更 ,而不是记录 SQL 语句。比如,执行 SQL 语句:update tb_book set status=‘1’,如果是 statement 日志格式,在日志中会记录一行 SQL 文件;而如果是 row ,由于是对全表进行更新,也就是每一行记录都会发生变更,row 格式的日志中会记录每一行数据变更。
statement 记录的是SQL语句,row 记录的是数据库中所有变化的行
1.2.3 MIXED
这是目前 MySQL 默认的日志格式,即混合了 STATEMENT 和 ROW 两种格式。默认情况下采用 STATEMENT 。但是在一些特殊情况下采用 ROW 来进行记录。MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点
1.3 bin-log 开启
bin-log 默认是关闭的,先用 sql 查询自己的 bin-log 是否是开启的
show variables like 'log_%';
查询结果如下,这里一般是 OFF ,但是我的已经打开了。
如果是 OFF 的话,先找到自己的 my.ini 文件(路径一般是:C:\ProgramData\MySQL\MySQL Server 5.6)
在 my.ini 文件下添加如下两句话:
# 开启二进制文件
log_bin=mysql-binlog
# 二进制文件的格式,有 statement 、row 、mixed 可选
binlog_format=STATEMENT
然后重启 mysql 的服务就好了
binlog 开启成功之后,你可以看到以下两个文件(C:\ProgramData\MySQL\MySQL Server 5.6\data)
- mysql-binlog.000001:日志文件
- mysql-binlog.index: 该文件是日志索引文件,记录日志的文件名
查看 binlog 文件的两种方式:
- 导出成文本文件:
D:\AppServ\MySQL\bin>mysqlbinlog >d:/test.txt
确保在bin目录下有mysqlbinlog.exe 没有的话搜索下载 - mysql 使用mysqlbinlog查看日志的问题
mysqlbinlog mysql-bin.00005 --startdatetime=‘时间’ --stopdatetime=‘时间’ --database=DB_name | grep -i -A 6 ‘id=111’
用grep…
2. 主从复制
2.1 复制概述
复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步
MySQL 支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制
2.2 复制原理
MySQL 的主从复制原理如下:
Binary log:二进制日志
Relay log:中继日志
从上层来看,复制分成三步:
- Master 主库在事务提交时,会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中
- 主库推送二进制日志文件 Binlog 中日志事件到从库的中继日志 Relay log
- Slave 重做中继日志中的事件,将改变反映它自己的数据
2.3 复制优势
- 主库出现问题,可以快速切换到从库提供服务
- 可以在从库上执行查询操作,从主库中更新,实现读写分离,降低主库的访问压力
- 可以在从库中执行备份,以避免备份期间影响主库的服务