Jason Gilmore - January 27, 2020
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

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