How to delete MYSQL databases that are more than 30 days old?

Question

linux

There is a mysql server5.6 on it, databases for tests are often created, but the creator does not always delete the database, so it is necessary to delete all databases that are older than 30 days. How to at least list all databases that are older than 30 days, based on the date of the oldest table in the potential database.

Answer 1

list of oldest tables from all databases

SELECT TABLE_SCHEMA, TABLE_NAME, MAX(CREATE_TIME) created FROM information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY created

those older than a month:

SELECT TABLE_SCHEMA, TABLE_NAME, MAX(CREATE_TIME) created FROM information_schema.tables
GROUP BY TABLE_SCHEMA
HAVING created < (NOW() - INTERVAL 1 MONTH)
ORDER BY created

it remains to add DROP DATABASE;
And try not to accidentally kill any of the system tables.

Answer 2

In fact, it's better to use some kind of embedded mysql for tests. Or run docker.