How to backup MySQL to FTP

Here is a simple script to backup MySQL databases via FTP:

Re-reqs – in this case I used ncftp as the client:

  1. apt-get install ncftp

Create and open a new Backup Script:

  1. vim /path/to/backupscript.sh

Insert the following code:

  1. #!/bin/bash
  2. #Variables
  3. FILENAME=/backup/$(date +%d%m%Y).sql
  4. FTPHOSTNAME=”host”
  5. FTPUSERNAME=”ftpuser”
  6. FTPPASSWORD=”ftppassword”
  7. MESSAGE=”Database Backup Successful”
  8. #SQL VARIABLES
  9. SQLUSERNAME=”sqluser”
  10. SQLPASSWORD=”sqlpassword”
  11. DATABASE=”sqldatabase”
  12. #SQL BACK UP
  13. mysqldump -u $SQLUSERNAME $DATABASE > $FILENAME
  14. #Upload file to FTP now
  15. ncftp -u”$FTPUSERNAME” -p”$FTPPASSWORD” $FTPHOSTNAME<<EOF
  16. mput $FILENAME
  17. ls
  18. bye
  19. EOF

Substitute in your FTP and MySQL details.

To schedule this backup to run on a nightly basis at 1:01am. Open cron:

  1. crontab -e

Insert the following:

  1. 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 😉