MediaWiki backup via SSH tunnel

From FVue
Jump to: navigation, search


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


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


        # 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= --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() {
        # 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
    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= --port=3307 --user=mysql_username --verbose dbname > wikidb.sql

    # Close ssh tunnel
cleanexit 0

See also: Bash: Error handling


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



Alternative, better solution with `ssh sleep'.


Improved script by letting `trap' pass variable $? to `cleanexit'.


Improved script by trapping signals.


Found a problem description at Peter Williams’ Weblog.


blog comments powered by Disqus