数据库日志系统分解-白红宇

标题索引

日志分类

日志操作

日志分解

日志分类

在数据库系统中日志主要分为6类，具体如下：

1、查询日志：主要记录日常查询的记录；

2、慢查询日志：主要记录查询时长超过设置的数值时间的语句，方便DBA运维人员进行查询；

3、错误日志：主要用于记录错误的事件，方便DBA运维人员对数据库的操作和日常维护；

4、二进制日志：主要用于记录sql语句对数据库的操作，方便DBA运维人员审计和备份恢复；

5、中继日志：主要用于数据库集群架构设计中数据库的主备；

6、事务日志：主要用于防止数据库服务器异常中断时，事物的恢复。

日志操作

1.查询日志，查询日志可以记录在文件系统的文件中，也可以记录在自身数据库的表中，具体的记录方式可通过全部变量进行查看或更改，默认只记录在文件之中，建议关闭查询日志

                         
MariaDB [(none)]>  show 
global
variables 
like
'general_log%'
;
+
------------------+----------------+
| Variable_name    | Value          |
+
------------------+----------------+
| general_log      | 
ON
|
| general_log_file | nginxser02.log |   #相对目录，相对数据文件安装的目录
+
------------------+----------------+
2 
rows
in
set
(0.00 sec)
MariaDB [(none)]>  show 
global
variables 
like
'log_output'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| log_output    | FILE  |               #值可以为file|
table
|none
+
---------------+-------+
1 row 
in
set
(0.00 sec)
        
      

2.慢查询日志，执行时间超出指定查询的日志，慢查询有可能数据库设计的不合理或表被锁定非设计原因，具体验证或修改可根据如下示例

                         
#查询慢查询设定时长，想修改时长并永久生效，写配置文件
MariaDB [(none)]> show 
global
variables 
like
'long_query_time'
; 
+
-----------------+-----------+
| Variable_name   | Value     |
+
-----------------+-----------+
| long_query_time | 10.000000 |
+
-----------------+-----------+
1 row 
in
set
(0.00 sec)
MariaDB [(none)]> show 
global
variables 
like
'slow_query%'
;
+
---------------------+---------------------+
| Variable_name       | Value               |
+
---------------------+---------------------+
| slow_query_log      | 
OFF
|
| slow_query_log_file | nginxser02-slow.log |
+
---------------------+---------------------+
2 
rows
in
set
(0.00 sec)
#慢查询日志记录的策略设定
MariaDB [(none)]> show 
global
variables 
like
'log_slow%'
;
+
---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                                        |
+
---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries    | 
OFF
|
| log_slow_rate_limit | 1                                                                                                            |
| log_slow_verbosity  |                                                                                                              |
+
---------------------+--------------------------------------------------------------------------------------------------------------+
4 
rows
in
set
(0.00 sec)
        
      

3.错误日志，错误日志记录所有数据库生命周期中所有的错误信息，具体如下操作

                         
MariaDB [(none)]> show 
global
variables 
like
'log_error%'
;
+
---------------+------------------------------+
| Variable_name | Value                        |
+
---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+
---------------+------------------------------+
1 row 
in
set
(0.00 sec)
#设置错误警告信息，1表示记录，0表示不记录
MariaDB [(none)]> show 
global
variables 
like
'log_war%'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| log_warnings  | 1     |
+
---------------+-------+
1 row 
in
set
(0.00 sec)
        
      

4.二进制日志，文件也为二进制文件，可利用数据库的恢复等，默认情况下是关闭的，且只能在配置文件中进行修改，其中变量sql_log_bin是否记录二进制日志的开关，而log-bin是记录二进制文件的路径，可以使用绝对路径，也可以使用基于数据库安装路径的相对路径，max_binlog_size是默认单个二进制文件最大大小，默认为1G，超出此大小则进行日志滚动，当然重启服务时也会进行滚动，sync_bin_log默认情况值为0，不会将内存中的日志实时同步进磁盘，若服务器异常断电则日志丢失，数据丢失，是否开启此功能根据架构设定自行设置，具体如下

                         
#默认情况下是开启了二进制文件记录，但是未指定二进制文件的记录位置
MariaDB [(none)]> show 
global
variables 
like
'log_bin'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| log_bin       | 
OFF
|
+
---------------+-------+
1 row 
in
set
(0.00 sec)
MariaDB [(none)]> show 
global
variables 
like
'sql_log_bin'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| sql_log_bin   | 
ON
|
+
---------------+-------+
1 row 
in
set
(0.00 sec)
MariaDB [(none)]> show 
global
variables 
like
'sync_binlog'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| sync_binlog   | 0     |
+
---------------+-------+
1 row 
in
set
(0.00 sec)
        
      

开启二进制文件所指定的路径路径(相对数据库安装的路径)，并设定记录在文件mysql-bin文件中，具体配置如下

                         
[ root@centos7 ~ ]
#vim /etc/my.cnf
[mysqld]z
 
log-bin=mysql-bin
 
#记录日志时的格式，格式有基于statement、raw或者二者的混杂格式
 
binlog_format=mixed

再次登陆数据库进行验证

                         
MariaDB [(none)]> show 
global
variables 
like
'log_bin'
;
+
---------------+-------+
| Variable_name | Value |
+
---------------+-------+
| log_bin       | 
ON
|
+
---------------+-------+
1 row 
in
set
(0.00 sec)
MariaDB [(none)]> help show ;
Name
: 
'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, 
or
status information about the server. This 
section
describes
those following:
SHOW AUTHORS
SHOW { 
BINARY
| MASTER} LOGS
SHOW BINLOG EVENTS [
IN
'log_name'
] [
FROM
pos] [LIMIT [offset,] row_count]
MariaDB [(none)]> show 
binary
logs;  #查看正在使用中的二进制日志
+
------------------+-----------+
| Log_name         | File_size |
+
------------------+-----------+
| mysql-bin.000001 |       264 |
| mysql-bin.000002 |       245 |
+
------------------+-----------+
2 
rows
in
set
(0.00 sec)
MariaDB [(none)]> show binlog events 
in
'mysql-bin.000002'
;
+
------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                      |
+
------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
+
------------------+-----+-------------+-----------+-------------+-------------------------------------------+
1 row 
in
set
(0.00 sec)
#查看二进制文件的索引文件，通过索引文件查看有多少个滚动日志文件
[ root@nginxser02 ~ ]#cat /var/lib/mysql/mysql-bin.
index
./mysql-bin.000001
./mysql-bin.000002
#通过专用命令查看二进制文件
[ root@nginxser02 ~ ]#mysqlbinlog /var/lib/mysql/mysql-bin.000002
/*!50530 
SET
@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 
SET
@@session.max_insert_delayed_threads=0*/;
/*!50003 
SET
@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# 
at
4
#171130  4:13:21 server id 1  end_log_pos 245  Start: binlog v 4, server v 5.5.52-MariaDB created 171130  4:13:21 
at
startup
# Warning: this binlog 
is
either 
in
use 
or
was 
not
closed properly.
ROLLBACK
/*!*/;
BINLOG '
        
      

5.中继日志，从犯服务器用于保存从主服务器的二进制日志中读取到的事件

6.事务日志工作原理主要是在事物开始时，在内存中划分一个buffer用于缓存事务日志，另外防止事务日志在内存中磁盘文件中的事物分组文件满时同步失败，因此在磁盘上创建一组文件，一组文件默认为2个文件，方便buffer向磁盘文件同步日志，磁盘文件中的事物日志再次向数据库系统同步，防止事物异常丢失。

事物1.png

图1-1 事物日志结构图

事务日志默认配置buffer为8M,每文件大小为5M,每组中有2个文件，事务日志存储路径为相对路径的根目录，具体如下：

                         
MariaDB [(none)]> show 
global
variables 
like
'innodb_log%'
;
+
---------------------------+---------+
| Variable_name             | Value   |
+
---------------------------+---------+
| innodb_log_block_size     | 512     |
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+
---------------------------+---------+
5 
rows
in
set
(0.00 sec)
        
      

查看事物在磁盘中同步的日志文件

                         
[ root@nginxser02 mysql ]
#ls -lh /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5.0M Nov 30 05:06 
/var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Nov  8 22:56 
/var/lib/mysql/ib_logfile1
        
      

另外事物日志有redo log 和undo log，redo即为事物完成且保存在事物日志之中，但并未同步至数据系统之中，因此要同步进数据库系统必须将事物日志进行redo，undo即为并未完成的事物且保存在事物日志之中，此时需要事物回滚，将之前的事物undo。

本文转自薛伟博 51CTO博客，原文链接：http://blog.51cto.com/weiboxue/2045770，如需转载请自行联系原作者