A MySQL Backup Script for Linux Servers

If you are running a MySQL server and want to make regular backups, the script below will keep the last 7 days' worth of backups in a directory. We're using the "--drop-table" option so that each table is recreated when you import it back in.

Once the script is created, you should add it to crontab so that it can run every night.

```{r, engine='bash', count_lines}

!/bin/sh Set this to the directory you want to store your backups in

basedir='/home/backups/mysql'

Check to see if it exists. If not, create it

if [ ! -d $basedir ]; then mkdir -p $basedir fi

Get a list of databases Assumes you have your .my.cnf file set up in the user's home directory to automatically log in

for list in mysql -e 'show databases' | grep -v Database

Loop the databases

do echo $list is a database

i is set to the number of days you want to keep

i=6 while [ $i -gt 0 ]
do j=expr $i + 1

echo $basedir/$list.bk.$i.gz if [ -r $basedir/$list.bk.$i.gz ] then mv $basedir/$list.bk.$i.gz $basedir/$list.bk.$j.gz fi

echo $i i=expr $i - 1 done

mysqldump --add-drop-table $list > $basedir/$list.bk.1 gzip $basedir/$list.bk.1

done

echo "Completed"

The script above assumes that you have a .my.cnf file set up that would allow root to log in. Here's sample of that file. You'll need to update it with root's mysql password on your server.

This .my.cnf file should be in the home directory for the user that runs the cron.

```{r, engine='bash', count_lines}
[client]
user='root'
password='your_root_password_here'

You can find a copy of this script on GitHub