拼多多网店注册,郑州网站建设优化公司,php 网站 整合 数据库,网络营销网站源码今天我们通过mysql日志了解mysqld的错误日志、慢查询日志、二进制日志#xff0c;redolog, undolog等。揭示它们的作用和用途#xff0c;让我们工作中更能驾驭mysql。
redo 日志
如果mysql事务提交后发生了宕机现象#xff0c;那怎么保证数据的持久性与完整性#xff1f;…今天我们通过mysql日志了解mysqld的错误日志、慢查询日志、二进制日志redolog, undolog等。揭示它们的作用和用途让我们工作中更能驾驭mysql。
redo 日志
如果mysql事务提交后发生了宕机现象那怎么保证数据的持久性与完整性
mysql 提供了 redo 来防止 数据丢失。
什么是 redo 日志
redo 日志也叫重做日志是一种基于磁盘的数据结构用于记录事务操作变化记录的是数据被修改之后的值在崩溃恢复后恢复事务写入的数据。特别注意redo 日志是inndb 引擎独有的一种日志
mysql 读取数据不是一条一条读取的而是会加载 硬盘中的一页数据放到 缓冲区中后续读取会先看缓冲区是否命中然后决定是否在冲磁盘。在redo 日志里也是会有缓冲区的概念下面我们看看 redo 日志是由哪几个组成的
组成
内存中的重做日志缓冲redo log buffer重做日志文件redo log file
有日志缓冲区有磁盘文件那么他们是怎么更新的呢下面我们就看看redo 日志的刷盘策略
刷盘策略
每次数据更新会先更新 redo log buffer然后根据 innodb_flush_log_at_trx_commit 来控制 redo log buffer 更新到 redo log file 的时机。innodb_flush_log_at_trx_commit 有三个值可选
innodb_flush_log_at_trx_commit0事务提交时在事务提交时每秒触发一次 redo log buffer 写磁盘操作并调用操作系统 fsync 刷新 IO 缓存。 innodb_flush_log_at_trx_commit1事务提交时InnoDB 立即将缓存中的 redo 日志写到日志文件中并调用操作系统 fsync 刷新 IO 缓存 innodb_flush_log_at_trx_commit2事务提交时InnoDB 立即将缓存中的 redo 日志写到日志文件中但不是马上调用 fsync 刷新 IO 缓存而是每秒只做一次磁盘 IO 缓存刷新操作 innodb_flush_log_at_trx_commit三种持久化选项的优劣势
innodb_flush_log_at_trx_commit0如果数据库奔溃有一秒的数据丢失。
innodb_flush_log_at_trx_commit1: InnoDB的默认配置为的是保证事务ACID特性。
innodb_flush_log_at_trx_commit2: 如果操作系统奔溃最多有一秒的数据丢失。
为什么需要 redo
1- 如果刷脏页还未完成MySQL这时候因为某些原因宕机重启后 Buffer Pool中修改的数据还没有及时的刷到磁盘中就会导致数据丢失无法保证事务的持久性。
为redo 就可以解决这个问题redo 记录的是数据库中每个页的修改而不是某一行修改成怎样.。 这样就可以用来恢复提交后的数据物理数据页且只能恢复到最后一次提交的位置。
这样再修改数据时InnoDB引擎会把更新记录先写在redo log中。在修改Buffer Pool中的数据当提交事务时调用fsync把redo log刷入磁盘。
2- 保证事务的持久性对于一个已经提交的事务在事务提交后即使系统发生了崩溃这个事务对数据库中所做的更改也不能丢失。 我很重要
1- 这里redo 日志用到了 WALWrite-Ahead Logging技术这个技术的核心就在于修改记录前一定要先写日志并保证日志先落盘才能算事务提交完成。
2- innodb 因为 redo 日志 具有了crash-safe 的能力 MySQL宕机重启后会自动去检查redo log将修改还未写入磁盘的数据从 redo log 恢复到MySQL中 相关配置
innodb_log_group_home_dir 定义InnoDB日志文件的目录路径。如果未配置此选项 InnoDB则会在 MySQL 数据目录 ( datadir) 中创建日志文件。
innodb_log_file_size要更改日志文件大小
innodb_log_files_in_group要增加日志文件的数量默认和推荐值为 2。
redo 文件怎么样的
从上面配置可以看出redo日志不是一个文件出现的而是以一个文件组出现的。他是一一个环形从头开始写写完又从头开始写 在个日志文件组中还有两个重要的属性分别是 write pos、checkpoint
write pos 是当前记录的位置一边写一边后移checkpoint 是当前要擦除的位置也是往后推移 额外补充
预写日志方式WAL: 数据落盘前需要先写日志比如说当一个事务开始时会记录该事物的LSN(日志序列号)当事务执行的时候会往日志缓存中插入事务日志提交的时候将日志缓存落盘, 这种方式称为预写日志方式checkpoint: checkpoint是为了定期将db buffer的内容刷新到data file。当遇到内存不足、db buffer已满等情况时需要将db buffer中的内容/部分内容特别是脏数据转储到data file中。在转储时会记录checkpoint发生的”时刻“。在故障回复时候只需要redo/undo最近的一次checkpoint之后的操作以下场景出发redo 日志 写文件 3.1 Redo log buffer空间不足时 3.2 事务commit 3.3 mysql 重启 3.4 binlog切换时
binlog
什么是binlog
binlog是数据库Server层和引擎无关)它记录了所有的 DDL数据定义语句和 DML数据操纵语句但是不包括 select 和 show 操作可以看常规查询日志。
作用
恢复某些数据的恢复需要二进制日志如当一个数据库全备文件恢复后我们可以通过二进制日志进行point-in-time的恢复复制通过复制和执行二进制日志使的一台远程的mysql数据库salve与一台mysql数据库master进行实时同步
数据类型
StatementStatement-Based Replication,SBR每一条会修改数据的 SQL 都会记录在 binlog 中。RowRow-Based Replication,RBR不记录 SQL 语句上下文信息仅保存哪条记录被修改。MixedMixed-Based Replication,MBRStatement 和 Row 的混合体。 优点 缺点 Statement 1- 只需要记录执行sql 避免了记录每一行的变化相较于row 能大大减少binlog日志量节约IO提高性能 2- 实时的还原 3- 主从版本可以不一样从服务器版本可以比主服务器版本高 如果sql中包含函数可能会出现执行结果不一致。这样就会导致数据不一致主从备份等等 row 清晰的记录每行数据的修改细节任何情况都可以被复制且能加快从库重放日志的效率保证从库数据的一致性 日志量太大了特别是批量 update、整表 delete、alter 表等操作由于要记录每一行数据的变化此时会产生大量的日志大量的日志也会带来 IO 性能问题。 此外新版的MySQL中对row级别也做了一些优化当表结构发生变化的时候会记录语句而不是逐行 Mixed Mixed level对以上两种类型的结合。不过新版本的MySQL对row level模式也被做了优化 1- 并不是所有的修改都会以row 格式记录如果遇到表结构变更的时候就会以statement模式来记录 2- 如果sql语句确实就是update或者delete等修改数据的语句那么还是会记录所有行的变更因此现在一般使用row level即可 3- 选取规则如果是采用 INSERTUPDATEDELETE 直接操作表的情况则日志格式根据 binlog_format 的设定而记录 4- 如果是采用 GRANTREVOKESET PASSWORD 等管理语句来做的话那么无论如何都采用statement模式记录 需要判断使用使用那种模式需要判断使用哪种格式所以更慢。
启动binlog
二进制日志文件默认的情况下是没有启动的我们需要手动配置log-bin[name]进行启动二进制日志。如果我们不指定name则默认二进制日志文件名为主机名后缀名为二进制日志的序列号。虽然开启二进制日志会对mysql数据库性能有所影响但是这个影响是有限的相对于可以使用复制和point-in-time的恢复这些性能可以接受。 查看当前是否开启
mysql SHOW VARIABLES LIKE log_bin;
----------------------
| Variable_name | Value |
----------------------
| log_bin | OFF |
----------------------
注意
根据mysql官方手册的测试表名开启二进制日志会使得性能下降1%SHOW VARIABLES LIKE datadir 可以查看数据库所在目录写入二进制日志的语句中的密码由服务器重写不会以纯文本形式出现5.7 启动的时候报错You have enabled the binary log, but you havent provided the mandatory server-id. 在配置log-bin同时加一个 server-id
binlog配置
max_binlog_size: 单个二进制日志文件的最大值(mysql5.0开始默认1G)超过该值就产生新的日志文件后缀名1,并记录到.index文件binlog_cache_size: 当一个线程开始一个事务的时候mysql会自动分配一个大小为binlog_cache_size(默认32KB)的缓存等到事务提交的时候直接将缓冲中的二进制日志写入二进制日志文件中。另外如果事务记录大于binlog_cache_size时mysql会将缓冲中的日志写入一个临时文件中。为了更好的设置binlog_cache_size值的大小可以通过SHOW GLOBAL STATUS查看binlog_cache_use, binlog_cache_disk_use来判断当前的的设置是否合理binlog_cache_use 记录使用缓冲写二进制日志的次数binlog_cache_disk_use: 记录了使用临时文件写二进制日志的次数sync_binlog: 表示每写缓冲多少次就同步到磁盘中
sync_binlog0表示每次提交事务都只write不fsync
sync_binlog1表示每次提交事务都会执行fsync
sync_binlogN(N1)表示每次提交事务都write累积N个事务后才fsync 。注意当sync_binlog1时使用innodb存储引擎时在一个事务发出commit动作之前由于sync_binlog1因此会立即将二进制日志文件写入磁盘。如果这时已经写入二进制日志但没有提交并且此时发生宕机那么下次数据库启动时候因commit操作没有发生所以这个事务会被回滚但是二进制日志已经记录了该事务信息不能回滚。这个问题可以通过将参数innodb_support_xa设为1解决。
binlog-do-db: 需要写入哪些库的日志binlog-ignore-db: 忽略写入哪些库的日志binlog_format: 记录二进制日志的格式5.1版本后引入 redo 日志和 binlog日志的区别
redolog 在InnoDB引擎中才有binlog 是MySQL的服务器层实现的任何储存引擎都有binlogRedo日志主要用于崩溃恢复过程保证MySQL宕机也不会影响持久性。Binlog日志主要用于数据恢复、主从复制和数据同步redo log 是物理日志内容基于磁盘的页Pagebin log的内容是二进制可以根据binlog_format参数自行设置。redo log在事务开始时即开始写入并 采用循环写的方式记录binlog 在事务提交时写入并通过追加的方式记录当文件大小大于给定值后后续的日志会记录到新的文件上。redo日志能维持crash-safe 能力 undo log
什么是undo 日志
Undo: 逻辑日志回滚日志将数据库逻辑地恢复到原来的样子所有修改都被逻辑地取消了。Undo存在数据库内部的一个特殊的段undo 段中,undo段位于共享表空间内。undo 日志保证事务的原子性也就是事务中的操作要么全部完成要么什么也不做。
原理
undo日志一般被称为回滚日志一般执行ROLLBACK时会将数据恢复到事务开始的状态。注意我们这里并不是将数据库物理的恢复到执行语句或事务之前的样子而是我们操作一步就会生成一个相反的操作放入undo 段中。比如说当我们insert一条记录的时候那么undo段中也会生成对应delete 一条记录。
作用
实现MVCC隐藏字段、Read View、undo log。在内部实现中InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性如不可见则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。回滚数据undo log记录了每个操作的逆操作可以逻辑恢复数据
undo 类型
InnoDB存储引擎中Undo Log可以分为以下两种类型
insert Undo Log insert Undo Log是指在insert操作中产生的Undo Log。事务具有隔离的特性所以insert操作的记录只对事务本身可见故该Undo Log可以在事务提交后直接删除。update Undo Log update Undo Log记录的是对delete和update操作产生的Undo Log。该Undo Log可能需要提供MVCC机制因此不能在事务提交时就进行删除。提交时放入Undo Log链表等待purge线程进行最后的删除。
Undo Log的配置参数
innodb_max_undo_log_size:Undo日志文件的最大值默认1GB初始化大小10M
innodb_undo_log_truncate:标识是否开启自动收缩Undo Log表空间的操作
innodb_undo_tablespaces:设置独立表空间的个数默认为0标识不开启独立表空间Undo日志保存在ibdata1中
innodb_undo_directory:Undo日志存储的目录位置 innodb_undo_logs: 回滚的个数 默认128 慢日志
概念
执行时间超过 long_query_time 且至少需要 min_examined_row_limit(默认是0) 检查行的 SQL 语句组成的日志文件这个日志文件就叫做慢查询日志。 慢查询日志的主要作用帮助我们发现那些特别耗时SQL让我们有针对性地进行优化从而提高系统的整体效率。如果发现数据库服务器发生阻塞、运行变慢的时候检查一下慢查询日志找到那些慢查询对解决问题很有帮助一般需要结合explain进行全面分析。 默认情况下MySQL数据库没有开启慢查询日志需要我们手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响慢查询日志支持将日志记录写入文件。 配置
查看慢日志相关配置
# 查看慢SQL是否开启
show variables like slow_query_log%;
# 查看慢查询设定的阈值 单位:秒
show variables like long_query_time;
# 慢日志开关 0为关闭1为开启
set global slow_query_logON;
# 日志文件
set global slow_query_log_file/xxx/xxx/xxx/host_name-slow.log;
# 设置慢日志阈值的时间
set global long_query_time2;
也可以在/etc/my.cnf配置
slow_query_log ON
slow_query_log_file /xx/xx/xx/host_name-slow.log
# 定义SQL的执行时间阈值(单位为秒)默认是10最小值为0可以指定0.1表示100ms。
long_query_time 2# 输出有效值TABLEFILENONE
log_output FILE# 查询扫描过的最少记录数
min_examined_row_limit 0# 该参数决定是否记录未使用索引的SQL
log_queries_not_using_indexes OFF# 该参数决定是否记录管理类的命令:ALTER TABLE,ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE,REPAIR TABLE
log_slow_admin_statements OFF # 默认是不记录这一类语句到慢日志# 该参数决定每分钟记录未使用索引的SQL的数量上限因为未使用索引的SQL可能会非常多导致慢日志空间增长飞快。
log_throttle_queries_not_using_indexes 0# 该参数在从库上设置决定是否记录在复制过程中超过long_query_time的SQL如果binlog格式是row则即使开启了该参数也不会记录相关SQL。
log_slow_slave_statements OFF慢日志是怎么生成的
服务器按以下顺序使用控制参数来确定是否将查询写入慢查询日志
1. 管理类语句不会记录除非开启了log_slow_admin_statements
2. 执行时间需要超过long_query_time或者log_queries_not_using_indexes 开启的并且记录数量在log_throttle_queries_not_using_indexes之下
3. SQL需要读取数据行数超过min_examined_row_limit
4. 从库的复制语句默认不记录除非binlog格式是statement且开启log_slow_slave_statements;
查看慢查询日志
可以通过mysqldumpslow查看比如说查看时间最长的10条sql语句 mysqldumpslow -s al -n 10 日志文件我们可以通过设置慢查询输出到表中。通过SHOW VARIABLES LIKE log_output 查看当前是输入file还是表通过 set global log_output TABLE设置输入表中查看 通用查询日志
什么是通用查询日志
通用查询日志是用户所做事情的所有记录。当客户端连接或断开连接时服务器将信息写入此日志并记录从客户端接收到的每个 SQL 语句。当你怀疑客户端中存在错误并想要准确了解客户端发送到mysqld的内容时常规查询日志可能非常有用。
配置
SHOW VARIABLES LIKE %general%;
返回结果 general_log 日志开关
general_log_file 日志文件默认名称为 host_name.log 部分日志内容
刚在我们在客户端执行了 SHOW VARIABLES LIKE %general%; 打开日志可以看到这条记录 错误日志
概念
错误日志Error Log 是 MySQL 中最常用的一种日志主要记录 MySQL 服务器启动和停止过程中的信息。它还包含服务器启动和关闭期间以及服务器运行期间发生的错误、警告和注释等诊断消息。例如如果mysqld注意到需要自动检查或修复某个表它就会向错误日志写入一条消息。
配置
错误日志默认是开启的没有关闭这个操作。可以通过 show variables like log_err%; 查看 错误日志配置默认是stderr 我们需要去/etc/my.cnf 进行配置 log_error 文件目录 。
log_error_verbosity日志记录等级 log_error_verbosity Value Permitted Message Priorities 1 ERROR 2 ERROR, WARNING 3 ERROR, WARNING, INFORMATION log_timestamps控制写入错误日志以及一般查询日志和慢查询日志文件的消息中时间戳的时区。
允许的log_timestamps值为UTC默认值和 SYSTEM本地系统时区。时间戳使用 ISO 8601 / RFC 3339 格式编写 加上表示 Zulu 时间 (UTC) 的尾值或指示相对于 UTC 的本地系统时区调整的偏移量。例如 YYYY-MM-DDThh:mm:ss.uuuuuuZ±hh:mm
2020-08-07T15:02:00.832521Z (UTC)
2020-08-07T10:02:00.832521-05:00 (SYSTEM)# my.cnf 配置
[mysqld]
log_timestampssystem 错误日志文件刷新和重命名
可以将这些错误日志删除以保证 MySQL 服务器上的硬盘空间。MySQL 的错误日志是以文本文件的形式存储在文件系统中的可以直接删除。
mysqladmin -uroot -p flush-logs 中继日志
中继日志relay log只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致要从主服务器读取二进制日志的内容并且把读取到的信息写入本地的日志文件中这个从服务器本地的日志文件就叫中继日志。然后从服务器读取中继日志并根据中继日志的内容对从服务器的数据进行更新完成主从服务器的数据同步。
名词解释
1- 脏页当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为“脏页”。
2- 干净页内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页”。
3- LSN称为日志的逻辑序列号(log sequence number)在innodb存储引擎中lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大。事务中更新操作会产生一个新的LSN。LSN不仅存在于redo log中还存在于数据页中。
4- 物理日志和逻辑日志
逻辑日志就是sql语句记录这个逻辑操作
物理日志就是具体到某个磁盘页
