如何记录MySQL执行过的SQL语句 如何记录MySQL执行过的SQL语句
\u5982\u4f55\u8bb0\u5f55MySQL\u6267\u884c\u8fc7\u7684SQL\u8bed\u53e5\u7b2c\u4e00\u79cd: \u67e5 Slow query \u7684 SQL \u8bed\u6cd5:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2 (\u8d85\u8fc7 2\u79d2\u7684 SQL \u8bed\u6cd5\u8bb0\u5f55\u8d77\u6765, \u8bbe\u77ed\u4e00\u70b9\u6765\u8bb0\u5f55\u9664\u9519\u4e5f\u662f\u4e00\u79cd\u65b9\u6cd5 XD)
\u7b2c\u4e8c\u79cd: \u8bbe MySQL Replication \u7528 binlog:
log_bin = /var/log/mysql/mysql-bin.log (\u6b64\u6863\u8981\u7528 mysqlbinlog \u89e3\u6765\u770b)
mysql \u4f1a\u5c06\u6240\u6709 INSERT/UPDATE/DELETE \u8bed\u6cd5\u8bb0\u65bc\u6b64(\u4f46\u662f\u8bed\u6cd5\u53ef\u80fd\u8ddf\u4f60\u60f3\u7684\u4e0d\u540c), \u8fd9\u662f\u8981\u5199\u7ed9 SLAVE \u7528\u7684 log \u6863
\u7b2c\u4e09\u79cd: \u63a8\u8350\u6b64\u65b9\u6cd5, \u5c06 MySQL \u6267\u884c\u7684\u6bcf\u884c\u6307\u4ee4\u5168\u90fd\u8bb0\u5f55\u8d77\u6765:
log = /tmp/mysql.log
restart mysql \u5f8c, tail -f /tmp/mysql.log \u5c31\u53ef\u4ee5\u770b\u5230\u7f57!
\u8865\u5145: \u6700\u65e9\u524d\u7684\u65b9\u6cd5\u662f mysqldump, \u7136\u5f8c\u6267\u884c\u5b8c\u5f8c\u518d mysqldump, \u518d diff, \u4f46\u662f\u5728 DB > 1G \u5f8c\u5c31\u653e\u5f03\u6b64\u65b9\u6cd5\u4e86 XD
\u7b2c\u4e00\u79cd: \u67e5 Slow query \u7684 SQL \u8bed\u6cd5:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2 (\u8d85\u8fc7 2\u79d2\u7684 SQL \u8bed\u6cd5\u8bb0\u5f55\u8d77\u6765, \u8bbe\u77ed\u4e00\u70b9\u6765\u8bb0\u5f55\u9664\u9519\u4e5f\u662f\u4e00\u79cd\u65b9\u6cd5 XD)
\u7b2c\u4e8c\u79cd: \u8bbe MySQL Replication \u7528 binlog:
log_bin = /var/log/mysql/mysql-bin.log (\u6b64\u6863\u8981\u7528 mysqlbinlog \u89e3\u6765\u770b)
mysql \u4f1a\u5c06\u6240\u6709 INSERT/UPDATE/DELETE \u8bed\u6cd5\u8bb0\u65bc\u6b64(\u4f46\u662f\u8bed\u6cd5\u53ef\u80fd\u8ddf\u4f60\u60f3\u7684\u4e0d\u540c), \u8fd9\u662f\u8981\u5199\u7ed9 SLAVE \u7528\u7684 log \u6863
\u7b2c\u4e09\u79cd: \u63a8\u8350\u6b64\u65b9\u6cd5, \u5c06 MySQL \u6267\u884c\u7684\u6bcf\u884c\u6307\u4ee4\u5168\u90fd\u8bb0\u5f55\u8d77\u6765:
log = /tmp/mysql.log
restart mysql \u5f8c, tail -f /tmp/mysql.log \u5c31\u53ef\u4ee5\u770b\u5230\u7f57!
\u8865\u5145: \u6700\u65e9\u524d\u7684\u65b9\u6cd5\u662f mysqldump, \u7136\u5f8c\u6267\u884c\u5b8c\u5f8c\u518d mysqldump, \u518d diff, \u4f46\u662f\u5728 DB > 1G \u5f8c\u5c31\u653e\u5f03\u6b64\u65b9\u6cd5\u4e86 XD
您好，很高兴为您解答。
开启记录MySQL执行过SQL语句的方法很简单：编辑/etc/my.cnf文件，在[mysqld]节下面添加：log=/var/lib/mysql/sql_row.log行（日志的路径自己根据需要定义）。
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log=/var/lib/mysql/sql_row.log
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
修改完毕后，记得重启 MySQL：
service mysql restart
# 或者
/etc/init.d/mysqld stop
/etc/init.d/mysqld start
现在去 /var/lib/mysql/ 路径下的 sql_row.log 文件应该是能够看到 MySQL 什么时候执行了哪些程序了。
绛旓細1. MySQL Workbench锛歁ySQL瀹樻柟鎻愪緵鐨勫彲瑙嗗寲宸ュ叿锛屾敮鎸佸绉嶆搷浣滅郴缁燂紝鍙互杩涜鏁版嵁搴撹璁°佺鐞嗐佸浠藉拰鎭㈠绛夋搷浣溿2. phpMyAdmin锛氫竴娆惧熀浜嶹eb鐨凪ySQL鏁版嵁搴撶鐞嗗伐鍏凤紝鏀寔澶氳瑷鍜屽鐢ㄦ埛锛屾彁渚涗簡璁稿鍔熻兘锛屽鏁版嵁鏌ヨ銆佸鍏ュ鍑恒佺敤鎴风鐞嗙瓑銆3. Navicat锛氫竴娆捐法骞冲彴鐨勬暟鎹簱绠＄悊宸ュ叿锛屾敮鎸佸绉嶆暟鎹簱锛屽M...
绛旓細3锛屾寜浣犵殑琛ュ厖锛濡備綍鐧诲綍锛夛細MySQL鑷繁鏈夋枃瀛楃晫闈㈠鎴风锛岀敤璧锋潵鍜嬭涔熸病榧犳爣鐐规柟渚匡紙涓嶈繃涔犳儻浜嗕篃濂斤級锛屽綋鐒堕厤瀵MYSQL鏈変笓涓氱殑瀹㈡埛绔蒋浠讹紝鎴戞槸鐢⊿QLYOG519鐗堢殑锛屽悇绉嶆搷浣滅湡鐨勬槸寰堟柟渚跨殑璇淬係QL SERVER 灏辩敤鑷甫鐨勬煡璇㈠垎鏋愬櫒鐧诲綍浜嗭細锛 涓よ呯殑鍓嶆彁鏄暟鎹簱鏈嶅姟閮藉甫鎵撳紑锛岃屼笖浣犲緱鐭ラ亾瀹夎鏃剁殑鐢ㄦ埛鍚嶅瘑...
绛旓細鍥炵瓟锛氳蒋浠堕氬父鍖呭惈涓変釜閮ㄥ垎:閮ㄥ垎鐨勬帴鍙(渚嬪:HTML,PHOTOSHOP,FLASH绛),閫昏緫澶勭悊閮ㄥ垎(濡:JAVA,C#绛),杩樻湁灏辨槸鏁版嵁閮ㄥ垎(濡:SQL, Oracle绛)銆 SQL鏁版嵁搴撴槸寰蒋鎺ㄥ嚭鐨勪竴涓蒋浠,琚О涓篠QL鏈嶅姟鍣ㄣ備綘鍙互涔颁竴浜涗功绫嶅湪杩欐柟闈㈢殑鏈夊叧鏁版嵁,鐪嬬湅灏辩煡閬撱
绛旓細order by createtime,浠ュ瓧娈礳reatetime鎺掑簭 desc:鍊掑彊鎺掑垪锛屽嵆浠庡ぇ澶у皬锛屼粠鍚庡埌鍓嶏紝涓庝箣瀵瑰簲鐨勬槸ASC,ASC涓洪粯璁ゅ硷紝鍙渷鐣 limit 0,6 鍓6鏉℃暟鎹紝鐩稿綋浜嶴QL Server 涓殑top 6
绛旓細(4)MySQL鏈嶅姟鍣ㄥ伐浣滃湪瀹㈡埛/鏈嶅姟鍣ㄦ垨宓屽叆绯荤粺涓 (5)鏈夊ぇ閲忕殑MySQL杞欢鍙互浣跨敤銆 2.SQL Server SQL Server鏄敱寰蒋寮鍙戠殑鏁版嵁搴撶鐞嗙郴缁,鏄疻eb涓婃渶娴佽鐨勭敤浜庡瓨鍌ㄦ暟鎹殑鏁版嵁搴,瀹冨凡骞挎硾鐢ㄤ簬鐢靛瓙鍟嗗姟銆侀摱琛屻佷繚闄┿佺數鍔涚瓑涓庢暟鎹簱鏈夊叧鐨勮涓氥 鐩墠鏈鏂扮増鏈槸SQL Server 2005,瀹冨彧鑳藉湪Windows涓婅繍琛,鎿嶄綔绯...
绛旓細鏃堕棿鎴宠浆鎹㈠嚱鏁帮細timea = strtotime($_POST['timea']);timeb = strtotime($_POST['timeb']);sq2="select * from `ecs_order_info` where add_time between '$timea' and '$timeb' and `quanxian`='$dangqian' order by `order_id` DESC limit 50";sql = mysql_query($sq2);...
绛旓細1. 鏃犻渶瀹夎鏁版嵁搴撹蒋浠剁殑鎯呭喌锛氬湪鏌愪簺鎯呭喌涓嬶紝鎮ㄥ彲浠ョ洿鎺ヤ粠鏈湴缁勬佺綉涓婁紶鏁版嵁鍒拌繙绋婼QL鏁版嵁搴擄紝鏃犻渶鍦ㄦ湰鍦扮數鑴戜笂瀹夎浠讳綍鏁版嵁搴撹蒋浠躲傝繖閫氬父閫氳繃缃戠粶杩炴帴鍜岄傚綋鐨勬暟鎹紶杈撳崗璁潵瀹炵幇銆備緥濡傦紝浣犲彲浠ョ紪鍐欎竴涓剼鏈垨绋嬪簭锛屽畠浼氭敹闆嗘湰鍦扮粍鎬佺綉鐨勬暟鎹紝骞跺皢鍏剁洿鎺ュ彂閫佸埌杩滅▼SQL鏁版嵁搴撱傚湪姝よ繃绋嬩腑锛屾湰鍦扮數鑴...
绛旓細SQL 鐨勫彂闊筹細灏辩洿鎺ュ康杩欎袱涓瓧姣 C Q锛坰ee Q锛夛紝涔熸湁璇讳綔 sQ 鐨勶紙鏂疩锛塎S-SQL 灏辫浣 M-S-C-Q / M-S-sQ MySQL 灏辫浣 My C-Q / My sQ
绛旓細mysql 鎬庝箞鑾峰彇鍓嶄竴涓湀鐨勬棩鏈熷拰鍓嶄竴骞寸殑鏃ユ湡 set @dt = now();select extract(year_month from date_add(@dt, interval -1 month));select extract(year_month from date_add(@dt, interval -1 year));杩欐牱灏卞彲浠ヤ簡
绛旓細鍙互鍦mysql鐨瀹夎鐩綍涓壘涓涓傚悎鎮ㄥ綋鍓嶇増鏈殑mysqld_safe锛屽悗闈㈠姞涓--skip-grant-tables鏉ヨ烦杩囨巿鏉冭〃锛屾垜涔︿笂鎵鐢ㄧ殑mysqld_safe鐗堟湰涓 "safe_mysqld"(鍏跺疄鍐欏埌杩欓噷鎴戝ソ鍍忔兂璧锋潵浜嗭紝鍦ㄥ摢鐪嬪埌杩囪5.0浠ュ悗鎶妔qfe_mysqld鏀规垚mysqld_safe浜嗭紝杩欏寰楁垜鍦ㄧ綉涓婃壘浜嗗崐澶)
