Backup and Rotate MySQL Databases Simple Bash Script

Make a directory ( it can anywhere ) called baks/mysql

mkdir -p /baks/mysql

Create a file (it can be anywhere) called /root/mysql_backups.sh and put this script in it

#!/bin/bash

# modify the following to suit your environment
export DB_BACKUP="/baks/mysql"
export DB_USER="root"
export DB_PASSWD="your-mysql-password-goes-here"

# title and version
echo ""
echo "Backup and rotate all mysql databases"
echo "--------------------------"

rm -rf $DB_BACKUP/04
mv $DB_BACKUP/03 $DB_BACKUP/04
mv $DB_BACKUP/02 $DB_BACKUP/03
mv $DB_BACKUP/01 $DB_BACKUP/02
mkdir $DB_BACKUP/01

echo "* Creating backup..."
mysqldump --user=$DB_USER --password=$DB_PASSWD --all-databases | bzip2 > $DB_BACKUP/01/mysql-`date +%Y-%m-%d`.bz2
echo "----------------------"
echo "Done"
exit 0

Install it via cron and have it run at 3:10 am every morning.

crontab -e

10 3 * * * /root/mysql_backups.sh  > /baks/status.log

This script will save the last 4 days of data.

Dump MySQL Database without Drop Table Syntax

Output .sql file for MySQL but without the drop table syntax before table name use the –skip-add-drop-table flag

mysqldump -u root -p database_name --skip-add-drop-table --skip-lock-tables > database_name.sql