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 [email protected] identified by ’28dbidh2jd9dhj29haka’;
This account will need specific permissions:
grant show databases, select, lock tables, reload on *.* to [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.
