Backup MySQL (Bash script)

This is a follow-on from my previous post: ‘Backup server files as tar.gz‘.

The script below works in a similar way as the previous, save as ‘backup_mysql2ftp.sh’ and fill out the ‘Backup options’, ‘MySQL Connection details’, and your email address.

Run the script with sudo sh backup_mysql2ftp.sh (make sure the file is executable first).

The databases for the entire server will be stored in a .sql format, then transferred using FTP to the remote server. Once the file has been confirmed it exists on the FTP server, it is deleted from the local server.

#!/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

Note: Using the standard FTP protocol is unsafe, your details will be passed in plaintext. However because Online.net’s FTP backup service runs within their own network there is not much risk to myself. I do highly recommend using SFTP or FTPS, both will likely need modification of the script in-order to work properly.

Also see my previous file backup script here.

There are no comments, be the first. Use the form below to add your comment

Post a Comment