一、日志文件类型
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
101
102
103
104
105
106
|
mysql> show variables like
'%log%'
;
+-----------------------------------------+-----------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------+
| back_log |
50
|
| binlog_cache_size |
32768
|
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size |
32768
|
| expire_logs_days |
0
|
| general_log | OFF |
| general_log_file | /
var
/lib/mysql/rh6.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
|
| innodb_undo_logs |
128
|
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_error | /
var
/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | 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_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit |
0
|
| slow_query_log | OFF |
| slow_query_log_file | /
var
/lib/mysql/rh6-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog |
0
|
| sync_relay_log |
0
|
| sync_relay_log_info |
0
|
+-----------------------------------------+-----------------------------+
47
rows
in
set (
0.02
sec)
mysql> show variables like
'%log%'
;
+-----------------------------------------+-----------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------+
| back_log |
50
|
| binlog_cache_size |
32768
|
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size |
32768
|
| expire_logs_days |
0
|
| general_log | OFF |
| general_log_file | /
var
/lib/mysql/rh6.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
|
| innodb_undo_logs |
128
|
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_error | /
var
/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | 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_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit |
0
|
| slow_query_log | OFF |
| slow_query_log_file | /
var
/lib/mysql/rh6-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog |
0
|
| sync_relay_log |
0
|
| sync_relay_log_info |
0
|
+-----------------------------------------+-----------------------------+
47
rows
in
set (
0.02
sec)
|
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
|
[root@rh6 ~]# more /
var
/log/mysqld.log</strong>
150402
10
:
26
:
07
mysqld_safe Starting mysqld daemon
with
databases
from
/
var
/lib/mysql
150402
10
:
26
:
08
InnoDB: The InnoDB memory heap is disabled
150402
10
:
26
:
08
InnoDB: Mutexes
and
rw_locks use GCC atomic builtins
150402
10
:
26
:
08
InnoDB: Compressed tables use zlib
1.2.
3
150402
10
:
26
:
08
InnoDB: Using Linux native AIO
150402
10
:
26
:
08
InnoDB: CPU does
not
support crc32 instructions
150402
10
:
26
:
08
InnoDB: Initializing buffer pool, size =
128.
0M
150402
10
:
26
:
08
InnoDB: Completed initialization of buffer pool
150402
10
:
26
:
08
InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number
in
ibdata files does
not
match
InnoDB: the log sequence number
in
the ib_logfiles!
150402
10
:
26
:
08
InnoDB: Database was
not
shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information
from
the .ibd files...
InnoDB: Restoring possible half-written data pages
from
the doublewrite
InnoDB: buffer...
150402
10
:
26
:
10
InnoDB:
128
rollback segment(s) are active.
150402
10
:
26
:
10
InnoDB: Waiting
for
the background threads to start
150402
10
:
26
:
11
InnoDB:
1.2.
4
started; log sequence number
1602901
150402
10
:
26
:
11
[Note] Recovering
after
a crash using mysql-bin
150402
10
:
26
:
11
[Note] Starting crash recovery...
150402
10
:
26
:
11
[Note] Crash recovery finished.
150402
10
:
26
:
11
[Note] Event Scheduler: Loaded
0
events
150402
10
:
26
:
11
[Note] /usr/local/mysql/bin/mysqld: ready
for
connections.
Version:
'5.6.4-m7-log'
socket:
'/tmp/mysql.sock'
port:
3306
Source distribution
<strong>[root@rh6 ~]# more /
var
/log/mysqld.log</strong>
150402
10
:
26
:
07
mysqld_safe Starting mysqld daemon
with
databases
from
/
var
/lib/mysql
150402
10
:
26
:
08
InnoDB: The InnoDB memory heap is disabled
150402
10
:
26
:
08
InnoDB: Mutexes
and
rw_locks use GCC atomic builtins
150402
10
:
26
:
08
InnoDB: Compressed tables use zlib
1.2.
3
150402
10
:
26
:
08
InnoDB: Using Linux native AIO
150402
10
:
26
:
08
InnoDB: CPU does
not
support crc32 instructions
150402
10
:
26
:
08
InnoDB: Initializing buffer pool, size =
128.
0M
150402
10
:
26
:
08
InnoDB: Completed initialization of buffer pool
150402
10
:
26
:
08
InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number
in
ibdata files does
not
match
InnoDB: the log sequence number
in
the ib_logfiles!
150402
10
:
26
:
08
InnoDB: Database was
not
shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information
from
the .ibd files...
InnoDB: Restoring possible half-written data pages
from
the doublewrite
InnoDB: buffer...
150402
10
:
26
:
10
InnoDB:
128
rollback segment(s) are active.
150402
10
:
26
:
10
InnoDB: Waiting
for
the background threads to start
150402
10
:
26
:
11
InnoDB:
1.2.
4
started; log sequence number
1602901
150402
10
:
26
:
11
[Note] Recovering
after
a crash using mysql-bin
150402
10
:
26
:
11
[Note] Starting crash recovery...
150402
10
:
26
:
11
[Note] Crash recovery finished.
150402
10
:
26
:
11
[Note] Event Scheduler: Loaded
0
events
150402
10
:
26
:
11
[Note] /usr/local/mysql/bin/mysqld: ready
for
connections.
Version:
'5.6.4-m7-log'
socket:
'/tmp/mysql.sock'
port:
3306
Source distribution
|
2、慢速查询日志(slow_query_log)
用--log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。获得初使表锁定的时间不算作执行时间。
如果没有给出file_name值, 默认未主机名,后缀为-slow.log。如果给出了文件名,但不是绝对路径名,文件则写入数据目录。
语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。
慢查询日志可以用来找到执行时间长的查询,可以用于优化。但是,检查又长又慢的查询日志会很困难。要想容易些,你可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。
在MySQL 5.1的慢查询日志中,不使用索引的慢查询同使用索引的查询一样记录。要想防止不使用索引的慢查询记入慢查询日志,使用--log-short-format选项。
在MySQL 5.1中,通过--log-slow-admin-statements服务器选项,你可以请求将慢管理语句,例如OPTIMIZE TABLE、ANALYZE TABLE和 ALTER TABLE写入慢查询日志。
用查询缓存处理的查询不加到慢查询日志中,因为表有零行或一行而不能从索引中受益的查询也不写入慢查询日志。
-
默认情况下MySql没有开启慢查询日志,开启慢查询日志:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> set global slow_query_log=
on
;
Query OK,
0
rows affected (
0.33
sec)
mysql> show variables like
'%slow_%'
;
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time |
2
|
| slow_query_log | ON |
| slow_query_log_file | /
var
/lib/mysql/rh6-slow.log |
+---------------------+-----------------------------+
3
rows
in
set (
0.02
sec)
<pre
class
=
"html"
name=
"code"
>mysql> set global slow_query_log=
on
;
Query OK,
0
rows affected (
0.33
sec)
mysql> show variables like
'%slow_%'
;
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time |
2
|
| slow_query_log | ON |
| slow_query_log_file | /
var
/lib/mysql/rh6-slow.log |
+---------------------+-----------------------------+
3
rows
in
set (
0.02
sec)
|
1
2
3
4
5
6
7
8
9
|
[root@rh6 mysql]# cat /
var
/lib/mysql/rh6-slow.log
/usr/local/mysql/bin/mysqld, Version:
5.6.
4
-m7 (Source distribution). started
with
:
Tcp port:
3306
Unix socket: /tmp/mysql.sock
Time Id Command Argument
[root@rh6 mysql]# cat /
var
/lib/mysql/rh6-slow.log
/usr/local/mysql/bin/mysqld, Version:
5.6.
4
-m7 (Source distribution). started
with
:
Tcp port:
3306
Unix socket: /tmp/mysql.sock
Time Id Command Argument
|
3、二进制日志(log_bin)
1)二进制文件介绍
2)二进制文件的功能
3)二进制文件的存储路径
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
|
开启二进制日志:
[root@rh6 mysql]
# cat /etc/my.cnf
[mysqld]
datadir=/
var
/lib/mysql
socket=/tmp/mysql.sock
#socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
binlog_format=mixed
[mysqld_safe]
log-error=/
var
/log/mysqld.log
pid-file=/
var
/run/mysqld/mysqld.pid
<strong>开启二进制日志:</strong>
[root@rh6 mysql]
# cat /etc/my.cnf
[mysqld]
datadir=/
var
/lib/mysql
socket=/tmp/mysql.sock
#socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
binlog_format=mixed
[mysqld_safe]
log-error=/
var
/log/mysqld.log
pid-file=/
var
/run/mysqld/mysqld.pid
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> show variables like
'%log_bin%'
;
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /
var
/lib/mysql/mysql-bin |
| log_bin_index | /
var
/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
5
rows
in
set (
0.00
sec)
mysql> show variables like
'%log_bin%'
;
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /
var
/lib/mysql/mysql-bin |
| log_bin_index | /
var
/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
5
rows
in
set (
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@rh6 ~]# ls -l /
var
/lib/mysql/
total
28704
-rw-rw----
1
mysql mysql
56
Jan
28
17
:
25
auto.cnf
-rw-rw----
1
mysql mysql
18874368
Apr
2
10
:
26
ibdata1
-rw-rw----
1
mysql mysql
5242880
Apr
2
10
:
26
ib_logfile0
-rw-rw----
1
mysql mysql
5242880
Jan
28
17
:
21
ib_logfile1
drwx------
2
mysql root
4096
Jan
28
17
:
21
mysql
-rw-rw----
1
mysql mysql
114
Apr
2
10
:
26
mysql-bin
.000001
-rw-rw----
1
mysql mysql
114
Apr
2
10
:
26
mysql-bin
.000002
-rw-rw----
1
mysql mysql
38
Apr
2
10
:
26
mysql-bin.index
drwx------
2
mysql mysql
4096
Jan
28
17
:
21
performance_schema
-rw-rw----
1
mysql mysql
173
Mar
31
11
:
05
rh6-slow.log
drwx------
2
mysql root
4096
Jan
28
17
:
21
test
|
1
2
3
|
[root@rh6 mysql]# cat mysql-bin.index
./mysql-bin
.000001
./mysql-bin
.000002
|
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
|
[root@rh6 mysql]# mysqlbinlog mysql-bin
.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
#
at
4
#
150402
10
:
26
:
11
server id
1
end_log_pos
114
Start: binlog v
4
, server v
5.6.
4
-m7-log created
150402
10
:
26
:
11
at
startup
# Warning:
this
binlog is either
in
use
or
was
not
closed properly.
ROLLBACK
/*!*/
;
BINLOG '
w6gcVQ8BAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADDqBxVEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAJLUw68=
'
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET <a target=_blank href="mailto:COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;">COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
</a></strong>
<strong>[root@rh6 mysql]# mysqlbinlog mysql-bin
.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
#
at
4
#
150402
10
:
26
:
11
server id
1
end_log_pos
114
Start: binlog v
4
, server v
5.6.
4
-m7-log created
150402
10
:
26
:
11
at
startup
# Warning:
this
binlog is either
in
use
or
was
not
closed properly.
ROLLBACK
/*!*/
;
BINLOG '
w6gcVQ8BAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADDqBxVEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAJLUw68=
'
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET <a target=_blank href="mailto:COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;">COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
</a></strong>
[html] view plaincopyprint?
<strong>[root@rh6 mysql]# mysqlbinlog mysql-bin
.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
#
at
4
#
150331
11
:
11
:
39
server id
1
end_log_pos
114
Start: binlog v
4
, server v
5.6.
4
-m7-log created
150331
11
:
11
:
39
at
startup
ROLLBACK
/*!*/
;
BINLOG '
axAaVQ8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABrEBpVEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAALen85c=
'
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
[root@rh6 mysql]# </strong>
<strong>[root@rh6 mysql]# mysqlbinlog mysql-bin
.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
#
at
4
#
150331
11
:
11
:
39
server id
1
end_log_pos
114
Start: binlog v
4
, server v
5.6.
4
-m7-log created
150331
11
:
11
:
39
at
startup
ROLLBACK
/*!*/
;
BINLOG '
axAaVQ8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABrEBpVEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAALen85c=
'
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> reset master;
Query OK,
0
rows affected (
0.08
sec)
[root@rh6 mysql]# ls -l
total
28700
-rw-rw----
1
mysql mysql
56
Jan
28
17
:
25
auto.cnf
-rw-rw----
1
mysql mysql
18874368
Apr
2
10
:
26
ibdata1
-rw-rw----
1
mysql mysql
5242880
Apr
2
10
:
26
ib_logfile0
-rw-rw----
1
mysql mysql
5242880
Jan
28
17
:
21
ib_logfile1
drwx------
2
mysql root
4096
Jan
28
17
:
21
mysql
-rw-rw----
1
mysql mysql
114
Apr
2
11
:
20
mysql-bin
.000001
-rw-rw----
1
mysql mysql
19
Apr
2
11
:
20
mysql-bin.index
drwx------
2
mysql mysql
4096
Jan
28
17
:
21
performance_schema
-rw-rw----
1
mysql mysql
173
Mar
31
11
:
05
rh6-slow.log
drwx------
2
mysql root
4096
Jan
28
17
:
21
test</span>
|