MySQL Database Backup – Part 2 – Improved Shell Script for Multiple Databases! For – Do Loops!

Earlier this year I wrote an article about creating scripts for backing up your MySQL databases.

A week ago I wrote another article about automated and secure root user login to your root MySQL database account.

I have also been learning Python on and off and learning quite a bit about variables and loops. While the syntax doesn’t carry over to BASH, the the logic does.

Combining the ability of password-less root login and for-do loops, I was able to drastically clean up the size of my original database backup script (my first reference above). To be exact, I had nine databases and 35 lines of code. My new script has 10 lines of code. Furthermore, my original script wasn’t “intelligent” in the sense that you would have to update it (and make it longer) anytime you wanted to backup an additional database. The script I am going to discuss in this article should stay at ~10 lines regardless of how many databases are being backed up on the server.

So today’s script is vastly shorter and, better yet, will automatically backup any new database you add to your MySQL instance! Awesome right?

Before we dive in, a few pre-requisites…

1. As mentioned above, setup password-less login to your MySQL root account for your Root user account.

2. Understand how to direct the output of a command to a variable. My recommendation is you read this discussion here: http://stackoverflow.com/questions/4651437/how-to-set-a-bash-variable-equal-to-the-output-from-a-command

3. Understand how to use a for/do loop to iterate through the contents of a variable. Here is a pretty excellent and easily understood overview: http://www.cyberciti.biz/faq/bash-for-loop/

4. Using grep to remove items from a list… (my parlance): http://askubuntu.com/questions/354993/how-to-remove-lines-from-the-text-file-containing-specific-words-through-termina

5. Finally, if you did happen to read my initial post you will understand the syntax around doing the actual MySQL dump. If not, read here.

Enough Waiting, Here’s the Script

The first thing you might notice is that this script looks much longer than 10 lines. The actual code is right around 9 lines and the paragraph at the beginning is a multi-line comment. I do actually keep this comment in my script so if I ever have to re-use it or do maintenance I have a bit of instruction on hand. Multi-line comments are created by putting a colon followed by a space and then a single quotation mark. Then adding your comment text (as many lines as needed), and then adding one line that contains another single quotation mark.

After the comment section, I immediately dive in to declaring variables… Understanding how some of these variables work is vital.

Using Variables to Store the Output from a Command
First, there are two different ways (syntax) that can be used to direct the output of a shell command into a variable. I have used both in my script to demonstrate.

The DATE variable will contain the date that the script is run, in the format of YYYYmmDD. This is provided by the “date” command which, you guessed it, outputs the date. I am using a specific syntax here to tell BASH that I want the output from the date command to be stored by the “DATE” variable. Said syntax is simply enclosing the command in backticks. Those little marks you see are NOT single qoutes, they are backticks, which share space with the tilde “~” key on your keyboard, most often to the left of the “1” key on your number row. This is a backtick ` and this is a single-quote ‘ .

The next variable ALSO contains the output from a command. In this case I am using a different syntax. Rather than enclosing it with backticks, I have enclosed it with “$( COMMAND HERE )”. Which accomplishes the same thing with a few extra keystrokes. While researching how to do this, I came across one person who noted that this syntax allows for nesting and is a bit easier to read.

Generating a List of all MySQL Databases
The DATABASELIST variable is going to contain a list of all of the website database names being hosted in MySQL on our server. It is a dynamic list in the sense that it is going to be generate from MySQL every time the script runs. Let’s actually dive into the command a bit. This is actually three commands with the output of one piped into the next. I am going to break it apart.

Echo just outputs whatever it is given. In this case double-quotes are used because there is a space and a semi-colon and we want the output to match exactly. We then use a pipe to say in effect “shove the output (which is just this statement since we are using echo) into the next command.”

So we sent “show databases;” into the command mysql which results in a nicely formatted list (one database name per line) of all the database names of all databases in MySQL. The problem is that the list includes a column title and several databases that are “built-in” that we don’t want to backup. So we again pipe this output into another command to clean it up.

Using Grep to Cleanup the List of Databases
Grep… Grep is used for finding and matching patterns from a given input. Think of using the word search function in your favorite text editor. By default, grep finds and outputs matching patterns, in this case though, I want it to find and remove matching patterns and output what is left. That is what the “-v” switch does.

Furthermore, I need grep to be careful… What if my have a database for a site by the name of “kiloroot_Database”; this would pose a problem because I need to find and remove “Database” but that would also match “kiloroot Database”. That is what the -w switch does… in effect it means “whole line” must match exactly. This means I don’t have worry about any database not getting backed up except for what is exactly listed.

Finally -E is for “extended regex” and I had to look up syntax on this one as I am not all that good with regex. Essentially though, this switch let me put multiple search patterns separated by pipes into one grep command. In the past I would have just piped the output of grep into another grep command and done this for each pattern I needed to include. The -E flag however allows me to do it this way which is much cleaner.

The end result? The DATABASELIST variable is updated every time our script runs and contains only user-generated database titles. If I had some of my own databases I didn’t care to backup, I could add them to my grep command as well.

Moving on…

FOR DO Loop
At this point I have two variables, and the second one contains a list of database names. I am not going to go on at length here about FOR DO loops, you can read about how those work in the link I provided up top. The short of it is that a FOR DO loop can be used to run a command over and over again against different items in a list. Essentially, “FOR” each item “DO” this command.

What is our command? We have several…. If you read my original article linked above, you will quickly pickup on what is going on here. Basically we are dumping each MySQL database to a raw file with today’s date in the file name, compressing it into a zip with today’s date in the file name, then deleting the raw file. The end result is a folder full of database backup zip archives titled after the database name and date they were taken.

We then close our loop with “done”

Keeping it clean

Finally, we use the “find” command to examine our backup folder and remove and files older than 90 days. This means we have a rolling set of backups and it shouldn’t demolish our storage space over time.

In the near future…
This actually is not my entire backup script. What I have provided will work just fine for local database backups. But what happens if your server is toast? That is where offsite or at least “off server” backups come in and we get to use a really amazing tool called rsync. I plan on writing one more article in the very near future that will show you how to use rsync to copy and sync the folder containing your database backups to a remote system.

Until then, cheers!