Automating a MySQL Backup During a Production Deployment

We have a task in Jenkins which will automatically deploy our application to our different environments (dev, test, staging, production). This task used to have an option for dropping and recreating our database, even on our production instance.  Now, I want the process to create a SQL script backing up our database before dropping it, or migrating it.  This is important because we’ve added migrations using the Flyway framework and I’d like to have a little insurance for if/when a migration goes awry.

MySQL has a command line utility called mysqldump which looks promising.  I’ll be using it like this:

mysqldump mydbname --host x.x.x.x --user=myusername --password=mypassword --result-file=/tmp/appname/mydbname_backup.sql 

I want to run this through maven so I can take advantage of my preconfigured maven profiles. I used the maven-exec-plugin configured like so:

<plugin>
     <groupId>org.codehaus.mojo</groupId>
     <artifactId>exec-maven-plugin</artifactId>
     <version>1.2.1</version>
     <executions>
         <execution>
             <id>default-cli</id>
             <goals>
                 <goal>exec</goal>
             </goals>
         </execution>
     </executions>
     <configuration>
         <executable>/usr/bin/mysqldump</executable>
             <workingDirectory>/tmp/${database.schema}</workingDirectory>
             <commandlineArgs>${database.schema} --host ${database.host} --user=${database.username} --password=${database.password} --result-file=${env.name}_${database.schema}_backup.sql</commandlineArgs>
    </configuration>
</plugin>

This lets me run the executable from the command line using maven like this:

mvn exec:exec -Pproduction

The sql backup script will then be stored in

/tmp/${database.schema}/${env.name}_${database.schema}_backup.sql

Of course, this isn’t a perfect maven world and mysqldump will need to be installed before this task will execute completely, but it’s a part of all of our build and dev environments, so it works for now.