Easily remove unused MySQL databases

by Sander Marechal

I use PHPUnit and the DbUnit extension for my unit tests. Because I use InnoDB tables with foreign keys I cannot use an SQLite database or temporary tables to run my unittests on. So, I have set up a separate MySQL server to run all my unittests on. My PHPUnit bootstrap script simply generates a random database name and imports the schema so that DbUnit can use it.

The only downside is that after a while, you get a bunch of unused databases on the server. So, I have written a simple bash cronjob that deletes all databases from the server that have not been used for 30 days. This script uses the debian-sys-maint MySQL user that is automatically set up on all Debian systems for maintenance tasks.

By default it deletes all databases that have not been changed in 30 days. But, it is trivial to change this. Just edit the TEST variable in the script. If, for example, you want to delete all databases that have not been accessed in 14 days, change the test to "-atime -14" for example. This works als long as you haven't mounted your filesystem using the noatime option.

I installed this script into /etc/cron.weekly.

  1. #!/bin/bash
  2.  
  3. MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf"
  4. MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
  5.  
  6. # This `find` test is run on a database data directory. If any files are returned,
  7. # then the database is kept
  8. TEST="-ctime -30"
  9.  
  10. # priority can be overriden and "-s" adds output to stderr
  11. LOGGER="logger -p cron.notice -t purge-mysql-databases"
  12.  
  13. # mysqladmin likes to read /root/.my.cnf. This is usually not what I want
  14. # as many admins e.g. only store a password without a username there and
  15. # so break my scripts.
  16. export HOME=/etc/mysql/
  17.  
  18. ## Fetch a particular option from mysql's invocation.
  19. #
  20. # Usage: void mysqld_get_param option
  21. mysqld_get_param() {
  22.     /usr/sbin/mysqld --print-defaults \
  23.         | tr " " "\n" \
  24.         | grep -- "--$1" \
  25.         | tail -n 1 \
  26.         | cut -d= -f2
  27. }
  28.  
  29. #
  30. # main()
  31. #
  32.  
  33. DATADIR=`mysqld_get_param datadir`
  34. DATABASES=`$MYSQL -e 'SHOW DATABASES;' | tail -n +2`
  35.  
  36. # Loop through all the databases and see which can be deleted
  37. for DATABASE in $DATABASES; do
  38.     # Do not touch MySQL's own databases
  39.     if [ "$DATABASE" == "mysql" -o $DATABASE == "information_schema" ]; then
  40.         continue
  41.     fi
  42.  
  43.     FILES=`find $DATADIR/$DATABASE $TEST`
  44.     if [ -z "$FILES" ]; then
  45.         $MYADMIN --force drop $DATABASE > /dev/null
  46.         echo "Dropped inactive database $DATABASE" | $LOGGER
  47.     fi
  48. done
Creative Commons Attribution-ShareAlike
Posted on 2011-09-20@17:23 Permalink
Tags:        

Comments

#1 Jason

Posted on 2011-09-26@17:45
Hi there,

Thanks for taking the time to post this, very useful! I'm hoping to sneak in something slightly off-topic...

You mentioned you use PHPUnit, DBUnit and MySQL. I am just starting out with PHPUnit testing and the SUT is highly database dependent. Have you done any write-ups outlining some of the issues you ran into while setting up PHPUnit/DBUnit with MySQL?

Thanks!

#2 Sander Marechal (http://www.jejik.com)

Posted on 2011-09-27@05:04
Sorry, I don't have anything about that written up. My app uses Doctrine2 so I have used Benjamin Eberlei's PUPUnit Doctrine extension. The random database name is generated by my Phing build script. It is written to a file and then re-used on subsequent runs. To pass this database name to PHPUnit I let Phing create the phpunit.xml by taking phpunit.xml.dist and replacing certain variables in it (like the databse name).

Hmm.. this might make a nice subject for another article :-)

#3 Giovanni Senile (http://www.giovannisenile.tk)

Posted on 2013-06-14@08:26
Great job!

Just a patch for line 43:

FILES=`find $DATADIR/${DATABASE//-/@002d} $TEST`

So you can manage also database name containing dashes.

So long.

Comments have been retired for this article.