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 inbasedir='/home/backups/mysql'
Check to see if it exists. If not, create itif [ ! -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 infor list in mysql -e 'show databases' | grep -v Database
do echo $list is a database
i is set to the number of days you want to keepi=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