MySQL 系统文件你了解吗

日志文件

• 查看错误日志

show variables like '%log_error%'

• 查看通用查询日志

show variables like '%general%'

• 二进制日志binary log 记录执行的更改操作

show variables like '%log_bin%';//是否开启
show variables like 'binlog' ;//参数查看
show binary logs; //查看日志文件

• 慢查询日志

show variables like '%slow_query%'; //参数是否开启
show variables liike '%long_query_time%';//时长
set long_query_time=5; // 可以修改值的大小

Undo log

• 介绍
  Undo: 意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作
  Undo log: 数据库事务开始之前，会将要修改的记录存到Undo日志里，当事务回滚或者数据库崩溃 时，可以利用Undo日志，撤销未提交事务对数据库产生的影响
  Undo Log 产生和销毁： Undo Log在事务 开始前产生，事务在提交时不会 立即删除Undo Log，innodb会将该事务对应的Undo Log放到删除列表中，后面通过后台线程 purge thread 进行回收处理。Undo Log属于一个逻辑日志，记录一个变化过程。例如执行一个delete，Undo Log 会 记录一个insert，执行一个update，Undo Log会记录一个相反的update
  Undo log 存储：Undo Log 采用段的方式进行管理和记录，在innodb数据文件中包含一种rollback segment 回滚段，内部包含1024个Undo Log segment

• 作用

  • 实现事务的原子性
    Undo Log是为了实现事务的原子性而出现的产物，事务处理过程中，如果出现了错误或者 用户执行了 rollback语句，MySQL可以使用Undo Log中的备份将 事务恢复到事务开始之前的状态
  • 实现多版本并发控制（MVCC）
    Undo Log 在MySQL innoDB引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log中的数据可以作为数据旧版本的快照供其他并发事务 进行快照读

    show variables like '%innodb_undo%';
  

Redo log

• 介绍

  Redo ： 顾名思义就是重做，以恢复操作为目的，在数据库发生意外时重现操作

  Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位（Redo Log），被称为重做日志

  Redo Log的生成和释放：随着事务的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入到磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log的使命也就完成了，Redo Log占用的空间就可以重用，被覆盖写入。

• 工作原理

  Redo Log是为了实现事务的持久性而出现的产物，防止在发生故障的时间点，尚有脏页未写入表的IBD文中，在重启MySQL服务的时候，根据Redo Log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性

• Redo Log写入机制
  Redo Log 文件内容是以顺序循环 的方式写入文件，写满时则回溯到第一个文件，进行覆盖写
  

  如上面简图

  • write Pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾就回到0号文件开头继续写

  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

    write pos 和checkpoint 之间还空着的部分，可以用来记录新的操作，如果write pos追上checkpoint，表示写满，这时候就不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下

• Redo Log相关配置参数

  每个iinnoDB存储引擎至少有一个重做日志文件组(Group),每个文件组至少有 2 个重做日志文件,默认为ib-logfile0和 ib-logfile1可以通过下面查看控制 Redo Log 存储

  show variables like '%innodb_log%';
  

  Redo Buffer 持久化到 Redo Log 的策略,可通过 innodb_flush_log_at_trx_commit 设置:

  • 0: 每秒提交Redo buffer -> OS cache -> flush cache to disk ,可能丢失一秒内的事务数据,由后台 master 线程每隔一秒执行一次操作

  • 1(默认值): 每次事务提交执行 Redo Buffer -> OS Cache -> flush cache to dish ,最安全,性能最差的方式

  • 2: 每次事务提交执行 Redo Buffer -> OS ,然后由后台 Master 线程每隔一秒执行 OS cache -> flush cache to disk 的操作

    一般建议选取值 2,因为 MySQL 挂了数据没有损失,整个服务器挂了才会损失一秒的提交数据.

Bin Log

• BinLog 记录模式

  Redo Log 是属于 InnoDB引擎所特有的日志,而 MySQl Server 也有自己的日志,即 Binary Log(二进制日志),简称 BinLog, BinLog 是记录所有数据库表结构变更一级表数据修改的二进制日志,不会 SELECT 和 SHOW 这类操作.BinLog 日志是以事件形式记录,还包含语句所执行 的消耗事件.开启 BinLog 日志有以下两个使用场景

  • 主从复制 在主库中开启 BinLog 功能,这样主库就可以把 BinLog 传递给从库,从库拿到 BinLog后实现数据恢复到主从数据一致性
  • 数据恢复 通过 mysqlbinlog 工具来恢复数据

  BinLog 文件名默认为主机名 _ binlog-序列号格式,例如tz_binlog-000001,也可以在配置文件中指定名称.

  文件记录模式有 STATEMENT 、ROW、MIXED 三种,具体含义如下

  • ROW(row-based replication ,RBR) : 日志中会记录每一行数据被修改的 情况 ,然后在 slave 端对相同的数据进行修改

    优点: 能清楚记录每一个行数据的修改细节,能完全实现主从数据同步和数据的恢复

    缺点: 批量操作,会产生大量的日志,尤其是 alter table 会让日志暴涨

  • STATMENT(statement-based replication,SBR): 每一条被修改数据的 SQL 都会记录到 master 的 BinLog 中,slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL再次执行.简称 SQL 语句复制

    优点: 日志量小,减少磁盘 IO,提升存储 和恢复速度

    缺点: 在某些情况下会导致主从数据不一致,比如 last_insert_id(),now()等函数

  • MIXED(mixed-based replication,MBR): 以上两种模式的混合使用,一般 会使用 STATEMENT 模式保存 Binlog,对于 STATEMENT 模式无法复制的操作使用 ROW 模式 保存 Binlog,MySQL 会根据执行 的 SQL 语句选择写入模式

• BinLog 文件结构

  MySql的 Binlog 文件中记录的是对数据库的修改操作,用来表示修改操作的数据结构是 Log event ,不同的修改操作对应不同的 Log event.比较常用的 log event 有:Query event,Row event,Xid event 等.

  Binlog 文件的内容就是各种 Log event 的集合

  BinLog文件中 Log event 结束如下所示:

  结构	说明
  timestamp 4字节	事件开始的执行时间
  Event Type 1 字节	指明该事件的类型
  server_id 1 字节	服务器的 server ID
  Event size 4 字节	该事件的长度
  Next_log pos 4 字节	固定 4 字节下一个 event 的开始位置
  Flag 2 字节	固定 2 字节的 event flags
  Fixed part	每种 Event Type 对应结构体固定的结构部分
  Variable part	每种 Event Type 对应结构体可变的结构部分

• Binlog 写入机制

  • 根据记录模式和操作触发 event 事件生成 log event(事件触发执行机制)

  • 将事务执行过程中产生 log event 写入缓冲区,每个事务线程都有一个缓冲区

    Log event 保存在一个 binlog_cache_mngr 数据结构中,在该结构中有两个缓存区,一个是 stmt_cache,用于存放不支持事务的信息,另一个是 trx_cache,用于存放支持事务 的信息

  • 事务在提交阶段会将产生的 log event 写入到 外部的 binlog 文件中,不同的事务是以串行的方式将 log event 写入到 binlog 中,所以一个事务包含的 log event 信息在 binlog 文件中是连续的,中间不会插入其它事务的了log event,binlog 是引擎插件上层的功能,事务提交第一个就会调用 binlog 功能接口,然后在调用其他引擎的功能接口,因此先写 binlog然后在执行 innodb 的 redo log/undo 和脏页刷新操作

• Binlog 文件操作

  • Binlog 状态查看

    show variables like '%log_bin%';
    

  • 开启 Binlog 功能

    set global log_bin=1;
    ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
    

    需要修改 my.cnf 或者 my.ini 配置文件,在[mysqld]下面增加 log_bin=mysql_bin_log,重启 MySQL 服务

    #log-bin=ON
    #log-bin-basename=mysqlbinlog
    binlog-format=ROW
    log-bin=mysqlbiinlog
    

  • 使用show binlog events命令

    show binary logs;//show master logs;
    show master status;
    show binlog events;
    show binlog events in 'mysqlbinlog.000001';
    

  • 使用 mysqlbinlog 命令

    mysqlbinlog '文件名'
    mysqlbinlog '文件名' > 'test.sql'
    

  • 使用binlog 恢复数据

    // 按照指定时间恢复
    mysqlbinlog --start-datetime="2020-12-09 18:00:00" --stop-datetime="2020-12-09 19:00:00" mysqlbinlog.000002 | mysql -uroot -p 密码
    // 按事件位置号恢复
    mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p 密码
    

  • 删除 Binlog 文件

    // 删除指定文件
    purge binary logs to 'mysqlbinlog.000001';
    // 删除指定 时间之前的
    purge binary logs before '2020-12-09 00:00:00';
    // 删除所有文件
    reset master;
    

    可以通过设置 expire_logs_days 参数来启动自动清理功能,默认值为 0 表示没启用,设置为 1 表示超出一天binlog 文件自动清除掉

• Redo log 和 BinLog 区别

  • Redo Log 是属于 InnoDB 引擎功能,Binlog 是属于 MySQL Server 自带功能,并且是二进制文件记录
  • Redo Log 属于逻辑日志,记录该数据页更新状态内容,BinLog 是逻辑日志,记录更新过程
  • Redo Log 日志是循环写,日志空间是固定的,BinLog 是追加写,写到一定文件大小写入下一个文件,文件大小可设置
  • Redo Log 作为服务器异常宕机后事务恢复使用,BinLog 可以作为主从复制和数据恢复使用,BinLog 没有自动 crash-safe 能力

数据文件

查看文件存储位置

show variables like '%datadir%';

• db.opt文件： 记录这个库默认使用的字符集和校验规则

• frm文件： 存储与表相关的元数据（meta）信息，包括表结构的定义信息，每一张表都会有一个frm文件

• MYD文件： MyISAM引擎专用，存放MyISAM表的数据，每一张表都会有一个MYD文件

• MYI文件： MyISAM引擎专用，存放MyISAM表的索引相关信息，每一张表都会有一个MYI文件

• ibd文件和IBDATA文件： 存放innoDB的数据文件包括索引，innoDB存储引擎有两种表空间方式，独享表空间和共享表空间。独享表空间使用.ibd文件存放数据，每一张表对应一个.ibd文件。共享表空间使用.ibdata文件，所有表共同使用一个（或者多个，可以配置）.ibdata文件

• ibdata1文件： 系统表空间数据文件，存储表元数据，Undo日志等

• ib_logfile0，ib_logfile1文件： redo log日志文件

pid 文件

pid文件是mysqld在unix/linux环境下的进程文件，和其他unix/linux服务端程序一样，存放自己的进程id

socket文件

socket文件也是在unix/linux环境下才有的，用户在unix/linux环境下客户端链接可以不使用tcp/ip，而直接使用unix socket 来链接mysql