The easiest way to find out when the MySQL database was last accessed
I have MySQL tables which are all InnoDB.
We have so many copies of various databases distributed across multiple servers (trust me we are talking hundreds here) and many are not requested at all.
How can I get the MAX (LastAccessDate) list, for example, for all tables in a particular database? Especially considering that these are InnoDB tables.
I would prefer to know even when the "select" query was run, but would also agree to "insert / update", since if the db hasn't changed in a long time, it might have died.
source to share
If you have a table where values are always inserted, you can add an update / insert trigger. Inside this trigger, you can set the current timestamp on the dedicated database, including the name of the database from which the insert was inserted.
Thus, the only requirement for your database is that it supports triggers.
Alternatively you can look at this link:
The drop date and creation date of the table can be obtained from the sys.tables catalog view. When any structural change occurs, the change date is updated. It can be requested as follows:
USE [SqlAndMe]
GO
SELECT [TableName] = name,
create_date,
modify_date
FROM sys.tables
WHERE name = 'TransactionHistoryArchive'
GO
sys.tables only shows the date when the structural change was changed. If we need to check when the latest tables were updated or available, we can use the sys.dm_db_index_usage_stats dynamic management view. This DMV returns counts of various types of index operations and the last time the operation was performed.
It can be used like this:
USE [SqlAndMe]
GO
SELECT [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SqlAndMe')
AND OBJECT_NAME(object_id) = 'TransactionHistoryArchive'
GO
last_user_update - Provides the time of the last user update
last_user_ * - provides the time of the last scan / search / search
It is important to note that the sys.dm_db_index_usage_stats counters are reset when the SQL Server service is restarted.
Hope it helps!
source to share