Mysql-日志详解
# Mysql日志详解
# 1、Binlog
Binlog属于Mysql server层的维护的一种二进制日志
,主要记录对于对Mysql数据更新的SQL语句,以“event”的形式保存在磁盘中
Binlog的写入时机是SQL transaction 执行完,但是任何相关的Locks还没释放或者事务还没最终commit前
,这样就保证了Binlog记录的操作时序与数据库实际的数据变更顺序一致
# 1.1、Binlog的主要作用
- 数据恢复:通过mysqlbinlog工具恢复数据
- 主从复制:MySQL在Master端开启binlog,Master把它的二进制日志传递给slaves并回放(中继日志)来达到master-slave数据一致的目的
# 1.2、Binlog文件类型
- 二进制日志索引文件(.index):记录所有的二进制文件
- 二进制日志文件(.00000*):记录所有 DDL 和 DML 语句事件
# 1.3、相关命令
-- 检查 Binlog 文件是否已开启
show variables like '%log_bin%';
-- 查看binlog列表
show binary logs;
-- 查看最新的binlog
show master status;
-- 查看 Binlog 日志
mysqlbinlog mysql-bin.00000* | more
-- 查看当前 Binlog 文件对应的详细事件信息
show binlog events in 'mysql-bin.00000*';
-- 查询当前 Binlog 日志使用格式 Statement|Row|Mixed
show global variables like '%binlog_format%';
--mysqlbinlog 命令手动恢复数据
mysqlbinlog --start-position=0 --stop-position=500 mysql-bin.00000* > /root/back.sql;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 1.4、相关参数
my.cnf文件中对于binlog的配置参数:
- log-bin=/home/mysql/binlog/ 设置此参数表示启用binlog功能,并制定二进制日志的存储目录
- max_binlog_size=104857600 日志文件最大字节(单位:字节) Binlog 最大和默认值是 1G
- expire_logs_days = 7 设置了只保留7天BINLOG(单位:天)
- binlog-do-db=db_name binlog日志只记录指定库的更新
- binlog-ignore-db=db_name binlog日志不记录指定库的更新
- sync_binlog=0 写缓冲多少次,刷一次磁盘,默认0
Binlog的过期删除不是服务定时执行,而是由事件触发的,能够触发的事件包括
服务器重启
服务器被更新
日志达到了最大长度
日志被刷新
Binlog的刷新频率:sync_binlog默认为0 ,代表由操作系统根据自身负载自行决定多久写一次磁盘,如果修改为1或者n,那么代表执行每1次或者n次事务提交时会立刻写入磁盘
# 1.5、日志格式
Binlog提供了三种模式来记录日志:
Statement 模式:基于 SQL 语句的复制(statement-based replication-SBR) 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能
缺点:特定函数功能不能进行复制,如sleep()、UUID()
Row 模式:基于行的复制(row-based replication-RBR) 优点:非常清楚的记录下每一行数据修改的细节 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容
Mixed 模式:混合模式复制(mixed-based replication-MBR) 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式
# 2、redo log
redo log属于Mysql Innodb引擎层维护的日志数据
,记录的数据是事务执行过程中的执行情况,主要用来保证事务的持久性
先回顾一下为什么需要redo log,为了解决什么问题,这样才能更好的理解和学习
数据库的主要功能就是数据存储,存储的地方就是操作系统的磁盘空间,这就涉及到了磁盘IO,如果每次更改数据库都需要把数据写入磁盘文件,那么文件IO就会成为数据库的性能瓶颈!
如何解决:性能不够,内存来凑。数据库在数据改变的时候先把改变数据放入内存进行修改,然后等待时机把内存数据同步到磁盘文件进行持久化。内存分为用户空间和内核空间,数据库层面只有先把数据写入用户空间,然后切换到内核空间,执行fsync()进行刷盘操作,把内核空间的数据写入数据库磁盘文件(随机写入),到了这一步还存在什么问题?
问题有两个:
1、写数据库文件属于随机IO,性能不高
2、以什么样的频率进行刷盘操作,如果存在延迟,那么如何保证系统崩溃的时候,数据不丢失?也就是事务已经提交了(数据进入内存),但是没有写入磁盘文件,这时候系统崩溃了,也就无法保证持久性
3、Innodb是以页为单位进行磁盘交互,如果一个事务只修改了一个数据页中的几个字段,那也需要把整个数据页刷到磁盘,太浪费了;如果一个事务跨多个数据页,那么在刷盘的时候随机IO性能差
所以有问题就需要有解决方案:redo log,解决了什么问题?
1、redo log 文件大小固定,可以使用顺序写(顺序磁盘IO是随机IO性能的3-5倍)
2、数据改变的时候,先写入redo log磁盘文件进行持久化,然后再写入内存,等待刷盘进行持久化,如果数据库数据持久化之前系统崩溃,那么就可以使用redo log进行数据恢复(崩溃回复crash-safe),这种先写磁盘然后写入内存等待刷盘的操作也称之为WAL(write ahead log)
3、redo log只记录修改的数据,减少IO数量
接下来整体再总结一下:
1、数据库事务开始的时候进行逐步写盘(先写入重做日志缓冲区Innodb_log_buffer,默认大小为8M)
2、redo log持久化成功之后,把数据的改变写入log buffer内存中
3、内存中的数据根据策略进行刷盘操作,进行数据库数据真正的持久化到磁盘
4、如果持久化之前数据崩溃,重启时使用redo log的数据进行数据恢复
引用《MySQL技术内幕 Innodb 存储引擎》(page37)上的原话
即使某个事务还没有提交,Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。 这一点是必须要知道的,因为这可以很好地解释再大的事务的提交(commit)的时间也是很短暂的。
Mysql提供了三种redo log持久化策略:
- 0 :只写redo log buffer(延迟刷),每秒落地一次,性能最高,但是一致性最差,有可能丢失1秒的数据,也就是每次事务提交,只把数据写入内存,每隔一秒进行一次OS flush的刷盘操作。
- 1 :写redo log buffer同时调用OS flush,性能最差,一致性最高
- 2 :写redo log buffer同时写os buffer,之后每隔一秒自动调用flush,性能高,一致性也好,这样即使mysql服务崩溃也不会丢失数据,但是如果操作系统崩溃,会丢失1秒的数据
如何查看目前使用的哪种策略:
show variables like '%innodb_flush_log_at_trx_commit%';
如何修改redo log持久化策略:
-- 修改mysql.ini文件中的属性
innodb_flush_log_at_trx_commit=1
2
redo log 和bin log之间的区别
redo log | binlog | |
---|---|---|
文件大小 | 大小固定 | 参数max_binlog_size设置 |
实现方式 | Innodb引擎专属实现 | server层实现,所有引擎都用 |
记录方式 | 循环写,写到结尾会回头覆盖最开始的日志 | 追加写,超过大小之后重开新文件进行记录 |
适用场景 | 崩溃回复crash-safe | 复制和数据恢复 |
文件内容 | 是数据页修改之后的物理记录 | 简单认为就是记录sql,属于逻辑日志 |
# 3、undo log
undo log属于Mysql Innodb引擎层维护的日志数据
,记录的数据是事务执行过程中数据的逻辑变化,主要用来保证事务的原子性
undo log主要有两个作用:
- 事务回滚
- MVCC
本篇文章主要记录关于事务回滚的作用,关于MVCC下次专门开一篇文章进行记录
先回顾一下为什么需要undo log?
事务原子性代表着事务内的所有操作,要么全部成功,要么全部失败,如果执行到中间的语句,执行失败了,需要把之前执行的数据全部恢复到执行之前的状态,这种机制就需要undo log来完成。undo log记录的是对应当前事务操作的相反操作,比如,事务插入一条数据,那么undo log就需要记录一条根据主键删除数据的日志,方便事务回滚的时候进行数据恢复。
那么什么时候会进行undo log的写入?
进行数据修改或者删除的时候会记录undo log,每一种操作对应的undo log的类型是不一样的,如图所示:
- insert操作:undo log的类型是 TRX_UNDO_INSERT_REC
- start,end:指向记录开始和结束的位置
- undo type:undo log的类型
- undo no:在当前事务中的undo log的编号
- table id:表空间id
- 主键列信息:记录主键或者唯一列信息
- delete操作:delete并不会直接删除数据,而是先将数据记录头中的delete_mark标记为1(因为其它并发的事务可能还需要读取此数据),提交事务后,后台有一个purge线程会将数据真正的删除。对应的undo log的类型是TRX_UNDO_DEL_MARK_REC,存储的信息比insert多出来三列
- old trx_id:这个属性会保存记录中的隐藏列trx_id,这个属性在MVCC并发读的时候会起作用
- old roll_pointer:这个属性保存了记录中的隐藏列roll_point,可以通过这个属性找到之前的undo log
- 索引列信息:这部分主要是用来在第二阶段事务提交后真是删除数据的
- update操作:update操作分为两种情况,一种是没有更新主键的,会产生类型为 TRX_UNDO_UPD_EXIST_REC的undo log;一种是更新主键的,会产生两种undo log,删除旧主键、插入新主键,也就是TRX_UNDO_INSERT_REC和TRX_UNDO_DEL_MARK_REC。
TRX_UNDO_UPD_EXIST_REC相比于TRX_UNDO_DEL_MARK_REC主要是多了更新列的信息
- 更新列信息:更新了哪一些列的原有信息
undo log链
在一个事务中,对数据的连续操作会生成一个又一个的undo log,这些undo log通过old roll_pointer进行链式的连接,组成一个调用链,在事务回滚的时候,通过undo no从大到小的顺序进行数据恢复
比如:存在一张user表,id、name、age三个字段,做如下操作
begin;
insert into user (id,name,age) values(1,'张三',18);
update user set age = 20 where id = 1;
update user set id = 2 where id = 1;
commit;
2
3
4
5
这三个操作会产生4个undo log,如下图所示
# 4、慢查询日志
快和慢都是相对来说的,对于SQL来说,执行时间多久算的上慢?这种没有统一的标准,都是根据业务场景来具体判断,Mysql可以进行参数配置来定义超过多久时间的SQL算是慢SQL
-- 慢查询日志状态
show variables like "slow_query_log";
-- 开启慢查询日志
set global slow_query_log = "ON";
-- 查看慢查询日志存储位置
show variables like "slow_query_log_file";
-- 修改日志存储位置和日志名称
set global slow_query_log_file = ${path}/${filename}.log;
--查看慢查询日志规定时间
show variables like "long_query_time";
--设置慢查询时间 单位 秒
set global long_query_time = 0.5;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17