MySQL Server has several logs that can help you find out what activity is taking place.
| Log Type | Information Written to Log |
|---|---|
| Error log | Problems encountered starting, running, or stopping |
| General query log | Established client connections and statements received from clients |
| Binary log | Statements that change data (also used for replication) |
| Relay log | Data changes received from a replication master server |
| Slow query log | Queries that took more than long_query_time |
查看General query log是否开启了：
mysql> show variables like '%general_log%';
+------------------+------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------+
| general_log | OFF |
| general_log_file | C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\PC-201202111331.log |
+------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
开启General query log日志：
mysql> set global event_scheduler=on;
查看日志：
Time Id Command Argument
131101 14:36:20 2 Connect root@localhost on
131101 14:36:57 1 Query show databases
131101 14:37:01 1 Query SELECT DATABASE()
131101 14:37:09 1 Query show tables
131101 14:37:17 1 Query select * from a
131101 14:37:47 1 Query show varibles like '%event%'
131101 14:37:52 1 Query show variables like '%event%'
131101 14:38:16 1 Query set event_scheduler=on
131101 14:38:23 1 Query set global event_scheduler=on
131101 14:39:32 1 Query show create event e_test_insert
131101 14:40:01 1 Query alter event e_test_insert disable
131101 14:41:31 2 Query SHOW EVENTS
131101 14:41:40 1 Query show events
131101 14:44:18 1 Query create event e_test_insert
on schedule every 1 second
do insert into test.a values(current_timestamp)
131101 14:44:19 5 Query insert into test.a values(current_timestamp)
131101 14:44:20 6 Query insert into test.a values(current_timestamp)
131101 14:44:21 7 Query insert into test.a values(current_timestamp)
131101 14:44:22 8 Query insert into test.a values(current_timestamp)
131101 14:44:23 9 Query insert into test.a values(current_timestamp)
131101 14:44:24 10 Query insert into test.a values(current_timestamp)
131101 14:44:25 11 Query insert into test.a values(current_timestamp)
131101 14:44:26 12 Query insert into test.a values(current_timestamp)
131101 14:44:27 13 Query insert into test.a values(current_timestamp)
131101 14:44:28 14 Query insert into test.a values(current_timestamp)
131101 14:44:29 15 Query insert into test.a values(current_timestamp)
131101 14:44:30 16 Query insert into test.a values(current_timestamp)
131101 14:44:31 17 Query insert into test.a values(current_timestamp)
131101 14:44:32 18 Query insert into test.a values(current_timestamp)
131101 14:44:33 19 Query insert into test.a values(current_timestamp)
131101 14:44:34 20 Query insert into test.a values(current_timestamp)
131101 14:44:35 21 Query insert into test.a values(current_timestamp)
131101 14:44:36 22 Query insert into test.a values(current_timestamp)
131101 14:44:37 23 Query insert into test.a values(current_timestamp)
131101 14:44:38 24 Query insert into test.a values(current_timestamp)
131101 14:44:39 25 Query insert into test.a values(current_timestamp)
131101 14:44:40 26 Query insert into test.a values(current_timestamp)
131101 14:44:41 27 Query insert into test.a values(current_timestamp)
131101 14:44:42 28 Query insert into test.a values(current_timestamp)
131101 14:44:43 29 Query insert into test.a values(current_timestamp)
131101 14:44:44 30 Query insert into test.a values(current_timestamp)
131101 14:44:45 1 Query show events status
131101 14:44:46 32 Query insert into test.a values(current_timestamp)
131101 14:44:47 33 Query insert into test.a values(current_timestamp)
131101 14:44:48 34 Query insert into test.a values(current_timestamp)
131101 14:44:49 35 Query insert into test.a values(current_timestamp)
131101 14:44:50 36 Query insert into test.a values(current_timestamp)
131101 14:44:51 37 Query insert into test.a values(current_timestamp)
131101 14:44:52 38 Query insert into test.a values(current_timestamp)
131101 14:44:53 39 Query insert into test.a values(current_timestamp)
131101 14:44:54 40 Query insert into test.a values(current_timestamp)
131101 14:44:55 41 Query insert into test.a values(current_timestamp)
131101 14:44:56 42 Query insert into test.a values(current_timestamp)
131101 14:44:57 43 Query insert into test.a values(current_timestamp)
131101 14:44:58 44 Query insert into test.a values(current_timestamp)
131101 14:44:59 45 Query insert into test.a values(current_timestamp)
131101 14:45:00 1 Query show events e_test_insert status
131101 14:46:17 1 Query alter event e_test_insert disable
看到这个日志中记录的值了么？
用户连接、查询插入、参数更改、显示参数值都被记录在这个日志中。
mysql> show variables like '%general_log%';
+------------------+------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------+
| general_log | ON |
| general_log_file | C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\PC-201202111331.log |
+------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
日志切换：
注意：win下不用停止日志开关！！
在mysql表general_log存放日志：
mysql> show tables like '%log%';
+-------------------------+
| Tables_in_mysql (%log%) |
+-------------------------+
| general_log |
| ndb_binlog_index |
| slow_log |
+-------------------------+
3 rows in set (0.00 sec)
mysql> select * from general_log;
Empty set (0.03 sec)
mysql> show create table general_log\G;
*************************** 1. row ***************************
Create Table: CREATE TABLE `general_log` (
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)
ERROR:
No query specified
my.ini配置（注意重启mysql）：
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
log_output=TABLE
查看配置：
mysql> show variables like '%output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%general_log%';
+------------------+------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------+
| general_log | OFF |
| general_log_file | C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\PC-201202111331.log |
+------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log=on
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%general_log%';
+------------------+------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------+
| general_log | ON |
| general_log_file | C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\PC-201202111331.log |
+------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.03 sec)
mysql> select * from general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| 2013-11-01 17:18:00 | root[root] @ localhost [127.0.0.1] | 1 | 1 | Query | show variables like '%general_log%' |
| 2013-11-01 17:18:24 | root[root] @ localhost [127.0.0.1] | 1 | 1 | Query | select * from mysql.generalog |
| 2013-11-01 17:18:56 | [root] @ localhost [127.0.0.1] | 2 | 1 | Connect | root@localhost on mysql |
| 2013-11-01 17:18:56 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | show variables like '%general_log%' |
| 2013-11-01 17:19:00 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | select * from general_log |
| 2013-11-01 17:20:00 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | select * from general_log |
| 2013-11-01 17:20:02 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | select * from general_log |
| 2013-11-01 17:20:32 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | show create table general_log |
| 2013-11-01 17:20:39 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | show create table general_log |
| 2013-11-01 17:22:12 | root[root] @ localhost [127.0.0.1] | 2 | 1 | Query | select * from general_log |
+---------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
10 rows in set (0.02 sec)
The Binary Log
The binary log has two important purposes:
1、For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers.
2、Certain data recovery operations require use of the binary log.
