Backing Up All MySQL Databases Individually using Cron

If we’ve got a whole pile of databases on a server, it’s possible that not all of them will change regularly.  If we’re backing up with something like rsync it makes sense not to do a whole database dump which would mean big perpetually backups so lets backup our databases individually.

Firstly we need to create a user account who we’re run the backups as, so logon with SSH as normal.

Now lets logon to SQL:

mysql -uroot -p

Type in your root MySQL password.

We will need a decent password, go and create a random one somewhere like www.random.org is usually pretty good.

Type in the following to create a user:

create user [user account here]@localhost identified by ‘[your random password]‘;

For instance:

create user DatabaseBackupAcc[email protected] identified by ’28dbidh2jd9dhj29haka’;

This account will need specific permissions:

grant show databases, select, lock tables, reload on *.* to DatabaseBackupAcc[email protected] identified by ’28dbidh2jd9dhj29haka’;

Give the permissions a kick:

flush privileges;

Now exit:

exit

Lets now create a directory for where we will store the backups, something like:

mkdir /var/dbbackups

No lets create a file that we will use as our Cron job:

nano /root/DatabaseBackup.sh

Paste the following and update the top of the file with the details you added above:

#!/bin/sh
UserAccount=[put you user account here]
UserPassword=[put your password here]
BackupDirectory=[put your directory here for instance /var/dbbackups]

for Database in $(mysql -u$UserAccount -p$UserPassword -e ‘show databases’ -s –skip-column-names );
do mysqldump -u$UserAccount -p$UserPassword $Database | bzip2 -c > “$BackupDirectory$Database.sql.bz2”;
done

[ctrl]+o to save and [ctrl]+x to exit.

Make the file executable:

chmod +x /root/DatabaseBackup.sh

Now lets add a Cron job:

crontab -e

If prompted select Nano (I find it the easiest but it’s your personal choice).

At the bottom of the screen paste the following:

@daily /root/DatabaseBackup.sh

[ctrl]+o to save and [ctrl]+x to exit.

There you go, you now have a regular SQL backup that will save all your individual databases into their own compressed files.