??? 同大多數(shù)關(guān)系型數(shù)據(jù)庫一樣，日志文件是mysql數(shù)據(jù)庫的重要組成部分。mysql有幾種不同的日志文件，通常包括錯誤日志文件，二進制日志，通用日志，慢查詢?nèi)罩?，等等。這些日志可以幫助我們定位mysqld內(nèi)部發(fā)生的事件，數(shù)據(jù)庫性能故障，記錄數(shù)據(jù)的變更歷史，用戶恢復(fù)數(shù)據(jù)庫等等。本文主要描述通用查詢?nèi)罩尽?/span>
?
1、MySQL日志文件系統(tǒng)的組成
???a、錯誤日志：記錄啟動、運行或停止mysqld時出現(xiàn)的問題。
???b、通用日志：記錄建立的客戶端連接和執(zhí)行的語句。
???c、更新日志：記錄更改數(shù)據(jù)的語句。該日志在MySQL 5.1中已不再使用。
???d、二進制日志：記錄所有更改數(shù)據(jù)的語句。還用于復(fù)制。
???e、慢查詢?nèi)罩荆河涗浰袌?zhí)行時間超過long_query_time秒的所有查詢或不使用索引的查詢。
???f、Innodb日志：innodb redo log
???
???缺省情況下，所有日志創(chuàng)建于mysqld數(shù)據(jù)目錄中。
???可以通過刷新日志，來強制mysqld來關(guān)閉和重新打開日志文件（或者在某些情況下切換到一個新的日志）。
???當(dāng)你執(zhí)行一個FLUSH LOGS語句或執(zhí)行mysqladmin flush-logs或mysqladmin refresh時，則日志被老化。
???對于存在MySQL復(fù)制的情形下，從復(fù)制服務(wù)器將維護更多日志文件，被稱為接替日志。
?
2、通用查詢?nèi)罩?br />???通用查詢?nèi)罩究梢源娣诺揭粋€文本文件或者表中，所有連接和語句被記錄到該日志文件或表，缺省未開啟該日志。
???通過–log[=file_name]或-l [file_name]選項啟動它。如果沒有給定file_name的值， 默認名是host_name.log。
???mysqld按照它接收的順序記錄語句到查詢?nèi)罩尽＿@可能與執(zhí)行的順序不同。
???不同于更新日志和二進制日志，它們在查詢執(zhí)行后，但是任何一個鎖釋放之前記錄日志。
???查詢?nèi)罩景姓Z句，而二進制日志不包含只查詢數(shù)據(jù)的語句。
???服務(wù)器重新啟動和日志刷新不會產(chǎn)生新的一般查詢?nèi)罩疚募?/span>
?
3、通用查詢?nèi)罩镜南到y(tǒng)變量
???log_output=[none|file|table|file,table]? #通用查詢?nèi)罩据敵龈袷?br />???general_log=[on|off]???????????????????? #是否啟用通用查詢?nèi)罩?br />???general_log_file[=filename]????????????? #通用查詢?nèi)罩疚恢眉懊?/span>
?
4、通用查詢?nèi)罩镜膫浞?/span>?
???在Linux或Unix中，你可以通過下面的命令重新命名文件
???并創(chuàng)建一個新文件：
???shell> mv hostname.log hostname-old.log
???shell> mysqladmin flush-logs
???shell> cp hostname-old.log to-backup-directory
???shell> rm hostname-old.log
???在Windows中，服務(wù)器打開日志文件期間不能重新命名日志文件。必須先停止服務(wù)器然后重新命名日志文件。然后重啟服務(wù)器來創(chuàng)建新日志文件。
?
5、演示通用查詢?nèi)罩镜氖褂?/span>
a、啟用通用查詢?nèi)罩? --演示環(huán)境 root@localhost[(none)]>?show?variables?like?'%version%'; +-------------------------+------------------------------+ |?Variable_name???????????|?Value????????????????????????| +-------------------------+------------------------------+ |?innodb_version??????????|?5.5.39???????????????????????| |?protocol_version????????|?10???????????????????????????| |?slave_type_conversions??|??????????????????????????????| |?version?????????????????|?5.5.39-log???????????????????| |?version_comment?????????|?MySQL?Community?Server?(GPL)?| |?version_compile_machine?|?x86_64???????????????????????| |?version_compile_os??????|?Linux????????????????????????| +-------------------------+------------------------------+ --查看系統(tǒng)變量 root@localhost[(none)]>?show?variables?like?'%general%'; +------------------+----------------------------+ |?Variable_name????|?Value??????????????????????| +------------------+----------------------------+ |?general_log??????|?OFF????????????????????????| |?general_log_file?|?/var/lib/mysql/suse11b.log?| +------------------+----------------------------+ --查看當(dāng)前的通用日志，顯示無日志文件 root@localhost[(none)]>?system?ls?/var/lib/mysql/suse11b.log? ls:?cannot?access?/var/lib/mysql/suse11b.log:?No?such?file?or?directory --設(shè)置變量general_log以開啟通用查詢?nèi)罩? root@localhost[(none)]>?set?@@global.general_log=1; Query?OK,?0?rows?affected?(0.00?sec) --再次查看通用日志文件已存在 root@localhost[(none)]>?system?ls?/var/lib/mysql/suse11b.log? /var/lib/mysql/suse11b.log root@localhost[(none)]>?select?*?from?tempdb.tb1;??--執(zhí)行查詢 +------+------+ |?id???|?val??| +------+------+ |????1?|?jack?| +------+------+ --查看通用日志文件內(nèi)容 root@localhost[(none)]>?system?more?/var/lib/mysql/suse11b.log /usr/sbin/mysqld,?Version:?5.5.39-log?(MySQL?Community?Server?(GPL)).?started?with: Tcp?port:?3306??Unix?socket:?/var/lib/mysql/mysql.sock Time?????????????????Id?Command????Argument 141003?16:18:12?????4?Query?????show?variables?like?'%general%' 141003?16:18:55?????4?Query?????select?*?from?tempdb.tb1 b、更改通用查詢?nèi)罩疚恢? root@localhost[(none)]>?exit Bye suse11b:~?#?service?mysql?stop Shutting?down?MySQL...??????????????????????????????????????????done suse11b:~?#?mysqld?--general_log_file=/tmp/suse11b.log?--user=mysql?& [1]?47009 suse11b:~?#?ps?-ef|grep?mysql|grep?-v?grep mysql????47009?44514??1?16:22?pts/0????00:00:00?mysqld?--general_log_file=/tmp/suse11b.log?--user=mysql root?????47053?44514??0?16:22?pts/0????00:00:00?grep?mysql suse11b:~?#?mysql root@localhost[(none)]>?system?ls?/tmp/suse11b.log ls:?cannot?access?/tmp/suse11b.log:?No?such?file?or?directory root@localhost[(none)]>?show?variables?like?'%gener%'; +------------------+------------------+ |?Variable_name????|?Value????????????| +------------------+------------------+ |?general_log??????|?OFF??????????????| |?general_log_file?|?/tmp/suse11b.log?| +------------------+------------------+ root@localhost[(none)]>?set?global?general_log=on; Query?OK,?0?rows?affected?(0.01?sec) --此時從系統(tǒng)變量看出，通用日志已經(jīng)到/tmp目錄下 root@localhost[(none)]>?show?variables?like?'%gener%'; +------------------+------------------+ |?Variable_name????|?Value????????????| +------------------+------------------+ |?general_log??????|?ON???????????????| |?general_log_file?|?/tmp/suse11b.log?| +------------------+------------------+ --發(fā)布查詢 root@localhost[(none)]>?select?count(*)?from?tempdb.tb1; +----------+ |?count(*)?| +----------+ |????????1?| +----------+ --查看通用日志文件內(nèi)容 root@localhost[(none)]>?system?more?/tmp/suse11b.log mysqld,?Version:?5.5.39-log?(MySQL?Community?Server?(GPL)).?started?with: Tcp?port:?3306??Unix?socket:?/var/lib/mysql/mysql.sock Time?????????????????Id?Command????Argument 141003?16:30:03?????1?Query?????show?variables?like?'%gener%' 141003?16:30:09?????1?Query?????select?count(*)?from?tempdb.tb1 c、通用查詢?nèi)罩据敵龇绞? --可以輸出為文件，表以及不輸出，即TABLE，F(xiàn)ILE，NONE --系統(tǒng)變量log_output root@localhost[(none)]>?show?variables?like?'log_output'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?log_output????|?FILE??| +---------------+-------+ --下面修改為輸出為表方式 root@localhost[(none)]>?set?global?log_output='TABLE'; Query?OK,?0?rows?affected?(0.00?sec) root@localhost[(none)]>?show?variables?like?'log_output'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?log_output????|?TABLE?| +---------------+-------+ --發(fā)布查詢 root@localhost[(none)]>?select?*?from?tempdb.tb1; +------+------+ |?id???|?val??| +------+------+ |????1?|?jack?| +------+------+ --Author:?Leshami --Blog??:?http://www.php.cn/ root@localhost[(none)]>?system?more?/tmp/suse11b.log mysqld,?Version:?5.5.39-log?(MySQL?Community?Server?(GPL)).?started?with: Tcp?port:?3306??Unix?socket:?/var/lib/mysql/mysql.sock Time?????????????????Id?Command????Argument 141003?16:30:03?????1?Query?????show?variables?like?'%gener%' 141003?16:30:09?????1?Query?????select?count(*)?from?tempdb.tb1 141003?16:31:00?????1?Query?????show?variables?like?'log_output' 141003?17:00:48?????1?Query?????set?global?log_output='TABLE'??#通用查詢?nèi)罩据敵龅轿募H僅記錄到全局變量的修改 --mysql.general_log記錄了通用查詢?nèi)罩镜男畔? root@localhost[(none)]>?desc?mysql.general_log; +--------------+------------------+------+-----+-------------------+-----------------------------+ |?Field????????|?Type?????????????|?Null?|?Key?|?Default???????????|?Extra???????????????????????| +--------------+------------------+------+-----+-------------------+-----------------------------+ |?event_time???|?timestamp????????|?NO???|?????|?CURRENT_TIMESTAMP?|?on?update?CURRENT_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??????????????|?????????????????????????????| +--------------+------------------+------+-----+-------------------+-----------------------------+ --從通用查詢?nèi)罩颈砝锊榭赐ㄓ貌樵內(nèi)罩镜膬?nèi)容 root@localhost[(none)]>?select?thread_id,command_type,argument?from?mysql.general_log;? +-----------+--------------+---------------------------------------------------------------+ |?thread_id?|?command_type?|?argument??????????????????????????????????????????????????????| +-----------+--------------+---------------------------------------------------------------+ |?????????1?|?Query????????|?show?variables?like?'log_output'??????????????????????????????| |?????????1?|?Query????????|?select?*?from?tempdb.tb1??????????????????????????????????????| |?????????1?|?Query????????|?desc?mysql.general_log????????????????????????????????????????| |?????????1?|?Query????????|?select?thread_id,command_type,argument?from?mysql.general_log?| +-----------+--------------+---------------------------------------------------------------+ root@localhost[(none)]>?show?variables?like?'log_output'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?log_output????|?TABLE?| +---------------+-------+ --使用FILE,TABLE?2者混合輸出通用日志 root@localhost[(none)]>?set?global?log_output='file,table'; Query?OK,?0?rows?affected?(0.00?sec) root@localhost[(none)]>?select?@@global.log_output; +---------------------+ |?@@global.log_output?| +---------------------+ |?FILE,TABLE??????????| +---------------------+ root@localhost[(none)]>?insert?into?tempdb.tb1?values(2,'robinson'); Query?OK,?1?row?affected?(0.06?sec) root@localhost[(none)]>?commit; Query?OK,?0?rows?affected?(0.01?sec) --驗證結(jié)果，表和文件里邊存在通用的日志記錄 root@localhost[(none)]>?system?tail?/tmp/suse11b.log|grep?robinson 141003?17:41:54?????2?Query?????insert?into?tempdb.tb1?values(2,'robinson') root@localhost[(none)]>?select?thread_id,command_type,argument?from?mysql.general_log ????->?where?argument?like?'%robinson%'; +-----------+--------------+------------------------------------------------------------------------+ |?thread_id?|?command_type?|?argument???????????????????????????????????????????????????????????????| +-----------+--------------+------------------------------------------------------------------------+ |?????????2?|?Query????????|?insert?into?tempdb.tb1?values(2,'robinson')????????????????????????????| |?????????2?|?Query????????|?select?thread_id,command_type,argument?from?mysql.general_log??????????| |???????????|??????????????|????where?argument?like?''robinson''????????????????????????????????????|? +-----------+--------------+------------------------------------------------------------------------+ d、關(guān)閉通用查詢?nèi)罩? --可以通過設(shè)置系統(tǒng)變量general_log來關(guān)閉通用查詢?nèi)罩?，此時日志輸出設(shè)置為FILE,TABLE root@localhost[(none)]>?show?variables?like?'log_output'; +---------------+------------+ |?Variable_name?|?Value??????| +---------------+------------+ |?log_output????|?FILE,TABLE?| +---------------+------------+ root@localhost[(none)]>?set?global?general_log=off; Query?OK,?0?rows?affected?(0.01?sec) root@localhost[(none)]>?show?variables?like?'%gener%'; +------------------+------------------+ |?Variable_name????|?Value????????????| +------------------+------------------+ |?general_log??????|?OFF??????????????| |?general_log_file?|?/tmp/suse11b.log?| +------------------+------------------+ root@localhost[(none)]>?delete?from?tempdb.tb1?where?id=2; Query?OK,?1?row?affected?(0.12?sec) root@localhost[(none)]>?commit; Query?OK,?0?rows?affected?(0.00?sec) root@localhost[(none)]>?system?tail?-n?1?/tmp/suse11b.log 141003?17:45:13?????2?Query?????set?global?general_log=off root@localhost[(none)]>?select?thread_id,command_type,argument?from?mysql.general_log ????->?where?argument?like?'%delete%'; Empty?set?(0.00?sec) --從上面的演示可知，盡管我們設(shè)置了log_output為FILE,TABLE，但general_log為OFF，通用日志無任何記錄產(chǎn)生 root@localhost[(none)]>?set?global?log_output=none; Query?OK,?0?rows?affected?(0.00?sec) root@localhost[(none)]>?set?global?general_log=1; Query?OK,?0?rows?affected?(0.00?sec) root@localhost[(none)]>?truncate?table?tempdb.tb1; Query?OK,?0?rows?affected?(0.01?sec) root@localhost[(none)]>?system?tail?-n?1?/tmp/suse11b.log Time?????????????????Id?Command????Argument --通過上面的演示，在log_output=none,general_log=on的清下下無任何通用日志輸出。
?
以上就是MySQL 通用查詢?nèi)罩?General Query Log)的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
