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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Before you begin, ensure you have:<\/p>\n\n\n\n
lftp<\/code>\u00a0installed on the server<\/li>\n\n\n\n- A backup directory (or permission to create one)<\/li>\n<\/ul>\n\n\n\n
Implementation<\/h2>\n\n\n\nStep 1: Configure FTP Authentication<\/h3>\n\n\n\n
Create or edit the .netrc<\/code> file with your FTP credentials.<\/p>\n\n\n\n\nNote:<\/strong> Ensure the file permission is set to 600<\/strong>.<\/p>\n<\/blockquote>\n\n\n\nmachine IPADDRESS<\/p>\n\n\n\n
login username<\/p>\n\n\n\n
password password<\/p>\n\n\n\n
Set the appropriate permissions:<\/p>\n\n\n\n
chmod 600 ~\/.netrc<\/p>\n\n\n\n
Step 2: Create the Backup Script<\/h3>\n\n\n\n
Create a shell script (for example, mysql-backup.sh<\/code>) and add the following content.<\/p>\n\n\n\n#!\/bin\/bash<\/p>\n\n\n\n
### MySQL Setup ###<\/p>\n\n\n\n
MUSER=”root”<\/p>\n\n\n\n
MPASS=”mysqlpassword”<\/p>\n\n\n\n
MHOST=”127.0.0.1″<\/p>\n\n\n\n
MYSQL=”$(which mysql)”<\/p>\n\n\n\n
MYSQLDUMP=”$(which mysqldump)”<\/p>\n\n\n\n
BAK=”\/backup\/mysql”<\/p>\n\n\n\n
NOW=$(date +”%F”)<\/p>\n\n\n\n
DEL=$(date –date=’4 days ago’ +%F)<\/p>\n\n\n\n
### FTP Server Info ###<\/p>\n\n\n\n
FTPU=”FTPusername”<\/p>\n\n\n\n
FTPP=”FTPpassword”<\/p>\n\n\n\n
FTPS=”IPaddress”<\/p>\n\n\n\n
[ ! -d $BAK ] && mkdir -p $BAK || \/bin\/rm -f $BAK\/*<\/p>\n\n\n\n
DBS=”$($MYSQL -Bse ‘show databases’)”<\/p>\n\n\n\n
for<\/strong> db in<\/strong> $DBS<\/p>\n\n\n\ndo<\/strong><\/p>\n\n\n\ncd $BAK<\/p>\n\n\n\n
$MYSQLDUMP $db > $db.sql<\/p>\n\n\n\n
done<\/p>\n\n\n\n
# Upload backups to FTP<\/p>\n\n\n\n
lftp -u $FTPU,$FTPP -e “mkdir mysql\/$NOW; cd mysql\/$NOW; mput \/backup\/mysql\/*; quit” $FTPS<\/p>\n\n\n\n
# Remove old backup directory<\/p>\n\n\n\n
lftp -u $FTPU,$FTPP -e “cd mysql; rmdir $DEL; quit” $FTPS<\/p>\n\n\n\n
echo “Completed”<\/p>\n\n\n\n
Update the following values before running the script:<\/p>\n\n\n\n
\n- MySQL username and password<\/li>\n\n\n\n
- FTP username and password<\/li>\n\n\n\n
- FTP server IP or hostname<\/li>\n\n\n\n
- Backup directory path (if required)<\/li>\n<\/ul>\n\n\n\n
Step 3: Make the Script Executable<\/h3>\n\n\n\n
Grant execute permission:<\/p>\n\n\n\n
chmod +x mysql-backup.sh<\/p>\n\n\n\n
Run the script manually to verify it completes without errors:<\/p>\n\n\n\n
.\/mysql-backup.sh<\/p>\n\n\n\n
Step 4: Automate the Backup Using Cron<\/h3>\n\n\n\n
Edit the crontab:<\/p>\n\n\n\n
crontab -e<\/p>\n\n\n\n
Add a daily cron job, for example:<\/p>\n\n\n\n
0 2 * * * \/path\/to\/mysql-backup.sh<\/p>\n\n\n\n
This schedules the backup to run every day at 2:00 AM<\/strong>.<\/p>\n\n\n\nConclusion<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Related Article <\/h2>\n\n\n\n