MySQL中查询日志与慢查询日志的基本学习教程
发布时间：2020-05-25 05:44:52 所属栏目：MsSql 来源：互联网
导读：一、查询日志查询日志记录MySQL中所有的query，通过"--log[=file_name]"来打开该功能。由于记录了所有的query，包括所有的select，体积比较大，开启后对性能也有比较大的影响，所以请大家慎用该功能。一般只
|
一、查询日志 查询日志记录MySQL中所有的query，通过"--log[=file_name]"来打开该功能。由于记录了所有的query，包括所有的select，体积比较大，开启后对性能也有比较大的影响，所以请大家慎用该功能。一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能。默认的查询日志文件名为：hostname.log. [root@node4 mysql5.5]# service mysql start Starting MySQL.... [ OK ] [root@node4 mysql5.5]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000,2011,Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like '%log'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log | OFF | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.00 sec) ----备注：log和general_log这两个参数是兼容的。而默认的情况下查询日志是不开启的 Query OK,0 rows affected,1 warning (0.03 sec) mysql> show variables like '%log'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | ON | | innodb_locks_unsafe_for_binlog | OFF | | log | ON | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.00 sec) ----其中log参数是过时的，在启动选项中使用log参数的话，会在err日志中显示出来。 ----修改my.cnf文件，添加log的参数设置 [root@node4 mysql5.5]# vi my.cnf [root@node4 mysql5.5]# cat ./my.cnf |grep '^log=' log=/tmp/mysqlgen.log ----清空err日志 [root@node4 mysql5.5]# cat /dev/null > /tmp/mysql3306.err [root@node4 mysql5.5]# ll /tmp/mysql3306.err -rw-rw---- 1 mysql root 0 Jul 31 07:50 /tmp/mysql3306.err [root@node4 mysql5.5]# service mysql start Starting MySQL... [ OK ] ----启动数据库后查看err日志的内容 [root@node4 mysql5.5]# cat /tmp/mysql3306.err 130731 07:51:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data 130731 7:51:32 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead. 130731 7:51:33 InnoDB: The InnoDB memory heap is disabled 130731 7:51:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130731 7:51:33 InnoDB: Compressed tables use zlib 1.2.3 130731 7:51:33 InnoDB: Initializing buffer pool,size = 128.0M 130731 7:51:33 InnoDB: Completed initialization of buffer pool 130731 7:51:33 InnoDB: highest supported file format is Barracuda. 130731 7:51:33 InnoDB: Waiting for the background threads to start 130731 7:51:34 InnoDB: 1.1.8 started; log sequence number 1625855 130731 7:51:34 [Note] Event Scheduler: Loaded 0 events 130731 7:51:34 [Note] /opt/mysql5.5/bin/mysqld: ready for connections. Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution ----使用最新的参数 ----general_log和general_log_file。 [root@node4 mysql5.5]# service mysql stop Shutting down MySQL. [ OK ] [root@node4 mysql5.5]# vi my.cnf [root@node4 mysql5.5]# cat ./my.cnf |grep '^general' general_log = 1 general_log_file = /tmp/mysqlgen.log [root@node4 mysql5.5]# service mysql start Starting MySQL... [ OK ] [root@node4 mysql5.5]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000,Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like '%log'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | ON | | innodb_locks_unsafe_for_binlog | OFF | | log | ON | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.04 sec) mysql> show variables like '%file'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | ft_stopword_file | (built-in) | | general_log_file | /tmp/mysqlgen.log | | init_file | | | local_infile | ON | | pid_file | /tmp/mysql3306.pid | | relay_log_info_file | relay-log.info | | slow_query_log_file | /opt/mysql5.5/data/node4-slow.log | +---------------------+-----------------------------------+ 7 rows in set (0.00 sec) ----在上面的操作中可以看到已经启用查询日志，并且文件目录是/tmp/mysqlgen.log。 ----查询日志记录了哪些东西？ 进行下面的查询 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test2 | +--------------------+ 5 rows in set (0.08 sec) mysql> use test; Database changed mysql> show tables; Empty set (0.00 sec) mysql> use test2; Database changed mysql> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | course | | jack | | sc | | student | | t | | teacher | +-----------------+ 6 rows in set (0.07 sec) mysql> drop table t; Query OK,0 rows affected (0.13 sec) mysql> select * from sc; Empty set (0.04 sec) ----可以看到上面的操作都记录在了mysqlgen.log里面。 [root@node4 ~]# tail -f /tmp/mysqlgen.log
/opt/mysql5.5/bin/mysqld,Version: 5.5.22-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
130731 7:55:41 1 Query show databases
130731 7:55:56 1 Query SELECT DATABASE()
1 Init DB test
130731 7:55:59 1 Query show tables
130731 7:56:19 1 Query SELECT DATABASE()
1 Init DB test2
130731 7:56:23 1 Query show tables
130731 7:56:27 1 Query drop table t
130731 7:56:39 1 Query select * from sc
（编辑：安卓应用网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |