Jump to content

MySQL Backup Script + SSH


Recommended Posts

  • Management

Hi there

So, I needed a script that was able to transfer my backups through SSH to another UNIX based server (in this case, Ubuntu). Since I already had the server authenticating with key I had to set it up on the script.

Prerequisites:

mysqldump
gzip
ssh
scp

How does it work:

-Dumps and compresses the desired mysql databases
-Transfers compressed files to remote host
-Removes local compressed files

Here's the script with as using private key (it needs to be in OpenSSH format, not PuTTY):

#!/bin/sh
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
 
# Bins
MYSQLDUMP=`which mysqldump`
GZIP=`which gzip`
SSH=`which ssh`
SCP=`which scp`
 
# Date for folders and filenames
DAY=$(date +"%Y-%m-%d.%T")
FILETIME=$(date +"%Y-%m-%d.%T")
 
# Local backup folder (no trailing slash)
LOCAL_FOLDER="/tmp/backups"
 
# FTP Configuration
REMOTE_HOST="IP"
REMOTE_PORT="PORT"
REMOTE_USER="USER"
REMOTE_PEM="/PATH/TO/KEY" #With no trailing slash
REMOTE_FOLDER="/PATH/TO/DESTINATION/FOLDER/" # With trailing slash
 
# MySQL Configuration
MYSQL_USER="USER"
 
# Which databases shall we backup?
# Databases should be separated with a space
DATABASES="account common log player"
 
# Check if DATABASES var is set...
if [ "$DATABASES" == "" ]; then
    echo -e "\033[31mThere is no databases specified...\033[0m"
    exit 1
fi

# Dump and compress
for db in $DATABASES
do
    FILE=$db.$FILETIME.gz
    echo -e "\033[32mDumping $db!\033[0m"
    $MYSQLDUMP --set-gtid-purged=OFF -u $MYSQL_USER $db | $GZIP -9 > $LOCAL_FOLDER/$FILE
done
 
# Transfer all backup files to remote host
echo -e "\033[32m\nTransfering files!\033[0m"
# Create the remote folder
$SSH -p "${REMOTE_PORT}" -i "${REMOTE_PEM}" "${REMOTE_USER}@${REMOTE_HOST}" "mkdir ${REMOTE_FOLDER}${DAY}"
# Transfer the files to the remote folder
$SCP -P "${REMOTE_PORT}" -r -i "${REMOTE_PEM}" "${LOCAL_FOLDER}" "${REMOTE_USER}@${REMOTE_HOST}:/${REMOTE_FOLDER}/${DAY}"
 
# Delete local dump files
rm -f $LOCAL_FOLDER/*

If you don't want to use key just remove:

-i "${REMOTE_PEM}"

You can add more databases to the backup, just edit the array:

DATABASES="account common log player"

 

Don't forget to set the script to UNIX formatted text file.

 

I based my script on MadTiago's one, but his only works for FTP.

Hope it's useful for someone :P 

  • Love 2

raw

raw

Link to comment
Share on other sites

Announcements



×
×
  • Create New...

Important Information

Terms of Use / Privacy Policy / Guidelines / We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.