Mysql数据库理论基础之十一 ---- 日志管理

一、简介

由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：

• 1、是一种数据库管理系统

• 2、是一种关联数据库管理系统

• 3、是一种开放源码软件，且有大量可用的共享MySQL软件

• 4、MySQL数据库服务器具有快速、可靠和易于使用的特点

• 5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中

• 
  

• InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。

• 
  

二、MySQL用户账号、權限管理     

 2.1. MySQL日志管理

mysql> SHOW GLOBAL VARIABLES LIKE ‘%log%‘;  #查看关于log的全局变量 

+--------------------------+-------------------------+

| Variable_name        | Value     |

+--------------------------+-------------------------+

| back_log             | 50   |

| binlog_cache_size       | 32768  |

| binlog_direct_non_transactional_updates | OFF |

| binlog_format            | MIXED   |

| binlog_stmt_cache_size      | 32768   |

| expire_logs_days          | 0     |

| general_log              | OFF   |

| general_log_file          | /mydata/data/lamp.log |

| innodb_flush_log_at_trx_commit | 1    |

| innodb_locks_unsafe_for_binlog | OFF   |

| innodb_log_buffer_size      | 8388608 |

| innodb_log_file_size       | 5242880 |

| innodb_log_files_in_group    | 2   |

| innodb_log_group_home_dir    | ./  |

| innodb_mirrored_log_groups   | 1   |

| log                  | OFF |

| log_bin                | ON   |

| log_bin_trust_function_creators | OFF  |

| log_error               | /mydata/data/lamp.err |

| log_output              | FILE  |

| log_queries_not_using_indexes | OFF  |

| log_slave_updates         | OFF  |

| log_slow_queries         | OFF   |

| log_warnings            | 1   |

| max_binlog_cache_size      | 18446744073709547520 |

| max_binlog_size          | 1073741824 |

| max_binlog_stmt_cache_size   | 18446744073709547520 |

| max_relay_log_size        | 0   |

| 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   |

| slow_query_log         | OFF  |

| slow_query_log_file     | /mydata/data/lamp-slow.log |

| sql_log_bin          | ON   |

| sql_log_off          | OFF  |

| sync_binlog          | 0   |

| sync_relay_log        | 0   |

| sync_relay_log_info     | 0   |

+------------------------+-------------------+

41 rows in set (0.00 sec)

 2.2. 日志分类

   1. 错误日志            2. 一般查询日志

   3. 慢查询日志           4. 二进制日志

   5. 中继日志            6. 事务日志

   7. 滚动日志

 2.3. 日志详解

    2.3.1. 错误日志   （默认是开启）

  在对应的数据目录中，以主机名+.err命名的文件，错误日志记录的信息类型：

•   2.3.1.1. 记录服务器运行中产生的错误信息

•   2.3.1.2. 记录服务在启动和停止是所产生的信息

•   2.3.1.3. 在从服务器上启动复制进程时，复制进程的信息也会被记录

•   2.3.1.4. 记录event事件错误日志及警告信息

配置mysql的主配置文件：

log_error =  /mydata/data/mysql . test .com.err 

#指定错误日志的位置，默认是在数据目录下，这个位置mysql用户必须有写权限

log_warning = {0|1} 

#默认开启，服务器运行中的警告日志也会记录在错误日志中

    2.3.2.  一般查询日志   （默认是关闭）

              对除慢查日志中记录的查询信息外，将都记录下来,这将对服务器产生大量压力，

        故繁忙的服务器应关闭这个日志与查询日志相关的变量。

log = {ON|OFF}        #是否启用查询日志，该指令在mysq5.6中已废弃

general_log = {ON|OFF}    #启动或关闭查询日志，默认是关闭的

general_log_file =  /mydata/data/mysql .log  #指定日志位置，默认在数据目录下

log_output = {TABLE|FILE|NONE} 

     #指定存放查询日志的位置，可以放在文件中，也可以放在数据库的表中，放在表中比放在文件中更容易查看。 如果组合中出现了NONE，那么其它设定都将失效，且无论是否启用日志功能，也不会记录任何相关的日志信息

    2.3.3.  慢查询日志  （默认是关闭）

  默认关闭状态，记录下来查询时间超过设定时长的查询，这些查询日志将被慢查日志记录下来  

slow_query_log  = {ON | OFF}    #开启慢慢查询日志，默认是关闭的 

slow_query_log_file =  /mydata/data/mysql-slow .log 

                   #慢查询日志的存放位置，默认在数据目录下

log_query_time = 10  #定义默认的时长，默认时长为10秒

log_query_not_using_indexes = {ON|OFF} 

                 #是否将没有使用索引的查询操作记录到慢查询日志

log_output = {TABLE|FILE|NONE}     

  #定义一般查询日志和慢查询日志的保存方式，默认为FILE，也可以TABLE及FILE的组合(用逗号隔开)， 若组合中出现了NONE，那么其它设定都将失效，且无论是否启用日志功能，也不会记录任何相关的日志信息

    2.3.4.  二进制日志   （默认是开启）

   精确记录用户对数据库中的数据进行操作的命令和操作的数据对象，及任何引起或可能引起数据库变化的操作。

2.3.4.1. 二进制日志文件的作用：

• 1）提供增量备份的功能；

• 2）提供数据基于时间点的恢复，这个恢复的时间点可以由用户控制；

• 3）为mysql的复制架构提供基础，将主服务器的二进制日志复制到从服务器上并执行同样的操作，即可实现数据进行同步；

2.3.4.2.  二进制日志格式：

• 基于语句   statement

• 基于行    row

• 混合方式   mixed

2.3.4.3.  二进制日志事件：

• position 基于位置

• datetime 基于时间

2.3.4.4. 二进制日志的查看与删除方式：

mysql> show binlog events  in   ‘mysql-bin.000012‘ ; 

mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.00011‘ FROM 107 

      #查看日志文件的事件信息且从事件107位置开始显示

mysql> purge binary logs to  ‘mysql-bin.000003‘ ;

                  #删除此日志之前的所有日志文件000001和000002

Usage:

mysql> SHOW BINARY LOGS; #查看所有的二进制日志文件

+------------------+-----------+

| Log_name   | File_size |

+------------------+-----------+

| mysql-bin.000001 | 1165 |

| mysql-bin.000002 | 126 |

| mysql-bin.000003 |  126 |

| mysql-bin.000004 |  884 |

| mysql-bin.000005 |  126 |

...

| mysql-bin.000015 | 1325 |

| mysql-bin.000016 |  390 |

| mysql-bin.000017 |  371 |

+------------------+-----------+

17 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000003‘;#删除之前的所有日志文件001和002

mysql> SHOW BINARY LOGS ;

+------------------+-----------+

| Log_name   | File_size |

+------------------+-----------+

| mysql-bin.000003 |   126 |

| mysql-bin.000004 |   884 |

| mysql-bin.000005 |   126 |

...

| mysql-bin.000015 |  1325 |

| mysql-bin.000016 |   390 |

| mysql-bin.000017 |   371 |

+------------------+-----------+

15 rows in set (0.00 sec)

2.3.4.5.  文件系统中查看二进制日志的命令：mysqlbinlog

--start-position        #开始位置  

--stop-position        #结束位置

--start-datetime  ‘yyyy-mm-dd hh:mm:ss‘ ;     #开始时间

--stop-datetime  ‘‘ ;    #结束时间

Usage:

[root@lamp ~]#mysqlbinlog mysql-bin.000001 #查看日志事件的全部信息

[root@lamp ~]# mysqlbinlog --start-datetime ‘2017-04-18 09:01:13‘ mysql-bin.000011          #查看日志事件从某時間點之后的信息

[root@lamp ~]# mysqlbinlog --start-position 193 mysql-bin.000011 

                  #查看日志时间从193位置开始之后的信息。

2.3.4.6.   配置mysql的主配置文件：  

sql_log_bin = {ON|OFF}

 #用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须有SUPER权限。

binlog_cache_size = 32768 

 #默认值32768，Binlog Cache用于在打开了二进制日志（binlog）记录功能的环境，它是MySQL用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。

 一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。

 同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存 

binlog_stmt_cache_size = 32768 

 #当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句   

log_bin = mysql-bin    

 #指定binlog的位置，默认在数据目录下

binlog- format   = {ROW|STATEMENT|MIXED} 

 #指定二进制日志的类型，默认为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。 

sync_binlog = 10 

 #设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步

max_binlog_cache_size = {4096 .. 18446744073709547520} 

 #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。

max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} 

 #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存

expire_log_days = {0..99} 

 #设定二进制日志的 过 期天数 ，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时

注： 一般建议将binlog日志与数据文件分开存放，不但可以提高mysql性能，还可以增加安全性！

    2.3.5.  中继日志

   主要在mysql服务器的中从架构中的从服务器上使用，当主从服务器进行数据同步时，从服务器将主服务器的二进制日志文件拷贝到己的主机上，并放在中继日志中，再调用SQL线程按照中继日志文件中的二进制日志文件执行，以达到数据的同步。

    开启的方法：  只能在从服务器上开启   
配置mysql的主配置文件：    

relay-log = host_name-relay-bin 

  #指定中继日志的位置和名字，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志  

relay-log-index = host_name-relay-bin.index 

 #指定中继日志的名字的索引文件的位置和名字，默认为数据目录中的host_name-relay-bin.index

relay-log-info-file = relay-log.info 

 #设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info

relay_log_purge = {ON|OFF} 

 #设定对不再需要的中继日志是否自动进行清理。默认值为ON

relay_log_space_limit =  0  

 #用于存储所有中继日志文件的可用空间大小。默认为 0 ，表示不限定。最大值取决于系统平台位数

max_relay_log_size = { 4096 .. 1073741824 } 

 #从服务器上中继日志的体积上限，到达此限度时，自动进行中继日志滚动。此值为 0 时，将使用max_binlog_size参数，同时为二进制日志和中继日志设定日志文件体积上限

    2.3.6.   事务日志

   详细的记录在什么时间发生了什么事务，在哪个时间对哪些数据进行了改变，能够实现事件的重放，一般只记录对数据进行改变的操作，对于读操作一般不进行记录。事务性存储引擎用于保证原子性、一致性、隔离性和持久性。

2.3.6.1.   主要功能    

  2.3.6.1.1.   将随机IO转换为顺序IO，大大的提高了数据库的性能，存储的数据可能存在在磁盘的不同位置，降低了数据的读取和操作性能。转换为顺序IO的原理为，先将数据存放在日志文件中，然后由RDBSM的后台将日志中的数据存放到磁盘上，这样就保证了存储的数据是连续的。    
  2.3.6.1.2.  为事件重放提供基础，事务日志详细的记录了时间发生的时间以及操作的数据对象，事务进程可以根据这些信息进行时间重放。

    
2.3.6.2.  默认的事务日志文件有两个，位于数据目录下以ibdata+number结尾的数字，我们可以对事务日志的位置、文件大小、增长方式进行定义，定义的方法如下：    

    配置mysql的主配置文件： (以使用支持事务的Innodb存储引擎为例)

innodb_flush_log_at_commit  

  #默认值为1，共有三个级别:

    0：每秒同步，并执行磁盘flush操作；

    1：每事务同步，并执行磁盘flush操作；

    2：每事务同步，但不执行磁盘flush操作

innodb_data_home_dir =  /mydata/data   

  #InnoDB所有共享表空间数据文件的目录路径，默认在数据目录下

innodb_data_file_path = ibdata1:1024M  

  #指定InnoDB的各个数据文件及其大小，文件大于一个时，彼此间用分号隔开。

innodb_mirrored_log_groups

   #事务日志是否启用镜像设定 

innodb_data_file_path = ibdata2:50M:autoextend

   #定义数据大小的增长方式

innodb_log_group_home_dir =  /mydata/data  

  #设定InnoDB重要日志文件的存储目录。缺省时使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件

innodb_log_group_home_dir =  /mydata/data  

  #设定InnoDB重要日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件

innodb_log_files_in_group = {2 .. 100} 

  #设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2

innodb_log_file_size = {108576 .. 4294967295} 

  #设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度

innodb_log_buffer_size = {262144 .. 4294967295} 

  #设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值

    2.3.7.   滚动日志

   只要是针对二进制日志进行滚动的，对某个类型的日志文件滚动一次就生成一个新的相对应的日志文件，通过这种方法保证日志文件的特定大小，从而保证服务器在对日志文件查询时有较高的响应能力。

滚动二进制日志的命令：

 mysql> FLUSH LOGS;        #实现二进制日志滚动

---end---13---

本文出自 “风过无痕” 博客，请务必保留此出处http://wangfx.blog.51cto.com/1697877/1936761

原文：http://wangfx.blog.51cto.com/1697877/1936761