MySQL 5.7.x 日志查看

通用查询日志

• 查看通用日志信息

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.25                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.25-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

• 查看当前的通用日志是否开启

mysql> show variables like '%general%';
+------------------+--------------------------------------------------+
| Variable_name    | Value                                            |
+------------------+--------------------------------------------------+
| general_log      | OFF                                              |
| general_log_file | /home/autoInstall_1.2.1/mysql/data/hessian03.log |
+------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

• 通用日志开启 && 关闭

开启通用日志查询：
	mysql> set global general_log = on;
	Query OK, 0 rows affected (0.00 sec)
关闭通用日志查询：
	set global general_log = off;

• 查看当前日志文件的格式

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

当前慢文件格式为file ，存储在数据库的数据文件中的hostname.log
可以是table格式，存储在数据库的数据文件中的mysql.general_log
设置通用日志输出为表方式：
set global log_output = 'file';
设置通用日志输出为表和文件方式：
set global log_output = 'file,table';

慢查询日志

mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------------------------------+
| Variable_name                          | Value                                                 |
+----------------------------------------+-------------------------------------------------------+
| binlog_rows_query_log_events           | OFF                                                   |
| ft_query_expansion_limit               | 20                                                    |
| have_query_cache                       | YES                                                   |
| log_queries_not_using_indexes          | OFF                                                   |
| log_throttle_queries_not_using_indexes | 0                                                     |
| long_query_time                        | 10.000000                                             |
| query_alloc_block_size                 | 8192                                                  |
| query_cache_limit                      | 1048576                                               |
| query_cache_min_res_unit               | 4096                                                  |
| query_cache_size                       | 1048576                                               |
| query_cache_type                       | OFF                                                   |
| query_cache_wlock_invalidate           | OFF                                                   |
| query_prealloc_size                    | 8192                                                  |
| slow_query_log                         | OFF                                                   |
| slow_query_log_file                    | /home/autoInstall_1.2.1/mysql/data/hessian03-slow.log |
+----------------------------------------+-------------------------------------------------------+
15 rows in set (0.00 sec)

1）slow_query_log 的值为ON 为开启慢查询日志，off表示关闭慢查询日志
2） slow_query_log_file 的值是记录的慢查询日志到文件中（默认为主机 名.log）
3）long_query_time 指定了慢查询的阈值，即执行语句的时间若超过这个值则为慢查询语句
4）log_queries_not_using_indexes 如果该值是ON，则会记录所有没有利用索引来进行查询的语句，前提是slow_query_log 的值也是ON,否则，不会奏效
查询当前慢查询的语句个数：
show global status like '%slow%';
可以通过查询语句查看慢查询的语句：
select * from mysql.slow_log;

错误日志

mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.00 sec)

错误日志归档，备份错误日志
shell>mv host_name.err host_name.err-old
shell> mysqladmin -u root -p flush-logs
ahell>mv host_name.err-old back-directory

二进制日志

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

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------------------+
| Variable_name                   | Value                                               |
+---------------------------------+-----------------------------------------------------+
| log_bin                         | ON                                                  |
| log_bin_basename                | /home/autoInstall_1.2.1/mysql/data/master-bin       |
| log_bin_index                   | /home/autoInstall_1.2.1/mysql/data/master-bin.index |
| log_bin_trust_function_creators | OFF                                                 |
| log_bin_use_v1_row_events       | OFF                                                 |
| sql_log_bin                     | ON                                                  |
+---------------------------------+-----------------------------------------------------+
6 rows in set (0.00 sec)

• 查看所有的二进制参数

mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)

• 查看文件的位置

mysql> show variables like '%datadir%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| datadir       | /home/autoInstall_1.2.1/mysql/data/ |
+---------------+-------------------------------------+
1 row in set (0.01 sec)

参考

• MySQL5.7 四种日志文件