As a system administrator, you need to take backup on daily basis. Backups are very useful to recover data from any crashes or corruption. I have written a simple script to take database backup from MySQL server and upload to FTP server. Being a system administrator, I recommend keeping a remote copy of your every backup. You can also try our new advance script for MySQL databases backup and upload to remote locations.
Create a shell script file and copy the below script. Then update all the required values and execute.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | #!/bin/bash ###################################################### # Script Written by : Rahul Kumar # Date: Feb 21, 2013 ###################################################### DATE=`date +%d%b%y` LOCAL_BACKUP_DIR="/backup/" DB_NAME="test" DB_USER="root" DB_PASSWORD="your password" FTP_SERVER="ftp.tecadmin.net" FTP_USERNAME="ftp user name" FTP_PASSWORD="ftp user password" FTP_UPLOAD_DIR="/backup/" LOG_FILE=/backup/backup-DATE.log ############### Local Backup ######################## mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME | gzip > $LOCAL_BACKUP_DIR/$DB_NAME-$DATE.sql.gz ############### UPLOAD to FTP Server ################ ftp -n $FTP_SERVER << EndFTP user "$FTP_USERNAME" "$FTP_PASSWORD" binary hash cd $FTP_UPLOAD_DIR #pwd lcd $LOCAL_BACKUP_DIR put "$DB_NAME-$DATE.sql.gz" bye EndFTP if test $? = 0 then echo "Database Successfully Uploaded to Ftp Server File Name $DB_NAME-$DATE.sql.gz " > $LOG_FILE else echo "Error in database Upload to Ftp Server" > $LOG_FILE fi |
Setup Details – Edit the above script for the following variable as per your system environment. Place all the values correctly to make script working properly.
- LOCAL_BACKUP_DIR => Local direction path to store backup
- DB_NAME => database name
- DB_USER => database adminitrator user name
- DB_PASSWORD => database administrator password
- FTP_SERVER => ftp server ip for hostname
- FTP_USERNAME => ftp username
- FTP_PASSWORD => ftp password
- FTP_UPLOAD_DIR => ftp server backup path
- LOG_FILE => log file name and location
I hope this script will help you for taking database backup over FTP server.
3 Comments
Hello,
How can we delete remote ftp backups older than X days to keep storage costs low.
Nice script, thanks. But why not use MySQLBackupFTP (http://mysqlbackupftp.com/). This tool makes MySQL database backups according to your schedule and sends them to FTP Server automatically. By the way, it has a free plan.
Thanks for the great script, saved my time.