MySQL日志:主要包含:错误日志、查询日志、慢查询日志、事务日志、二进制日志、中继日志;

日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化;也就是说用来记录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时,可以通过日志查看文件出错的原因,并且可以通过日志文件进行数据恢复。

错误日志

  在mysql数据库中,错误日志功能是默认开启的。并且,错误日志无法被禁止。默认情况下,错误日志存储在mysql数据库的数据文件中。错误日志文件通常的名称为hostname.err。其中,hostname表示服务器主机名。

默认情况下错误日志大概记录以下几个方面的信息:服务器启动和关闭过程中的信息(未必是错误信息,如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等)、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。

记录如下几类信息:

 (1) mysqld启动和关闭过程中输出的信息; 

 (2) mysqld运行中产生的错误信息; 

 (3) event scheduler运行时产生的信息;

 (4) 主从复制架构中,从服务器复制线程启动或关闭时产生的日志;

日志存储位置:

log_error= /var/log/mariadb/mariadb.log | OFF 注:# 给出文件路径,直接表示on。

log_warnings={ON|OFF} # 把警告的信息也记录下来,记录到同一个文件当中 

mysql>show global variables like 'log_error%';
mysql>show global variables like 'log_warnings%';
+---------------+------------------------------+
| Variable_name| Value             |
+---------------+------------------------------+
| log_error  | /var/log/mariadb/mariadb.log|
| log_warnings|  1              |
+---------------+------------------------------+

查询日志

  默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作,其中还包含增删查改等信息,在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO,会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

日志存储位置:

 文件:file     # 自己指定的文件日志

 表:table (mysql.general_log)  # 数据库当听指定的数据日志表

    desc general_log;  # 里的各个字段的信息

 general_log={ON|OFF}   # 是否开启,默认是没有开启的

 general_log_file=HOSTNAME.log   # 如果不手动指定文件路径的话,会自动在当前的目录下创建一个以主机名为文件名的日志文件  如: general_log_file   centos7.log

 log_output={FILE|TABLE|NONE}  # 日志是记录到数据库当中还是以文件的方式存储

   file:   文件有效,表无效

   table:  表有效,文件无效

   none:   两者都无效,就算上面开启了记录日志的功能,也是无效的

mysql>show global variables like 'log_out%';
mysql>show global variables like 'general%';
mysql>set @@global.general_log=on; # 修改全局变量值,也可以修改当前会话的值-->session
mysql>set @@global.log_output='table';
+------------------+-------------+
| Variable_name  | Value    |
+------------------+-------------+
| general_log   |  OFF    |
| general_log_file| centos7.log|
| log_output   | FILE    |
+------------------+-------------+

慢查询日志

  慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化。一般建议开启,它对服务器性能的影响微乎其微,但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。

慢查询:运行时间超出指定时长的查询;  # 默认为10s

 long_query_time

日志存储位置:

文件:FILE

表:TABLE  - - > mysql.slog_log

log_slow_queries={ON|OFF}   # 是否启用

slow_query_log={ON|OFF}    # 是否启用,mariadb默认使用  注:会根据版本的不同来指定

slow_query_log_file=

log_output={FILE|TABLE|NONE}

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_rate_limit

log_slow_verbosity

+-----------------+-----------+
| Variable_name | Value   |
+-----------------+-----------+
| log_output   | FILE    |  
| long_query_time  | 10.000000   |  
| slow_query_log  | OFF     |
| slow_query_log_file  | centos7-slow.log |
| log_slow_queries   | OFF    |                                                                                                       |
| log_slow_rate_limit  | 1    |                                                                                                        |
| log_slow_verbosity  |    
| log_slow_filter    | admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |                                   |
+-----------------+-----------+

事务日志

  事务型存储引擎innodb用于保证事务特性的日志文件,存储引擎在修改表的数据时只需要修改其内存拷贝,再把改修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的,我们通常称之为预写式日志,修改数据需要写两次磁盘。

  如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

日志存储位置:

innodb_log_files_in_group   # 至少要有2个

 注:在数据库目录下 - -> ib_logfile0 、ib_logfile1

innodb_log_group_home_dir

innodb_log_file_size

innodb_mirrored_log_groups

 注:能将信息存储的随机,变成顺序存储的一个过程(提升了读写性能),因为首先全部写入的事务日志,然后全部写入,并不是执行一个事务写入一次(数据存放的位置基于不固定)。

+---------------------------+---------+
| Variable_name         | Value  |
+---------------------------+---------+
| innodb_log_files_in_group | 2     |
| innodb_log_group_home_dir | ./    |
| innodb_mirrored_log_groups| 1     |
| innodb_log_file_size    | 5242880 |
+---------------------------+---------+

二进制日志

 用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW),也可能是二者混合;

  功用:“重放”  # 读语句是不会记录的

  二进制日志也叫作变更日志,主要用于记录修改数据或有可能引起数据改变的mysql语句,并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。

二进制日志中常用的定义格式:

log_bin=/PATH/TO/BIN_LOG_FILE # 最好放在其它目录、磁盘上,万一数据库崩了,还要依靠二进制日志恢复

session.sql_log_bin={ON|OFF}  # 是否开启二进制日志文件

max_binlog_size=1073741824   # 单位字节,单个文件最大值,超过自动滚动

sync_binlog={1|0}         # 是否做同步(数据从内存同步到磁盘)


binlog_format={STATEMENT|ROW|MIXED}

 1、语句(statement):默认的记录格式;

 2、行(row):定义的并非数据本身而是这一行的数据是什么;

 3、混合模式(mixed):交替使用行和语句、由mysql服务器自行判断。

其中基于行的定义格式数据量会大一些但是可以保证数据的精确性。

示例格式:

# at 553

#160831  9:56:08 server id 1  end_log_pos 624  Query  thread_id=2  exec_time=0  error_code=0

SET TIMESTAMP=1472608568/*!*/;

BEGIN

/*!*/;

事件的起始位置:# at 553

事件发生的日期时间:#160831  9:56:08

事件发生的服务器id:server id 1   # 默认的都是1

事件的结束位置:end_log_pos 624

事件的类型:Query

事件发生时所在服务器执行此事件的线程的ID: thread_id=2 

语句的时间戳与将其写入二进制日志文件中的时间差:exec_time=0    # 没有1秒都为0

错误代码:error_code=0

设定事件发生时的时间戳:SET TIMESTAMP=1472608568/*!*/;

事件内容:BEGIN

mysql>SHOW MASTER|BINARY LOGS;  # 查看所有二进制日志文件信息
mysql>SHOW MASTER STATUS;      # 查看当前的二进制日志文件信息(文件、节点位置)
mysql>SHOW BINLOG EVENTS IN 'bin-log.000002';  # 查看某个二进制日志文件当中详细信息
[root-xdg]# mysqlbinlog -j 223  bin-log.000002  # bash当中查看二进制日志命令,文件路径

中继日志

  当要使用主-从复制的时候,就要使用到中继日志了,它是从主数据库服务器上记录下来二进制日志文件,再同步到自己的数据库服务器上的中继日志文件,然而读取中继日志文当中的内容来进行同步的功能;

  主数据库服务器:会开启一个dump-thread的进程来响应

  从数据库服务器:会开启一个io-thread的进程来请求,通过SQL-thread来执行中继日志当中的信息同步到本地

日志存储位置:

 relay_log = /path/filename  # 在配置文件当中开启记录中继日志

 server_id = 2  # 主-从配置的时候,分别指定不同的设备名

+-----------------------+----------------+
| Variable_name      | Value       |
+-----------------------+----------------+
| relay_log         | /app/relay-log |
| relay_log_index     |           |
| relay_log_info_file  | relay-log.info |
| relay_log_purge     | ON         |
| relay_log_recovery   | OFF        |
| relay_log_space_limit | 0          |
| server_id           | 2            |
+-----------------------+----------------+