Mysql 日志管理详解
日志对于服务器维护来说是非常重要的，它记录了服务器运行的信息，许多操作都会写入到日志文件，通过日志文件可以了解服务器的运行状态、服务器性能等；对维护人员进行排错，故障处理，优化等提供详细的依据。MySQL中有六种不同类型的日志一、日志种类1、错误日志：记录mysql启动，运行或停止时出现的问题信息，一般也会记录警告信息2、一般查询日志：记录与建立的客户端连接和执行的语句3、慢查询日志：记录所有执行时间超高long_query_time 的所有查询或不使用索引的查询，可以帮助我们定位服务器性能的问题，优化索引。4、二进制日志：任何引起或可能引起数据变化的操作，主要用于复制和即时点恢复5、中继日志：从主服务器的二进制日志文件中复制而来的时间，并保存为slave 服务器的日志文件6、事务日志：记录InnoDB 等支持事务的存储引擎执行事务时产生的日志Mysql 中对于日志文件环境变量非常多，可以使用一下命令查看mysql> show global variables like '%log%';+-----------------------------------------+----------------------------------------+| Variable_name | Value |+-----------------------------------------+----------------------------------------+| back_log | 50 || binlog_cache_size | 32768 || binlog_direct_non_transactional_updates | OFF || binlog_format | STATEMENT || binlog_stmt_cache_size | 32768 || expire_logs_days | 0 || general_log | OFF || general_log_file | /application/mysql/data/bogon.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 | OFF || log_bin_trust_function_creators | OFF || log_error | /application/mysql/data/bogon.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 | /application/mysql/data/bogon-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)二、错误日志 错误日志主要记录：
1、服务器启动和关闭过程中的信息2、服务器运行过程中的错误信息3、事件调度器运行一个事件时产生的信息4、在从服务器上启动从服务器进程时产生的信息 错误日志定义：
可以用–log-error [ = file_name ]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行flush logs，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出–log-error选项，则不会重新命名）。错误日志一般有log_error(错误日志文件) 和log_warnings(启用警告信息) 两个变量可以定义,默认使用log_warnings。
mysql> show global variables like '%log_error%';+---------------+-----------------------------------+| Variable_name | Value |+---------------+-----------------------------------+| log_error | /application/mysql/data/bogon.err |+---------------+-----------------------------------+1 row in set (0.00 sec)mysql> show global variables like '%log_warnings%';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_warnings| 1 |+---------------+-------+1 row in set (0.00 sec)三、查询日志 3.1 一般查询日志 启动开关：general_log={ON|OFF} 日志文件变量：general_log_file [ =/PATH/TO/file]
全局日志开关：log={ON|OFF}；该开关打开后，所有日志都会被启用
记录类型：log_output={TABLE|FILE|NONE}；log_output定义了日志的输出格式，可以是表，文件，若设置为NONE，则不启用日志。因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。由于一般查询使用量比较大，启用写入日志文件，服务器的I/O操作较多，会大大降低服务器的性能，所以默认为关闭的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show global variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------+
| general_log | OFF |
| general_log_file | /application/mysql/data/bogon.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql> show global variables like '%general_log_file%';
+------------------+-----------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------+
| general_log_file | /application/mysql/data/bogon.log |
+------------------+-----------------------------------+
1 row in set (0.00 sec)
可以使用以下命令开启general_log：
1
2
3
4
5
6
7
8
mysql> show global variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------+
| general_log | ON |
| general_log_file | /application/mysql/data/bogon.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)
3.2、慢查询日志 查询超时时间：long_query_time
启动慢查日志：log_slow_queries={YES|NO}
启动慢查日志 : slow_query_log
日志记录文件：slow_query_log_file [= file_name ]
MySQL如果启用了slow_query_log=ON选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
1
2
3
4
5
6
7
8
mysql> show global variables like '%slow_query_log%';
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /application/mysql/data/bogon-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)
默认没有启用慢查询，为了服务器调优，建议开启。
1
2
3
4
5
6
7
8
mysql> show global variables like '%long_query%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
超长时间默认为10秒，超过了即为慢查询。
四、二进制日志
二进制日志启动开关：log-bin [= file_name]
二进制日志用于记录所有更改数据的语句，主要用于复制和即时点恢复。二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新,二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
查看二进制日志的工具为：mysqlbinlog
1
2
3
4
5
6
7
8
9
10
11
12
二进制日志的格式：
1、基于语句: statement level;#优点：存储量小,节约IO，提高性能
2、基于行: row level;#优点：1非常清楚的记录每一行数据修改的细节，不会出现某些特定情况下存储过程
3、混合方式: mixed level;#继承了row level模式和statement level模式的优点，mysql 自动选择最佳的格式
二进制日志事件：
1、产生的时间：starttime
2、相对位置：position
二进制日志文件：
1、索引文件
2、二进制日志文件
在数据目录下有一个mysql-bin.index便是索引文件，以mysql-bin开头并以数字结尾的文件为二进制日志文件。
日志的滚动：
MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。
另外如果需要手动滚动，则使用命令：
1
2
3
4
5
6
7
8
mysql> show binary logs;#查看所有的二进制文件
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1067 |
| mysql-bin.000002 | 513 |
+------------------+-----------+
2 rows in set (0.00 sec)
插入数据后，position会发生改变。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
修改之前的position
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 929 ####未改变前的position 为929
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
修改之后
mysql> insert into master(name) values('lilei');#插入一条数据
Query OK, 1 row affected (0.02 sec)
mysql> insert into master(name) values('lilei');
Query OK, 1 row affected (0.01 sec)
mysql> insert into master(name) values('lilei');
Query OK, 1 row affected (0.02 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1547#####插入三条语句后的position 变成了1547
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002' from 929;
+------------------+------+------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos| Event_type | Server_id | End_log_pos | Info |
+------------------+------+------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000002 |929 | Query | 1 | 1001 | BEGIN |
| mysql-bin.000002 | 1001 | Query | 1 | 1108 | use `master01`; insert into master(name) values('lilei') |
| mysql-bin.000002 | 1108 | Xid | 1 | 1135 | COMMIT /* xid=51 */ |
| mysql-bin.000002 | 1135 | Query | 1 | 1207 | BEGIN |
| mysql-bin.000002 | 1207 | Query | 1 | 1314 | use `master01`; insert into master(name) values('lilei') |
| mysql-bin.000002 | 1314 | Xid | 1 | 1341 | COMMIT /* xid=52 */ |
| mysql-bin.000002 | 1341 | Query | 1 | 1413 | BEGIN |
| mysql-bin.000002 | 1413 | Query | 1 | 1520 | use `master01`; insert into master(name) values('lilei') |
| mysql-bin.000002 | 1520 | Xid | 1 | 1547 | COMMIT /* xid=53 */ |
+------------------+------+------------+-----------+-------------+----------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> purge binary logs to 'mysql-bin.000002';#用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000002 | 1547 |
+------------------+-----------+
1 row in set (0.00 sec)
使用命令mysqlbinlog查看二进制日志内容：基本语法：mysqlbinlog log-files
常用options（类似字节偏移数）：
--start-position ：开始位置
--stop-position ：结束位置
--start-datetime 'yyyy-mm-dd hh:mm:ss'：开始时间
--stop-datetime 'yyyy-mm-dd hh:mm:ss'：结束时间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# bin/mysqlbinlog --start-position 513 --stop-position 1067 ./data/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
#161229 21:56:12 server id 1end_log_pos 107 Start: binlog v 4, server v 5.5.53-log created 161229 21:56:12 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
/BVlWA8BAAAAZwAAAGsAAAABAAQANS41LjUzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD8FWVYEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
五、中继日志
六、事务日志事务性存储引擎用于保证（ACID）原子性、一致性、隔离性和持久性；其不会立即写到数据文件中，而是写到事务日志中。
1
2
3
4
innodb_flush_log_at_trx_commit:
0: 每秒同步，并执行磁盘flush操作；
1：每事务同步，并执行磁盘flush操作；
2: 每事务同步，但不执行磁盘flush操作；
1
2
3
4
5
6
7
8
mysql> show global variables like '%innodb_flush%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
+--------------------------------+-------+
2 rows in set (0.00 sec)
页:
[1]