如何在MySql中记录SQL日志记录
\u5982\u4f55\u67e5\u770bmysql\u6570\u636e\u5e93\u64cd\u4f5c\u8bb0\u5f55\u65e5\u5fd71\u3001\u9996\u5148\u786e\u8ba4\u4f60\u65e5\u5fd7\u662f\u5426\u542f\u7528\u4e86mysql>show variables like 'log_bin'\u3002
2\u3001\u5982\u679c\u542f\u7528\u4e86\uff0c\u5373ON\uff0c\u90a3\u65e5\u5fd7\u6587\u4ef6\u5c31\u5728mysql\u7684\u5b89\u88c5\u76ee\u5f55\u7684data\u76ee\u5f55\u4e0b\u3002
3\u3001\u600e\u6837\u77e5\u9053\u5f53\u524d\u7684\u65e5\u5fd7mysql> show master status\u3002
4\u3001\u770b\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u7528mysqlbinlog\uff0cshell>mysqlbinlog mail-bin.000001\u6216\u8005shell>mysqlbinlog mail-bin.000001 | tail\uff0cWindows \u4e0b\u7528\u7c7b\u4f3c\u7684\u3002
MySQL\u7684\u65e5\u5fd7\u64cd\u4f5c\uff1a
1\u3001\u9996\u5148\uff0c\u767b\u9646mysql\u540e\uff0c\u6267\u884csql\u8bed\u53e5\uff1ashow variables like 'log_bin'\u3002
2\u3001#\u9519\u8bef\u65e5\u5fd7log-errol\u5f00\u542f\u65b9\u5f0f\uff1a\u5728my.ini\u7684[mysqld]\u9009\u9879\u4e0b\uff1a\u6dfb\u52a0\u4ee3\u7801\uff1alog-error=E:\log-error.txt\u3002
\u8bb0\u5f55\u5185\u5bb9\uff1a\u4e3b\u8981\u662f\u8bb0\u5f55\u542f\u52a8\u3001\u8fd0\u884c\u6216\u505c\u6b62mysqld\u65f6\u51fa\u73b0\u7684\u81f4\u547d\u6027\u95ee\u9898\uff0c\u90fd\u662f\u7cfb\u7edf\u7ea7\u7684\u9519\u8bef\u8bb0\u5f55\u3002
3\u3001#\u67e5\u8be2\u65e5\u5fd7\uff1alog\uff0c\u5f00\u542f\u65b9\u5f0f\uff1a\u5728my.ini\u7684[mysqld]\u9009\u9879\u4e0b\uff1a\u6dfb\u52a0\u4ee3\u7801\uff1alog=E:/mysql_log.txt\u3002
4\u3001#\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff1alog-bin\uff0c\u5f00\u542f\u65b9\u5f0f\uff1a\u5728my.ini\u7684[mysqld]\u9009\u9879\u4e0b\uff1a\u6dfb\u52a0\u4ee3\u7801\uff1alog-bin=E:/mysql_log_bin\uff0c\u8bb0\u5f55\u5185\u5bb9\uff1a\u4e3b\u8981\u662f\u8bb0\u5f55\u6240\u6709\u7684\u66f4\u6539\u6570\u636e\u7684\u8bed\u53e5\uff0c\u53ef\u4f7f\u7528mysqlbinlog\u547d\u4ee4\u6062\u590d\u6570\u636e\u3002
1\u3001WIN\u7684\u8bdd\uff0c\u5728\u5b89\u88c5\u76ee\u5f55\u4e0b\uff0c\u627e\u5230my.ini\u6587\u4ef6
\u67e5[mysqld]\u533a\u6bb5\uff0c\u6dfb\u52a0\u65e5\u5fd7\u7684\u914d\u7f6e
\u6bd4\u5982\u8bf4\uff1a[mysqld]log=C:/temp/mysql.log
log_slow_queries=C:/temp/mysql_slow.log
long_query_time=1
log\u6307\u793a\u65e5\u5fd7\u6587\u4ef6\u5b58\u653e\u76ee\u5f55
log_slow_queries\u6307\u793a\u8bb0\u5f55\u6267\u884c\u65f6\u95f4\u957f\u7684sql\u65e5\u5fd7\u76ee\u5f55\uff1b
long_query_time\u6307\u793a\u591a\u957f\u65f6\u95f4\u7b97\u662f\u6267\u884c\u65f6\u95f4\u3002
2\u3001LINUX\u4e0b\u7684\u8bdd\uff0c\u6587\u4ef6\u540d\u4e3amy.cnf
Linux\u4e0b\u8fd9\u4e9b\u914d\u7f6e\u9879\u5176\u5b9e\u90fd\u6709\u7684\uff0c\u53ea\u662f\u88ab\u6ce8\u91ca\u6389\u4e86\uff0c\u5220\u53bb\u6ce8\u91ca\u7b26#\u4e4b\u7c7b\u7684\u5c31\u53ef\u4ee5\u4e86\u3002
\u4f60\u4e0d\u613f\u5220\u6ce8\u91ca\u7b26\uff0c\u613f\u610f\u7684\u8bdd\u624b\u5de5\u518d\u6dfb\u52a0\u4e5f\u53ef\u4ee5\u2026\u2026
\u7136\u540e\u91cd\u542fmysql\u670d\u52a1\u5c31OK\u4e86\u3002
错误日志在Mysql数据库中很重要，它记录着mysqld启动和停止，以及服务器在运行过程中发生的任何错误的相关信息。
1.配置信息
--log-error=[file-name]用来指定错误日志存放的位置。
如果没有指定[file-name]，默认hostname.err做为文件名，默认存放在DATADIR目录中。
也可以将log-error配置到my.cnf文件中，这样就省去了每次在启动mysqld时都手工指定--log-error.例如:
[mysql@test2]$ vi /etc/my.cnf
# The MySQL server
[mysqld]
....
log-error = /var/lib/mysql/test2_mysqld.err
.....
2.错误信息样板
080313 05:21:55 mysqld started
080313 5:21:55 InnoDB: Started; log sequence number 0 43655
080313 5:21:55 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
080313 5:24:13 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
080313 5:24:13 InnoDB: Starting shutdown...
080313 5:24:16 InnoDB: Shutdown completed; log sequence number 0 43655
080313 5:24:16 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
080313 05:24:16 mysqld ended
080313 05:24:47 mysqld started
080313 5:24:47 InnoDB: Started; log sequence number 0 43655
080313 5:24:47 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
080313 5:33:49 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
三.查询日志
查询日志记录了clinet的所有的语句。
Note:由于log日志记录了数据库所有操作，对于访问频繁的系统，此种日志会造成性能影响，建议关闭。
1.配置信息
--log=[file-name]用来指定错误日志存放的位置。
如果没有指定[file-name]，默认为主机名(hostname)做为文件名，默认存放在DATADIR目录中。
也可以将log配置到my.cnf文件中，这样就省去了每次在启动mysqld时都手工指定--log.例如:
# The MySQL server
[mysqld]
......
#query-log
log = /var/lib/mysql/query_log.log
......
2.读取查询日志
查询日志是纯文本格可，可以使用OS文本读取工具直接打开查看。例如：
[mysql@test2]$ tail -n 15 query_log.log
080313 7:58:28 17 Query show tables
080313 8:07:45 17 Quit
080313 10:01:48 18 Connect root@localhost on
080313 10:02:38 18 Query SELECT DATABASE()
18 Init DB test
080313 10:02:42 18 Query show tables
080313 10:03:07 18 Query select * from pet
080313 10:06:26 18 Query insert into pet values('hunter','yxyup','cat','f','1996-04-29',null)
080313 10:06:39 18 Query select * from pet
080313 10:07:13 18 Query update pet set sex='m' where name='hunter'
080313 10:07:38 18 Query delete from pet where name='hunter'
080313 10:13:48 18 Query desc test8
080313 10:14:13 18 Query create table t1(id int,name char(10))
080313 10:14:41 18 Query alter table t1 add sex char(2)
[mysql@test2]$
四.慢查询日志
慢查询日志是记录了执行时间超过参数long_query_time(单位是秒)所设定值的SQL语句日志。
Note:慢查询日志对于我们发现性能有问题的SQL有很帮助，建议使用并经常分析
1.配置信息
--log-slow-queries=[file-name]用来指定错误日志存放的位置。
如果没有指定[file-name]，默认为hostname-slow.log做为文件名，默认存放在DATADIR目录中。
也可以将log-slow-queries配置到my.cnf文件中，这样就省去了每次在启动mysqld时都手工指定--log-slow-queries.例如:
# The MySQL server
[mysqld]
......
#slow-query-log
log-slow-queries = /var/lib/mysql/slow_query_log.log
......
2.读取慢查询日志
[mysql@test2]$ cat slow_query_log.log
/usr/local/mysql/bin/mysqld, Version: 5.0.26-standard-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 080313 5:41:46
# User@Host: root[root] @ localhost []
# Query_time: 108 Lock_time: 0 Rows_sent: 0 Rows_examined: 8738
use test;
select count(1) from t1 a, t1 b,t1 c where a.id=b.id and b.name=c.name;
# Time: 080313 5:52:04
# User@Host: root[root] @ localhost []
# Query_time: 583 Lock_time: 0 Rows_sent: 0 Rows_examined: 508521177
select count(1) from t1 a, t1 b where a.id=b.id;
/usr/local/mysql/bin/mysqld, Version: 5.0.26-standard-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 080313 10:39:59
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 4537467 Rows_examined: 4537467
use test;
select id from tail;
如果慢查询日志记录很多可以使用mysqldumpslow进行分类汇总
[mysql@test2]$ mysqldumpslow slow_query_log.log
Reading mysql slow query log from slow_query_log.log
Count: 1 Time=583.00s (583s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from t1 a, t1 b where a.id=b.id
Count: 1 Time=108.00s (108s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from t1 a, t1 b,t1 c where a.id=b.id and b.name=c.name
Count: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=4537467.0 (4537467), root[root]@localhost
select id from tail;
mysql有以下几种日志：
错误日志： -log-err
查询日志： -log
慢查询日志: -log-slow-queries
更新日志: -log-update
二进制日志： -log-bin
在mysql的安装目录下，打开my.ini,在后面加上上面的参数，保存后重启mysql服务就行了。
例如：
#Enter a name for the binary log. Otherwise a default name will be used.
#log-bin=
#Enter a name for the query log file. Otherwise a default name will be used.
#log=
#Enter a name for the error log file. Otherwise a default name will be used.
log-error=
#Enter a name for the update log file. Otherwise a default name will be used.
#log-update=
查看日至：
1. 首先确认你日志是否启用了
mysql>show variables like 'log_bin';
如果启用了，即ON
那日志文件就在mysql的安装目录的data目录下
cat/tail 日志文件名
2. 怎样知道当前的日志
mysql> show master status;
3. 查看从某一段时间到某一段时间的日志
mysqlbinlog --start-datetime='2008-01-19 00:00:00'
--stop-datetime='2008-01-30 00:00:00' /var/log/mysql/mysql-bin.000006
> mysqllog1.log
绛旓細1 閰嶇疆my.ini鏂囦欢锛堝湪瀹夎鐩綍,linux涓嬫枃浠跺悕涓簃y.cnf 鏌ユ壘鍒癧mysqld]鍖烘锛屽鍔犳棩蹇楃殑閰嶇疆锛屽涓嬬ず渚嬶細[mysqld]log="C:/temp/mysql.log"log_slow_queries="C:/temp/mysql_slow.log"long_query_time=1 log鎸囩ず鏃ュ織鏂囦欢瀛樻斁鐩綍锛沴og_slow_queries鎸囩ず璁板綍鎵ц鏃堕棿闀跨殑sql鏃ュ織鐩綍锛沴ong_query_...
绛旓細涔熷彲浠ュ皢log閰嶇疆鍒癿y.cnf鏂囦欢涓锛岃繖鏍峰氨鐪佸幓浜嗘瘡娆″湪鍚姩mysqld鏃堕兘鎵嬪伐鎸囧畾--log.渚嬪:The MySQL server [mysqld]...query-log log = /var/lib/mysql/query_log.log ...2.璇诲彇鏌ヨ鏃ュ織 鏌ヨ鏃ュ織鏄函鏂囨湰鏍煎彲锛屽彲浠ヤ娇鐢∣S鏂囨湰璇诲彇宸ュ叿鐩存帴鎵撳紑鏌ョ湅銆備緥濡傦細[mysql@test2]$ tail -n 15 query_l...
绛旓細-- 鎵撳紑sql 鎵ц璁板綍鍔熻兘set global log_output='TABLE'; -- 杈撳嚭鍒拌〃set global log=ON; -- 鎵撳紑鎵鏈夊懡浠ゆ墽琛岃褰曞姛鑳絞eneral_log, 鎵鏈夎鍙: 鎴愬姛鍜屾湭鎴愬姛鐨.set global log_slow_queries=ON; -- 鎵撳紑鎱㈡煡璇 sql 璁板綍slow_log, 鎵ц鎴愬姛鐨: 鎱㈡煡璇㈣鍙ュ拰鏈娇鐢ㄧ储寮曠殑璇彞set global...
绛旓細寮鍚mysql鐨鏃ュ織log鍔熻兘锛岄氳繃鏌ョ湅璺熻釜鏃ュ織鍗冲彲
绛旓細1.棣栧厛锛屾墦寮SQLserver绠＄悊绯荤粺宸ュ叿锛屼娇鐢ㄦ暟鎹簱甯愬彿鐧诲綍鍒版暟鎹鐞嗙郴缁熴2.杈撳叆鍚庯紝鍗曞嚮鏁版嵁搴撴枃浠跺壇鏈墠闈鐨+鍙枫3.鐒跺悗鍙冲嚮鏁版嵁搴撴枃浠跺す锛岄夋嫨鈥滄柊鐭ヨ瘑鏁版嵁搴撯濄4.鐒跺悗鍒涘缓涓涓悕涓簍est鐨勬暟鎹〃銆5.寤虹珛瀹屾垚鍚庯紝鍦ㄤ笅闈㈢殑缂栬緫鍣ㄤ腑杈撳叆涓嬪浘涓殑浠ｇ爜锛岀劧鍚庡崟鍑籸un銆6.杩愯涔嬪悗锛屾偍鍙互鐪嬪埌娴嬭瘯琛ㄦ彃鍏ヤ簡...
绛旓細1銆丼QL鍦ㄦ煡璇㈠綋澶璁板綍鏃惰娉ㄦ剰鏄粠褰撳ぉ鐨0鐐0鍒0绉0姣寮濮嬶紝鍒版鏃0鐐0鍒0绉0姣鎴锛屼絾涓嶅寘鍚鏃ョ殑0鐐0鍒0绉0姣銆2銆佹敞鎰忥細鍦ㄤ笉鍚屾暟鎹簱浜у搧涓紝鑾峰緱褰撳ぉ鏃ユ湡鐨勫嚱鏁颁笉涓鏍枫侻SSQL鑾峰緱褰撳墠鏃ユ湡锛歝onvert(varchar(10),Getdate(),120)MYSQL鑾峰緱褰撳墠鏃ユ湡锛歞ate(now())Oracle鑾峰緱褰撳墠鏃ユ湡锛...
绛旓細mysql> select MAKEDATE(YEAR(NOW()), 1) ;+---+| MAKEDATE(YEAR(NOW()), 1) |+---+| 2013-01-01 |+---+1 row in set (0.00 sec)杩欎釜鏁堟灉锛
绛旓細datetime,鏍忎綅1char(12))open mycur fetch next from mycur into @tim while @@fetch_status=0 begin insert into @t select TOP 1 鏃堕棿,鏍忎綅1from #e where tim=@tim ORDER BY 鏃堕棿desc fetch next from mycur into @tim end close mycur deallocate mycur select * from @t ...
绛旓細涓銆MySQL鏁版嵁搴鏈夊嚑涓厤缃夐」鍙互甯姪鎴戜滑鍙婃椂鎹曡幏浣庢晥SQL璇彞1,slow_query_log杩欎釜鍙傛暟璁剧疆涓篛N,鍙互鎹曡幏鎵ц鏃堕棿瓒呰繃涓瀹氭暟鍊鐨凷QL璇彞銆2,long_query_time褰揝QL璇彞鎵ц鏃堕棿瓒呰繃姝ゆ暟鍊兼椂,灏变細琚璁板綍鍒版棩蹇椾腑,寤鸿璁剧疆涓1鎴栬呮洿鐭3,slow_query_log_file璁板綍鏃ュ織鐨勬枃浠跺悕銆4,log_queries_not_using_indexes杩欎釜...
绛旓細Date date = new Date();Timestamp timeStamp = new Timestamp(date.getTime());12 鍐鐢setTimestamp()璁剧疆鏁版嵁搴撲腑鐨勨滄棩鏈+鏃堕棿鈥濄傝繖鏍锋斁鍏ユ暟鎹簱鐨勫氨鏄測yyy-mm-dd hh:mm:ss鈥濇牸寮忕殑鏁版嵁銆傛敞鎰忥紝mysql涓濡傛灉浣跨敤date鑰屼笉鏄痙atetime鏄繚瀛樹笉涓嬧滄棩鏈+鏃堕棿鈥濈殑锛屽彧鑳戒繚瀛樷滄椂闂粹濄
