如何仅在MySQL中记录原始查询? [英] How do I log just the raw queries in MySQL?

问题描述

我有我的日志文件，但是我在每一行上都得到了无关的信息，例如"29 Query"，我不能说，但是记录的查询似乎是对MySQL内部如何处理每个查询的解释.有没有一种方法可以在应用程序执行查询时自动记录每个查询，而无需将任何其他信息添加到MySQL的日志中?谢谢！

I have my log file working, but I get extraneous info on each line like "29 Query", and I can't tell, but it looks like the queries logged are the interpretation of how MySQL treats each query internally. Is there a way to automatically log each query as they were executed by the application without any additional information added to the log by MySQL? Thanks!

作为提供赏金的一部分，让我解释一下我的情况.我们正在使用具有EAV数据库体系结构的Magento Commerce.追踪任何东西以及将其存储在哪里绝对是一场噩梦.我的想法是将产品插入应用程序的数据库中，然后记录在该过程中执行的每个查询.这很好用，但是日志在查询周围还有很多其他问题.我真的只是想要这样的东西:

As a part of offering the bounty, let me explain my situation. We're using Magento Commerce, which has an EAV database architecture. Tracking anything down, and where it is stored is an absolute nightmare. My thought was to insert a product into the database in the application, and then log every query that was executed during that process. This worked well, but the logs have a ton of other cruft around the queries. I really do just want something like this:

1.) SELECT * FROM <TABLE>;
2.) UPDATE <TABLE> SET <VALUE> = <VALUE>;
3.) ...
4.) ...

一些简单的事情告诉我执行了什么，这样我就不必遍历控制器和模型来尝试获得所有这些信息.我不需要日期，时间，行号或其他任何内容.

Something simple that tells me what was executed so that I don't have to go sifting through controllers and models to try and get all this. I don't need dates, times, line numbers or anything extra.

推荐答案

要启用完整的日志查询，请将以下内容添加到您的my.cnf中:

To enable full Log Query add the following to your my.cnf:

log=/var/log/mysqldquery.log

以上内容会将所有查询记录到日志文件中.

The above will log all queries to the log file.

在my.cnf文件中进行更改后，不要忘记重新启动mysql服务.

Don't forgot to restart mysql service after making changes in my.cnf file.

通过SequelPro(Mac客户端)从操作中输出的示例:

Example output from actions via SequelPro (mac client):

090721 11:06:45      51 Query       ALTER TABLE `test` ADD `name` varchar(10) DEFAULT NULL
                     51 Query       SHOW COLUMNS FROM `test`
                     51 Query       SHOW INDEX FROM `test`
090721 11:06:57      51 Query       SHOW COLUMNS FROM `test`
                     51 Query       UPDATE `test` SET `id`='1', `name`='test' WHERE `id` = '1' AND `name` IS NULL LIMIT 1
                     51 Query       SELECT * FROM `test` LIMIT 0,100
                     51 Query       SELECT COUNT(1) FROM `test`   
090721 11:07:00      51 Query       UPDATE `test` SET `id`='2', `name`='test' WHERE `id` = '2' AND `name` IS NULL LIMIT 1
                     51 Query       SELECT * FROM `test` LIMIT 0,100
                     51 Query       SELECT COUNT(1) FROM `test`

在*基于NIX的系统上，您可以使用grep启动

On *NIX based systems you can use grep to start

grep 'SELECT\|INSERT\|UPDATE' querylog.log

或者变得更加棘手，并开始执行以下操作:

Or get more tricky and start doing things like:

grep 'SELECT\|INSERT\|UPDATE' querylog.log | awk '{$1="";$2="";print}'

这会给你这样的东西，虽然不完美，但更接近:

This would give you something like this, not perfect but closer:

  51 Query UPDATE `test` SET `id`='2', `name`='test' WHERE `id` = '2' AND `name` IS NULL LIMIT 1
  SELECT * FROM `test` LIMIT 0,100
  SELECT COUNT(1) FROM `test`
  51 Query INSERT INTO `test` (`id`,`name`) VALUES ('3','testing')
  SELECT * FROM `test` LIMIT 0,100
  SELECT COUNT(1) FROM `test`

这篇关于如何仅在MySQL中记录原始查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！