IT源码网

MySQL日志体系介绍

qq123 2021年05月15日 数据库 375 0

MySQL提供了几种日志功能,如下:

日志类型 作用
Error log 记录mysqld在启动、关闭、运行时出现的错误、告警等
General query log 记录MySQL执行的所有SQL
Binary log 数据库变更记录,类似一个账本,记着流水账
Relay log 只在从库存在,内容就是主库的binlog
Slow query log 慢查询日志
DDL log DDL操作日志

默认情况下,
1.以上的日志功能都是关闭的(DDL log除外,这种日志是自动生成的,不能干预);
2.如果没有特别指定,所有开启的日志功能,输出信息都写在数据库数据目录下。

一、Error log
错误日志会在MySQL启动和关闭的时候记录相关信息。它包含如error,warnings,note等诊断信息。例如当mysqld进程异常终止时,mysqld_safe会自动将其拉起,并在错误日志里记录相关信息。
linux下是用–log-error指定将信息定向到标准输出还是文件中。
如果–log-error没指定,mysqld会将错误信息输出到标准输出;
除非–log-error指定绝对路径和文件名,否则会将错误信息输出到数据目录下的host_name.err文件。
–log-error可以在启动数据库时命令行指定,也可以在my.cnf文件中[mysqld],[server]或[mysqld_safe]中指定。
同时,可以通过sys_log选项,将错误日志输出到syslog中。

二、Binary log
简称binlog,用事件(event)的方式来记录数据库的变更过程。binlog会记录那些有可能会更改数据的SQL,或者以数据行的方式去记录,例如一条delete语句,虽然可能删除了0行,但binlog中还是会有记录。但select或show等不导致数据变更的操作不会记录。开启binlog会使得数据库性能轻微地下降,但是其所带来的功能,是绝对物超所值的。
binlog有两个重要的作用:
1.在主从复制中,从库通过应用主库的binlog来达成同步;
2.在备份恢复中,通过binlog来完成指定时间点的不完整恢复。
binlog通过–log-bin参数开启或指定具体路径,默认名称是pid-file选项内容,默认路径是数据目录。–log-bin参数如果指定了.xx之类的后缀,会被忽略,而在实际生成时,MySQL会自动在–log-bin之后加上递增的数字后缀。
在MySQL重启或执行flush logs时,会自动生产新的binlog,后缀加一。一般情况下,binlog文件大小不会超过max_binlog_size,但如果有一个大事务,在文件达到max_binlog_size时还没写完,则文件会扩展至能写完这个事务为止。
跟binlog成双成对的还有一个文件,名称是binlog+.index后缀,里面记录这当前的所有binlog。
不要手动rm binlog,MySQL有专门的语法去清理。如果想一次性清理全部binlog,可以使用reset master,但要注意检查当前从库是否已经抓取了所有binlog。也可以利用purge binary log to ''的方式,将binlog清理至指定的文件或日志。
binlog是二进制格式的,可以利用mysqlbinlog工具查看其内容。常用命令为
mysqlbinlog --no-defaults -v mysql-bin.00001
详细用法可–help查看帮助。
前面说到binlog可以用来进行时间点的不完整恢复,大致用法如下
mysqlbinlog mysql-bin.00001|mysql -u -p
binlog与事务:
在事务执行完成,锁释放或事务提交前,要先完成binlog的写入。对于非事务性表的操作,执行完成后立即写入binlog。所以在一个事务中,如果混合了事务表和非事务表,在事务异常需要回滚时,由于非事务表不能回滚,会导致数据的不一致。
在一个事务提交前,事务的binlog会先缓存在binlog cache buffer里,在完成提交前再将整个事务的binlog刷入文件。
binlog不得不说的一个关键点是binlog_format:
1.statement,语句格式,即binlog中直接记录执行的SQL语句;
2.row,行格式,binlog中记录的是数据行所发生的变化,例如一个update语句更新一百行数据,那么binlog中将记录那一百行数据的变化,而不是那条update语句;
3.mixed,混合格式,即以上两种格式的混合,一般情况下是statement格式,在一定的条件下会自动转变为row格式。
statement格式的优点是节省空间,缺点是存在不确定因素;row格式的优点是精准的记录数据变化,缺点是耗费空间。例如一条update语句更新了一百万行数据,那么statement格式只需记录一条SQL,而row格式就要记录一百万行数据的变化。但考虑一种情况,SQL是update tb set time=now() where id=10,那么以statement格式记录的binlog在一段时间之后用来搭建从库或者做恢复,那么now()就是当前的时间了,也就造成了数据的不可还原;而如果是row格式的binlog,由于记录的是数据行的变化,所以不受时间函数的影响。
一般情况下都推荐使用row格式,极少数情况根据实际而定。
设定binlog格式语句如下:
set global binlog_format=‘row|statement|mixed’;

三、General query log
全局查询日志记录所有MySQL执行的命令,包括用户连接/断开在内的所有客户端发送过来的命令。
mysqld会按照接收命令的顺序原样记录在全局查询日志中,这个顺序可能跟执行的顺序不太一样,所以跟binlog里的SQL顺序也是有区别的。
需要注意的是,当binlog_format是row时,由于更新语句的记录都是数据行的格式,所以这些更新操作是不会记录到查询日志里的。当binlog_format是mixed时同理。
开启general log:
set global general_log=1;
set global general_log_file=file_path;
关闭general log:
set global general_log=0;

四、Slow query log
慢查询日志,顾名思义就是记录慢查询的SQL了。参数long_query_time设置慢查询阈值,min_examined_row_limit设置检索数据的条目,一个SQL两个指标都超过了就会记录在慢查询日志里。
开启/关闭慢查询的方式:
–slow_query_log=0|1
–slow_query_log_file=file-name
general query log和slow query log都可以通过–log-output=table|file来指定日志存放在库表还是文件中,可以用逗号隔开同时采用两种方式。
慢查询还可以监控没使用到索引的SQL,通过log_queries_not_using_indexes开启。但可能这样的SQL会非常多,很容易就导致磁盘空间爆满,那么可以通过log_throttle_queries_not_using_indexes来设定一分钟内记录多少个没使用索引的SQL。

五、DDL log
DDL日志,记录由DDL操作产生的元数据操作,例如drop table和alter table,如果这些操作发生异常终端,MySQL利用DDL 日志来进行恢复。
DDL日志记录在数据目录的ddl_log.log,内容是二进制格式的,不要对其进行人工编辑。
DDL日志在大部分正常运行的情况下是不会出现的,只有在需要用来记录一些元数据操作的时候才会产生,而且在MySQL启动后,会自动清理DDL日志。
需要注意的是,DDL日志最大只能达到4GB,如果达到上限,需要将其清理后,才能继续进行DDL操作。


评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL列权限授予及注意事项