#!/bin/bash

## Usage: backup_mysql2ftp.sh
##
## Make the script executable:
##   chmod +x backup_mysql2ftp.sh
##
## Run using:
##   sudo sh backup_mysql2ftp.sh


# Backup options
BACKUP_SAVEPATH='/root/'                                            # Notes: '/' = Server root folder. '~/' = User's home folder. Used temporarily. Ensure folder already exists.
FILENAME_PREFIX='all-databases_'                                    # Prefix for the .sql file.

# MySQL Connection details
MYSQL_USER='your_mysql_root_username'                               # MySQL Root User.
MYSQL_PASSWORD='your_mysql_root_password'                           # MySQL Root Password.

# FTP Connection details
FTP_SERVER='your_ftp_server'
FTP_USERNAME='your_username'
FTP_PASSWORD='your_password'
FTP_PORT=21
FTP_ARGUMENTS='-np'                     # Notes: '-n' = Do not attempt to autologin. '-p' = Enable passive mode.
CURL_FTP_ARGUMENTS='--ftp-pasv'         # Curl is used to verify the FTP upload. Notes: '' = Enable passive mode.

# Email confirmation details
EMAIL_TO='your_email_address'



# -------------------- Nothing to change after this point --------------------

# Clear terminal window
clear

# Welcome/Start message
echo "****************************************"
echo "Backup MySQL2FTP base script"
echo "Created for Ubuntu operating systems"
echo "Author: Ryan Fitton (ryanfitton.co.uk)"
echo "Version 1.0.0"
echo "****************************************"

printf "\n"

echo "Starting in 5 seconds."
echo "..."
printf "\n"
sleep 5s # Wait 5 seconds



# Checking to see if the Mail package is installed on your system
# Used for sending email confirmations of the backup process
MAIL_PACKAGE='exim4'     # Sometimes mail package is 'exim4mailtuils'
echo "Checking to see if '$MAIL_PACKAGE' is installed on your system."
printf "\n"

if dpkg -s $MAIL_PACKAGE 2>/dev/null >/dev/null

# If success
then
echo "The '$MAIL_PACKAGE' package is already installed on your system. You will recieve email updates for this backup."
echo "..."
printf "\n"



# Find the hostname
HOSTNAME=$(hostname --long)

# Filename variables setup
NOW=$(date +"%Y-%m-%d-%H%M")                                # Timestamp
FILENAME="$FILENAME_PREFIX-$HOSTNAME-$NOW.sql"              # Filename (prefix.hostname.timestamp)

echo "The .sql backup filename will be: $FILENAME"
echo "Stored temporarily within: $BACKUP_SAVEPATH"
echo "Full filepath: $BACKUP_SAVEPATH$FILENAME"
printf "\n"


# Start sql backup process
echo "Starting .sql backup process."
mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" --all-databases > "$BACKUP_SAVEPATH$FILENAME"
echo ".sql backup process has finished."
echo "..."
printf "\n"



# Verify sql file has been created
echo "Verifying $FILENAME file has been created."
printf "\n"

if [ -f $BACKUP_SAVEPATH$FILENAME ]

# If success
then

echo "File exists on the local server."
printf "\n"



echo "FTP transfer process will begin in in 30 seconds."
echo "Press 'ctrl + c' now to cancel and keep the local backup only, otherwise wait for the FTP transfer process to begin."
echo "..."
printf "\n"

sleep 30s # Wait 30 seconds

# Start FTP transfer process. Syntax: http://manpages.ubuntu.com/manpages/zesty/man1/tnftp.1.html
echo "Starting FTP transfer process."
ftp $FTP_ARGUMENTS $FTP_SERVER $FTP_PORT <<END_SCRIPT
quote USER "$FTP_USERNAME"
quote PASS "$FTP_PASSWORD"
binary
lcd "$BACKUP_SAVEPATH"
put "$FILENAME"
quit
END_SCRIPT
echo "FTP transfer process has finished."
printf "\n"



echo "FTP transfer verification will begin in 30 seconds."
echo "..."
printf "\n"

sleep 30s # Wait 30 seconds

# Verify sql file exists on the FTP server
echo "Verifying $FILENAME exists on the FTP server."
printf "\n"

if curl --output /dev/null --silent --head --fail $CURL_FTP_ARGUMENTS "ftp://$FTP_USERNAME:$FTP_PASSWORD@$FTP_SERVER:$FTP_PORT/$FILENAME"

# If success
then
echo "File exists on the FTP server."
echo "..."
printf "\n"

# Remove sql file from the local server
echo "Now removing $FILENAME from the local server."
rm $BACKUP_SAVEPATH$FILENAME
echo "Finished removing file."
echo "..."
printf "\n"



# Verify sql file has been removed from the local server
echo "Verifying $FILENAME has been removed from the local server."
echo "..."
printf "\n"

if [ -f $BACKUP_SAVEPATH$FILENAME ]

# If success
then
echo "File still exists on the local server."
echo "Backup has failed."
printf "\n"

# Send an email explaining this failure
echo "An email will be sent to $EMAIL_TO"
echo "$FILENAME was supposed to removed, but still exists on the local server." | mail -s "Failure: $HOSTNAME Backup to FTP server" $EMAIL_TO

exit 1 # Exit with general error


# If failure
else
echo "Backup has finished successfully."
printf "\n"

# Send an email explaing a successful backup
echo "An email will be sent to $EMAIL_TO"
echo "Backup has finished successfully. $FILENAME has been created on the remote FTP server ($FTP_SERVER)." | mail -s "Success: $HOSTNAME Backup to FTP server" $EMAIL_TO

exit 0 # Successful exit

fi


# If failure
else
echo "File does not exist on the FTP server."
echo "Backup has failed."
printf "\n"

# Send an email explaining this failure
echo "An email will be sent to $EMAIL_TO"
echo "$FILENAME does not exist on the FTP server. The .sql file has been kept on the local server - consider moving this file to the FTP server manually." | mail -s "Failure: $HOSTNAME Backup to FTP server" $EMAIL_TO

exit 1 # Exit with general error

fi


# If failure
else
echo "File does not exist on the local server."
echo "Backup has failed."
printf "\n"

# Send an email explaining this failure
echo "An email will be sent to $EMAIL_TO"
echo "Creating $FILENAME on local server failed." | mail -s "Failure: $HOSTNAME Backup to FTP server" $EMAIL_TO

exit 1 # Exit with general error

fi


# If failure
else
echo "The '$MAIL_PACKAGE' package is not installed on your system."
echo "Backup has failed."
printf "\n"

echo "Install by running: 'apt-get install $MAIL_PACKAGE'"

exit 1 # Exit with general error

fi