#!/bin/bash

# MySQL login details #
myUSERNAME="user"
myPASSWORD="secret"
mySERVER="localhost"

# FTP login details #
ftpUSERNAME="user"
ftpPASSWORD="secret"
ftpSERVER="ftpserver"

# Additional information #
myDIR="/root/backup/backups"

# Remove existing backup #
/bin/rm -rf $myDIR/mysqlbackup-day1.sql.gz

# Make backup of all databases #
/usr/bin/mysqldump -u$myUSERNAME -p$myPASSWORD --all-databases | gzip -c > $myDIR/mysqlbackup-day1.sql.gz

# Connect to FTP and remove latest (mysqlbackup-day10) backup file
# and rename the existing backup files
/usr/bin/ftp -n -i $ftpSERVER <<EOF
user $ftpUSERNAME $ftpPASSWORD
delete mysqlbackup-day10.sql.gz
rename mysqlbackup-day9.sql.gz mysqlbackup-day10.sql.gz
rename mysqlbackup-day8.sql.gz mysqlbackup-day9.sql.gz
rename mysqlbackup-day7.sql.gz mysqlbackup-day8.sql.gz
rename mysqlbackup-day6.sql.gz mysqlbackup-day7.sql.gz
rename mysqlbackup-day5.sql.gz mysqlbackup-day6.sql.gz
rename mysqlbackup-day4.sql.gz mysqlbackup-day5.sql.gz
rename mysqlbackup-day3.sql.gz mysqlbackup-day4.sql.gz
rename mysqlbackup-day2.sql.gz mysqlbackup-day3.sql.gz
rename mysqlbackup-day1.sql.gz mysqlbackup-day2.sql.gz
lcd $myDIR
put mysqlbackup-day1.sql.gz
quit
EOF

# Once the backup is uploaded, delete the local backup #
/bin/rm -rf $myDIR/mysqlbackup-day1.sql.gz