一、MySQL日志的分类
(1)错误日志、(2)一般查询日志、(3)慢查询日志、(4)二进制日志、(5)中继日志、(6)事务日志
二、查看MySQL的日志参数变量
|
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
|
mysql> SHOW GLOBAL VARIABLES LIKE
'%log%'
;
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 1048576 |
| 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/localhost
.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error |
/tmp/localhost
.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| 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 | ON |
| slow_query_log_file |
/mydata/data/localhost-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)
|
三、MySQL日志分类详解
错误日志：
问题：
1、哪些参数可以定义错误日志
2、错误日志记录了哪些信息
3、如何查看错误日志的记录信息
问题1：以下参数与定义错误日志相关
|
1
2
|
log_error：定义是否启用错误日志的功能和错误日志的存储位置
log_warnings：定义是否将警告信息也定义至错误日志中
|
|
1
2
3
4
5
6
|
[mysqld]
log_error = DIR/[filename]
解析：其中，DIR参数指定错误日志的路径filename参数是错误日志的名称，没有指定该参数时默认为主机名。重启mysql服务器即可生效。
例如：
[mysqld]
log_error =
/tmp/localhost
.err
|
问题2：错误日志记录了以下信息
|
1
2
3
4
|
1. 服务器启动和关闭过程中的信息
2. 服务器运行过程中的错误信息
3. 事件调度器运行一个事件时产生的信息
4. 在主从复制模型当中，在从服务器启动或停止从服务器中继进程时所产生的日志信息
|
问题3：查看错误日志
|
1
2
|
1.初始化数据库
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ --basedir=/usr/local/mysql
|
|
1
2
|
2.启动数据库
# service mysqld start
|
|
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
|
3.查看错误日志信息
[root@localhost ~]
# cat localhost.com.err
150226 11:43:12 mysqld_safe mysqld from pid
file
/mydata/data/localhost
.com.pid ended
150226 11:45:41 mysqld_safe Starting mysqld daemon with databases from
/mydata/data
150226 11:45:41 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed
in
a future release. Please use the full name instead.
150226 11:45:41 [Note] Plugin
'FEDERATED'
is disabled.
150226 11:45:41 InnoDB: The InnoDB memory heap is disabled
#禁用了InnoDB memory的堆功能。
150226 11:45:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
#Mutexes（互斥量）和rw_locks（行级锁）是GCC编译的是InnoDB内置的。
150226 11:45:41 InnoDB: Compressed tables use zlib 1.2.3
#默认压缩工具是zlib
150226 11:45:41 InnoDB: Using Linux native AIO
150226 11:45:41 InnoDB: Initializing buffer pool, size = 2.0G
#InnoDB引擎的缓冲池（buffer pool）的值大小
150226 11:45:42 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data
file
.
/ibdata1
did not exist:
InnoDB: a new database to be created!
150226 11:45:42 InnoDB: Setting
file
.
/ibdata1
size to 10 MB
InnoDB: Database physically writes the
file
full: wait...
150226 11:45:42 InnoDB: Log
file
.
/ib_logfile0
did not exist: new to be created
InnoDB: Setting log
file
.
/ib_logfile0
size to 256 MB
InnoDB: Database physically writes the
file
full: wait...
InnoDB: Progress
in
MB: 100 200
150226 11:45:43 InnoDB: Log
file
.
/ib_logfile1
did not exist: new to be created
InnoDB: Setting log
file
.
/ib_logfile1
size to 256 MB
InnoDB: Database physically writes the
file
full: wait...
InnoDB: Progress
in
MB: 100 200
150226 11:45:44 InnoDB: Log
file
.
/ib_logfile2
did not exist: new to be created
InnoDB: Setting log
file
.
/ib_logfile2
size to 256 MB
InnoDB: Database physically writes the
file
full: wait...
InnoDB: Progress
in
MB: 100 200
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
150226 11:45:46 InnoDB: Waiting
for
the background threads to start
150226 11:45:47 InnoDB: 5.5.33 started; log sequence number 0
150226 11:45:47 [Note] Server
hostname
(bind-address):
'0.0.0.0'
; port: 3306
150226 11:45:47 [Note] -
'0.0.0.0'
resolves to
'0.0.0.0'
;
#0.0.0.0会反解主机名，这里反解失败
150226 11:45:47 [Note] Server socket created on IP:
'0.0.0.0'
.
150226 11:45:47 [Note] Event Scheduler: Loaded 0 events 事件调度器没有任何事件，因为没有装载。
150226 11:45:47 [Note]
/usr/local/mysql/bin/mysqld
: ready
for
connections.
#mysql启动完成等待客户端的请求。
Version:
'5.5.33-log'
socket:
'/tmp/mysql.sock'
port: 3306 MySQL Community Server (GPL)
#创建一个本地sock用于本地连接。
|
一般查询日志：
问题：
1、一般查询日志与哪些参数变量相关
2、一般查询日志作用，是否要开启此日志
问题1：以下参数与一般查询日志相关
|
1
2
3
4
|
general_log：定义是否开启查询日志
general_log_file：定义查询日志文件存放的路径
log：是否开启所有日志功能
log_output {TABLE|FILE|NONE}：日志的输出的位置
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
log_output:日志的输出位置
如果此项为TABLE，那么日志错输入到表中，如下：
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
#一般查询日志的table表
| help_category |
......
+-------------------------
|
问题2：一般查询日志的作用，是否开启此日志
|
1
|
默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。
|
慢查询日志：
问题：
1、哪些参数与慢查询日志相关
2、什么是慢查询
3、定义、查询慢查询参数
问题1：哪些参数与慢查询日志相关
|
1
2
3
4
|
long_query_time：定义慢查询的事件
log_slow_queries={ON|OFF}：是否开启慢查询
slow_query_log：开启慢查询
slow_query_log_file=
/PATH/TO/SOMEFILE
：定义慢查询文件的存放路径
|
问题2：什么是慢查询
|
1
2
|
慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录
mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。
|
问题3：定义、查询慢查询参数
|
1
2
3
4
|
[mysqld]
log_slow_queries = ON
long_query_time = 2
slow_query_log_file =
/tmp/mysql_slow_query
.log
|
|
1
2
3
4
5
6
7
8
9
|
mysql> SHOW GLOBAL VARIABLES LIKE
'%slow%'
;
+---------------------+---------------------------+
| Variable_name | Value |
+---------------------+---------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file |
/tmp/mysql_slow_query
.log |
+---------------------+---------------------------+
|
|
1
2
3
4
5
6
7
8
9
|
mysql> SHOW GLOBAL VARIABLES LIKE
'%long%'
;
+---------------------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------------------+----------+
| long_query_time | 2.000000 |
| max_long_data_size | 16777216 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+----------+
3 rows
in
set
(0.00 sec)
|
二进制日志
补充：2015-02-28
在对数据库进行恢复的时候，建议session级别的关闭二进制日志!
-
数据恢复执行的DDL、DML、DCL操作没必要记录到二进制文件中
-
磁盘IO会影响恢复的时间
|
1
2
3
4
5
6
7
8
|
mysql>
set
session sql_log_bin = 0;
mysql> show session variables like
'%sql_log_bin%'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row
in
set
(0.00 sec)
|
中继日志：
|
1
|
从服务器上，从主服务器复制主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件。
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show global variables like
'%relay_log%'
;
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| 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 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------+----------------+
|
事务日志：ACID，将随机IO转换成顺序IO
|
1
2
3
4
5
6
|
事务性存储引擎用于保证原子性、一致性、隔离性和持久性
记录了每一次和事务执行过程当中对数据修改的信息
innodb_flush_log_at_trx_commit
0：每秒同步，并执行磁盘flush操作
1：每事务同步，并执行磁盘刷写；默认为1（安全性高 保证事务不会丢失）
2：每事务同步，但不执行磁盘flush操作；（效能高，但是数据不能得到保证）
|
|
1
2
3
4
5
6
7
8
9
|
mysql> show global variables like
'%innodb_log%'
;
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_log_buffer_size | 8388608 |
#事务日志的buffer大小
| innodb_log_file_size | 5242880 |
#事务日志文件的大小
| innodb_log_files_in_group | 2 |
#事务日志文件组的个数
| innodb_log_group_home_dir | ./ |
#事务日志存放路径；数据目录（--datadir）
+---------------------------+---------+
|
四、MySQL存储引擎MyISAM和InnoDB的比较
MyISAM
|
1
2
3
4
5
6
|
不支持事务
不支持外键
默认仅支持表锁，锁力度太大导致并发能力不强
共享锁（读锁）对于读操作比较多的场景当中，MyISAM存储引擎有了更强的性能，但是对于读写操作都比较适中的情况下，反而性能很差。
支持B树索引、FULLTEXT索引、空间索引
支持表压缩
|
InnoDB
|
1
2
3
4
|
事务性引擎
行级锁，锁力度精细
支持B树索引、聚簇索引、自适应
hash
索引
表空间、raw磁盘设备（裸设备）
|
五、MySQL参数变量详解
|
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
expire_logs_days={0..99}
设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。作用范围为全局，可用于配置文件，属动态变量。
general_log={ON|OFF}
设定是否启用查询日志，默认值为取决于在启动mysqld时是否使用了--general_log选项。如若启用此项，其输出位置则由--log_output选项进行定义，如果log_output的值设定为NONE，即使用启用查询日志，其也不会记录任何日志信息。作用范围为全局，可用于配置文件，属动态变量。
general_log_file=FILE_NAME
查询日志的日志文件名称，默认为“
hostname
.log"。作用范围为全局，可用于配置文件，属动态变量。
binlog-
format
={ROW|STATEMENT|MIXED}
指定二进制日志的类型，默认为STATEMENT。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话，可用于配置文件，且属于动态变量。
log={YES|NO}
是否启用记录所有语句的日志信息于一般查询日志(general query log)中，默认通常为OFF。MySQL 5.6已经弃用此选项。
log-bin={YES|NO}
是否启用二进制日志，如果为mysqld设定了--log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。
log_bin_trust_function_creators={TRUE|FALSE}
此参数仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为0，表示除非用户除了CREATE ROUTING或ALTER ROUTINE权限外还有SUPER权限，否则将禁止创建或修改存储函数，同时，还要求在创建函数时必需为之使用DETERMINISTIC属性，再不然就是附带READS SQL DATA或NO SQL属性。设置其值为1时则不启用这些限制。作用范围为全局级别，可用于配置文件，属动态变量。
log_error=
/PATH/TO/ERROR_LOG_FILENAME
定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。
log_output={TABLE|FILE|NONE}
定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。
log_query_not_using_indexes={ON|OFF}
设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别，可用于配置文件，属动态变量。
log_slave_updates
用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。
log_slow_queries={YES|NO}
是否记录慢查询日志。慢查询是指查询的执行时间超出long_query_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow_query_log。作用范围为全局级别，可用于配置文件，属动态变量。
log_warnings=
#
设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。
long_query_time=
#
设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。
max_binlog_cache_size{4096 .. 18446744073709547520}
二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。作用范围为全局级别，可用于配置文件，属动态变量。
max_binlog_size={4096 .. 1073741824}
设定二进制日志文件上限，单位为字节，最小值为4K，最大值为1G，默认为1G。某事务所产生的日志信息只能写入一个二进制日志文件，因此，实际上的二进制日志文件可能大于这个指定的上限。作用范围为全局级别，可用于配置文件，属动态变量。
max_relay_log_size={4096..1073741824}
设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别，可用于配置文件，属动态变量。
innodb_log_buffer_size={262144 .. 4294967295}
设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I
/O
操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_file_size={108576 .. 4294967295}
设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1
/n
，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I
/O
操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_files_in_group={2 .. 100}
设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。
innodb_log_group_home_dir=
/PATH/TO/DIR
设定InnoDB重做日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log=file_name
设定中继日志的文件名称，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log_index=file_name
设定中继日志的索引文件名，默认为为数据目录中的host_name-relay-bin.index。作用范围为全局级别，可用于选项文件，属非动态变量。
relay-log-info-
file
=file_name
设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info。作用范围为全局级别，可用于选项文件，属非动态变量。
relay_log_purge={ON|OFF}
设定对不再需要的中继日志是否自动进行清理。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。
relay_log_space_limit=
#
设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数。作用范围为全局级别，可用于选项文件，属非动态变量。
slow_query_log={ON|OFF}
设定是否启用慢查询日志。0或OFF表示禁用，1或ON表示启用。日志信息的输出位置取决于log_output变量的定义，如果其值为NONE，则即便slow_query_log为ON，也不会记录任何慢查询信息。作用范围为全局级别，可用于选项文件，属动态变量。
slow_query_log_file=
/PATH/TO/SOMEFILE
设定慢查询日志文件的名称。默认为
hostname
-slow.log，但可以通过--slow_query_log_file选项修改。作用范围为全局级别，可用于选项文件，属动态变量。
sql_log_bin={ON|OFF}
用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。
sql_log_off={ON|OFF}
用于控制是否禁止将一般查询日志类信息记录进查询日志文件。默认为OFF，表示不禁止记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。
sync_binlog=
#
设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步。
|
附加一个附属变量图
如何大家对MySQL的变量感兴趣的，可以参考马哥的博客链接如下：
MySQL 5.5 服务器变量详解(一)
http://mageedu.blog.51cto.com/4265610/1058357
MySQL 5.5 服务器变量详解(二)
http://mageedu.blog.51cto.com/4265610/1062628
本文转自zys467754239 51CTO博客，原文链接：http://blog.51cto.com/467754239/1615385，如需转载请自行联系原作者
