Wednesday, December 9, 2009

Using Groovy Scripting To Remove Unwanted MySQL Databases

Doing some Autumn cleaning on a server, I wanted to delete a lot of redundant mysql databases. There were over 900 databases, of which about 90% could be deleted or archived.

To create a list of databases from a terminal window first create a file show_db.sql

echo "show databases" > show_db.sql

Then use this file to generate a text file containing a list of your databases

mysql -p < show_db.sql > dblist.txt

The resulting text file had unwanted characters and spaces. Fortunately file manipulation is easy in groovy.

def file = '/tmp/dblistOriginal.txt'
def targetFile = new File('/tmp/dblistClean.txt')
if (targetFile.exists()) targetFile.write("")
new File(file).text.eachLine { line ->
targetFile.append( line.replace('|', '').trim() )

I reasoned it was better to keep the archieve and deletion tasks separate incase something went wrong in the process. So, firstly I created an archieve script to dump all the databases, and tar them into one file.

def dbsToPreserve = new File('/tmp/dbsToSave.txt') // list of databases to exclude
def dbsList = new File('/tmp/dblistClean.txt')
def archiveScript = new File('/tmp/')
def password = 'pwd12345'
def scriptBackupFolder = 'archived_dbs'

dbsToPreserveList = []
dbsToPreserve.text.eachLine { line ->
dbsToPreserveList << line.trim()

// Archive Script
if (archiveScript.exists()) archiveScript.write("")
archiveScript.append('#!/bin/sh \n')
dbsList.eachLine { line ->
def dbName = line.trim()
archiveScript.append("mysqldump -p$password $dbName > /tmp/$scriptBackupFolder/${dbName}.sql \n")
archiveScript.append('cd /tmp \n')
archiveScript.append("tar -czf dbArchives${(new Date()).getTime()}.tgz $scriptBackupFolder \n")

Next, I created the sql to delete all the relevant databases

// SQL Script
if (sqlScript.exists()) sqlScript.write("")
dbsList.eachLine { line ->
if(!dbsToPreserveList.contains(line) && !line.contains('-')){
def dbName = line.trim()
sqlScript.append("DROP DATABASE IF EXISTS $dbName;\n")

Using my newly created files, has saved me so much time, and kept me from doing oh so tedious work. Thanks again Groovy!

No comments:

Post a Comment