How to create a BZ2 backup of my database without using PhpMyAdmin?

I own a huge site and with that comes a lot of database information, rather than downloading a copy of the database in GZ format, I download it in BZ2, which means that it takes far less time to download it and if the site goes down, I can upload it to the database without resorting to timeouts.

Anyway, recently PhpMyAdmin has a bug that doesn’t allow a huge export and it times out after a while. “This interface does not work with large databases or large tables. Please see phpMyAdmin bug 4046.”

Is there a way that I could export my database to a BZ2 format without using PhpMyAdmin. I have a Linux server with cPanel, my web hosting provider is Arvixe and it looks like there’s not going to be a fix for this bug for quite some time. Any ideas?

I would use add a cron job that runs a BASH script to dump your compressed db file.

Here’s a good script you can customize to do the job for you, just substitute the variables with your data:

=====================================

#!/bin/bash
# -*- coding: UTF-8 -*-
# Script for dumping MySQL base automatically using cron.

# exec test
exec_test () {
[ $? -eq 0 ] && echo “– Command successfully executed” || echo “– Command failed; exit 1”
}

# Filenames
database_name=”test”
backup_folder=”/path/to/backup/folder/”
mydate=`date ‘+%m%d%y’`
mytime=`date ‘+%T %m.%d.%y.’`
filename1=\
“$backup_folder/\
$database_name_\
$mydate.bck.sql”
username=”some”
password=”emos”

# Logging
exec 3>&1 4>&2
trap ‘exec 2>&4 1>&3’ 0 1 2 3
exec 1>>$backup_folder/$database_name.log 2>&1
# Everything below will go to the log file

dump_base () {
echo “###########################”
echo “STARTING on: $mytime”
echo “Base dumping…”
mysqldump -u$username -p$password $database_name > $filename1
}

compress_base () {
echo “Compressing base…”
bzip2 $filename1
# rm filename1
}

dump_base ; exec_test
compress_base ; exec_test

echo “Done, quit!”
=====================================