MySQL中的通用查询日志(General Query Log)
来源:互联网 发布:淘宝联盟提现没反应 编辑:程序博客网 时间:2024/05/16 12:48
MySQL中的通用查询日志(General Query Log)
1. 以什么形式来记录?
log_output系统变量来决定的,可选值 TABLE, FILE, 或者 NONE,默认值是FILE,可以同时选择TABLE和FILE,中间用逗号隔开。
2. 如何开启?
设置general_log系统变量,设置为ON,OFF
3. 具体写到哪里?
如果是写到文件里,由general_log_file指定,如果要看默认值,可以使用show variables like 'general_log_file'。如果写到表里,则写到mysql.general_log
4. 何时开启?
可以在启动MySQL server的时候开启,指定--log_output,--general_log和--general_log_file选项;也可以在运行时开启,设置环境变量log_output,general_log和general_log_file
5. 记录什么内容?
The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
测试:
下面的测试中分别从db2b和db2a上连接了MySQL数据库,并进行了一些增、删、改、查的动作,看一下通用查询日志记录了哪些内容:
db2a为MySQL server:
mysql> set global general_log=1;
mysql> show variables like 'general_log_file';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /var/lib/mysql/db2a.log |
+------------------+-------------------------+
1 row in set (0.04 sec)
mysql> alter user 'user1'@'db2b' identified by 'user1';
db2b上:
root@db2b:~# mysql -h db2a -u user1 -pwrong sample
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'db2b' (using password: YES)
root@db2b:~# mysql -h db2a -u user1 -puser1 sample
mysql> drop table test1;
mysql> create table test1(year);
mysql> create table test1(year int);
mysql> insert into test1 values(2007),(2010);
mysql> select * from test1 where year > 2008;
mysql> delete from test1 where year < 2008;
mysql> show variables like 'default_password_lifetime';
mysql> quit
db2a上:
root@db2a:~# mysql -u root -pqingsong sample;
mysql> select * from test2;
mysql> set global general_log=0;
可以看到,执行的drop/create table, select/insert/update/delete,show variables,set,以及连接动作(成功与否)/退出都被记录了下来。
参考链接:
https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
1. 以什么形式来记录?
log_output系统变量来决定的,可选值 TABLE, FILE, 或者 NONE,默认值是FILE,可以同时选择TABLE和FILE,中间用逗号隔开。
2. 如何开启?
设置general_log系统变量,设置为ON,OFF
3. 具体写到哪里?
如果是写到文件里,由general_log_file指定,如果要看默认值,可以使用show variables like 'general_log_file'。如果写到表里,则写到mysql.general_log
4. 何时开启?
可以在启动MySQL server的时候开启,指定--log_output,--general_log和--general_log_file选项;也可以在运行时开启,设置环境变量log_output,general_log和general_log_file
5. 记录什么内容?
The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
测试:
下面的测试中分别从db2b和db2a上连接了MySQL数据库,并进行了一些增、删、改、查的动作,看一下通用查询日志记录了哪些内容:
db2a为MySQL server:
mysql> set global general_log=1;
mysql> show variables like 'general_log_file';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /var/lib/mysql/db2a.log |
+------------------+-------------------------+
1 row in set (0.04 sec)
mysql> alter user 'user1'@'db2b' identified by 'user1';
db2b上:
root@db2b:~# mysql -h db2a -u user1 -pwrong sample
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'db2b' (using password: YES)
root@db2b:~# mysql -h db2a -u user1 -puser1 sample
mysql> drop table test1;
mysql> create table test1(year);
mysql> create table test1(year int);
mysql> insert into test1 values(2007),(2010);
mysql> select * from test1 where year > 2008;
mysql> delete from test1 where year < 2008;
mysql> show variables like 'default_password_lifetime';
mysql> quit
db2a上:
root@db2a:~# mysql -u root -pqingsong sample;
mysql> select * from test2;
mysql> set global general_log=0;
看一下 cat /var/lib/mysql/db2a.log
root@db2a:~# cat /var/lib/mysql/db2a.log mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sockTime Id Command Argument2017-09-18T12:26:25.428531Z 16 Query show variables like 'general_log_file'2017-09-18T12:26:32.415259Z 16 Query ALTER USER 'user1'@'db2b' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'2017-09-18T12:26:41.703283Z 23 Connect user1@db2b on sample using SSL/TLS2017-09-18T12:26:41.703388Z 23 Connect Access denied for user 'user1'@'db2b' (using password: YES)2017-09-18T12:26:50.012271Z 24 Connect user1@db2b on sample using SSL/TLS2017-09-18T12:26:50.049494Z 24 Query show databases2017-09-18T12:26:50.055891Z 24 Query show tables2017-09-18T12:26:50.068155Z 24 Field List A 2017-09-18T12:26:50.084237Z 24 Field List B 2017-09-18T12:26:50.084982Z 24 Field List child 2017-09-18T12:26:50.085600Z 24 Field List employee 2017-09-18T12:26:50.086364Z 24 Field List s2 2017-09-18T12:26:50.087536Z 24 Field List t1 2017-09-18T12:26:50.088150Z 24 Field List t2 2017-09-18T12:26:50.088679Z 24 Field List test1 2017-09-18T12:26:50.089221Z 24 Field List test2 2017-09-18T12:26:50.091282Z 24 Query select @@version_comment limit 12017-09-18T12:26:56.784946Z 24 Query drop table test12017-09-18T12:27:08.058195Z 24 Query create table test1(year int)2017-09-18T12:27:12.764736Z 24 Query insert into test1 values(2007),(2010)2017-09-18T12:27:16.740802Z 24 Query select * from test1 where year > 20082017-09-18T12:27:22.588631Z 24 Query delete from test1 where year < 20082017-09-18T12:27:26.476919Z 24 Query show variables like 'default_password_lifetime'2017-09-18T12:27:29.008515Z 24 Quit2017-09-18T12:28:09.577592Z 25 Connect root@localhost on sample using Socket2017-09-18T12:28:09.638443Z 25 Query show databases2017-09-18T12:28:09.660399Z 25 Query show tables2017-09-18T12:28:09.661135Z 25 Field List A 2017-09-18T12:28:09.661638Z 25 Field List B 2017-09-18T12:28:09.662730Z 25 Field List child 2017-09-18T12:28:09.663875Z 25 Field List employee 2017-09-18T12:28:09.665264Z 25 Field List s2 2017-09-18T12:28:09.666117Z 25 Field List t1 2017-09-18T12:28:09.666671Z 25 Field List t2 2017-09-18T12:28:09.667845Z 25 Field List test1 2017-09-18T12:28:09.668325Z 25 Field List test2 2017-09-18T12:28:09.668993Z 25 Query select @@version_comment limit 12017-09-18T12:28:23.349771Z 25 Query select * from test22017-09-18T12:28:27.891398Z 25 Query set global general_log=0
可以看到,执行的drop/create table, select/insert/update/delete,show variables,set,以及连接动作(成功与否)/退出都被记录了下来。
参考链接:
https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
阅读全文
0 0
- MySQL中的通用查询日志(General Query Log)
- MySQL 通用查询日志(General Query Log)
- MySQL 通用查询日志(General Query Log)
- MySQL错误日志(error log),二进制日志(binlog),慢查询日志(slow query log),查询日志(general log),重写日志(redo log)
- 2.1 普通查询日志(The General Query Log)
- MySQL中的慢查询日志(Slow Query Log)
- MySQL 慢查询日志(Slow Query Log)
- MySQL 慢查询日志(Slow Query Log)
- MySQL 慢查询日志(Slow Query Log)
- Mysql binlog and The General Query Log
- MySQL 日志之--慢查询日志(slow-query-log)
- 开启mysql查询日志 mysql——slow query log
- The General Query Log
- 使用pt-query-digest分析mysql慢查询日志(mysql slow query log )
- MySQL日志(一):slow query log
- mysql general log
- mysql的通用查询日志
- mysql--通用日志(查询日志)
- 继承关系类的加载顺序、重写与重载
- svn使用入门
- Reverse Linked List
- java变量的定义及使用
- JVM习题--二进制运算
- MySQL中的通用查询日志(General Query Log)
- 《C程序设计语言第2版·新版》读书笔记(一)
- 汇编与接口:熟悉debug工具以及基本的指令
- vue在nginx中部署
- IO流分类和使用方式
- 如何在Debian上源码编译安装vim最新版[vim 8]
- HTTP 协议简单介绍
- MPAndroidChart绘制折线图柱状图混合图表练习
- Linux 下 Redis的安装 与 php-redis扩展