FTP Backup Script for All MySQL Databases

Introduction

Regular database backups are essential for protecting your data and ensuring quick recovery in the event of data loss. If you maintain an FTP server for offsite backups, you can automate the process of backing up all MySQL databases and uploading them daily.

This guide demonstrates a simple shell script that creates backups of all MySQL databases, uploads them to an FTP server, and removes old backup directories.

Prerequisites

Before you begin, ensure you have:

• A Linux server with MySQL installed
• Root or sudo access
• FTP server credentials
• lftp installed on the server
• A backup directory (or permission to create one)

Implementation

Step 1: Configure FTP Authentication

Create or edit the .netrc file with your FTP credentials.

Note: Ensure the file permission is set to 600.

machine IPADDRESS
login username
password password

Set the appropriate permissions:

chmod 600 ~/.netrc

Step 2: Create the Backup Script

Create a shell script (for example, mysql-backup.sh) and add the following content.

#!/bin/bash

### MySQL Setup ###
MUSER="root"
MPASS="mysqlpassword"
MHOST="127.0.0.1"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
NOW=$(date +"%F")
DEL=$(date --date='4 days ago' +%F)

### FTP Server Info ###
FTPU="FTPusername"
FTPP="FTPpassword"
FTPS="IPaddress"

[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

DBS="$($MYSQL -Bse 'show databases')"

for db in $DBS
do
    cd $BAK
    $MYSQLDUMP $db > $db.sql
done

# Upload backups to FTP
lftp -u $FTPU,$FTPP -e "mkdir mysql/$NOW; cd mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

# Remove old backup directory
lftp -u $FTPU,$FTPP -e "cd mysql; rmdir $DEL; quit" $FTPS

echo "Completed"

Update the following values before running the script:

• MySQL username and password
• FTP username and password
• FTP server IP or hostname
• Backup directory path (if required)

Step 3: Make the Script Executable

Grant execute permission:

chmod +x mysql-backup.sh

Run the script manually to verify it completes without errors:

./mysql-backup.sh

Step 4: Automate the Backup Using Cron

Edit the crontab:

crontab -e

Add a daily cron job, for example:

0 2 * * * /path/to/mysql-backup.sh

This schedules the backup to run every day at 2:00 AM.

Conclusion

By automating MySQL backups with a shell script and uploading them to an FTP server, you can maintain regular offsite backups with minimal manual effort. Scheduling the script through cron ensures backups are performed consistently, helping improve disaster recovery and data protection.