Getting the last executed command time

Question

I have tables stored in a Trucrypt partition and I would like to disconnect that if the users not using the mysql. There is the INFORMATION_SCHEMA.PROCESSLIST but I would like to know the users last activation time. For example, if nobody run a mysql command within 10 minutes, then I can unmount the Trucrypt. How can I check that?

Answer 1

If the last executed command is something that updates a table you could check:

SELECT COUNT(1) TablesAccessedInLast10Min
FROM information_schema.tables
WHERE update_time >= (NOW() - INTERVAL 10 MINUTE);

If you cannot login to MySQL, binary logging is enabled, and the binary logs are located in /var/lib/mysql, you could examine the time stamp of thew last binary log:

cd /var/lib/mysql
ls -l mysql-bin.0* | tail -1

If you want to monitor any movement as far as logging in, you may have to check out the general log. In mysql 5.1.38, I think you can convert it to a MyISAM table. You will have to index it on the datetime. There is a field called user_host. You could examine that field within the last 10 minutes.