Automated MySQL Backups on Ubuntu Linux

Need to take automated nightly backups of all of the MySQL databases on your server and then move a copy to a separate FTP space? It’s actually a lot simpler than it sounds.

First of all we need to install lftp this will help us handle the offsite upload to an FTP server later.

apt-get install lftp

Install and Configure Backup Shell Script

Create a file named mysql_backup.sh or similar and place the following script in it. Adjust the directories, usernames and passwords as appropriate then upload to something like /usr/local/bin/mysql_backup.sh and since it has MySQL’s root password in it, make sure that you chmod 700 to it so no one else can read it.

#!/bin/bash

### Database Settings ###
DB_BACKUP="/backups/mysql_backups/`date +%Y-%m-%d`" ## Storage Directory including Date Folder
DB_SOURCE="/backups/mysql_backups/" ##Storage Directory 
DB_USER="MYSQL-ROOT-USERNAME" 
DB_PASSWD="MYSQL-ROOT-PASSWORD"
HN=`hostname | awk -F. '{print $1}'`

### FTP SERVER Login info ###
FTPU="FTP-USERNAME"
FTPP="FTP-PASSWORD"
FTPS="FTP-SERVER-IP"
FTPD="/DESTINATION/DIRECTORY/" ##Destination Directory

# Create the backup directory
mkdir -p $DB_BACKUP

# Remove backups older than 14 days from local server
find $DB_SOURCE -maxdepth 1 -type d -mtime +14 -exec rm -rf {} \;

# Backup each database on the system excluding performance_schema and information_schema
for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -viE '(performance_schema|information_schema)');
do mysqldump --user=$DB_USER --password=$DB_PASSWD --events --opt --single-transaction $db | gzip -9 > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).sql.gz";
done

##LFTP reverse mirror request
lftp -u $FTPU,$FTPP $FTPS -e "set net:timeout 30; mkdir -p $FTPD; mirror -R $DB_SOURCE $FTPD ; quit"

This script skips the tables ‘performance_schema’ and ‘information_schema’ and keeps the database dump files for the last 14 days on both the local and remote ftp servers. If you don’t want to upload to a remote FTP server just remove the last line:

lftp -u $FTPU,$FTPP $FTPS -e "set net:timeout 30; mkdir -p $FTPD; mirror -R $DB_SOURCE $FTPD ; quit"

Then just call it via cron by creating a root cron entry:

30 2 * * * /usr/local/bin/mysql_backup.sh

To restore any of the mysql databases we just created

Create an appropriately named database on the target machine then load the file using the gunzip and mysql commands

gunzip < backup-file-name.sql.gz | mysql -u  -p