window下的配置:
MySQL中对于日志文件的环境比变量非常多,可以使用以下命令来查看:
mysql> show global variables like '%log%'; +-----------------------------------------+-----------------------------------------+ | Variable_name | Value | +-----------------------------------------+-----------------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /mydata/data1/localhost.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | /mydata/data1/localhost.localdomain.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 | /mydata/data1/localhost-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)
启动开关:general_log={ON|OFF}
日志文件变量:general_log_file [ =/PATH/TO/file]
全局日志开关:log={ON|OFF} 该开关打开后,所有日志都会被启用
记录类型:log_output={TABLE|FILE|NONE}
log_output定义了日志的输出格式,可以是表,文件,若设置为NONE,则不启用日志,因此,要启用通用查询日志,需要至少配置general_log=ON,log_output={TABLE|FILE}。而general_log_file如果没有指定,默认名是host_name.log。由于一般查询使用量比较大,启用写入日志文件,服务器的I/O操作较多,会大大降低服务器的性能,所以默认为关闭的。
mysql> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'general_log_file';
+------------------+-----------------------------+
| Variable_name | Value |
+------------------+-----------------------------+
| general_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\data |
+------------------+-----------------------------+
1 row in set (0.01 sec)
配置general_log=ON:
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec)
然后重启一下MySQL服务:
进入管理员的界面:
net stop mysql
net start mysql
通过查询语句获取文件路径,会默认生成一个以 主机名.log文件,用记事本就可以查看操作的查询日志了。
mysql> show global variables like 'general_log_file';
+------------------+-----------------------------+
| Variable_name | Value |
+------------------+-----------------------------+
| general_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\data |
+------------------+-----------------------------+
1 row in set (0.01 sec)
窍门:可以装一个Windows版的tail
在Windows上模仿Linux的行为,使用类似的命令解决,比如:
tail for Windows: http://www.trisunsoft.com/tail-for-windows.html
Tail for Win32: https://sourceforge.net/projects/tailforwin32/
将解压的tail.exe文件放到 C:\Windows\System32里就OK就可以使用类似Linux的tail命令了。
Linux下的配置 (–Ubuntu):
进入到 /etc/mysql/mysql.conf.d
打开mysqld.cnf(只读文件,更改需要管理员权限):
sudo vim mysqld.cnf#general_log_file = /var/log/mysql/mysql.log #general_log = 1
将注释的#去掉,重新启动一下MySQL服务:service mysql restart
打开文件: tail -f /var/log/mysql/mysql.log
完成之后就可以实时监控对数据库的查询操作了。