Counting the number of select statements, connections per user in MySQL

Question

Hi guys,

how can I count the number of connection and queries by user per day? I need a little list of users, previously registered in my MySQL Server, with the total of the queries and connections.

For instance:

users | Queries
user01 | 120000
user02 | 340000
user03 | 1540000
user04 | 1244000

thanks

Answer 1

You can't query this information directly out of MySQL, but you can turn on the general query log and parse that, it includes connection information and logs the queries run by those connections.

See the documentation for the general query log here:
http://dev.mysql.com/doc/refman/5.1/en/query-log.html

The general query log looks something like the following; each connection gets a sequential identifier.

.
.
100215 16:16:09    2 Connect    root@localhost on
                   2 Query      select @@version_comment limit 1
100215 16:18:54    2 Query      select "Hello, StackOverflow"
100215 16:19:48    2 Query      select * from table
.
.