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.

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.

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:

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

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

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *