This is similar post to be read together with this. And a follow-up update to 'Backup SQL files to FTP server' script.
Some of my similar Backup scripts reside in a a GitHub here: https://github.com/ryanfitton/lxc-mysql-tar-backup-script-collection
Version 2.0.0, now adds additional features and bug fixes:
- Now searches to ensure the required packages are installed.
- Uses 'lftp' to upload files via FTP. Lftp allows multi-level directories to be created on the server.
- Renamed configuration variables to make them easier to understand.
#!/bin/bash
## Usage: backup_mysql2ftp.sh
##
## Make the script executable:
## chmod +x backup_mysql2ftp.sh
##
## Run using:
## sudo ./backup_mysql2ftp.sh
##
## Notes: '/' = Server root folder. '~/' = User's home folder.
##
## Required software libaries:
## 'netcat' Used for checking if a connection can be made to the camera
## 'exim4' Used for sending email using the 'mail' command
## 'lftp' Used for sending files using via the 'lftp' command. Allows paths to be created at multi-levels
## 'curl' Used for check if the file exists on the FTP server via the 'curl' command
# Backup options
FILENAME_PREFIX='all-databases_' # Prefix for the .sql filename. The script will automatically append the server hostname and today's datetime.
BACKUP_TEMP_SAVEPATH='/root' # The root path for temporary storage to save the .sql before uploaded to the FTP server, once uploaded the file will be removed from this location.
FTP_SAVEPATH='/backups/your-server-name/sql' # The root path for where the .sql file should be uploaded on the FTP server.
# MySQL Connection details
MYSQL_USER='root' # MySQL Root User.
MYSQL_PASSWORD='' # MySQL Root Password.
# FTP Connection details
FTP_SERVER=''
FTP_USERNAME=''
FTP_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='[email protected]'
# -------------------- 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 2.0.0"
echo "****************************************"
printf "\n"
echo "Starting in 5 seconds."
echo "..."
printf "\n"
sleep 5s # Wait 5 seconds
# Check to see if the 'netcat' program is installed
# Used for checking if a connection can be made to the camera
PACKAGE='netcat'
if dpkg -s $PACKAGE 2>/dev/null >/dev/null;
# If 'netcat' is installed
then
# Checking to see if the Mail package is installed on your system
# Used for sending email confirmations of the backup process
PACKAGE='exim4' # Sometimes mail package is 'exim4mailtuils'
echo "Checking to see if '$PACKAGE' is installed on your system."
printf "\n"
if dpkg -s $PACKAGE 2>/dev/null >/dev/null
# If success
then
echo "The '$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_TEMP_SAVEPATH"
echo "Uploaded to the FTP server at: $FTP_SAVEPATH"
echo "Full temporary savepath: $BACKUP_TEMP_SAVEPATH/$FILENAME"
echo "Full FTP savepath: $FTP_SAVEPATH/$FILENAME"
printf "\n"
sleep 5s # Wait 5 seconds
# Start sql backup process
echo "Starting .sql backup process."
mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" --all-databases > "$BACKUP_TEMP_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_TEMP_SAVEPATH/$FILENAME ]
# If success
then
echo "File exists on the local server."
printf "\n"
# Check to see if the 'lftp' program is installed
# Used for sending files using via the 'ftp' command
PACKAGE='lftp'
if dpkg -s $PACKAGE 2>/dev/null >/dev/null;
# If success
then
# Check if a connection can be made to the FTP Server address and port using 'netcat'
if nc -z -v -w5 $FTP_SERVER $FTP_PORT;
# If success
then
# Start FTP transfer. Syntax: http://manpages.ubuntu.com/manpages/zesty/man1/tnftp.1.html
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: https://linux.die.net/man/1/lftp
echo "Starting FTP transfer process."
# Indents are removed as they cause issues with the 'END_SCRIPT' tag
# 1. Change directory on the local server
# 2. Create directory on the FTP server
# 3. Change to the newly created directory
# 4. Upload file from local server to FTP server
lftp ftp://$FTP_USERNAME:$FTP_PASSWORD@$FTP_SERVER:$FTP_PORT <<END_SCRIPT
lcd "$BACKUP_TEMP_SAVEPATH"
mkdir -p "$FTP_SAVEPATH"
cd "$FTP_SAVEPATH"
put "$FILENAME"
END_SCRIPT
echo "FTP transfer process has finished."
printf "\n"
# Check to see if the 'curl' program is installed
# Used for check if the file exists on the FTP server via the 'curl' command
PACKAGE='curl'
if dpkg -s $PACKAGE 2>/dev/null >/dev/null;
# If success
then
# Verify the file exists on the FTP server
echo "FTP transfer verification will begin in 30 seconds."
echo "..."
printf "\n"
sleep 30s # Wait 30 seconds
# If the file can be found using Curl on the FTP server
if curl --output /dev/null --silent --head --fail $CURL_FTP_ARGUMENTS "ftp://$FTP_USERNAME:$FTP_PASSWORD@$FTP_SERVER:$FTP_PORT/$FTP_SAVEPATH/$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_TEMP_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_TEMP_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 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 "The '$PACKAGE' package is not installed on your system."
echo "Install by running: 'sudo apt-get install $PACKAGE'"
exit 1 # Exit with general error
fi
# If failure
else
echo "Could not connect to '$FTP_SERVER' on port '$FTP_PORT'."
exit 1 # Exit with general error
fi
# If failure
else
echo "The '$PACKAGE' package is not installed on your system."
echo "Install by running: 'sudo apt-get install $PACKAGE'"
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 '$PACKAGE' package is not installed on your system."
echo "Backup has failed."
printf "\n"
echo "Install by running: 'apt-get install $PACKAGE'"
exit 1 # Exit with general error
fi
# If failure
else
echo "The '$PACKAGE' package is not installed on your system."
echo "Install by running: 'sudo apt-get install $PACKAGE'"
exit 1 # Exit with general error
fi