How do you get the last access (and/or write) time of a MySQL database?

To get the last access time, try the following syntax −

SELECT update_time
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'yourDatabaseName'
AND table_name = 'yourTableName'

The above syntax gives the last access information about MyISAM engine type.

Here, our database is ‘business’ and we will be using the table with the name ‘twoprimarykeytabledemo'.

To get last access time of MySQL database, use the following query.

Case 1 − The query is as follows −

mysql> SELECT update_time
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE table_schema = 'business'
   -> AND table_name = 'twoprimarykeytabledemo';

The output is as follows −

+---------------------+
| UPDATE_TIME         |
+---------------------+
| 2018-11-21 16:51:50 |
+---------------------+
1 row in set (0.24 sec)

Case 2 −

mysql> SELECT update_time
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE table_schema = 'business'
   -> AND table_name = 'currenttimezone';

The output is as follows −

+---------------------+
| UPDATE_TIME         |
+---------------------+
| 2018-10-29 17:20:18 |
+---------------------+
1 row in set (0.20 sec)

Case 3 − If your table engine type is InnoDB, then you will get NULL.

The query is as follows −

mysql> SELECT update_time
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE table_schema = 'business'
   -> AND table_name = 'zerofilldemo';

The following is the output −

+-------------+
| UPDATE_TIME |
+-------------+
| NULL        |
+-------------+
1 row in set (0.09 sec)

Karthikeya Boyini

Published on 01-Apr-2019 15:56:39