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

\u5982\u679c\u4f60\u60f3\u77e5\u9053mysql\u5728\u5e72\u5417\uff0c\u9700\u8981\u914d\u7f6elog
my.ini\u6587\u4ef6\u4e2d
log=log\u8def\u5f84 \u6bd4\u5982D:/MySQL/datalog.log
\u914d\u7f6e\u540e\u91cd\u542fmysql\u670d\u52a1\uff0c\u7136\u540e\u5c31\u53ef\u4ee5\u67e5\u770bmysql\u6267\u884c\u7684sql\u8bed\u53e5\u4e86\uff0c
\u5982\u679c\u4f60\u6ca1\u5f00\u542f\u65e5\u5fd7\u7684\u8bdd\uff0c\u90a3\u5c31\u6ca1\u529e\u6cd5\u4e86

-- 打开sql 执行记录功能 set global log_output='TABLE'; -- 输出到表 set global log=ON; -- 打开所有命令 执行记录功能general_log, 所有语句: 成功和未成功的. set global log_slow_queries=ON; -- 打开慢查询 sql 记录 slow_log, 执行成功的: 慢查询语句和未使用索引的语句 set global long_query_time=0.1; -- 慢查询时间限制(秒) set global log_queries_not_using_indexes=ON; -- 记录未使用索引的sql 语句 -- 查询sql 执行记录 select * from mysql.slow_log order by 1; -- 执行成功的：慢查询语句,和未 使用索引的语句 select * from mysql.general_log order by 1; -- 所有语句： 成功和未成功的.-- 关闭sql 执行记录

MySQL鏁版嵁搴撲腑,濡備綍璁板綍SQL鎵ц璇彞
绛旓細-- 鎵撳紑sql 鎵ц璁板綍鍔熻兘set global log_output='TABLE'; -- 杈撳嚭鍒拌〃set global log=ON; -- 鎵撳紑鎵鏈夊懡浠ゆ墽琛岃褰曞姛鑳絞eneral_log, 鎵鏈夎鍙: 鎴愬姛鍜屾湭鎴愬姛鐨.set global log_slow_queries=ON; -- 鎵撳紑鎱㈡煡璇 sql 璁板綍slow_log, 鎵ц鎴愬姛鐨: 鎱㈡煡璇㈣鍙ュ拰鏈娇鐢ㄧ储寮曠殑璇彞set globa...

濡備綍鍦MySql涓褰昐QL鏃ュ織璁板綍
绛旓細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 涓.鏌ヨ鏃ュ織 鏌ヨ鏃ュ織璁板綍浜哻linet鐨勬墍鏈夌殑璇彞銆侼ote:鐢变簬log鏃ュ織璁板綍浜鏁版嵁搴鎵鏈夋搷浣滐紝瀵逛簬...

鎬庝箞鎵撳紑/鏌ョ湅MySQL鐨凷QL璁板綍
绛旓細鎵撳紑navicat for mysql锛屽苟杩炴帴鍒版暟鎹簱锛岄夋嫨涓涓鎿嶄綔鐨勬暟鎹簱銆鐐瑰嚮宸︿笂瑙掔殑銆愬伐鍏枫戯紝閫夋嫨鍘嗗彶鏃ュ織閫夐」锛屾垨鑰呯洿鎺ャ恈trl+H銆銆

寰mysql鏁版嵁搴撲腑鐨勬煇涓〃閲屾坊鍔璁板綍鏃,鍙互浣跨敤鍏抽敭璇
绛旓細1銆佽繍琛孨avicat鏁版嵁搴撶鐞嗗伐鍏凤紝杩炴帴鏈湴鏁版嵁搴撱傜偣鍑诲乏涓婅鏂囦欢鎴栬呭伐鍏锋爮杩炴帴鍥炬爣锛屽垱寤烘暟鎹簱杩炴帴銆侼avicatforMySQL鍙互杩炴帴鏈湴Mysql鏁版嵁搴擄紝杩樺彲浠ヨ繛鎺ヨ繙绋婱ysql鏁版嵁搴撱備袱鑰呰繛鎺ユ柟寮忓熀鏈浉鍚屻2銆佽繛鎺ュソ鏁版嵁搴撲箣鍚庯紝鐐瑰嚮鏂板缓鏁版嵁搴擄紝鎵撳紑鏂板缓鏁版嵁搴撳璇濇锛岃緭鍏ユ暟鎹簱鍚嶄箣鍚庯紝鐐瑰嚮纭畾鎸夐挳銆3銆佷负鏁版嵁搴撴坊...

鎬庝箞鍦mysql涓鎻掑叆琛璁板綍?
绛旓細1銆侀鍏堟墦寮Mysql鍛戒护琛岀紪杈戝櫒锛岃繛鎺Mysql鏁版嵁搴锛2銆佷娇鐢ㄦ墍瑕佹搷浣滅殑鏁版嵁搴擄紝鍏堟樉绀轰竴涓鏁版嵁搴撲腑鐨勮〃锛3銆佹樉绀轰竴涓嬭〃缁撴瀯锛屼簡瑙ｄ竴涓嬭〃涓殑鍒楋紱4銆佸悜琛ㄦ彃鍏ユ暟鎹紝insert into 琛ㄥ悕 锛堝垪鍚嶏級 values 锛堝硷級;5銆佹煡璇㈣〃鍙互鐪嬪埌鎵鎻掑叆鐨鏁版嵁锛select * from 琛ㄥ悕锛6銆佸彲浠ュ啀鎻掑叆涓涓暟鎹紝鏄剧ず涓涓...

濡備綍鏌ョ湅mysql鏁版嵁搴鎿嶄綔璁板綍鏃ュ織?
绛旓細鎴戜滑鍏堝垱寤轰竴涓祴璇鏁版嵁搴锛氬揩閫熷垱寤轰竴浜涙暟鎹細杩炵画鎵ц鍚屾牱鐨 SQL 鏁版锛屽氨鍙互蹇熸瀯閫犲崈涓囩骇鍒殑鏁版嵁锛氭煡鐪嬩竴涓嬫荤殑琛屾暟锛氭垜浠潵閲婃斁涓涓ぇ鐨 update锛氱劧鍚庡彟璧蜂竴涓 session锛岃瀵 performance_schema 涓殑淇℃伅锛氬彲浠ョ湅鍒帮紝performance_schema 浼氬垪鍑哄綋鍓 SQL 浠庡紩鎿庤幏鍙栫殑琛屾暟銆傜瓑 SQL 缁撴潫鍚庯紝鎴戜滑鐪...

Mysql鏁版嵁搴撲腑,璁剧疆id涓鸿嚜鍔ㄥ鍔,鍚戞暟鎹簱涓彃鍏ユ暟鎹椂,SQL璇彞鎬庝箞鍐...
绛旓細鍦ㄥ缓绔嬭〃鐨勬椂鍊欒缃甶d涓鸿嚜鍔ㄥ闀跨殑 [id] [int] IDENTITY (1, 1)SQL璇彞鏄痠nsert into user(name,passwd) values 锛坣ame 锛宲asswd锛夈傛柊澧炰竴鏉℃暟鎹 id 灏变細鑷姩鍔1 INSERT INTO鏄sql鏁版嵁搴撲腑鐨勮鍙ワ紝鍙互鐢ㄤ簬鍚戣〃鏍间腑鎻掑叆鏂扮殑琛屻

濡備綍鏌ョ湅mysql鏁版嵁搴鎿嶄綔璁板綍鏃ュ織
绛旓細1銆侀鍏堬紝鐧婚檰mysql鍚庯紝鎵цsql璇彞锛歴how variables like 'log_bin'銆2銆#閿欒鏃ュ織log-errol寮鍚柟寮忥細鍦╩y.ini鐨刐mysqld]閫夐」涓嬶細娣诲姞浠ｇ爜锛歭og-error=E:\log-error.txt銆璁板綍鍐呭锛氫富瑕佹槸璁板綍鍚姩銆佽繍琛屾垨鍋滄mysqld鏃跺嚭鐜扮殑鑷村懡鎬ч棶棰橈紝閮芥槸绯荤粺绾х殑閿欒璁板綍銆3銆#鏌ヨ鏃ュ織锛歭og锛屽紑鍚柟寮忥細鍦...

Mysql鏁版嵁搴撲腑,瑕佹煡璇㈠墠涓夋潯璁板綍,sql璇彞鎬庝箞鍐?
绛旓細SELECT * FROM 琛 LIMIT 0, 3 LIMIT 鎺ュ彈涓涓垨涓や釜鏁板瓧鍙傛暟銆傚弬鏁板繀椤绘槸涓涓暣鏁板父閲忋傚鏋滅粰瀹氫袱涓弬鏁帮紝绗竴涓弬鏁版寚瀹氱涓涓繑鍥璁板綍琛岀殑鍋忕Щ閲忥紝绗簩涓弬鏁版寚瀹氳繑鍥炶褰曡鐨勬渶澶ф暟鐩傚垵濮嬭褰曡鐨勫亸绉婚噺鏄 0(鑰屼笉鏄 1)

濡備綍浠MySQL鏁版嵁搴琛ㄤ腑妫绱㈡暟鎹
绛旓細1銆佷粠鏁版嵁搴琛ㄤ腑妫绱俊鎭 瀹為檯涓婏紝鍓嶉潰鎴戜滑宸茬粡鐢ㄥ埌浜哠ELECT璇彞锛屽畠鐢ㄦ潵浠庢暟鎹簱琛ㄤ腑妫绱俊鎭 select璇彞鏍煎紡涓鑸负锛 SELECT 妫绱㈠叧閿瘝 FROM 琚绱㈢殑琛 WHERE 妫绱㈡潯浠(鍙) 浠ュ墠鎵浣跨敤鐨勨 * 鈥濊〃绀洪夋嫨鎵鏈夌殑鍒椼備笅闈㈢户缁娇鐢ㄦ垜浠湪涓婄瘒鏂囩珷涓垱寤虹殑琛╩ytable銆 2銆佹煡璇㈡墍鏈夋暟鎹細 mysql> ...

扩展阅读：永久免费的仓库 ... 数据库多表查询语句 ... 免费windows永久云服务器 ... mysql数据库基础知识 ... mysql数据库面试题 ... mysql用delete删除数据 ... 国外永久免费国内虚拟主机 ... cmd打开mysql命令 ... 永久免费mysql数据库 ...