其它数据库日志

日志类型

1、MySQL 有不同类型的日志文件,用来存储不同类型的日志,分为二进制日志、错误日志、通用查询日志、慢查询日志

(1)慢查询日志:记录所有执行时间超过 long_query_time 所有查询,方便对查询进行优化

(2)通用查询日志:记录所有连接的起始时间和终止时间,以及连接发送给数据库服务器的所有指令,可以复原操作,审计数据库操作

(3)错误日志:记录 MySQL 服务的启动、运行、停止 MySQL 服务时出现的问题,方便了解服务器的状态,从而对服务器进行维护

(4)二进制日志:记录所有更改数据的语句,用于主从服务器之间的数据同步,以及服务器遇到故障时,数据的无损恢复

2、MySQL 8 新增两种支持的日志

(1)中继日志:用于主从服务器架构中,从服务器存放主服务器二进制日志内容的一个中间文件,从服务器通过读取中继日志的内容,来同步主服务器上的操作

(2)数据定义语句日志:记录数据定义语句执行的元数据操作

3、除二进制日志外,其他日志都是文本文件

4、默认情况下,所有日志创建于 MySQL 数据目录中

 

日志的弊端

1、日志功能会 降低MySQL数据库的性能 。例如,在查询非常频繁的MySQL数据库系统中,如果开启了通用查询日志和慢查询日志,MySQL数据库会花费很多时间记录日志

2、日志会 占用大量的磁盘空间 。对于用户量非常大、操作非常频繁的数据库,日志文件需要的存储空间设置比数据库文件需要的存储空间还要大

 

通用查询日志(general_log)

1、记录用户的所有操作,包括启动和关闭 MySQL 服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等

2、当数据发生异常时,查看通用查询日志,还原操作时的具体场景,可以帮助我们准确定位问题

3、查看通用查询日志相关信息

SHOW VARIABLES LIKE 'general_log';

4、启动日志

(1)永久性方式:修改 my.cnf 或 my.ini 配置文件来设置,在 [mysqld] 组下加入 log 选项,并重启 MySQL 服务

[mysqld]
general_log=ON
#日志文件所在目录路径,filename为日志文件名
general_log_file=[path[filename]]

(2)如果不指定目录和文件名,通用查询日志将默认存储在 MySQL 数据目录中的 hostname.log 文件中,hostname 表示主机名

(3)临时性方式

#开启通用查询日志
SET GLOBAL general_log=on;
#设置日志文件保存位置
SET GLOBAL general_log_file='path/filename';
#关闭通用查询日志
SET GLOBAL general_log=off;
#查看设置后情况
SHOW VARIABLES LIKE 'general_log%';

5、停止日志

(1)永久性方式:修改 my.cnf 或 my.ini 文件,把 [mysqld] 组下 general_log 值设置为 OFF,或注释 general_log,修改保存后,再重启 MySQL 服务,即可生效

[mysqld] 
general_log=OFF

(2)临时性方式

#使用SET语句停止MySQL通用查询日志功能:
SET GLOBAL general_log=off;

6、删除 / 刷新日志

(1)如果数据的使用非常频繁,那么通用查询日志会占用服务器非常大的磁盘空间,可以删除很长时间之前的查询日志,以保证 MySQL 服务器上的硬盘空间

(2)在该目录下手动删除通用查询日志

(3)刷新 MySQL 数据目录,创建新的日志文件,前提:必须将旧日志文件复制出来或改名,且开启通用日志

mysqladmin -uroot -p flush-logs

 

错误日志(error log)

1、记录 MySQL 服务器启动、停止运行的时间,以及系统启动、运行、停止过程中的诊断信息,包括错误、警告、提示等

2、通过错误日志可以查看系统的运行状态,即时发现故障、修复故障

3、如果 MySQL 服务出现异常,错误日志是发现问题、解决故障的首选

4、启动日志

(1)在 MySQL 数据库中,错误日志功默认开启,且错误日志无法被禁止

(2)默认情况下,错误日志存储在 MySQL 数据库的数据目录下,名称默认为 mysqld.log (Linux)或 hostname.err(MAC)

(3)如果需要制定文件名,则需要在 my.cnf 或 my.ini 配置,修改配置项后,需要重启 MySQL 服务以生效

[mysqld] 
#path为日志文件所在的目录路径,filename为日志文件名
log-error=[path/[filename]]

5、删除 / 刷新日志

(1)MySQL 错误日志是以文本文件的形式存储在文件系统中的,可以直接删除,以保证 MySQL 服务器上的硬盘空间 

(2)在运行状态下删除错误日志文件后,MySQL 并不会自动创建日志文件

(3)重建日志

mysqladmin -uroot -p flush-logs

(4)重建日志前,必须执行以下命令,否则报错

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

 

flush-logs 指令操作

1、MySQL 5.5 以前版本,flush-logs 将错误日志文件重命名为 filename.err_old,并创建新的日志文件

2、MySQL 5.5.7 开始,flush-logs 只是重新打开日志文件,做刷新操作,并不做日志备份和创建的操作

3、如果日志文件不存在,MySQL 启动或执行 flush-logs 时会自动创建新的日志文件,重新创建错误日志,大小为 0 字节

 

MySQL 8.0 新特性

1、MySQL 8.0 错误日志生成新的日志

2、旧版本日志问题

(1)默认情况下内容过于冗长

(2)遗漏有用的信息

(3)难以过滤某些信息

(4)没有标识错误信息的子系统源

(5)没有错误代码,解析消息需要识别错误

(6)引导消息可能会丢失

(7)固定格式

3、改进

(1)采用组件架构,通过不同的组件执行日志的写入和过滤功能

(2)写入错误日志的全部信息,都具有唯一的错误代码,从 10000 开始

(3)增加一个新的消息分类:system,用于在错误日志中始终可见的非错误但服务器状态更改事件的消息

(4)增加额外的附加信息,例如关机时的版本信息,发起关机的用户

(5)两种过滤方式:Internal、Dragnet

(5)三种写入形式:经典、JSON、syseventlog

 

二进制日志(bin log)

1、bin log 即 binary log,二进制日志文件 / 变更日志(update log)

2、记录数据库所有执行的 DDL、DML 等数据库更新事件的语句,但不包含没有修改任何数据的语句,如 SELECT

3、以事件形式记录,并保存在二进制文件中,可以再现数据更新操作的全过程

4、如果想要记录所有语句,需要使用通用查询日志

5、应用场景

(1)数据恢复 ,如果 MySQL 数据库意外停止,可以通过二进制日志文件来查看用户执行的操作,然后根据记录来恢复数据库服务器

(2)数据复制 ,由于日志的延续性和时效性,master 把它的二进制日志传递给 slaves,来达到 master-slave 数据一致

6、查看二进制日志相关信息

(1)在 MySQL 8,默认开启

SHOW VARIABLES LIKE '%log_bin%';

(2)log_bin_basename:二进制日志的基本文件名,后面会追加标识来表示每一个文件

(3)log_bin_index:二进制文件的索引文件,这个文件管理所有 binlog 文件的目录

(4)log_bin_trust_function_creators:限制存储过程,因为二进制日志用于主从复制,而存储函数有可能导致主从的数据不一致,所以当开启二进制日志后,需要限制存储函数的创建、修改、调用

(5)log_bin_use_v1_row_events:此只读系统变量已弃用,ON 表示使用版本 1 二进制日志行,OFF 表示使用版本 2 二进制日志行(MySQL 5.6 默认值为 2)

7、日志参数设置

(1)永久性方式

[mysqld]

#启用二进制日志,需要打开主机,mysql-bin为二进制日志文件的目录和名称
log-bin=mysql-bin

#此参数控制二进制日志文件保留的时长,单位是秒,默认2592000(30天)
binlog_expire_logs_seconds

#控制单个二进制日志大小,当前日志文件大小超过此变量时,执行切换动作
#此参数的最大和默认值是1GB,该设置并不能严格控制Binlog大小
#Binlog比较靠近最大值而同时有一个较大事务时,为了保证事务的完整性,可能不做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束
#一般情况下可采取默认值
max_binlog_size

(2)临时性方式

#全局级别 
SET GLOBAL sql_log_bin=0;
#会话级别 
SET sql_log_bin=0; 

8、查看日志

(1)查看当前的二进制日志文件列表及大小

SHOW BINARY LOGS;

(2)当 MySQL 创建二进制日志文件时,先创建一个以 filename 为名称、以 .index 为后缀的文件,再创建一个以 filename 为名称、以 .000001 为后缀的文件

(3)MySQL 服务重新启动一次 ,以 .000001 为后缀的文件就增加一个,并且后缀名按 1 递增,即日志文件的个数与 MySQL 服务启动的次数相同

(4)如果日志长度超过 max_binlog_size 上限(默认是 1 GB),则创建一个新的日志文件

(5)查看方式一:mysqlbinlog,不容易分辨查看到 pos 点信息

(6)查看方式二

SHOW binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

(7)IN 'log_name':指定要查询的 binlog 文件名,不指定默认第一个 binlog 文件

(8)FROM pos:指定从哪个 pos 起始点开始查起,不指定默认从整个文件首个 pos 点开始算

(9)LIMIT [offset]:偏移量,不指定默认 0

(10)row_count:查询总条数,不指定默认所有行

9、binlog 查看格式

(1)Statement:默认,每一条会修改数据的 SQL 都会记录在 binlog 中,优点:不需要记录每一行的变化,减少 binlog 日志量,节约 IO,提高性能

(2)Row:MySQL 5.1.5 版本开始支持 row level 的复制,它不记录 SQL 语句上下文相关信息,仅保存哪条记录被修改,优点:row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

(3)Mixed:MySQL 5.1.8 版本开始支持,结合 Statement 与 Row

10、使用日志恢复数据

(1)使用 mysqlbinlog 命令读取 filename 中的内容,然后使用 mysql 命令将这些内容恢复到数据库中

mysqlbinlog [option] filename | /usr/bin/mysql –uroot -p密码 -v '数据库名';

(1)filename:日志文件名

(2)option:可选项,重要的两对参数:--start-date、--stop-date 和 --start-position、-- stop-position

(3)--start-date、--stop-date:可以指定恢复数据库的起始时间点和结束时间点

(4)--start-position、--stop-position:可以指定恢复数据的开始位置和结束位置

(5)使用 mysqlbinlog 命令进行恢复操作时,必须是编号小的先恢复

11、删除二进制日志

(1)MySQL 二进制文件可以配置自动删除,同时 MySQL 提供安全的手动删除二进制文件的方法

(2)PURGE MASTER LOGS:删除指定二进制日志文件

#删除指定日志文件名前面的所有日志
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'
#删除指定时间之前的所有日志
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'

(3)RESET MASTER:删除所有二进制日志文件,MySQL 会重新创建二进制文件,新的日志文件扩展名将重新从 000001 开始编号

RESET MASTER;

12、其他场景

(1)二进制日志可以通过数据库的全量备份、二进制日志中保存的增量信息,完成数据库的无损恢复,但若数据量大、数据库、数据表多的场景(如:分库分表应用),起止位置不容易管理

(2)解决:配置主从数据库服务器,一主多从架构,把二进制日志文件的内容通过中继日志,同步到从数据库服务器中,有效避免数据库故障导致的数据异常等问题

 

binlog 写入机制

1、写入时机

(1)事务执行过程中,先把日志写到 binlog cache

(2)事务提交时,再把 binlog cache 写到 binlog 文件中

2、一个事务的 binlog 不能被拆开

(1)无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为 binlog cache

(2)可以通过 binlog_cache_size 控制单个线程 binlog cache 大小,如果存储内容超过该参数,需要暂存到磁盘(Swap)

3、binlog 日志刷盘流程

(1)write:指把日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,所以速度比较快

(2)fsync,将数据持久化到磁盘的操作

(3)write、fsync 时机,由参数 sync_binlog 控制,默认是 0

(4)sync_binlog=0,表示每次提交事务都只 write,由系统自行判断什么时候执行 fsync,虽然性能得到提升,但是机器宕机,page cache 会丢失 binglog

(5)为安全起见,sync_binlog 设置为 1,表示每次提交事务都会执行 fsync,与 redo log 刷盘流程相同

(6)折中方式,可以设置为 N(N > 1),表示每次提交事务都 write,但累积 N 个事务后才 fsync

(7)在出现 I/O 瓶颈的场景中,将 sync_binlog 设置成一个比较大的值,可以提升性能,同样,如果机器宕机,会丢失最近 N 个事务的 binlog 日志

 

binlog、redolog 对比

1、redo log :物理日志,记录在某个数据页上的修改,由 InnoDB 存储引擎层产生

2、binlog:逻辑日志,记录语句的原始逻辑,属于MySQL Server 层

3、都属于持久化的保证,但侧重点不同

(1)redo log 让 InnoDB 拥有崩溃恢复能力

(2)binlog 保证 MySQL 集群架构的数据一致性

4、写入时机不同

(1)在执行更新语句过程,会记录 redo log 与 binlog,以基本的事务为单位

(2)redo log 在事务执行过程中可以不断写入

(3)binlog 只有在提交事务时才写入

5、两阶段提交

1、为了解决两份日志之间的逻辑一致问题,InnoDB 使用两阶段提交方案

2、原理:将 redo log 写入拆为两个步骤 prepare、commit

(3)写入 binlog 时发生异常,也不会有影响,因为 MySQL 根据 redo log 日志恢复数据时,发现 redo log 还处于 prepare 阶段,并且没有对应 binlog,就会回滚该事务。

(4)若 redo log 设置 commit 阶段发生异常,也不会回滚事务,虽然 redo log 是处于 prepare 阶段,但能通过事务 id 找到对应 binlog 日志,所以 MySQL 认为是完整的,就会提交事务恢复数据

 

中继日志

1、只在主从服务器架构的从服务器上存在

2、从服务器为了与主服务器保持一致,要从主服务器读取二进制日志的内容,并且把读取到的信息写入本地的日志文件(中继日志)

3、从服务器读取中继日志,并根据中继日志的内容,对从服务器的数据进行更新,完成主从服务器的数据同步

4、搭建主从服务器之后,中继日志默认会保存在从服务器的数据目录下

5、文件名的格式是:从服务器名 -relay-bin.序号

6、中继日志有一个索引文件:从服务器名 -relay-bin.index,用来定位当前正在使用的中继日志

7、中继日志与二进制日志的格式相同,可以用 mysqlbinlog 工具进行查看

8、从服务器宕机

(1)有时为了系统恢复,要重装操作系统,可能会导致从服务器名称与之前不同,而中继日志里是包含从服务器名

(2)重命名可能导致恢复从服务器时,无法从宕机前的中继日志里读取数据

(3)解决:把从服务器的名称,改回中继日志中的名称

posted @   半条咸鱼  阅读(55)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示