mysql 中继日志路径_MySQL学习笔记之六：MySQL日志

MySQL日志主要包括：查询日志、慢查询日志、错误日志、二进制日志、中继日志、事务日志

查看与日志相关的服务器变量：SHOW GLOBAL VARIABLES LIKE '%log%';MariaDB [(none)]> show global variables like '%log%';

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

| Variable_name                             | Value                                                                                                        |

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

...

...

| binlog_annotate_row_events                | OFF                                                                                                          |

| binlog_cache_size                         | 32768                                                                                                        |

| binlog_checksum                           | NONE                                                                                                         |

| binlog_commit_wait_count                  | 0                                                                                                            |

| binlog_commit_wait_usec                   | 100000                                                                                                       |

| binlog_direct_non_transactional_updates   | OFF                                                                                                          |

| binlog_format                             | MIXED                                                                                                        |

| binlog_optimize_thread_scheduling         | ON                                                                                                           |

| binlog_stmt_cache_size                    | 32768                                                                                                        |

| expire_logs_days                          | 0                                                                                                            |

| general_log                               | OFF                                                                                                          |

| general_log_file                          | node1.log                                                                                                    |

| gtid_binlog_pos                           | 0-1-2218                                                                                                     |

| gtid_binlog_state                         | 0-1-2218                                                                                                     |

| innodb_api_enable_binlog                  | OFF                                                                                                          |

| innodb_flush_log_at_timeout               | 1                                                                                                            |

| innodb_flush_log_at_trx_commit            | 1

...

...

1、一般查询日志：记录查询信息，I/O开销太大，不建议开启

general_log = {ON|OFF}: 是否启用一般查询日志；默认为OFF

log_output = {TABLE|FILE|NONE}: 一般查询和慢查询日志的输出方式；

默认为FILE，即记录于文件中；如果记录于表中，这个表位于mysql库中；可既记录在表中，又记录在文件中，如log_output = TABLE,FILE；NONE表示不记录

general_log_file = /path/to/somefile：当log_output有FILE类型时，日志信息的输出位置；若使用相对路径，则是相对数据目录而言；默认为HOSTNAME.log

2、慢查询日志：记录查询执行时长超过指定时长的查询操作信息；当mysql服务器性能较低时，可作排错参考。

查询超过多久可判定为慢查询？

SELECT @@GLOBAL.long_query_time;   #默认为10秒

slow_query_log = {ON|OFF}：是否启用慢查询日志；默认为OFF

slow_query_log_file = /path/to/somefile：日志文件路径；可使用相对路径；默认为HOSTNAME-slow.log

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

日志过滤器；查询慢的原因有时并非语句本身执行慢，而是被阻塞了，像这类查询信息就不应记录，我们应只记录那些语句本身执行慢的查询信息

long_query_time = 2

log_slow_rate_limit = 1：是否对慢查询进行限速

log_slow_verbosity =  ：是否以详细格式记录；默认为空ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

MariaDB [(none)]> select @@global.long_query_time;

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

| @@global.long_query_time |

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

|                10.000000 |

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

1 row in set (0.08 sec)

MariaDB [(none)]> set global slow_query_log = 'ON';   #开启慢查询日志

Query OK, 0 rows affected (0.19 sec)

MariaDB [(none)]> \! vim /etc/my.cnf

...

[mysqld]

...

slow_query_log = ON   #在[mysqld]段下添加该项

3、错误日志，它会记录：

mysqld启动和关闭过程中输出的信息；

mysqld运行中产生的错误信息；

event scheduler运行一个event时产生的日志信息；

在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息；

log_error = /path/to/somefile；默认为数据目录下HOSTNAME.err

log_warnings = {0|1}：是否记录警告信息于错误日志中；默认值为1

4、二进制日志：精确地记录引起数据改变的操作，常用于数据的备份和恢复中，很重要！

⑴服务器变量：

sql_log_bin = ON

log_bin = ON

binlog_format = MIXED|STATEMENT|ROW：日志记录格式，默认为MIXED

基于“语句”记录是常用的方式，但有时也需要基于“行”记录(场景示例：UPDATE tb5 SET TIME =CURRENT_TIME() WHERE id = 1;)，所以采用“混合”方式比较合理。

max_binlog_size = 1073741824：二进制日志文件的单文件上限；

如果超过该值，则自动滚动日志，后缀名+1，并记录到以.index结尾的日志索引文件

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

max_binlog_stmt_cache_size = 18446744073709547520：语句缓存的大小

sync_binlog = 0：设定多久同步一次二进制日志文件至磁盘；0表示由文件系统自己控制日志缓存的刷新；如果是正值则表示每sync_binlog次事务提交就同步一次；

⑵二进制日志文件的构成：

日志文件：文件名前缀.文件名后缀，如mysql-bin.000001

日志索引文件：文件名前缀.index，如：mysql-bin.index

⑶二进制日志文件的输出路径：

在配置文件中，log-bin=/path/to/somefile，可使用绝对路径或相对路径，相对路径是相对于数据目录而言；路径以日志文件的前缀结束。例如：

log-bin=mysql-bin

log-bin=/log/master-bin

⑶二进制日志的格式：

# at 19364

#140829 15:50:07 server id 1  end_log_pos  19486 Query  thread_id=13  exec_time=0  error_code=0

SET TIMESTAMP=1409298607/*!*/;

GRANT SELECT ON tdb.* TO tuser@localhost

/*!*/;

# at 19486

140829 15:50:07：事件发生的日期和时间

server id 1：事件发生所在服务器的标识

end_log_pos 19486：事件的结束位置

Query：事件的类型

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

exec_time=0：语句的时间戳与将其写入二进制文件中的时间差

error_code=0：错误代码

SET TIMESTAMP=1409298607/*!*/;GRANT SELECT ON tdb.* TO tuser@localhost：事件内容

GTID 0-1-2：事件所属的全局事务的GTID，GTID事件专属

⑷二进制日志的相关命令：

SHOW {BINARY | MASTER} LOGS;  #查看主服务器端处于由mysqld维护状态中的二进制日志文件

SHOW MASTER STATUS;  #查看主服务器端当前正在使用的二进制日志及所处的位置

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];  #显示指定的二进制日志文件中的相关事件

例 show binlog events in 'mysql-bin.000005' from 3339 limit 5;

FLUSH LOGS;  #滚动日志

⑸安全地清理二进制日志

二进制日志虽然重要，但为防止其将磁盘占满，我们可定期清理一些过期的二进制日志

①可手动清理：

mysql > PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };   #清除某个指定二进制文件或时间点之前的所有二进制文件

②或制定周期性任务计划

0 0 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL n DAY"

③还可设置服务器变量 expire_logs_days，该变量设置会在服务器重启或mysql切换二进制日志时生效

注意：不要用rm命令直接删除二进制日志文件，那样会导致mysql-bin.index文件与磁盘上的文件不一致

⑹mysqlbinlog：一款用于处理二进制日志的mysql客户端工具

用法：mysqlbinlog [options] log_file ...

常用选项：

-j, --start-position=#：从指定的事件位置查看

--stop-position=#：只显示到指定的事件位置

--start-datetime=name

--stop-datetime=name

时间格式：YYYY-MM-DD hh:mm:ss

--read-from-remote-server：从远程服务器读取二进制日志

-d,--database=name: 列出指定库的相关条目

-u, --user=   #指定以哪个用户身份连接，缺省为root

-h, --host=   #指定连接到哪台服务器，缺省为localhost

-p, --password=

例：mysqlbinlog --start-datetime='2015-09-01 15:30:00' --stop-datetime='2015-09-01 16:00:00' /mydata/data/mysql-bin.* > /backup/incre-`date +%F`.sql

解析row级别binlog的方法：

mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000011

注意：-v 选项不能缺，否则依然看不到具体的语句MariaDB [(none)]> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |     67304 |

| mysql-bin.000002 |    977605 |

| mysql-bin.000003 |       345 |

| mysql-bin.000004 |       326 |

| mysql-bin.000005 |     16629 |

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

5 rows in set (0.07 sec)

MariaDB [(none)]> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 |    16629 |              |                  |

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

1 row in set (0.08 sec)

MariaDB [testdb]> show binlog events in 'mysql-bin.000002' from 578 limit 4;

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

| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                          |

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

| mysql-bin.000002 | 578 | Gtid       |         1 |         616 | BEGIN GTID 0-1-2220                                           |

| mysql-bin.000002 | 616 | Query      |         1 |         720 | use `testdb`; delete from students where Name = 'tom'         |

| mysql-bin.000002 | 720 | Intvar     |         1 |         748 | INSERT_ID=14                                                  |

| mysql-bin.000002 | 748 | Query      |         1 |         860 | use `testdb`; insert students (Name,Gender) value ('jack',25) |

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

4 rows in set (0.00 sec)

MariaDB [testdb]> exit

[root@node1 data]# mysqlbinlog mysql-bin.000001

...

# at 578

#160303  4:06:44 server id 1  end_log_pos 616 GTID 0-1-2220

/*!100001 SET @@session.gtid_seq_no=2220*//*!*/;

BEGIN

/*!*/;

# at 616

#160303  4:06:01 server id 1  end_log_pos 720 Querythread_id=49exec_time=0error_code=0

SET TIMESTAMP=1456949161/*!*/;

delete from students where Name = 'tom'

/*!*/;

...

5、中继日志：主从架构中从服务器上同步主服务器操作的中间日志

max_relay_log_size = {4096..1073741824}：从服务器上中继日志的体积上限，超过此值会自动滚动日志。此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限。

relay_log = /path/to/somefile：中继日志的文件路径，默认为host_name-relay-bin。

relay_log_index = /path/to/somefile：中继日志的索引文件路径，默认为host_name-relay-bin.index。

relay-log-info-file = /path/to/somefile：中继服务用于记录中继信息的文件，默认为relay-log.info。

relay_log_purge = {ON|OFF}：对不再需要的中继日志是否自动进行清理。默认值为ON。

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

6、事务日志(innodb存储引擎)：将随机I/O转换为顺序I/O，以提升事务操作效率

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

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

innodb_flush_log_at_trx_commit = 1

0：每一秒将缓冲写入日志，并且刷新到磁盘，最极端的情况是丢失一秒的数据变更

1：默认值，每提交一次事务就把缓存的redo日志写入日志文件并刷新到磁盘，最安全

2：每提交一次事务就把缓存的redo日志写入日志文件，但不保证立即刷新到磁盘

innodb_log_files_in_group = {2 .. 100}：事务日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。

innodb_log_group_home_dir = /PATH/TO/DIR：事务日志文件的存储目录，默认为数据目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件。

innodb_mirrored_log_groups = #：事务日志镜像组数。默认值为1，表示只有原本，无副本

[root@node1 ~]# ls /mydata/data   #查看数据目录

aria_log.00000001  hellodb  ib_logfile0  multi-master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  node1.err  performance_schema  testdb

aria_log_control   ibdata1  ib_logfile1  mysql              mysql-bin.000002  mysql-bin.000004  mysql-bin.index   node1.pid  test

#ib_logfile为事务日志文件，mysql-bin.00000#为二进制日志文件

mysql-bin.index为二进制日志索引文件，node1.err为错误日志文件