宿迁做网站多少钱,上海哪里有做网站的,网易工作做网站工资奖金高吗,青海门户网站建设【MySQL系列】- MySQL日志详解 文章目录 【MySQL系列】- MySQL日志详解一、My日志分类二、重做日志#xff08;redo log#xff09;redo 日志格式简单日志格式复杂的 redo 日志格式 三、回滚日志#xff08;undo log#xff09;undo log的作用undo log数据结构 四、二进制日…【MySQL系列】- MySQL日志详解 文章目录 【MySQL系列】- MySQL日志详解一、My日志分类二、重做日志redo logredo 日志格式简单日志格式复杂的 redo 日志格式 三、回滚日志undo logundo log的作用undo log数据结构 四、二进制日志bin log4.1 什么是 Bin Log4.2 应用场景4.3 开启Binlog 五、错误日志error logerror log的参数: 六、慢查询日志Slow Query Log参数说明如下: 七、一般查询日志general log如何开启 general log配置文件中永久开启SQL命令行中临时开启 八、一般查询日志general log中继日志的作用查看中继日志 对于线上数据库应用系统突然遭遇数据库宕机的情况下可以查看数据库错误日志来定位具体的问题。因为日志中记录了数据库运行中的诊断信息包含错误警告与注解等信息。 数据日志除了可以发现错误日志在数据复制数据恢复操作审计及确保数据的永久性和一致性等方面都有着不可替代的作用。
日志作用非常大很多奇怪的问题都可以通过日志找到答案。所有在MySQL中要养成查看日志的习惯提升数据开发运维的能力。
一、My日志分类
回滚日志undo log重做日志redo log二进制日志bin log错误日志error log慢查询日志show query log一般查询日志general log中继日志relay log
下面会结合场景详解讲解每个日志应用及其使用场景
二、重做日志redo log
redo 日志是为了在系统因奔溃而重启时恢复奔溃前的状态而提出的。lnnoDB 存储引擎是以页为单位来管理存储空间的我们进行的增删改查操作从本质上来说都是在访问页面包括读页面、写页面、创建新页面等操作。在真正访问页面之前需要先把在磁盘中的页加载到内存中的[[Buffer Pool]] 中之后才可以访问。事务的持久性的特征要求对于一个己经提交的事务在事务提交后即使系统发生了崩溃这个事务对数据库所做的更改也不能丢失。 如果我们只在内存的 Buffer Pool 中修改了页面假设在事务提交后突然发生了某个故障导致内存中的数据都失效了那么这个已经提交的事务在数据库中所做的更改也就跟着丢失了这是我们所不能忍受的。那么如何保证这个持久性呢一个很简单的做法就是在事务提交完成之前把该事务修改的所有页面都刷新到磁盘。不过这个简单粗暴的做法存在下面这些问题
刷新一个完整的数据页太浪费了。有时我们仅仅修改了某个页面中的一个字节但是由于 InnoDB 是以页为单位来进行磁盘 I/O 的也就是说在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘。一个页面的默认大小是 16 KB因为修改了一个字节就要刷新 16KB 的数据到磁盘上显然太浪费了。随机 I/O 刷新起来比较慢。一个事务可能包含很多语句即使是一条语句也可能修改许多页面而且该事务修改的这些页面可能并不相邻。这就意味着在将某个事务修改的 Buffer Pool 中的页面刷新到磁盘时需要进行很多的随机 I/O。随机 I/O 比顺序 I/O 要慢尤其是对于传统的机械硬盘。
只是想让已经提交了的事务对数据库中的数据所做的修改能永久生效即使后来系统崩溃在重启后也能把这种修改恢复过来。所以没有必要在每次提交事务时就把该事务在内存中修改过的全部页面刷新到磁盘只需要把修改的内容记录一下就好。这样在事务提交时就会把上述内容刷新到磁盘中。即使之后系统崩溃了重启之后只要按照上述内容所记录的步骤重新更新一下数据页那么该事务对数据库中所做的修改就可以被恢复出来这样也就意味着满足持久性的要求。
因为在系统因崩溃而重启时需要按照上述内容所记录的步骤重新更新一下数据页所以上述内容也称为重做日志 redo log。 我们也可以将它称为 redo 日志。相较于在事务提交时将所有修改过的内存中的页面刷新到磁盘中只将该事务执行过程中产生的 redo 日志刷新到磁盘具有下面这些好处 redo 日志占用的空间非常小在存储表空间 ID、页号、偏移量以及需要更新的值时需要的存储空间很小。 redo 日志是顺序写入磁盘在执行事务的过程中每执行一条语句就可能产生若干条 redo 日志这些日志是按照产生的顺序写入磁盘的也就是使用顺序 I/O。
redo 日志格式
redo 日志本质上只是记录了一下事务对数据库进行了哪些修改。InnoDB 针对事务对数据库的不同修改场景定义了多种类型的 redo 日志但是绝大部分类型的 redo 日志都有如下图所示的这种通用结构.
type这条 redo 日志的类型。space ID表空间 ID。page number页号。data这条redo日志的具体内容。
简单日志格式
如果没有为某个表显式地定义主键并且表中也没有定义不允许存储 NULL 值的 UNIQUE 键那么 InnoDB 会自动为表添加一个名为 row_id 的隐藏列作为主键。为这个 row_id 隐藏列进行赋值的方式如下
服务器会在内存中维护一个全局变量每当向某个包含 row_id 隐藏列的表中插入一条记录时就会把这个全局变量的值当作新记录的 row_id 列的值并且把这个全局变量自增 1。每当这个全局变量的值为 256 的倍数时就会将该变量的值刷新到系统表空间页号为 7 的页面中一个名为 Max Row ID 的属性中 之所以不是每次自增该全局变量时就将该值刷新到磁盘是为了避免频繁刷盘。当系统启动时 会将这个 Max Row ID 属性加载到内存中并将该值加上 256 之后赋值给前面提到的全局变量因为在系统上次关机时该全局变量的值可能大于磁盘页面中 Max Row ID 属性的值。
Max Row ID 属性占用的存储空间是 8 字节。当某个事务向某个包含 row id 隐藏列的 表插入一条记录并且为该记录分配的 row_id 值为 256 的倍数时就会向系统表空间页号为 7 的页面的相应偏移量处写入 8 字节的值。这个写入操作实际上是在 Buffer Pool 中完成的我们需要把这次对这个页面的修改以 redo 日志的形式记录下来。这样在事务提交之后即使系统崩溃了也可以将该页面恢复成崩溃前的状态。在这种对页面的修改是极其简单的情况下redo 日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值、具体修改后的内容是啥就好了。InnoDB 把这种极其简单的 redo 日志称为物理日志并且根据在页面中写入数据的多少划分了几种不同的 redo 日志类型。
MLOG_1BYTEtype 字段对应的十进制数字为 1表示在页面的某个偏移量处写入 1 字节的 redo 日志类型。MLOG_2BYTEtype 字段对应的十进制数字为 2表示在页面的某个偏移量处写入 2 字节的 redo 日志类型。MLOG_4BYTEtype 字段对应的十进制数字为 4表示在页面的某个偏移量处写入 4 字节的 redo 日志类型。MLOG_8BYTEtype 字段对应的十进制数字为 8表示在页面的某个偏移量处写入 8 字节的 redo 日志类型。MLOG_WRITE_STRlNGtype 字段对应的十进制数字为 30表示在页面的某个偏移处写入一个字节序列。
Max Row ID 属性实际占用 8 字节的存储空间所以在修改页面中的这个属性时会记录一条类型为 MLOG_8BYTE 的 redo 日志。
【插入日志格式图1】
其余的 MLOG_1BYTE、MLOG_2BYTE、MLOG_4BYTE 类型的 redo 日志结构与 MLOG_8BYTE 的日志结构类似只不过具体数据中包含的字节数量不同罢了。MLOG_WRITE_STRlNG 类型的 redo 日志表示写入一个字节序列但是因为不能确定写入的具体数据占用多少字节所以需要在日志结构中添加一个 len 字段。
【插入日志格式图2】
复杂的 redo 日志格式 MLOG_REC_INSERTtype 字段对应的十进制数字为 9表示在插入一条使用非紧凑行格式 REDUNDANT的记录时redo 日志的类型。 MLOG_COMP_REC_INSERT type 字段对应的十进制数字为 38 表示在插入一条使用紧凑行格式 COMPACT、DYNAMIC、COMPRESSED 的记录时redo 日志的类型。 MLOG_COMP_PAGE_CREATEtype 字段对应的十进制数字为 58表示在创建一个存储紧凑行格式记录的页面时redo 日志的类型。 MLOG_COMP_REC_DELETEtype 字段对应的十进制数字为 42 表示在删除一条使用紧凑行格式记录时redo 日志的类型。 MLOG_COMP_LIST_START_DELETEtype 字段对应的十进制数字为 44表示在从某条给定记录开始删除页面中一系列使用紧凑行格式的记录时redo 日志的类型。 MLOG_COMP_LIST_END_DELETEtype 字段对应的十进制数字为 43与 MLOG_COMP_LIST_START_DELETE 类型的 redo 日志呼应表示删除一系列记录直到 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志对应的记录为止。数据页中的记录按照索引列大小的顺序组成单向链表。有时我们需要删除索引列的值在某个区间内的所有记录这时如果每删除一条记录就写一条 redo 日志效率可能有点低。MLOG_COMP_LUST_START_DELETE 和 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志可以很大程度上减少 redo 日志的条数。 MLOG_ZlP_PAGE_COMPRESStype 字段对应的十进制数字为 51:表示在压缩一个数据页时 redo 日志的类型。
复杂Redo日志既包含物理层面的意思也包含逻辑层面的意思
从物理层面看这些日志都指明了对哪个表空间的哪个页进行修改从逻辑层面看在系统崩溃后重启时并不能直接根据这些日志中的记载在页面内的某个偏移量处恢复某个数据而是需要调用一些事先准备好的函数在执行完这些函数后才可以将页面恢复成系统崩溃前的样子
三、回滚日志undo log
回滚日志保存了事务发生之前的数据的一个版本用于事务的回滚操作同时也是实现多版本并发控制MVCC下读操作快照读的关键技术。
undo log的作用
事务发生错误时回滚rollback数据更新之前会把原始数据保存在回滚日志中保证事务出错回滚或者我们手动回滚的时候能够在回滚日志中找到最初的数据。提供了MVCC的非锁定读快照读依赖undo log实现。
undo log数据结构
DB_ROW_ID和MVCC的关系不大这个是我们创建表的时候如果没有加PRIMARY KEY那么InnoDB就自动生成主键列idDB_ROW_ID通过这个id作为主键创建索引树在B树的叶子节点上存放数据。由于InnoDB的数据和索引是存放在一起的如果我们没有设置主键InnoDB会自动生成主键。DB_TRX_ID事务ID我们每打开一个客户端session输入begin向服务器请求开启一个事务。事务开启请求发到MySQL server上MySQL server为每个事务都会分配一个全局的不冲突的事务IDInnoDB存储引擎分配的因为它才支持事务。当某个事务修改数据时DB_TRX_ID放的就是该事务的ID同一个事务无论怎么更改数据这个事务ID都不会改变。DB_ROLL_PTR回滚指针我们看到存放的是地址这个地址表示的是一个数据的内存的位置。
四、二进制日志bin log
4.1 什么是 Bin Log
Bin Log是一个二进制格式的文件是MySQL最重要的日志它记录了所有的DDL及DML语句除了数据查询select、show等以事件形式记录还包含语句所执行的消耗时间MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
4.2 应用场景
MySQL主从复制MySQL Replication在Master端开启binlogMaster把它的二进制日志传递给slaves来达到master-slave数据一致的目的数据恢复通过使用 mysqlbinlog工具来使恢复数据。
4.3 开启Binlog
一般来说开启binlog日志大概会有1%的性能损耗。启用binlog通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项在配置文件中加入 log-bin配置表示启用binlog如果没有给定值写成 log-bin则默认名称为主机名。注名称若带有小数点则只取第一个小数点前的部分作为名称
[mysqld]
log-binmy-binlog-name也可以通过SET SQL_LOG_BIN 1命令来启用Bin Log通过SET SQL_LOG_BIN 0 命令停用Bin Log
启用 binlog 之后须重启MySQL才能生效。
五、错误日志error log
rror log记录中MySQL启动和关闭服务的时候详细的日志信息包括在MySQL实例在运行过程中遇到的错误、警告等信息也会记录在error log中。需要注意的是这里的错误和警告并不是指我们在执行某些SQL语句的时候遇到的错误信息和警告信息而是MySQL底层进程级别的错误和警告信息。
对于error log我们平时使用的最多的场景是这样的在为了满足某些需求的时候我们需要添加或修改my.cnf配置文件中参数然后重试重启MySQL实例如果此时MySQL启动失败我们会经常去看下error log中的错误提示信息然后根据错误提示去做对应的调整。最后再次尝试重启MySQL服务。此时的error log会给我们提供很大的帮助。
error log的参数:
log_error 这个参数控制着错误日志到底输出到哪里。如果是输出到控制台中则该参数的值stderr这表示是standard error标准错误输出也就是会把error log写到当前的命令行控制台中。如果不是把错误日志输出到stderr则需要配置一个具体的文件路径和名称。例如把错误日志输出到/tmp/mysql_error.log文件中。此时我们定义了路径为/tmp错误日志的文件名称为mysql_error.log。一般情况下在使用yum和apt-get方式安装MySQL数据库的时候MySQL的error log一般都是默认输出到/var/log/mysql目录下。log_error_verbosity这个参数控制着error log的输出级别。参数取值为1、2、3中的其中一个值默认值为3。建议使用默认值3。三个参数所代表的日志级别如下 1只输出error级别的日志到error log中。2输出error和warning级别的日志到error log中。3输出error和warning还有note级别的日志到error log中。 log_timestamps这个参数控制着日志文件中输出的每一行日志中的时间戳是什么样的。这个参数的默认值为UTC如果发现日志文件中时间戳和当前数据库服务器的系统时间不一致这里说一个我经常遇到的问题就是发现所有的日志信息都是和服务器的时间差8个小时就是因为log_timestamps参数的值为UTC把它改为system之后发现日志中时间戳就和当前系统时间一致了。通常需要修改这个参数为SYSTEM修改后此时的日志总的时间戳就和服务器的系统时间一致了。这个参数不仅仅适用于error log像前面我们介绍的slow query log慢查询日志中时间戳也是受这个参数控制。
六、慢查询日志Slow Query Log
日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志可以查找出哪些查询语句的执行效率低以便进行优化。
通俗的说MySQL 慢查询日志是排查问题的 SQL 语句以及检查当前 MySQL 性能的一个重要功能。如果不是调优需要一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。
默认情况下慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能。命令和执行过程如下
SHOW VARIABLES LIKE slow_query%;
SHOW VARIABLES LIKE long_query_time;参数说明如下:
**slow_query_log**慢查询开启状态**slow_query_log_file**慢查询日志存放的位置一般设置为 MySQL 的数据存放目录**long_query_time**查询超过多少秒才记录
七、一般查询日志general log
在默认情况下MySQL是不会打开general log的这个log里面会记录MySQL所有的SQL语句不管是查询语句还是DML语句还是DDL语句还是DCL语句这些语句统统都会被记录在general log文件中。就连我们连接和断开MySQL数据库的这些语句。
MySQL会把它收到的所有SQL语句按照接收的顺序依次记录在general log中。我们需要注意的是这里接受的SQL语句的顺序并不等于SQL语句就是按照这个接受的顺序来执行因为有的时候一些SQL可能需要等待其他锁被释放后才会被真正的执行SQL语句的执行顺序是和binlog中的顺序是相匹配的。
General log默认不开启的原因有两个
日志将会非常大对磁盘是一个很大的压力。因为所有的操作都会被记录下来对MySQL数据的性能有一定的影响。
如何开启 general log 配置文件中永久开启 [mysqld]
general_log1
general_log_filemysql_general_log.log
log_output/var/lib/mysqlSQL命令行中临时开启 show variables like general_log; -- 查看日志是否开启
show variables like general_log_file; -- 看看日志文件保存位置
show variables like log_output; -- 看看日志输出类型 table或file
set session general_logon; -- 这是一个全局参数不能只针对当前session启用。set global general_logon; -- 对全局开启general log八、一般查询日志general log
中继日志relay log只在主从服务器架构的从服务器上存在。从服务器Slave为了与主服务器(Master)保持一致要从主服务器读取二进制日志的内容并且把读取到的信息写入本地的日志文件中这个从服务器本地的日志文件就叫中继日志。然后从服务器读取中继日志并根据中继日志的内容对从服务器的数据进行更新完成主从服务器的数据同步起到了中介作用。
搭建好主从服务器之后中继日志默认会保存在从服务器的数据目录下。
文件名的格式是从服务器名 - relay-bin.序号。中继日志还有一个索引文件从服务器名 - relay-bin.index用来定位当前正在使用的中继日志。
中继日志的作用
中继日志用于主从服务器架构中从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容来同步主服务器上的操作。
中继日志是连接mastert(主服务器)和slave(从服务器)的信息它是复制的核心I/O线程将来自master的binlog存储到中继日志中中继日志充当缓冲这样master不必等待slave执行完成就可以发送下一个binlog。
查看中继日志
中继日志文件的格式与二进制日志文件相同并且可以使用mysqlbinlog进行读取。
