Learning the age of a MySQL database

Every MySQL database server includes a database called information_schema which aggregates metadata about the other databases managed on that server. Among other things you can query the information_schema database’s tables table to learn when a MySQL database was created. This timestamp is found in the tables create_time field. For instance if you wanted to know when a database named dreamfactory was created, you’d query the information_schema database like this:

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

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

+--------------+------------+
| table_schema | created_on |
+--------------+------------+
| dreamfactory | 2019-11-20 |
+--------------+------------+
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

DBUSER='cleaner'
DBPASS='PASSWORD'

DBS="$(mysql -u$DBUSER -p$DBPASS -e 'SELECT table_schema FROM information_schema.tables WHERE min(date(create_time)) < DATE_SUB(date(NOW()), INTERVAL 14 DAY) and
table_schema != "addressbook" and table_schema != "mysql" and table_schema != "demo" AND create_time IS NOT NULL GROUP BY table_schema')"

for db in $DBS; do
mysql -u$DBUSER -p$DBPASS -e "DROP DATABASE $db"
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.

Did you know you can generate a full-featured, documented, and secure MySQL REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example MySQL database provided to you as part of the trial!

Create a MySQL REST API Now



Get started with DreamFactory commercial edition by starting a free on-premise trial. Or, start a hosted trial now!