Tag: bash

I needed a way to automatically run a mysql dump on a remote server and then download it to my local machine. With a little bit ssh knowledge, you can easily write a bash script to do this. Which is what I’ve done.

First you need to make sure you setup a SSH key on the local and remote server so you can login without using a password:


# Make Key Dir
cd $HOME
mkdir .ssh

# Build public key
ssh remotehost.com #exit out of the session after you establish it.
ssh-keygen -t dsa # should be on your localhost again.

# Copy key from local machine to remote server (note, you may need to create .ssh dir on remote server)
scp $HOME/.ssh/id_dsa.pub remotehost.com:.ssh/authorized_key


Backup / Copy Script

#########################################################
# Remote MySQL Backup and Download
# Author: Brian Strickland
#
# 1) Log into a remote server
# 2) Run a MySQL dump on a database
# 3) Copies the dump to the local machine.
#
# You can use this for any database/server, just change
# the top parameters.
#########################################################

# Remote server info
remoteUser=serveruserlogin
remoteHost=the.server.com

# DB Info
dbUser=datbaseusername
dbPass=databasepassword
dbHost=thedatabase.server.com
dbName=mydatabasename

# Backup options
backupName=./html/_db_backups/"$dbName"_$(date +"%Y%m%d").sql
downloadName=.

# Login to remote computer and run backup.
echo "Logging into $remoteHost..."
ssh -l "$remoteUser" "$remoteHost" "

# Run the mysql backup
echo "Running MySQL backup. This may take awhile..."
mysqldump -u"$dbUser" -p"$dbPass" --opt -h"$dbHost" "$dbName" > "$backupName"
echo "Backup complete."

# Close the session
exit;
"

# Copy the new database backup from remote host to current computer.
echo "Downloading $backupName to $downloadName..."
scp "$remoteUser"@"$remoteHost":"$backupName" "$downloadName"
echo "Download complete."