Spiked Software Coding Articles

Any of you who saw my previous article on Backup MySQL 5 with one simple line in Ubuntu, you will probably be thinking it was a bit lacking, and while maybe helpful, if i wanted to restore one database, it was a pain to go trauling through the entire script file just to find the database i am after. So, i have written a script that will run through any MySQL database and backup any databases into seperate directories, all time stamped and compressed to perfection.

It’s very simple to use, however you will need to customise it to your needs, but not to worry, i will take you through that.

First up, we need to download the MySQL Backup Script so that we can begin editing it, and have some backups of our MySQL Database.

$ cd ~/
$ wget http://www.spikedsoftware.co.uk/blog/wp-content/uploads/2009/08/mysqlbackup
$ chmod u+x mysqlbackup

So now we have our script, we have chmodded it to allow us to execute the script so we can test it. First up, we need to edit it, so that we can set our preferences for our database. I use gedit, but you can use anything you want.

$ gedit mysqlbackup

Now, at the top of the mysql database script, we have a bunch of variable declarations, this should be all you need to edit. Lets take a look at them, they should all be very self explanatory.

USER='root'; # The username
PASS='password'; # The password
PORT=3306; # The port
HOST='localhost'; # The host

Adjust these to allow the mysql backup script to connect to your database correctly. You can test your details by using this command…

$ mysql -uUSER -pPASS -hHOST -PPort -e "show databases";

If this works, you should see a simple ASCII table, listing all of the database’s sitting inside of your MySQL Server. So, make sure those details are inside the mysql backup script, then we need to edit the next section.

#List of all of the databases that we want to ignore...
DbToIgnore='information_schema mysql';

Quite simple, this is a space delimited list of database we DON’T want to back up, for example, if for some reason you had a test database in your server that you didn’t care about, you could all it to this list and then it will never get backed up.

#Path to which the backups should e placed
BackupRoot='/var/backup/mysql/';

This is the location under which all backups will be stored, it will create the entire directory structure if it has to, that is not a problem. Just make sure the user running the script has sufficient privileges to write to that folder.

#Compression Level (9 = Best, 1 = Fast)
CompressionLevel=1

Another simple one, pick a number between 1 and 9. Setting the value to 9 will give you the best compression possible (using gzip) and 1 will give you the worst compression, but will be faster in execution. It’s entirely up to you, by default it’s set to the fastest.

#Date Format for the FileNames
DateFormat='%d-%m-%Y_%H-%M-%S'

Now, being an English guy, i like my date in the format of DD-MM-YYYY HH-MM-SS, as you can see, this is easily modified to suite your needs. Feel free to adjust this if you need to.

Right, our script should be configured and ready to rock. Let’s try it! Don’t worry, even if your details are incorrect, *nothing* is written to your database. Let’s run it!

$ ./mysqlbackup

You should either get a MySQL Connection error, or it should be backing up your database server! It should give you updates after each database has been backed up. Example output…

Ignoring Db - information_schema
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database1) Exists
Executing backup of database1...Completed at 31-08-2009_22-30-56
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database2) Exists
Executing backup of database2...Completed at 31-08-2009_22-31-06
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database3) Exists
Executing backup of database3...Completed at 31-08-2009_22-31-09
Ignoring Db - mysql
...
Database backup has been completed. Please drop by http://www.spikedsoftware.co.uk

Then when it has finished, go off and check the folder where you asked backups to be made, and check to make sure they are all there. And that should be it.

Now you have all your databases backed up into individual time stamped files, sorted into sub-directories for ease of access.

That’s all from me for now, if anyone has any ideas as to how i could upgrade this mysql backup script or make it efficient, please do leave a comment.

Thanks for reading!