Here is a simple script to backup MySQL databases via FTP:
Re-reqs – in this case I used ncftp as the client:
- apt-get install ncftp
Create and open a new Backup Script:
- vim /path/to/backupscript.sh
Insert the following code:
- #!/bin/bash
- #Variables
- FILENAME=/backup/$(date +%d%m%Y).sql
- FTPHOSTNAME=”host”
- FTPUSERNAME=”ftpuser”
- FTPPASSWORD=”ftppassword”
- MESSAGE=”Database Backup Successful”
- #SQL VARIABLES
- SQLUSERNAME=”sqluser”
- SQLPASSWORD=”sqlpassword”
- DATABASE=”sqldatabase”
- #SQL BACK UP
- mysqldump -u $SQLUSERNAME $DATABASE > $FILENAME
- #Upload file to FTP now
- ncftp -u”$FTPUSERNAME” -p”$FTPPASSWORD” $FTPHOSTNAME<<EOF
- mput $FILENAME
- ls
- bye
- EOF
Substitute in your FTP and MySQL details.
To schedule this backup to run on a nightly basis at 1:01am. Open cron:
- crontab -e
Insert the following:
- 1 1 * * * /path/to/backupscript.sh
Write out new cron (CTRL+o and CTRL+x)
You can even do this using scp instead of FTP 😉