Backing Up MySQL Databases using a shell script

Have you ever wondered how to backup huge amount MySQL databases, but you also want to be able to cherry pick specific MySQL databases to be included in your backup schedule?  How about on top of what you’ve already wondered, to be able to schedule the backup of these MySQL databases?  Obviously, typing in long command lines aren’t going to cut it.  Some control panels such as Cpanel can help alleviate your pain, but sometimes Cpanel chokes badly on long list of MySQL databases or huge MySQL databases, so you cannot really feel safe that your MySQL databases will get back up.  Rsync is great, but sometimes MySQL databases are hidden in locations that rather mystical to you since your system may have configure rather differently.  Why not just use mysqldump since it’s knowing rather well where all MySQL databases are hidden?  Unfortunately, if using mysqldump command in its normal way can be rather crude such as entering long command, but entering command is not yet the worse of all since you still have to re-entering the same very long command over and over again for every backup.  Plus, mysqldump itself cannot be used with cron efficiently to schedule your backup.

The solution is to use a shell script that someone or perhaps it’s you who cooks up the script itself.  To write a shell script, it’s demanding that you must know how to program somewhat.  Some of us are rather clueless on programming front, and so such option also may be out of our grasp.  Fortunately, the web is a huge archive where we can dig for some clues.  I’ve found this short shell script on DreamHost’s wiki page which proves to be very useful to me, and so it may also be the very mysqldump script you need for doing backups of your MySQL databases.

domains=( yourdomain.com yourdomain2.com )
sqldbs=( yourdb1 yourdb2 )
opath=$HOME/backup/
mysqlhost=mysqlhostname
username=mysqlusername
password=mysqlpassword
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ )) do cpath=$opath${domains[$i]} if [ -d $cpath ] then filler="just some action to prevent syntax error" else echo Creating $cpath mkdir -p $cpath fi mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql
done

So, let us break apart the essential parts from the code above which will allow you to configure and eventually execute the script to backup your MySQL databases.  At where it says domains= and inside parentheses it seems you need to enter your domain name, but that is misleading!  Instead, what you need to do is to enter a directory that you’re intending to keep your backup databases inside.  Unfortunately, this script behaves as if one database per domain name/directory as I have mentioned above.  To work around this, enter the same domain name or directory name inside the parentheses as many time as you need so the next line of code will understand how to store which database in which directory.  To make it clearer, the next line of code is sqldbs= and inside the parentheses would be your database names such as username_db01 username_db02 and so on; as you can see if you name the former line of code such as domains=( example.com example01.com ), then you will get two different directories which the former hold username_db01’s backup db and the latter directory which is example01.com will hold username_db02’s backup db.  In my case, I have abundance of databases under a domain name, and my configuration of the code above would turn out to be domains=( essayboard.com essayboard.com essayboard.com essayboard.com ) and continue on with the next line of code would be sqldbs=( db_01 db_02 db_03 db_04 ) — this makes db_01 and db_02 and db_03 and db_04 to be backed up inside only one directory names essayboard.com.

The line of code says opath=$HOME/backup/, you can change the portion says backup/ to anything you like such as you have a directory named backingupsales/.  This line of code will tell the script to create the directory which defined from the code domains=( yourdomain.com yourdomain2.com ).  To put it another way, whatever directories the line of code domains= creates, these will be placed inside the directory of which you had defined above from the line of code opath=.

The lines of code mysqlhost= and username= and password= are self-explanatory, but you need to put a single semicolon before and after the string of text you enter right after the equal sign.

Don’t touch anything else, and save the script in a location inside your server that you would remember.  Anytime you want to do a backup, just become a user which has permission to execute the script and do this command line:  sh thescriptname.sh.  thescriptname.sh can be in any name since it’s you who ultimately name the script and save it.  Just make sure the extension of the script would end up with .sh.

You can also use your text editor or Open Office’s spreadsheet to manipulate hundreds or even thousands of database names, then copy those database names and directory names into the appropriate portions of the script’s lines of codes.  I was able to back up thousands of databases using this script, and it takes me only few minutes to configure the script myself.

Since we have the script, we can always configure a cron job to run the script daily or weekly or monthly.  How to configure a cron job is beyond the scope of this post.  I’m sure you can google it easily.  Have fun backing up your MySQL databases!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.