Making MySql Backups is a crucial thing and should be done automatically by some routine rather then people doing it by hand. Doing the backup is one thing – securing it is another. Here is a way to dump a database and zip it password protected with 7-zip (http://www.7-zip.org)

You need to install 7-zip first – see docs for more. once installed you should read the command line documentation since there are some important flags you should understand. The following script makes a mysql dump with optional conditions, stores the dump first in a .sql dump file, zips the file 7-zip pass protected, removes the original temporary .sql dump file and writes out/err stream to dump log file.

php

//do backup
$cmd = null;
$where = "";

$cmd[] = 'mysqldump'
$cmd[] = '--opt';
$cmd[] = '--add-drop-table=true';
$cmd[] = '--create-options=true';
$cmd[] = '--single-transaction=true';
$cmd[] = '--no-autocommit=true';
$cmd[] = '--extended-insert=false';
$cmd[] = '--complete-insert=false';
$cmd[] = '--net_buffer_length=5000';
if(!empty($where))
{
	$cmd[] = '--where="'.$where.'"';
}
$cmd[] = '-h '.$your_db_host;
$cmd[] = '-u '.$your_db_user;
$cmd[] = '\'-p'.$your_db_pass.'\'';
$cmd[] = $your_db_name;
$cmd[] = '> '.$your_path_to_sql_dump.'.sql';
$cmd[] = '&& 7za a -t7z -mx9 -p'.$your_7zip_pass.' '.$your_path_to_7z_file.'.7z '.$your_path_to_sql_dump.'.sql';
$cmd[] = '&& rm '.$your_path_to_sql_dump.'.sql';
$cmd[] = '>> '.$your_path_to_log_file.'-dump.log 2>&1';

if(($proc = proc_open(trim(implode(" ", $cmd)), array(0 => array('pipe', 'r'), 1 => array('pipe', 'w'), 2 => array('pipe', 'w')), $pipes)) !== false)
{
	if(($stdout = stream_get_contents($pipes[1])) === false)
	{
		die("unable open stdout stream");
	}
	if(($stderr = stream_get_contents($pipes[2])) === false)
	{
		die("unable open stderr stream");
	}
	if(!@fclose($pipes[1]))
	{
		die("unable to close stdout stream");
	}
	if(!@fclose($pipes[2]))
	{
		die("unable to close stderr stream");
	}
	if(($res = (int)proc_close($proc)) === -1)
	{
		die("unable to close process");
	}
	if(!empty($stderr))
	{
		die("unable to dump backup, code: $res, error: $stderr");
	}
    echo $stdout;
}else{
	die("unable to execute command");
}

?>

In detail: the line:

“7za a -t7z -mx9 -p’.$your_7zip_pass.’ ‘.$your_path_to_7z_file.’.7z ‘.$your_path_to_sql_dump.’.sql’”

tells 7-zip to make an archive using the standard 7z format and the highest compression ratio. The password is set with the -p switch. The -p with no value will prompt your for an password if called from the shell btw. You can tweak around to see what suits you the best. I had problems with the .tgzip format throwing errors on my server. The 7z format seems to be stable.

7-zip is not the only alternative! You can also use TAR and: http://loop-aes.sourceforge.net/aespipe/ to make an archive and pipe the stream directly into aespipe to encrypt the dump AES 256 Bit. Change the line for 7-zip into:

“&& tar -cvf ‘.$your_path_to_tar_file. ‘.tar.aes -C ‘.$path.’ –use-compress-program=/usr/local/bin/bz2aespipe ‘.$file.’.sql”

The -C switch changes directory to the path where the .sql dump file and the tar is located. the “–use-compress-programm” flag will pipe the tar stream into a wrapper for aespipe which i found somethere (look for bz2aespipe). its content is defined as:

#! /bin/sh

# FILE FORMAT
# 10 bytes: constant string 'bz2aespipe'
# 10 bytes: itercountk digits
# 1 byte: '0' = AES128, '1' = AES192, '2' = AES256
# 1 byte: '0' = SHA256, '1' = SHA384, '2' = SHA512, '3' = RMD160
# 24 bytes: random seed string
# remaining bytes are bzip2 compressed and aespipe encrypted

# These definitions are only used when encrypting.
# Decryption will autodetect these definitions from archive.
ENCRYPTION=AES256
HASHFUNC=SHA256
ITERCOUNTK=100
WAITSECONDS=10
PWDFILE=/usr/local/bin/aespipe.pwd

if test x$1 = x-d ; then
    # decrypt
    n=`head -c 10 - | tr -d -c 0-9a-zA-Z`
    if test x${n} != xbz2aespipe ; then
        echo "bz2aespipe: wrong magic - aborted" >/dev/tty
        exit 1
    fi
    itercountk=`head -c 10 - | tr -d -c 0-9`
    if test x${itercountk} = x ; then itercountk=0; fi
    n=`head -c 1 - | tr -d -c 0-9`
    encryption=AES128
    if test x${n} = x1 ; then encryption=AES192; fi
    if test x${n} = x2 ; then encryption=AES256; fi
    n=`head -c 1 - | tr -d -c 0-9`
    hashfunc=SHA256
    if test x${n} = x1 ; then hashfunc=SHA384; fi
    if test x${n} = x2 ; then hashfunc=SHA512; fi
    if test x${n} = x3 ; then hashfunc=RMD160; fi
    seedstr=`head -c 24 - | tr -d -c 0-9a-zA-Z+/`
    aespipe -e ${encryption} -H ${hashfunc} -S "${seedstr}" -C ${itercountk} -d | bzip2 -d -q
else
    # encrypt
    echo -n bz2aespipe
    echo ${ITERCOUNTK} | awk '{printf "%10u", $1;}'
    n=`echo ${ENCRYPTION} | tr -d -c 0-9`
    aesstr=0
    if test x${n} = x192 ; then aesstr=1; fi
    if test x${n} = x256 ; then aesstr=2; fi
    n=`echo ${HASHFUNC} | tr -d -c 0-9`
    hashstr=0
    if test x${n} = x384 ; then hashstr=1; fi
    if test x${n} = x512 ; then hashstr=2; fi
    if test x${n} = x160 ; then hashstr=3; fi
    seedstr=`head -c 18 /dev/urandom | uuencode -m - | head -n 2 | tail -n 1`
    echo -n ${aesstr}${hashstr}${seedstr}
    bzip2 | aespipe -e ${ENCRYPTION} -H ${HASHFUNC} -S ${seedstr} -P ${PWDFILE} -C ${ITERCOUNTK} -T -w ${WAITSECONDS}
fi
exit 0

Note the line “PWDFILE=/usr/local/bin/aespipe.pwd” here you must store your password so your are not asked for one and the encrypting will run automatically. I tried the encrypting and decrypting and had no problems. I can not really say if aespipe runs stable or not because in the end i preferred 7-zip with backups stored on a different server via ssh2/sftp and a jail rooted user account.

Both are valid options and a consideration when wanting to have at least some sort of security.