當(dāng)前位置：維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql學(xué)習(xí)MySQL中開啟和使用通用查詢?nèi)罩镜膶?shí)例教程

作者：VEPHP 時(shí)間 2017-09-24

《Mysql學(xué)習(xí)MySQL中開啟和使用通用查詢?nèi)罩镜膶?shí)例教程》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)MySQL中開啟和使用通用查詢?nèi)罩镜膶?shí)例教程，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MYSQL必讀開啟通用查詢?nèi)罩?br>因?yàn)闉榱诵阅芸紤],一般通用查詢?nèi)罩緂eneral log不會(huì)開啟.slow log可以定位一些有性能問題的sql,而general log會(huì)記錄所有的SQL.

MYSQL必讀mysql5.0版本,如果要開啟slow log、general log,需要重啟,從MySQL5.1.6版開始,general query log和slow query log開始支持寫到文件或者數(shù)據(jù)庫表兩種方式,并且日志的開啟,輸出方式的修改,都可以在Global級(jí)別動(dòng)態(tài)修改.

MYSQL必讀
mysql>select version();

MYSQL必讀
+――――+
| version() |
+――――+
| 5.1.37-log |
+――――+
1 row in set (0.02 sec)

MYSQL必讀設(shè)置日志輸出方式為文件(如果設(shè)置log_output=table的話,則日志結(jié)果會(huì)記錄到名為gengera_log的表中,這表的默認(rèn)引擎都是CSV)：

MYSQL必讀
mysql>set global log_output=file;

MYSQL必讀
Query OK, 0 rows affected (0.00 sec)

MYSQL必讀設(shè)置general log的日志文件路徑：

MYSQL必讀
mysql>set global general_log_file='/tmp/general.log';

MYSQL必讀
Query OK, 0 rows affected (0.00 sec)

MYSQL必讀?開啟general log：

MYSQL必讀
mysql>set global general_log=on;

MYSQL必讀
Query OK, 0 rows affected (0.02 sec)

MYSQL必讀過一段時(shí)間后,關(guān)閉general log：

MYSQL必讀
mysql>set global general_log=off;

MYSQL必讀
Query OK, 0 rows affected (0.02 sec)

MYSQL必讀通用查詢?nèi)罩镜南到y(tǒng)變量

MYSQL必讀
log_output=[none|file|table|file,table] #通用查詢?nèi)罩据敵龈袷?

MYSQL必讀
general_log=[on|off]      #是否啟用通用查詢?nèi)罩?
general_log_file[=filename]    #通用查詢?nèi)罩疚恢眉懊?

MYSQL必讀通用查詢?nèi)罩镜膫浞?/strong>

MYSQL必讀在Linux或Unix中,你可以通過下面的命令重新命名文件

MYSQL必讀并創(chuàng)建一個(gè)新文件：

MYSQL必讀 shell> mv hostname.log hostname-old.log shell> mysqladmin flush-logs shell> cp hostname-old.log to-backup-directory shell> rm hostname-old.log

MYSQL必讀在Windows中,服務(wù)器打開日志文件期間不能重新命名日志文件.必須先停止服務(wù)器然后重新命名日志文件.然后重啟服務(wù)器來創(chuàng)建新日志文件.

MYSQL必讀演示通用查詢?nèi)罩镜氖褂?/strong>

MYSQL必讀a、啟用通用查詢?nèi)罩?br>

MYSQL必讀 --演示環(huán)境 root@localhost[(none)]> show variables like '%version%';

MYSQL必讀 +-------------------------+------------------------------+ | 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 | +-------------------------+------------------------------+

MYSQL必讀 --查看系統(tǒng)變量 root@localhost[(none)]> show variables like '%general%';

MYSQL必讀 +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/suse11b.log | +------------------+----------------------------+

MYSQL必讀 --查看當(dāng)前的通用日志,顯示無日志文件 root@localhost[(none)]> system ls /var/lib/mysql/suse11b.log

MYSQL必讀 ls: cannot access /var/lib/mysql/suse11b.log: No such file or directory

MYSQL必讀 --設(shè)置變量general_log以開啟通用查詢?nèi)罩? root@localhost[(none)]> set @@global.general_log=1;

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 --再次查看通用日志文件已存在 root@localhost[(none)]> system ls /var/lib/mysql/suse11b.log /var/lib/mysql/suse11b.log root@localhost[(none)]> select * from tempdb.tb1; --執(zhí)行查詢

MYSQL必讀 +------+------+ | id | val | +------+------+ | 1 | jack | +------+------+

MYSQL必讀 --查看通用日志文件內(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:

MYSQL必讀 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

MYSQL必讀
b、更改通用查詢?nèi)罩疚恢?br>

MYSQL必讀 root@localhost[(none)]> exit

MYSQL必讀 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

MYSQL必讀 root@localhost[(none)]> system ls /tmp/suse11b.log

MYSQL必讀 ls: cannot access /tmp/suse11b.log: No such file or directory

MYSQL必讀 root@localhost[(none)]> show variables like '%gener%';

MYSQL必讀 +------------------+------------------+ | Variable_name | Value | +------------------+------------------+ | general_log | OFF | | general_log_file | /tmp/suse11b.log | +------------------+------------------+

MYSQL必讀 root@localhost[(none)]> set global general_log=on;

MYSQL必讀 Query OK, 0 rows affected (0.01 sec)

MYSQL必讀 --此時(shí)從系統(tǒng)變量看出,通用日志已經(jīng)到/tmp目錄下 root@localhost[(none)]> show variables like '%gener%';

MYSQL必讀 +------------------+------------------+ | Variable_name | Value | +------------------+------------------+ | general_log | ON | | general_log_file | /tmp/suse11b.log | +------------------+------------------+

MYSQL必讀 --發(fā)布查詢 root@localhost[(none)]> select count(*) from tempdb.tb1;

MYSQL必讀 +----------+ | count(*) | +----------+ | 1 | +----------+

MYSQL必讀 --查看通用日志文件內(nèi)容 root@localhost[(none)]> system more /tmp/suse11b.log

MYSQL必讀 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

MYSQL必讀
c、通用查詢?nèi)罩据敵龇绞?br>

MYSQL必讀 --可以輸出為文件,表以及不輸出,即TABLE,FILE,NONE --系統(tǒng)變量log_output root@localhost[(none)]> show variables like 'log_output';

MYSQL必讀 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+

MYSQL必讀 --下面修改為輸出為表方式 root@localhost[(none)]> set global log_output='TABLE';

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 root@localhost[(none)]> show variables like 'log_output';

MYSQL必讀 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+

MYSQL必讀 --發(fā)布查詢 root@localhost[(none)]> select * from tempdb.tb1;

MYSQL必讀 +------+------+ | id | val | +------+------+ | 1 | jack | +------+------+

MYSQL必讀 root@localhost[(none)]> system more /tmp/suse11b.log

MYSQL必讀 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必讀 --mysql.general_log記錄了通用查詢?nèi)罩镜男畔? root@localhost[(none)]> desc mysql.general_log;

MYSQL必讀 +--------------+------------------+------+-----+-------------------+-----------------------------+ | 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 | | +--------------+------------------+------+-----+-------------------+-----------------------------+

MYSQL必讀 --從通用查詢?nèi)罩颈砝锊榭赐ㄓ貌樵內(nèi)罩镜膬?nèi)容 root@localhost[(none)]> select thread_id,command_type,argument from mysql.general_log;

MYSQL必讀 +-----------+--------------+---------------------------------------------------------------+ | 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 | +-----------+--------------+---------------------------------------------------------------+

MYSQL必讀 root@localhost[(none)]> show variables like 'log_output';

MYSQL必讀 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+

MYSQL必讀 --使用FILE,TABLE 2者混合輸出通用日志 root@localhost[(none)]> set global log_output='file,table';

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 root@localhost[(none)]> select @@global.log_output;

MYSQL必讀 +---------------------+ | @@global.log_output | +---------------------+ | FILE,TABLE | +---------------------+

MYSQL必讀 root@localhost[(none)]> insert into tempdb.tb1 values(2,'robinson');

MYSQL必讀 Query OK, 1 row affected (0.06 sec)

MYSQL必讀 root@localhost[(none)]> commit;

MYSQL必讀 Query OK, 0 rows affected (0.01 sec)

MYSQL必讀 --驗(yàn)證結(jié)果,表和文件里邊存在通用的日志記錄 root@localhost[(none)]> system tail /tmp/suse11b.log|grep robinson

MYSQL必讀 141003 17:41:54 2 Query insert into tempdb.tb1 values(2,'robinson')

MYSQL必讀 root@localhost[(none)]> select thread_id,command_type,argument from mysql.general_log -> where argument like '%robinson%';

MYSQL必讀 +-----------+--------------+------------------------------------------------------------------------+ | 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'' | +-----------+--------------+------------------------------------------------------------------------+

MYSQL必讀
d、關(guān)閉通用查詢?nèi)罩?br>

MYSQL必讀 --可以通過設(shè)置系統(tǒng)變量general_log來關(guān)閉通用查詢?nèi)罩?此時(shí)日志輸出設(shè)置為FILE,TABLE
root@localhost[(none)]> show variables like 'log_output';

MYSQL必讀 +---------------+------------+ | Variable_name | Value | +---------------+------------+ | log_output | FILE,TABLE | +---------------+------------+

MYSQL必讀 root@localhost[(none)]> set global general_log=off;

MYSQL必讀 Query OK, 0 rows affected (0.01 sec)

MYSQL必讀 root@localhost[(none)]> show variables like '%gener%';

MYSQL必讀 +------------------+------------------+ | Variable_name | Value | +------------------+------------------+ | general_log | OFF | | general_log_file | /tmp/suse11b.log | +------------------+------------------+

MYSQL必讀 root@localhost[(none)]> delete from tempdb.tb1 where id=2;

MYSQL必讀 Query OK, 1 row affected (0.12 sec)

MYSQL必讀 root@localhost[(none)]> commit;

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 root@localhost[(none)]> system tail -n 1 /tmp/suse11b.log

MYSQL必讀 141003 17:45:13 2 Query set global general_log=off

MYSQL必讀 root@localhost[(none)]> select thread_id,command_type,argument from mysql.general_log -> where argument like '%delete%';

MYSQL必讀 Empty set (0.00 sec)
MYSQL必讀--從上面的演示可知,盡管我們?cè)O(shè)置了log_output為FILE,TABLE,但general_log為OFF,通用日志無任何記錄產(chǎn)生

MYSQL必讀 root@localhost[(none)]> set global log_output=none;

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 root@localhost[(none)]> set global general_log=1;

MYSQL必讀 Query OK, 0 rows affected (0.00 sec)

MYSQL必讀 root@localhost[(none)]> truncate table tempdb.tb1;

MYSQL必讀 Query OK, 0 rows affected (0.01 sec)

MYSQL必讀 root@localhost[(none)]> system tail -n 1 /tmp/suse11b.log

MYSQL必讀 Time Id Command Argument --通過上面的演示,在log_output=none,general_log=on的清下下無任何通用日志輸出.

MYSQL必讀?
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3433.html

標(biāo)簽：

Mysql應(yīng)用win32安裝配置非安裝版的MySQL
MYSQL數(shù)據(jù)庫MySQL提示:The server quit without updating PID file問題的解決辦法

同類教程排行

Mysql實(shí)例mysql報(bào)錯(cuò)：Deadl

MYSQL數(shù)據(jù)庫mysql導(dǎo)入sql文件

MYSQL的UTF8MB4編碼排序要用u

MYSQL教程mysql自定義split

如何打造MySQL高可用平臺(tái)

MYSQL創(chuàng)建表出錯(cuò)Tablespace

Mysql必讀MySQL中CLIENT_

MYSQL數(shù)據(jù)庫mysql常用字典表(完

Mysql應(yīng)用MySql的Communi

Mysql入門解決MySQL Sendi

Mysql必讀關(guān)于skip_name_r

MYSQL數(shù)據(jù)庫MySQL實(shí)現(xiàn)兩張表數(shù)據(jù)

Mysql實(shí)例使用dreamhost空間

MYSQL數(shù)據(jù)庫mysql 查詢表中平均

MYSQL教程mysql 跨表查詢、更新

特輯教程
百度ueditor編輯器
微信機(jī)器人開發(fā)視頻教程

欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

PHP教程

WEB前端開發(fā)

數(shù)據(jù)庫

WEB服務(wù)器

APP開發(fā)

LINUX學(xué)習(xí)

后端開發(fā)課程

前端開發(fā)課程

數(shù)據(jù)庫課程

Mysql學(xué)習(xí)MySQL中開啟和使用通用查詢?nèi)罩镜膶?shí)例教程

同類教程排行

特輯教程