MySQL日志类型

  • 错误日志(Error log): mysqld启动,运行,停止相关的警告或错误信息
  • 普通查询日志(General query log): 记录从连接客户端发出的SQL语句和MySQL命令
  • 二进制日志(Binary log): 对数据库执行更新的语句,二进制日志也被用来在主从复制时使用
  • 中继日志(Relay log): 主库推送的主库二进制日志中的事件到从库的中继日志,从库根据中继日志重做数据变更操作
  • 慢查询日志(Slow query log): 执行时间超过long_query_time设定值的SQL语句,也可包含没有使用索引的SQL语句
  • DDL log (metadata log): Metadata operations performed by DDL statements

错误日志

MySQL的错误日志是默认开启,且必须开启。查看错误日志的位置:

1show variables like 'log_error'\G
2*************************** 1. row ***************************
3Variable_name: log_error
4        Value: stderr
51 row in set (0.01 sec)

在my.cnf [mysqld]通过log-error指定错误日志文件的位置:

1[mysqld]
2log-error=/var/log/mysql.err

普通查询日志

查看普通查询日志是否开启:

1show variables like 'general_log' \G
2*************************** 1. row ***************************
3Variable_name: general_log
4        Value: OFF
51 row in set (0.00 sec)

临时开启MySQL的普通查询日志(只在MySQL实例运行期间有效,MySQL重启后,会重新恢复到之前的状态):

1SET GLOBAL general_log = 'ON';

日志开启后,查看日志位置:

1 show variables like 'general_log_file';
2+------------------+---------------------------------+
3| Variable_name    | Value                           |
4+------------------+---------------------------------+
5| general_log_file | /var/lib/mysql/144dbea47cac.log |
6+------------------+---------------------------------+
71 row in set (0.00 sec)

关闭general log:

1SET GLOBAL general_log = 'OFF';

开启普通日志会影响MySQL的性能,一般只有在特别情况下,需要追踪具体日志的时候才会临时打开一下。

二进制日志

二进制日志顾名思义,在格式上是二进制的,需要借助专门的工具查看。 二进制日志记录了数据库的变化情况,可用作主库从库复制同步。

查看二进制日志是否开启:

1show variables like 'log_bin'\G
2*************************** 1. row ***************************
3Variable_name: log_bin
4        Value: OFF
51 row in set (0.01 sec)

log_bin默认是不开启的,并且是一个只读变量,只能在my.cnf中配置,重启MySQL实例生效。

1[mysqld]
2server_id = 1
3log-bin     = /var/lib/mysql/binlog
4log_bin_trust_function_creators=1
5binlog_format = MIXED
6expire_logs_days = 99

使用mysqlbinlog命令查看二进制日志:

1mysqlbinlog -d demodb  binlog.00003

mysqlbinlog命令常用选项:

 1-s					以精简的形式显示日志内容
 2-v					以详细的形式显示日志内容
 3-d      			只显示指定数据库的日志内容
 4-o					跳过指定行数的日志
 5-r      			日志内容写入指定文件
 6--start-datetime  
 7					显示指定时间范围内的日志内容(2004-12-25 11:25:56)
 8--stop-datetime         
 9--start-position        
10					显示指定位置间隔内的日志内容
11--stop-position     

获取当前正在使用的日志文件和位置信息

1mysql> show master status;
2+---------------+----------+--------------+------------------+-------------------+
3| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+---------------+----------+--------------+------------------+-------------------+
5| binlog.000003 |      319 |              |                  |                   |
6+---------------+----------+--------------+------------------+-------------------+
71 row in set (0.00 sec)

使用bin log恢复数据,结合--start-datetime, --stop-datetime可精确控制恢复范围。

1mysqlbinlog -s binlog.000003 | mysql -h 192.168.61.100 -u root -p

查看Bin log相关的参数:

 1mysql> show variables like '%binlog%';
 2+-----------------------------------------+----------------------+
 3| Variable_name                           | Value                |
 4+-----------------------------------------+----------------------+
 5| binlog_cache_size                       | 32768                |
 6| binlog_checksum                         | CRC32                |
 7| binlog_direct_non_transactional_updates | OFF                  |
 8| binlog_error_action                     | ABORT_SERVER         |
 9| binlog_format                           | MIXED                |
10| binlog_group_commit_sync_delay          | 0                    |
11| binlog_group_commit_sync_no_delay_count | 0                    |
12| binlog_gtid_simple_recovery             | ON                   |
13| binlog_max_flush_queue_time             | 0                    |
14| binlog_order_commits                    | ON                   |
15| binlog_row_image                        | FULL                 |
16| binlog_rows_query_log_events            | OFF                  |
17| binlog_stmt_cache_size                  | 32768                |
18| innodb_api_enable_binlog                | OFF                  |
19| innodb_locks_unsafe_for_binlog          | OFF                  |
20| log_statements_unsafe_for_binlog        | ON                   |
21| max_binlog_cache_size                   | 18446744073709547520 |
22| max_binlog_size                         | 1073741824           |
23| max_binlog_stmt_cache_size              | 18446744073709547520 |
24| sync_binlog                             | 1                    |
25+-----------------------------------------+----------------------+
2620 rows in set (0.00 sec)

慢日志

慢日志可以记录执行时间过长的SQL语句或者没有使用索引的查询语句。

查看是否开启了慢日志:

1mysql> show variables like 'slow_query_log';
2+----------------+-------+
3| Variable_name  | Value |
4+----------------+-------+
5| slow_query_log | OFF   |
6+----------------+-------+
71 row in set (0.00 sec)

my.cnf中配置开启慢日志:

1[mysqld]
2slow-query-log=1
3slow-query-log-file=/var/log/mysql/slow-queries.log
4long_query_time = 3
5log-queries-not-using-indexes

使用mysqldumpslow命令可以分析慢日志文件:

 1 mysqldumpslow  --help
 2  --verbose    verbose
 3  --debug      debug
 4  --help       write this text to standard output
 5
 6  -v           verbose
 7  -d           debug
 8  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
 9                al: average lock time
10                ar: average rows sent
11                at: average query time
12                 c: count
13                 l: lock time
14                 r: rows sent
15                 t: query time
16  -r           reverse the sort order (largest last instead of first)
17  -t NUM       just show the top n queries
18  -a           don't abstract all numbers to N and strings to 'S'
19  -n NUM       abstract numbers with at least n digits within names
20  -g PATTERN   grep: only consider stmts that include this string
21  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
22               default is '*', i.e. match all
23  -i NAME      name of server instance (if using mysql.server startup script)
24  -l           don't subtract lock time from total time

慢日志中返回数据行行数最多的top 10 SQL语句:

1mysqldumpslow -s r -t 10 /var/log/mysql/slow-queries.log

慢日志中执行次数最多的top 10语句:

1mysqldumpslow -s c -t 10 /var/log/mysql/slow-queries.log

慢日志中包含LIKE的SQL:

1mysqldumpslow -g 'like' 10 /var/log/mysql/slow-queries.log

DDL日志

参考