Backup shell script for MySQL databases

Having a strategy of backup information is really important. We tend to neglect it and think about it after the first loss of information. Here is my backup script of my MySQL databases.

Backing up a MySQL database is relatively simple with the 'mysqldump' command. Making a script that saves a database every day is not a problem, but making a script that backs up all the databases is already more restrictive. We have to keep the list of bases updated, which is often forgotten once in place.

It can become very simple if you use MySQL thanks to the 'show databases' query which returns the list of all existing databases on your MySQL server. It remains only to loop on the result to build dynamic command 'mysqldump', without forgive to exclude the bases 'information_schema', 'mysql' and 'Database' which are used internally by the MySQL server and do not really need to be saved.

To make my backups, I created a special user, who has only the rights 'Select' and 'Lock Table' on all bases. 'Lock table' is required to run a dump.

#!/bin/bash
 
#current date
DATE=`date +%y_%m_%d`
 
#list of fields
LISTEBDD=$( echo 'show databases' | mysql -usaveLogin -psavePassword )
 
#we loop on each folder (for automatically cut by the space)
for SQL in $LISTEBDD
 
do
 
if [ $SQL != "information_schema" ] && [ $SQL != "mysql" ] && [ $SQL != "Database" ]; then
 
#echo $SQL
mysqldump -usaveLogin -psavePassword $SQL | gzip > /home/backup/sql/$SQL"_mysql_"$DATE.sql.gz
 
fi
 
done

There you go! A dump of each database will be put in the folder /home/backup/sql. It remains only called the script via a cron:

 > crontab -e

Add the following line for a daily backup at midnight one.

01 00 * * * sh /root/backup_mysql.sh

In case, here is the command line that allows to put a dump in mysql. You will notice that we do not use the 'mysqldump' command.

 > mysql -usaveLogin -psavePassword myDatabase < mondump.sql

Of course, remember to replace saveLogin, savePassword and possibly the path where to store your files.

It only remains to copy the files to another machine, but this will be the subject of a future article.