MySQL Server的日志
2016-12-11
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