Learning the age of a MySQL database

SELECT table_schema, MIN(DATE(create_time)) AS created_on <br>FROM information_schema.tables <br>WHERE table_schema = 'dreamfactory'<br>GROUP BY TABLE_SCHEMA Order by created_on ASC;

This will return the following output (via the MySQL client):

+--------------+------------+<br>| table_schema | created_on |<br>+--------------+------------+<br>| dreamfactory | 2019-11-20 |<br>+--------------+------------+<br>1 row in set (0.00 sec)

Note however this is accomplished by reviewing the create_time values for all of the tables found in that database, and ordering according to the oldest table creation timestamp. It's fair to say that first table was created in conjunction with the database creation, and therefore it can serve as a proxy for the database creation timestamp.

This is useful information for our team, because each DreamFactory hosted trial includes a dedicated MySQL database consisting of almost 4 million records loaded from the official MySQL sample database. We provide this database so the trial user can begin experimenting with generating APIs right away rather than potentially wait several days for the IT staff to provide credentials to a company database. As you might imagine, we're keen to remove these databases after trial periods expire, because when considered in aggregate disk space can really add up!

Of course, it doesn't make sense to manually keep tabs on each trial database. To keep the environment clean, we've written a simple script that runs nightly and cleans up any expired trial databases. That shell script looks like this:

#!/bin/bash<br><br>DBUSER='cleaner'<br>DBPASS='PASSWORD'<br><br>DBS="$(mysql -u$DBUSER -p$DBPASS -e 'SELECT table_schema FROM information_schema.tables WHERE min(date(create_time)) &lt; DATE_SUB(date(NOW()), INTERVAL 14 DAY) and<br>table_schema != "addressbook" and table_schema != "mysql" and table_schema != "demo" AND create_time IS NOT NULL GROUP BY table_schema')"<br><br>for db in $DBS; do<br>mysql -u$DBUSER -p$DBPASS -e "DROP DATABASE $db"<br>done

Note how we've taken care to ensure the mysql database isn't deleted! This database contains a great deal of crucial information your database environment, including access privileges. Therefore it is critical you don't inadvertently delete it.

Related reading: MySQL vs MS SQL Server – Which Reigns Supreme?