MySQL中开启和使用通用查询日志的实例教程
开启通用查询日志
因为为了性能考虑,一般通用查询日志generallog不会开启。slowlog可以定位一些有性能问题的sql,而generallog会记录所有的SQL。
mysql5.0版本,如果要开启slowlog、generallog,需要重启,从MySQL5.1.6版开始,generalquerylog和slowquerylog开始支持写到文件或者数据库表两种方式,并且日志的开启,输出方式的修改,都可以在Global级别动态修改。
mysql>selectversion();
+————+ |version()| +————+ |5.1.37-log| +————+ 1rowinset(0.02sec)
设置日志输出方式为文件(如果设置log_output=table的话,则日志结果会记录到名为gengera_log的表中,这表的默认引擎都是CSV):
mysql>setgloballog_output=file;
QueryOK,0rowsaffected(0.00sec)
设置generallog的日志文件路径:
mysql>setglobalgeneral_log_file='/tmp/general.log';
QueryOK,0rowsaffected(0.00sec)
开启generallog:
mysql>setglobalgeneral_log=on;
QueryOK,0rowsaffected(0.02sec)
过一段时间后,关闭generallog:
mysql>setglobalgeneral_log=off;
QueryOK,0rowsaffected(0.02sec)
通用查询日志的系统变量
log_output=[none|file|table|file,table]#通用查询日志输出格式
general_log=[on|off]#是否启用通用查询日志 general_log_file[=filename]#通用查询日志位置及名字
通用查询日志的备份
在Linux或Unix中,你可以通过下面的命令重新命名文件
并创建一个新文件:
shell>mvhostname.loghostname-old.log shell>mysqladminflush-logs shell>cphostname-old.logto-backup-directory shell>rmhostname-old.log
在Windows中,服务器打开日志文件期间不能重新命名日志文件。必须先停止服务器然后重新命名日志文件。然后重启服务器来创建新日志文件。
演示通用查询日志的使用
a、启用通用查询日志
--演示环境 root@localhost[(none)]>showvariableslike'%version%';
+-------------------------+------------------------------+ |Variable_name|Value| +-------------------------+------------------------------+ |innodb_version|5.5.39| |protocol_version|10| |slave_type_conversions|| |version|5.5.39-log| |version_comment|MySQLCommunityServer(GPL)| |version_compile_machine|x86_64| |version_compile_os|Linux| +-------------------------+------------------------------+
--查看系统变量 root@localhost[(none)]>showvariableslike'%general%';
+------------------+----------------------------+ |Variable_name|Value| +------------------+----------------------------+ |general_log|OFF| |general_log_file|/var/lib/mysql/suse11b.log| +------------------+----------------------------+
--查看当前的通用日志,显示无日志文件 root@localhost[(none)]>systemls/var/lib/mysql/suse11b.log
ls:cannotaccess/var/lib/mysql/suse11b.log:Nosuchfileordirectory
--设置变量general_log以开启通用查询日志 root@localhost[(none)]>set@@global.general_log=1;
QueryOK,0rowsaffected(0.00sec)
--再次查看通用日志文件已存在 root@localhost[(none)]>systemls/var/lib/mysql/suse11b.log /var/lib/mysql/suse11b.log root@localhost[(none)]>select*fromtempdb.tb1;--执行查询
+------+------+ |id|val| +------+------+ |1|jack| +------+------+
--查看通用日志文件内容 root@localhost[(none)]>systemmore/var/lib/mysql/suse11b.log /usr/sbin/mysqld,Version:5.5.39-log(MySQLCommunityServer(GPL)).startedwith:
Tcpport:3306Unixsocket:/var/lib/mysql/mysql.sock TimeIdCommandArgument 14100316:18:124Queryshowvariableslike'%general%' 14100316:18:554Queryselect*fromtempdb.tb1
b、更改通用查询日志位置
root@localhost[(none)]>exit
Bye suse11b:~#servicemysqlstop ShuttingdownMySQL...done suse11b:~#mysqld--general_log_file=/tmp/suse11b.log--user=mysql& [1]47009 suse11b:~#ps-ef|grepmysql|grep-vgrep mysql4700944514116:22pts/000:00:00mysqld--general_log_file=/tmp/suse11b.log--user=mysql root4705344514016:22pts/000:00:00grepmysql suse11b:~#mysql
root@localhost[(none)]>systemls/tmp/suse11b.log
ls:cannotaccess/tmp/suse11b.log:Nosuchfileordirectory
root@localhost[(none)]>showvariableslike'%gener%';
+------------------+------------------+ |Variable_name|Value| +------------------+------------------+ |general_log|OFF| |general_log_file|/tmp/suse11b.log| +------------------+------------------+
root@localhost[(none)]>setglobalgeneral_log=on;
QueryOK,0rowsaffected(0.01sec)
--此时从系统变量看出,通用日志已经到/tmp目录下 root@localhost[(none)]>showvariableslike'%gener%';
+------------------+------------------+ |Variable_name|Value| +------------------+------------------+ |general_log|ON| |general_log_file|/tmp/suse11b.log| +------------------+------------------+
--发布查询 root@localhost[(none)]>selectcount(*)fromtempdb.tb1;
+----------+ |count(*)| +----------+ |1| +----------+
--查看通用日志文件内容 root@localhost[(none)]>systemmore/tmp/suse11b.log
mysqld,Version:5.5.39-log(MySQLCommunityServer(GPL)).startedwith: Tcpport:3306Unixsocket:/var/lib/mysql/mysql.sock TimeIdCommandArgument 14100316:30:031Queryshowvariableslike'%gener%' 14100316:30:091Queryselectcount(*)fromtempdb.tb1
c、通用查询日志输出方式
--可以输出为文件,表以及不输出,即TABLE,FILE,NONE --系统变量log_output root@localhost[(none)]>showvariableslike'log_output';
+---------------+-------+ |Variable_name|Value| +---------------+-------+ |log_output|FILE| +---------------+-------+
--下面修改为输出为表方式 root@localhost[(none)]>setgloballog_output='TABLE';
QueryOK,0rowsaffected(0.00sec)
root@localhost[(none)]>showvariableslike'log_output';
+---------------+-------+ |Variable_name|Value| +---------------+-------+ |log_output|TABLE| +---------------+-------+
--发布查询 root@localhost[(none)]>select*fromtempdb.tb1;
+------+------+ |id|val| +------+------+ |1|jack| +------+------+
root@localhost[(none)]>systemmore/tmp/suse11b.log
mysqld,Version:5.5.39-log(MySQLCommunityServer(GPL)).startedwith: Tcpport:3306Unixsocket:/var/lib/mysql/mysql.sock TimeIdCommandArgument 14100316:30:031Queryshowvariableslike'%gener%' 14100316:30:091Queryselectcount(*)fromtempdb.tb1 14100316:31:001Queryshowvariableslike'log_output' 14100317:00:481Querysetgloballog_output='TABLE'#通用查询日志输出到文件仅仅记录到全局变量的修改
--mysql.general_log记录了通用查询日志的信息 root@localhost[(none)]>descmysql.general_log;
+--------------+------------------+------+-----+-------------------+-----------------------------+ |Field|Type|Null|Key|Default|Extra| +--------------+------------------+------+-----+-------------------+-----------------------------+ |event_time|timestamp|NO||CURRENT_TIMESTAMP|onupdateCURRENT_TIMESTAMP| |user_host|mediumtext|NO||NULL|| |thread_id|int(11)|NO||NULL|| |server_id|int(10)unsigned|NO||NULL|| |command_type|varchar(64)|NO||NULL|| |argument|mediumtext|NO||NULL|| +--------------+------------------+------+-----+-------------------+-----------------------------+
--从通用查询日志表里查看通用查询日志的内容 root@localhost[(none)]>selectthread_id,command_type,argumentfrommysql.general_log;
+-----------+--------------+---------------------------------------------------------------+ |thread_id|command_type|argument| +-----------+--------------+---------------------------------------------------------------+ |1|Query|showvariableslike'log_output'| |1|Query|select*fromtempdb.tb1| |1|Query|descmysql.general_log| |1|Query|selectthread_id,command_type,argumentfrommysql.general_log| +-----------+--------------+---------------------------------------------------------------+
root@localhost[(none)]>showvariableslike'log_output';
+---------------+-------+ |Variable_name|Value| +---------------+-------+ |log_output|TABLE| +---------------+-------+
--使用FILE,TABLE2者混合输出通用日志 root@localhost[(none)]>setgloballog_output='file,table';
QueryOK,0rowsaffected(0.00sec)
root@localhost[(none)]>select@@global.log_output;
+---------------------+ |@@global.log_output| +---------------------+ |FILE,TABLE| +---------------------+
root@localhost[(none)]>insertintotempdb.tb1values(2,'robinson');
QueryOK,1rowaffected(0.06sec)
root@localhost[(none)]>commit;
QueryOK,0rowsaffected(0.01sec)
--验证结果,表和文件里边存在通用的日志记录 root@localhost[(none)]>systemtail/tmp/suse11b.log|greprobinson
14100317:41:542Queryinsertintotempdb.tb1values(2,'robinson')
root@localhost[(none)]>selectthread_id,command_type,argumentfrommysql.general_log ->whereargumentlike'%robinson%';
+-----------+--------------+------------------------------------------------------------------------+ |thread_id|command_type|argument| +-----------+--------------+------------------------------------------------------------------------+ |2|Query|insertintotempdb.tb1values(2,'robinson')| |2|Query|selectthread_id,command_type,argumentfrommysql.general_log| |||whereargumentlike''robinson''| +-----------+--------------+------------------------------------------------------------------------+
d、关闭通用查询日志
--可以通过设置系统变量general_log来关闭通用查询日志,此时日志输出设置为FILE,TABLE
root@localhost[(none)]>showvariableslike'log_output';
+---------------+------------+ |Variable_name|Value| +---------------+------------+ |log_output|FILE,TABLE| +---------------+------------+
root@localhost[(none)]>setglobalgeneral_log=off;
QueryOK,0rowsaffected(0.01sec)
root@localhost[(none)]>showvariableslike'%gener%';
+------------------+------------------+ |Variable_name|Value| +------------------+------------------+ |general_log|OFF| |general_log_file|/tmp/suse11b.log| +------------------+------------------+
root@localhost[(none)]>deletefromtempdb.tb1whereid=2;
QueryOK,1rowaffected(0.12sec)
root@localhost[(none)]>commit;
QueryOK,0rowsaffected(0.00sec)
root@localhost[(none)]>systemtail-n1/tmp/suse11b.log
14100317:45:132Querysetglobalgeneral_log=off
root@localhost[(none)]>selectthread_id,command_type,argumentfrommysql.general_log ->whereargumentlike'%delete%';
Emptyset(0.00sec)--从上面的演示可知,尽管我们设置了log_output为FILE,TABLE,但general_log为OFF,通用日志无任何记录产生
root@localhost[(none)]>setgloballog_output=none;
QueryOK,0rowsaffected(0.00sec)
root@localhost[(none)]>setglobalgeneral_log=1;
QueryOK,0rowsaffected(0.00sec)
root@localhost[(none)]>truncatetabletempdb.tb1;
QueryOK,0rowsaffected(0.01sec)
root@localhost[(none)]>systemtail-n1/tmp/suse11b.log
TimeIdCommandArgument --通过上面的演示,在log_output=none,general_log=on的清下下无任何通用日志输出。