mysql—日志

追茶到底
278 阅读5分钟

当前操作环境

# 服务器环境
[root@mysql]$lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.6.1810 (Core) 
Release:	7.6.1810
Codename:	Core

# mysql版本
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+

mysql日志类型

日志类型说明
错误日志（ErrorLog）记录在启动、运行或者停止mysqld时遇到的问题
通用查询日志（General Query Log）记录建立的客户端连接和执行的语句
二进制日志（Binary Log）记录更改数据的语句，逻辑日志
慢查询日志 （Show Query Log）记录所有执行时间超过long_query_time秒的所有查询
重做日志（redo log）物理层面的改动日志，主要用于数据重做（仅存在innodb）
回滚日志（undo log）记录的逻辑日志，主要用于MVCC中记录回滚（仅存在innodb）
中继日志（relay log）保存从主服务器中继到从属服务器的二进制日志事件，并记录有关中继日志中当前状态和位置的信息

1、错误日志（ErrorLog）

1.1 简介

记录mysql运行过程中Error、Warning、Note等信息，系统出错或者某条记录出问题

1.2 配置

mysql> show variables like 'log_error%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| log_error                | /data/server/mysql/mysqld.log |
| log_error_verbosity | 3                             |
+---------------------+-------------------------------+

一般通过配置文件my.cnf进行配置

[root@mysql]$grep -Ri  "error" /etc/my.cnf
log-error=/data/server/mysql/mysqld.log

1.3 案例

[root@mysql]$tail -f mysqld.log 
2020-10-25T02:33:44.910929Z 0 [Note] InnoDB: Buffer pool(s) load completed at 201025 10:33:44
2020-10-25T02:33:44.922505Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2020-10-25T02:33:44.922583Z 0 [Note] Server hostname (bind-address): '*'; port: 8004
2020-10-25T02:33:44.922658Z 0 [Note] IPv6 is available.
2020-10-25T02:33:44.922678Z 0 [Note]   - '::' resolves to '::';
2020-10-25T02:33:44.922708Z 0 [Note] Server socket created on IP: '::'.
2020-10-25T02:33:44.929486Z 0 [Note] Failed to start slave threads for channel ''
2020-10-25T02:33:44.937759Z 0 [Note] Event Scheduler: Loaded 0 events
2020-10-25T02:33:44.938055Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.26-log'  socket: '/tmp/mysql.sock'  port: 8004  MySQL Community Server (GPL)

2、通用查询日志（General Query Log）

2.1 简介

记录mysql的日常日志，包括查询，修改，更新等每条sql语句

2.2 配置

mysql> show variables like 'general%';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| general_log      | ON                                             |
| general_log_file | /data/server/mysql/izbp12xvjjwqe4ogfp2sdjz.log |
+------------------+------------------------------------------------+
2 rows in set (0.01 sec)

设置日志开启 set global general_log=ON;

2.3 案例

[root@mysql]$tail -f izbp12xvjjwqe4ogfp2sdjz.log 
2020-09-26T08:57:43.260205Z	   33 Query	SELECT DATABASE()
2020-09-26T08:57:43.262119Z	   33 Prepare	SHOW INDEXES FROM `sys_operation_records` FROM `qmPlus` WHERE Key_name = ?
2020-09-26T08:57:43.263391Z	   33 Execute	SHOW INDEXES FROM `sys_operation_records` FROM `qmPlus` WHERE Key_name = 'idx_sys_operation_records_deleted_at'
2020-09-26T08:57:43.266796Z	   33 Close stmt	
/usr/local/mysql/bin/mysqld, Version: 5.7.26 (MySQL Community Server (GPL)). started with:
Tcp port: 8004  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/usr/local/mysql/bin/mysqld, Version: 5.7.26 (MySQL Community Server (GPL)). started with:
Tcp port: 8004  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

3 二进制归档日志（Binary Log）

3.1 简介

二进制日志，包含一些事件，这些事件描述了数据库的改动，如建表、数据改动等，主要用于备份恢复、回滚操作等；
binlog是mysql的Server层实现的，所有引擎都可以使用。

3.2 作用

包含了所有更新了数据或者已经潜在更新了数据DML语句(insert、update、delete)
包含关于每个更新数据库DDL语句的执行时间信息
主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新

3.3 注意事项

一般开启binlog日志大概会有1%的性能损耗；但保障数据库完整性，对于重要数据库值得以性能换完整

3.4 应用场景

  • mysql主从复制：mysql replication在master端开启binlog，然后主库将日志同步到从库达到master-slave数据一致的目的
  • 数据恢复：通过使用mysqlbinlog工具来恢复数据

3.5 日志格式

  • statement：记录的是每一条执行的sql语句
  • row：记录是的每一行数据的变化
  • mixed：statement和row两种的混合模式，会根据当前sql语句来区别对待

3.6 配置

mysql> show variables like "%log_bin%";
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /data/server/mysql/bin-log         |
| log_bin_index                   | /data/server/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
| sql_log_bin                     | ON                                 |
+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)

cat my.cnf
# Binary Log
log_bin=/data/server/mysql/bin-log
log_bin_index=/data/server/mysql/mysql-bin.index
binlog_format=ROW
server-id=1
expire_logs_days=7
配置项值说明
log_bin/data/server/mysql/bin-log是否开启日志
log_bin_index/data/server/mysql/mysql-bin.index
binlog_formatrow日志格式row/statement/mined
server-id1指定服务
expire_logs_da7过期时间

3.7 常用有关Binlog操作命令

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看最新一个binlog日志文件名称和位置
show master status;

# 事件查询命令
# in 'log_name' : 指定要查询的binlog文件名（不指定就是第一个binlog文件）
# from pos ：从指定pos起始点开始查询（不指定就是从这个文件首个pos点开始算）
# limit offset ： 偏移量（不指定就是0）
# row_count : 查询总条数（不指定就是中行数）
show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]

# 查询指定文件
show binlog events in 'bin-log.000002';

# 删除当前binlog文件
reset master

# 删除从库的中继日志
reset slave

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2020-10-25 17:30:00'

# 删除指定文件
purge master logs to 'bin-log.000002'

# 设置过期时间
set global expire_logs_days=1;
flush logs;

3.8 binlog写入的时机

对于支持事务的引擎如InnoDB而言，需要等到事务提交了才会记录binlog；binlog刷入磁盘与参数sync_binlog相关。

值说明
0表示不受binlog控制，由文件系统控制缓存的刷新
非0 n表示每n次事务提交， 会将日志文件刷新到磁盘

4 慢查询日志（Show Query Log）

4.1 简介

记录mysql慢查询日志，修改配置文件my.cnf

4.2 慢查询配置相关命令

mysql> show variables like "%slow%";
+---------------------------+-----------------------------------------------------+
| Variable_name             | Value                                               |
+---------------------------+-----------------------------------------------------+
| log_slow_admin_statements | OFF                                                 |
| log_slow_slave_statements | OFF                                                 |
| slow_launch_time          | 2                                                   |
| slow_query_log            | OFF                                                 |
| slow_query_log_file       | /data/server/mysql/izbp12xvjjwqe4ogfp2sdjz-slow.log |
+---------------------------+-----------------------------------------------------+
5 rows in set (0.00 sec)

slow_query_log配置为OFF，说明为开启慢查询日志

打开慢查询日志功能

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

查看默认设置的慢查询的时间

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

5 重做日志（redo log）

是indodb独有的日志功能

image.png

5.1 简介

用来保证事务的持久性

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page脏数据。

5.2 redo log已经存了物理逻辑，为什么还是用binlog?

  • 1、redo log的大小固定，采用循环写入，日志会被覆盖掉，无法用于数据回滚、数据恢复等操作；binlog是可以追加写入的
  • 2、redo log是Innodb引擎层使用的，并不是所有引擎都支持；binlog是mysql的server层实现的，所有引擎都可以使用
  • 3、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑
5.3 作用

innodDB保证数据库发生异常重启，之前的提交记录不会丢失

6.回滚日志（undo log）

6.1 简介

用来保证事务的原子性以及InnoDB的MVCC;
主要为了事务的回滚服务，undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚

6.2 注意事项

undo log记录的逻辑日志，仅存在innodb日志

参考

Mysql日志
腾讯工程师带你深入解析 MySQL binlog

avatar
文章
阅读
粉丝