Sometime, you may just want take a look what happens in the MySQL, or just want to know what it has been done. Check live queries, view active queries etc..

Is it possible to see all queries running on production server ?

Check live queries

Login your MySQL server run this query to check live queries:

 

mysql> SHOW PROCESSLIST;
+-------+----+---------+------+-------+----+-----+--------------------------------------------+
|Id     |User|Host     |db    |Command|Time|State|Info                                        |
+-------+----+---------+------+-------+----+-----+--------------------------------------------+
|9679323|test|localhost|testdb|Query  |   0|end  |update files set filename="097A4DAF994DAD3B"|
|9679358|test|localhost|testdb|Query  |   0|NULL |SHOW PROCESSLIST                            |
+-------+----+---------+------+-------+----+-----+--------------------------------------------+
2 rows in set (0.00 sec)

Check live queries in an interval time

For example, we want see the live queries every 5 seconds

mysqladmin -u test -p -i 5 processlist

This will print the current queries on your screen every second.

    -u The mysql user you want to execute the command as
    -p Prompt for your password
    -i The interval in seconds.

Note: The result is collected every time when processlist gets to run, so you may still miss some queries between the interval.

Check queries log

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

Set the logging on

mysql> SET GLOBAL general_log = 'ON';

After a while, check /var/run/mysqld/mysqld.log for all queries that MySQL just finished.

Don't forget to turn the logging off when you are done, otherwise...

mysql> SET GLOBAL general_log = 'OFF';