MYSQL日志系统

MYSQL日志系统

MYSQL日志的分类

一、服务器日志(MYSQL的工作状态)

  • 记录进程启动运行过程中的特殊事件,帮助分析MYSQL服务遇到的问题。
  • 根据需求抓取特定的SQL语句,追踪可能存在性能问题的业务SQL。

二、事务日志(MYSQL的工作内容)

  • 记录应用程序对数据的所有更改
  • 可用于数据恢复
  • 可用于实例间数据同步

三、服务器日志

1.服务器错误日志

log_error=/var/log/…/mysql.log

内容并不全是错误消息,它记录了实例启动过程中的重要消息,如果mysqld进程无法正常启动首先要查看错误日志。

2.慢查询日志

slow_query_log=1#开启慢查询,该参数可以动态调整。

slow_query_log_file=/var/log/…/slow_log.log#日志位置

long_query_time=5#超过long_query_time(秒)的就是慢查询,5.5版本之前查询精度是秒,而5.5之后是徽秒。

3.综合查询日志

general_log=1#开启综合查询日志

general_log_file=/var/log/…/general_log.log#日志存储位置

平时不要开启综合查询日志,因为会增加IO开销。

4.服务器日志的相关参数

https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

log_output,用来设置慢查询和综合查询日志存放的位置,它有三个值:

(1)file,慢查询和综合查询日志存储到磁盘位置上。

(2)table,记录到mysql库中的general_log(综合查寻表)和slow_log(慢查寻表)表中

(3)none,虽然开启了日志,但什么也不做。

5.flush logs

https://dev.mysql.com/doc/refman/5.7/en/flush.html

如果日志太大,可以定期截断并切换新文件。

先将旧日志移动到备份位置,然后flush logs生成新文件。

  • 要执行flush用户需要有RELOAD权限

四、事务日志

1.存储引擎事务日志

也就是redo log,和之前学的一样,文件名是ib_logfileN,ib_logfile默认是两个,ib_logfile0和ib_logfile1,事务写入的顺序是先从ib_logfile0开始写,ib_logfile0写满还没有持久化到磁盘,那么会继续写ib_logfile1,如果两个文件都写满了,而ib_logfil0中的事务还没有持久化完成,那么后面的事务不会写入ib_logfile0,事务会进入等待状态。直到持久化完成并清空ib_logfile0后,才会继续写入。

所以在数据库写入量很大的时候,ib_logfile文件可以多设置几个。控制ib_logfile大小和数量的参数是:innodb_log_file_sizeinnodb_log_files_in_group。这两个参数之和要略小于512G。

2.二进制日志

  • bin log(binary log)
  • 记录引起数据变化的SQL语句或数据逻辑变化的内容
  • MYSQL服务层记录和存储引擎无关(如果开启了bin log并使用InnoDB引擎的话,会同时存在bin log和redo log两种事务日志)

(1)bin log的主要作用

  • 备份恢复数据
  • 数据库主从同步
  • 挖掘分析SQL语句

(2)开启bin log

  • 主要参数

log_bin = /var/log/…/mysql-binlog #静态初始化参数,数据库启动后不能修改,该值设置的是bin log的前缀,如果只设置该值为on(非零),日志保存在datadir目录下,文件名前缀为datadir + ‘/’ + hostname + ‘-bin’。

sql_log_bin=1#该是全局只读,只能在SESSION下设置,值为1时记录bin log,0时不记录bin log。要改变该值,需要有SUPER权限。

sync_binlog=1 #binlog持久化的方式,当值为0时不同步到磁盘;当这个值大于0时,这个值是binary log提交组的数目,用来周期性同步到磁盘;当sync_binlog=1时,所有事务在被提交前会被同步到binary log,因此这可以保证即使是服务器意外重启,任何丢失的事务也只是准备状态。如果设置为2的话,就到达两个事务时才会同步到本地磁盘。

server_id=如果不配置这个参数无法启动数据库,并提示错误:

[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start.

 

(3)bin log管理

主要参数:

max_binlog_size =100MB#单个bin log的大小,到达大小限制就会生成一个新的bin log文件,和使用flush logs截断bin log有同样的效果。

expire_logs_days=7#保存binlog的天数,单位是天

手动删除binlog(在mysql中使用):

purge binary logs to "文件名.00000N"#后缀名之前的文件会被删除

purge binary logs before "日期"
#日期之前的文件会被删除

查看bin log内容:

在mysql里可以使用:show binlog events in "binlog-name"show binlog events in "binlog-name" from 位置,这个位置参数是binlog日志文件内容的偏移量,指定偏移量后可以看指定位置之后的日志。

还可以使用mysqlbinlog工具:

mysqlbinlog --base64-output=decode-rows "binlog-name"

binlog的格式:

binlog_format={ROW|STATMENT|MIXED}

ROW:记录写入数据库的值;在插入数据时,有一些内容是变化的,如uuid()函数,每次产生不同的值,如果只记录操作命令,在恢复数据库时,有效性会受到影响。而ROW格式会把写入数据库的值记录到BINLOG中,而不只是记录使用了uuid函数。但该格式的BINLOG会很大,因为可能使用一条命令就有可能更新上千条记录,而ROW格式会把改变的这上千条记录每条都记下来做了哪些改变。

STATMENT:记录操作的命令;只是简单的记录当时操作了哪些命令。

MIXED:MYSQL会自己判断哪些用ROW格式记录,哪些用STATMENT记录;如果不会影响日后恢复,就会以STATMENT格式记录,如果会对日后恢复有影响,就会以ROW格式记录。

PS:ROW格式的BINLOG只能使用mysqlbinlog工具查看,在MYSQL中使用show binlog events 命令无法查看到有用的信息。

innodb_log_file_size:设置log group中每个log文件的大小单位是byte;日志文件总大小(innodb_log_file_size * innodb_log_files_in_group)略小于512G;大日志文件使crash recovery更慢。

log group: The set of files that make up the redo log, typically named ib_logfile0 and ib_logfile1.

发表评论