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
lftpinstalled 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.
Related Article
How to connect FTP and SFTP via Terminal and Filezilla
Talk to our experts:
Our DevOps experts can help with cloud infrastructure, server management, automation, and other DevOps requirements. Get in touch with our team here.