转载~
日志一般分为逻辑日志与物理日志两类
mysql
数据最终是保存在数据页中的,物理日志记录的就是数据页变更 。「mysql
数据库中日志是重要组成部分,记录着数据库运行期间各种状态信息」。主要有6类:
binlog
)和事务日志(包括重做日志redo log
和撤销日志 undo log
)」。「记录对MySQL数据库执行的更改操作,包括语句的发生时间、执行时长,主要用于数据库恢复和主从复制」。但不记录select、show等不修改数据库的SQL。
「binlog
是逻辑日志,在 mysql Server
层进行记录,属于mysql
全局日志,无论使用什么存储引擎都会记录 binlog
日志」。
「binlog产生的时机」
「事务提交的时候,一次性将事务中的sql语句(一个事物可能对应多个sql语句)按照一定的格式记录到binlog中」。
「binlog记录方式」
「binlog
是通过追加的方式记录数据库执行的写操作(不包括读操作)信息,以二进制保存在磁盘中。」
「开启binlog」
在my.inf主配置文件中添加如下配置
#打开binlog日志 默认是OFF
log_bin=ON
#binlog日志的基本文件名,后面会追加标识来表示每一个文件 例如:mysql-bin.000094
log_bin_basename=/var/lib/mysql/mysql-bin
#指定的是binlog文件的索引文件 管理所有的binlog文件的目录 记录有多少个binlog文件等
log_bin_index=/var/lib/mysql/mysql-bin.index
#或者将以上三个配置合二为一
log-bin=/var/lib/mysql/mysql-bin
#mysql会根据这个配置自动设置log_bin为on状态,自动设置log_bin_index文件为指定文件名后跟.index
「binlog大小设置与过期时间」
「binlog
是通过追加的方式进行写入的,有两个参数需要控制binlog大小设置与过期时间」
[mysqld]
expire_logs_days = 7
max_binlog_size = 104857600
set global max_binlog_size=104857600;#100M
show variables like '%max_binlog_size%';
show variables like '%expire_logs_days%';
expire_logs_days
set global max_binlog_size=104857600;#100M
set global expire_logs_days = 7;
show variables like '%max_binlog_size%';
show variables like '%expire_logs_days%';
「查看配置」
show variables like '%max_binlog_size%';
show variables like '%expire_logs_days%';
「命令配置」
及时生效,重启失效,
「max_binlog_size」
「max_binlog_size
代表每个 binlog
文件的大小,当文件大小达到给定值之后,会生成新的文件来保存日志,默认值是1GB」。
「expire_logs_days」
「mysql自动清除过期binlog日志的天数。默认值为0,表示没有自动删除」。
「配置文件配置」
永久性,需要重启,修改my.conf:
在实际应用中, binlog
的主要使用场景有两个,分别是 「主从复制」 和 「数据恢复」 。
Master
端开启 binlog
,然后将 binlog
发送到各个 Slave
端, Slave
端执行 binlog
从而达到主从数据一致。
mysqlbinlog
工具来恢复数据。
「对于 InnoDB
存储引擎而言,只有在事务提交时才会记录biglog
日志」,此时记录还在内存中,那么 biglog
是什么时候刷到磁盘中的呢?
「mysql
通过 sync_binlog
参数控制 biglog
的刷盘时机,取值范围是 0-N
:」
commit
的时候都要将 binlog
写入磁盘;binlog
写入磁盘。show variables like '%sync_binlog%';
「MySQL 5.7.7
之后版本的 sync_binlog
默认值是 1
,配置为1也是数据最不容易丢失的,但是设置一个大一些的值可以提升数据库性能,因此实际情况下也可以将值适当调大,牺牲一定的一致性来获取更好的性能。」
binlog
日志格式通过 binlog_format
指定,有三种格式,分别为 STATMENT
、 ROW
和 MIXED
。日志。
在
MySQL 5.7.7
之前,默认的格式是STATEMENT
,MySQL 5.7.7
之后,默认值是ROW
。
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
SQL
语句的复制( statement-based replication, SBR
),每一条修改数据的sql语句会记录到binlog
中 。
row-based replication, RBR
),不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了 。
alter table
的时候会让日志暴涨STATMENT
和 ROW
两种模式的混合复制(mixed-based replication, MBR
),一般的复制使用STATEMENT
模式保存 binlog
,对于 STATEMENT
模式无法复制的操作使用 ROW
模式保存 binlog
「重做日志(Redo Log)是InnoDB存储引擎所特有的日志」。了解Redo Log从以下几个方向来看:
「InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,如果每次读写数据都需要磁盘I/O,效率会很低。」
为提高读写效率,InnoDB添加了缓存池(Buffer Pool)作为访问数据库的缓冲,其包含了磁盘中部分数据页的映射。
Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。而Redo Log 解决了这个问题。
mysql
是如何保证一致性」
最简单的做法是在每次事务提交的时候,将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有性能瓶颈,主要体现在两个方面:
Innodb
是以 页
为单位进行磁盘交互的,而一个事务很可能只修改一个数据页里面的几个字节,这个时候将完整的数据页刷到磁盘的话,太浪费资源了!因此 mysql
设计了 redo log
, 「redo log是物理日志,只记录事务对数据页做了哪些修改,而不是某一行或某几行修改成什么样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置),相对而言文件更小并且是顺序IO」。
Redo Log 默认是在事务提交的时候将日志写入磁盘,为什么它比直接将 Buffer Pool中修改的数据写入磁盘要快呢?
当出现实例故障,导致数据未能更新到数据文件,则数据库重启时须redo,重新把数据更新到数据文件。读写事务在执行的过程中,都会不断的产生redo log。默认情况下,重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。
redo log
包括两部分
redo log buffer
),临时性redo log file
),永久性。mysql
每执行一条 DML
语句,先将记录写入 redo log buffer
,后续「某个时间点再一次性将多个操作记录写到 redo log file
」。这种 「先写日志,再写磁盘」 的技术就是 「「预写式日志」」(Write-Ahead Logging
,缩写 WAL)。
「在计算机操作系统中,用户空间( user space
)下的缓冲区数据是无法直接写入磁盘的,中间必须经过操作系统内核空间( kernel space
)的缓冲区( OS Buffer
)」。因此, redo log buffer
写入 redo log file
实际上是「先写入 OS Buffer
,然后再通过系统调用 fsync()
将其刷到 redo log file
中」。
「redo log file刷盘时机」
「mysql
支持三种将 redo log buffer
写入 redo log file
的时机」,可以通过 innodb_flush_log_at_trx_commit
参数配置:
mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set, 1 warning (0.01 sec)
innodb_flush_log_at_trx_commit
= 0
【「延迟写」】:「提交事务时不会立即将数据redo log buffer
写入到 OS Buffer
,而是通过 InnoDB 的主线程每秒写入 OS Buffer
并调用 fsync()
将其刷到 redo log file
中」。
innodb_flush_log_at_trx_commit
= 1
【「实时写实时刷」】:「每次提交事务时都会将数据redo log buffer
写入到 OS Buffer
并立即调用 fsync()
将其刷到 redo log file
中」。
innodb_flush_log_at_trx_commit
= 2
【「实时写延迟刷」】:「每次提交事务时都会将数据redo log buffer
写入到 OS Buffer
中,然后每间隔一秒调用 fsync()
将其刷到 redo log file
中」
「在innodb中,既有redo log
需要刷盘,还有 数据页
也需要刷盘, redo log
存在的意义主要就是降低对 数据页
刷盘的要求」 。
「redo log
实际上记录数据页的变更,而这种变更记录是没必要全部保存,因此 redo log
实现上采用了【大小固定,循环写入】的方式,当写到结尾时,会回到开头循环写日志」。如下图:
write pos
:表示 redo log
当前记录的 LSN
(逻辑序列号)位置check point
:表示 「数据页更改记录」 刷盘后对应 redo log
所处的 LSN
(逻辑序列号)位置。write pos
到 check point
之间的部分是 redo log
空着的部分,用于记录新的记录;
check point
到 write pos
之间是 redo log
待落盘的数据页更改记录。
当 write pos
追上check point
时,会先推动 check point
向前移动,空出位置再记录新的日志。
启动
innodb
的时候,无论上次是正常关闭或异常关闭,都会进行恢复操作。 重启innodb
时,首先会检查磁盘中数据页的LSN
,如果数据页的LSN
小于日志中的LSN
,则会从checkpoint
开始恢复。 如果在宕机前正处于check point
的刷盘过程,且「数据页的刷盘进度超过了日志页的刷盘进度」,此时会出现数据页中记录的LSN
大于日志中的LSN
,这时超出日志进度的部分将不会重做,因为这本身就表示已经做过的事情,无需再重做。
redo log 主要用来。binlog是用来进行归档的。
「一个更新的sql先执行到redo log内为预提交状态,binlog写入,写入之后通知redo log改提交状态」
世间没有后悔药,但是在MySQL中实现了重头开始,撤销日志(Undo Logs)就是MySQL的后悔药。
「撤消日志是在事务开始之前保存的被修改数据的备份,用于回滚事务」。
撤消日志属于逻辑日志,根据每行记录进行记录。
撤消日志存在于系统表空间、撤消表空间和临时表空间中。
Undo:意为撤销或取消,undo即返回指定某个状态的操作
「undo log」
「一种用于撤销回退的日志,在事务开始之前,会先记录存放到 Undo 日志文件里,备份起来,当事务回滚时或者数据库崩溃时用于回滚事务」。
「undo log记录的是什么」
「undo log中记录的是当前事务操作中的相反操作」。
undo日志属于逻辑日志,记录的是一个操作过程,sql执行delete或者update操作都会记录一条undo日志
一条insert语句在undo log中会对应一条delete语句,
一条delete语句在undo log中会对应一条insert语句,
update语句会在undo log中对应相反的update语句,
「Undo log的存储由InnoDB存储引擎实现」,数据保存在InnoDB的数据文件中,innodb存储引擎对undo的管理采用回滚段(rollback segment)的数据结构。
回滚段(rollback segment)中有1024个undo log segment,
mysql> show variables like '%innodb_undo%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_undo_directory | .\ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+-------+
4 rows in set, 1 warning (0.03 sec)
innodb_undo_directory
定义存储的目录路径,默认值.\,表示datadir,
datadir参数在my.in中配置
[mysql]
# 设置mysql数据库的数据的存放目录
datadir="D:\mysql-8.0.13-winx64\data"
innodb_undo_log_truncate
开启1(ON)/关闭0(OFF)在线回收(收缩)undo log日志文件,支持动态设置,默认关闭
innodb_undo_logs
回滚段rollback segment的数量,Mysql5.5版本后默认设置为128
innodb_undo_tablespaces
默认值为0,表示undo log全部写入一个表空间文件,可以设置这个变量,平均分配到多少个文件中。
「undo log在事务开启之前产生,当事务提交后,InnoDB会将事务对应的undo日志保存在删除list中,后台通过清除线程进行回收处理」。
以一条sql执行update、select过程,如图:
「先来回顾一下几个日志的主要作用」
「准备更新一条数据到事务的提交的流程描述:」
「在回滚段中,每个 undo log 段都有一个类型字段,共有两种类型」:
insert
新记录时产生的undo log
, 其回滚段类型为 insert undo logs,仅用于事务回滚,并且在事务提交后可以被立即丢弃」。update
或delete
时产生的undo log
,其回滚段类型为 update undo logs; 不仅在事务回滚时需要,在实现MVCC快照读时也需要」Undo log的清理也分为两种情况
上述提到的 「purge 线程,是一个周期运行的垃圾收集线程,主要用来收集 undo log 段」。
「错误日志(error log):记录mysql服务的启停时正确和错误的信息,还记录启动、停止、运行过程中的错误信息。」
「指定错误日志文件」
「在my.ini的[mysqld]下:添加代码:log-error=file_name.txt。」
log-error=E:\log-error.txt。
「如果没有指定file_name,则默认的错误日志文件为datadir目录下的 hostname
.err ,hostname表示当前的主机名。」
「查看错误日志位置」
show variables like 'log_error';
「查询日志分为一般查询日志和慢查询日志」。通过查询是否超出变量「long_query_time」指定时间的值来判定的。
「在超时时间内完成的查询是一般查询,可以将其记录到一般查询日志中,超出时间的查询是慢查询,可以将其记录到慢查询日志中。」
「long_query_time」
# 指定慢查询超时时长,超出此时长的属于慢查询,会记录到慢查询日志中
long_query_time = 10
# 定义一般查询日志和慢查询日志的输出格式,不指定时默认为file
log_output={TABLE|FILE|NONE}
「记录了服务器接收到的每一个查询或是命令」,无论这些查询或是命令是否正确甚至是否包含语法错误,general log 都会将其记录下来 。
开启General logmysql服务器需要不断地记录日志,会产生一定的系统开销。 所有Mysql默认关闭一般查询日志。
「开启general log」
set global general_log=on;#为全局变量
「开启general log」
set global general_log=off;
「查看general log是否开启」
show global variables like 'general_log';
「设置日志文件路径」
默认是库文件路径下主机名加上.log
set global general_log_file='/tmp/general.log';
「查看日志输出格式」
show variables like 'log_output';
「慢查询日志记录执行时间超过long_query_time和没有使用索引的查询语句,并且只会记录执行成功的语句。」
「查询超出变量 long_query_time 指定时间值的为慢查询。不包含查询获取锁(包括锁等待)的时间」。
「查看慢查询指定时间」
「默认10s」
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
「查看慢查询的条数」
mysql> show status like "%slow_queries%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 4 |
+---------------+-------+
1 row in set (0.00 sec)
以上Slow_queries = 4 说明查询超过10秒的查询有4个
「启用慢查询日志」
1与ON等价,0与OFF等价。
mysql> set @@global.slow_query_log=on;
#或者
mysql> set @@global.slow_query_log=1;
「查看是否启用慢查询日志」
show variables like 'slow_query%';
slow_query_log={1|ON|0|OFF} 与 log_slow_queries={``yes``|no} 都是表示是否启用慢查询日志,两个同时变化。
「【MySQL记录慢查询日志是在查询执行完毕且已经完全释放锁之后记录的】,因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(先执行完先记录)」。
「主要作用:主从复制」
「【从服务器I/O线程】将主服务器的【二进制日志】读取过来记录到从服务器本地文件,然后【从服务器SQL线程】会读取relay-log日志的内容并应用到从服务器,从而使从服务器和主服务器的数据保持一致」
show variables like '%relay%';