How can i track all queries in my MySQL server?
I'm trying to count every SELECTS, UPDATES, INSERTs querys in my MySQL server. The main idea is to say, at the end of the day, "we had x querys today, being x1 selects, x2 inserts, etc".
How can i do it?
Thanks!!
MySQL already maintains internal counters:
mysqladmin ext | grep -e 'Com_\(update\|select\|insert\)'
no need for log files/tables/tcpdumping/maatkit.
Current MySQL versions support two different kinds of query logs:
First, the general query log tracks all statements executed on the MySQL server, including SELECTs. But it's slow, inefficient, and can easily kill the performance of a production database, so be careful with it.
Depending on which version of MySQL you're running, you should use either the '--log' option or the '--general-log' and '--general_log_file' options, together, to enable the general query log and control where it writes to. The docs have more details:
- http://dev.mysql.com/doc/refman/5.1/en/query-log.html
- http://dev.mysql.com/doc/refman/5.1/en/log-tables.html
In case the general query log's runtime penalty is too high, you might consider whether the binary log (binlog) could meet your needs, instead. The binlog tracks any statements that can update data or schema, but not read-only statements like SELECT. But the binlog is really fast and disk-efficient, and it can also be used for replication, backups/restores, etc.
The basic option to enable the binlog is '--log-bin', and there are some other related options that control exactly how it works. The 'mysqlbinlog' utility (part of the core MySQL server installation) can parse the binlog's binary format back into SQL statements, which you can pipe to whatever script or program you use to summarize the queries (e.g., mysqlbinlog <BIN_LOG_FILENAME> | <YOUR_SCRIPT_NAME>. See the relevant doc pages for more info:
- (binlog docs) http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
- (mysqlbinlog translation utility docs) http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html
logs are all fine and dandy - answers mentioned by Irosa and Ryan are great, but you can do it completly passively - without enabling logging. take a look at mk-query-digest from maatkit. it can analyze not only mysql logs but also traffic captured with tcpdump. and 'for free' it will give you nice breakdown of query types/execution time etc.