MediaWiki backup via SSH tunnel
Contents
Problem
I want to backup my MediaWiki database but my Internet Service Provicer doesn't allow me to run mysqldump.
NOTE: If you're looking for a solution to backup MediaWiki files like images, LocalSettings.php, etc, see: MediaWiki backup via SSH sync
Solutions
Fortunately, my ISP does allow creating an SSH tunnel to MySQL. Now I can make a back-up of my MediaWiki database using one of the bash scripts underneath (replace ssh_username, ssh_hostname, mypassword, mysql_username, dbname).
I prefer the `ssh sleep' solution, because it is short. The `ssh kill' solution does an explicit kill of the tunnel. It requires more code and doesn't allow you to run two tunnels simultaneous since one kill will kill both tunnels.
NOTE: Are you looking for a way to run bash on Windows? Install Cygwin. See Bash and Windows for configuration tips.
Solution 1: `ssh sleep'
#!/bin/bash -eu
# Archive wiki
dumpfile=wiki_mysqldump.sql.gz
# Cd to this script's dir
cd "$(dirname "$(which "$0")")"
# Remove possible dumpfile
[ -f $dumpfile ] && rm $dumpfile
# Set up SSH tunnel. Options:
# -f Background ssh
# -L Forward local port `3307' to remote port `localhost:3306'
# NOTE: Remote command `sleep 9' will auto-exit tunnel if not activated within 9 seconds
ssh -f -L 3307:localhost:3306 ssh_username@ssh_hostname sleep 9
# Dump wiki database via ssh tunnel
mysqldump --password=mypassword --host=127.0.0.1 --port=3307 --user=mysql_username --verbose wikidb | gzip -9 > $dumpfile 2>&1
Solution 2: `ssh kill'
#!/bin/bash -u
# Archive wiki
# Trap non-normal exit signals: 1/HUP, 2/INT, 3/QUIT, 15/TERM, ERR
# NOTE1: - 9/KILL cannot be trapped.
# - 0/EXIT isn't trapped because:
# - with ERR trap defined, trap would be called twice on error
# - with ERR trap defined, syntax errors exit with status 0, not 2
# NOTE2: trap ERR' has the same effect as 'set -o errexit' or 'set -e':
# simple commands exitting with a non-zero status, cause 'exit'
trap 'cleanexit $?' 1 2 3 15 ERR
#--- cleanexit() -----------------------------------------------------------
# Wrapper around 'exit' to cleanup on exit.
# @param $1 integer (optional) Exit status. Default is 0 (success).
function cleanexit() {
exit_status=${1:-0}
# Was SSH tunnel opened?
if [ "$cmd_ssh_tunnel" ]; then
# Yes, SSH tunnel was opened;
# Find tunnel process and kill it
ps ax | grep "$cmd_ssh_tunnel" | awk '{print $1}' | xargs -i kill {} 2&>/dev/null
fi
exit $exit_status
} # cleanexit()
# Set up SSH tunnel to MySQL on remote server.
# @NOTE1: The 'ssh -fNL ...' command will launch the tunnel in the
# background as a separate process. $! is set by the shell as the
# PID of the ssh command - not the tunnel - so when it comes time
# to shutdown the tunnel you have to figure out the pid from ps.
# @NOTE2: I use local port 3307 for the tunnel, because I already have 3306
# occupied with a local MySQL server.
cmd_ssh_tunnel='ssh -f -N ssh_username@ssh_hostname -L 3307:localhost:3306'
if $cmd_ssh_tunnel; then
# Dump wiki database
mysqldump --password=mypassword --host=127.0.0.1 --port=3307 --user=mysql_username --verbose dbname > wikidb.sql
fi
# Close ssh tunnel
cleanexit 0
See also: Bash: Error handling
Restore
To restore the database:
mysql --user=mysql_username --password=mypassword dbname < wikidb.sql
See also
- MediaWiki Upgrade Procedure
- George Notaras' personal small checklist, to follow when upgrading MediaWiki
- Auto-closing SSH tunnels
- Auto-close SSH tunnel via sleep
Journal
20070401
Alternative, better solution with `ssh sleep'.
20060628
Improved script by letting `trap' pass variable $? to `cleanexit'.
20060526
Improved script by trapping signals.
20060131
Found a problem description at Peter Williams’ Weblog.
