原创

Mysql 日志启动查询详细解析

        日志是Mysql数据库的重要组成部分,日志文件中记录着mysql数据库运行期间发生的变化,当数据库意外出现异常问题时可以通过日志查看文件错误原因,并通过日志文件进行数据恢复。


Mysql包含以下几种日志:

1)错误日志    -log-err 记录启动、运行或停止时出现的问题。

2)查询日志    -log 记录建立的客户端连接和执行的语句。

3)慢查询日志  -log-slow-queries 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

4)更新日志    -log-update 记录InnoDB等支持事务的存储引擎执行事务时产生的日志。

5)二进制日志  -log-bin 记录所有更改数据的语句。主要用于复制和即时点恢复。


查看所有日志,执行SQL语句SHOW  GLOBAL VARIABLES LIKE '%log%';,以win7系统为例,具体如下:

mysql> SHOW  GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+---------------------------------------------------------------+
| Variable_name                           | Value                                                         |
+-----------------------------------------+---------------------------------------------------------------+
| back_log                                | 50                                                            |
| binlog_cache_size                       | 32768                                                         |
| binlog_direct_non_transactional_updates | OFF                                                           |
| binlog_format                           | STATEMENT                                                     |
| binlog_stmt_cache_size                  | 32768                                                         |
| expire_logs_days                        | 0                                                             |
| general_log                             | OFF                                                           |
| general_log_file                        | C:\ProgramData\MySQL\MySQL Server 5.5\Data\Mrxxx-PC.log      |
| innodb_flush_log_at_trx_commit          | 1                                                             |
| innodb_locks_unsafe_for_binlog          | OFF                                                           |
| innodb_log_buffer_size                  | 1048576                                                       |
| innodb_log_file_size                    | 14680064                                                      |
| innodb_log_files_in_group               | 2                                                             |
| innodb_log_group_home_dir               | .\                                                            |
| innodb_mirrored_log_groups              | 1                                                             |
| log                                     | OFF                                                           |
| log_bin                                 | OFF                                                           |
| log_bin_trust_function_creators         | OFF                                                           |
| log_error                               | C:\ProgramData\MySQL\MySQL Server 5.5\Data\Mrxxx-PC.err      |
| log_output                              | FILE                                                          |
| log_queries_not_using_indexes           | OFF                                                           |
| log_slave_updates                       | OFF                                                           |
| log_slow_queries                        | 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_index                         |                                                               |
| relay_log_info_file                     | relay-log.info                                                |
| relay_log_purge                         | ON                                                            |
| relay_log_recovery                      | OFF                                                           |
| relay_log_space_limit                   | 0                                                             |
| slow_query_log                          | OFF                                                           |
| slow_query_log_file                     | C:\ProgramData\MySQL\MySQL Server 5.5\Data\Mrxxx-PC-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)

注意:log_err可直接定义文件路径或ON|OFF;log_warings需使用1|0定义来启动。


查看是否启动日志,执行SQL语句show variables like 'log_%';,结果如下:

mysql> show variables like 'log_%';
+---------------------------------+----------------------------------------------------------+
| Variable_name                   | Value                                                    |
+---------------------------------+----------------------------------------------------------+
| log_bin                         | OFF                                                      |
| log_bin_trust_function_creators | OFF                                                      |
| log_error                       | C:\ProgramData\MySQL\MySQL Server 5.5\Data\Mrxxx-PC.err |
| log_output                      | FILE                                                     |
| log_queries_not_using_indexes   | OFF                                                      |
| log_slave_updates               | OFF                                                      |
| log_slow_queries                | OFF                                                      |
| log_warnings                    | 1                                                        |
+---------------------------------+----------------------------------------------------------+
8 rows in set (0.00 sec)

ON 启动,OFF 停止


开启慢查询,具体操作步骤如下:

long_query_time =2  指是指执行超过多久的sql会被log下来,这里是2秒

log-slow-queries= /usr/local/mysql/log/slowquery.log 将查询返回较慢的语句进行记录

log-queries-not-using-indexes = nouseindex.log 就是字面意思,log下来没有使用索引的query

log=mylog.log 对所有执行语句进行记录

关注下方微信公众号“Java精选”(w_z90110),回复关键字领取资料:如HadoopDubboCAS源码等等,免费领取资料视频和项目。 

涵盖:程序人生、搞笑视频、算法与数据结构、黑客技术与网络安全、前端开发、Java、Python、Redis缓存、Spring源码、各大主流框架、Web开发、大数据技术、Storm、Hadoop、MapReduce、Spark、elasticsearch、单点登录统一认证、分布式框架、集群、安卓开发、iOS开发、C/C++、.NET、Linux、Mysql、Oracle、NoSQL非关系型数据库、运维等。

相关推荐

评论

分享:

支付宝

微信