Automatic backup content from wordpress to another mirror site

This step only apply if you’re using linux-based webhosting service.

1. Create a file to be run periodically in cpanel.
(/home/user/bin/blogdbbackup.sh)
the content of file is such as below :

#!/bin/sh
# filename
t=date "+%A"
username=backupuser
password=backuppass
database=blog_db
# dump the sql table
mysqldump -u$username -p$password --opt $database > /home/user/dbbackup/$(date "+%A").sql
# make a tarball
gzip -f /home/user/dbbackup/$(date "+%A").sql
# grab the size of file and send email
du -h /home/user/dbbackup/$(date "+%A").sql.gz >> /home/user/dbbackup/$(date "+%A").msg.txt
mail -s "Blog DB Backup $(date +%m%d%Y)" youremail@address.com < /home/user/dbbackup/$(date "+%A").msg.txt
exit $?

and cronjob entry to have it run at 01.00 AM everday.. is as below:


* 1 * * * /home/user/bin/blogdbbackup.sh


now need to grab that everyday off to the remote site.

2. Create a FTP user that just to access that particular folder for backup purpose..

3.at the backup site.. Create a fetcher script at the other end of the server to grab the new file every day or whatever desired period.
(backup server) .. something like this.
(/home/namran/.bin/today_sync)

#!/bin/sh
remote_host=blog.namran.net
local_dbname=localdb
local_dbuser=localuser
local_dbpass=localpass
local_dir=/home/namran/src/blog_namran_net/sql
cd $local_dir
cat f.ftp > fnow.ftp
DAY=`date +%A`
echo "get $DAY.sql.gz" >> fnow.ftp
echo "bye" >> fnow.ftp
ftp -i -n $remote_host < fnow.ftp
echo "# DONE ftp"
echo "# Importing MySQL record..."
# import new data into current database.
gunzip -f $DAY.sql.gz
mysql $local_dbname -u$localuser -p$localpass < $DAY.sql
echo "# Updating SQL record to fit new hostname.."
# fix to current hostname
mysql $local_dbname -u$localuser -p$localpass  < update_sql.sql
# go get all the images file from the HTTP.
echo "# Done..with mySQL.. fetching images file from uploads.."
cd /home/namran/src/blog_namran_net/docroot/blog/wp-content/uploads/2009
lftp -e "mirror -n && exit " http://blog.namran.net/blog/wp-content/uploads/2009
# DOne..
echo "########## COmpleted ##########"

the content of "f.ftp" is as below :

quote USER username@namran.net
quote PASS userpassword
bin
prompt off
cd /
ls


and the content of "/home/namran/src/blog_namran_net/sql/update_sql.sql" is as below.

UPDATE wp_options SET option_value = replace(option_value, 'http://blog.namran.net', 'http://bc.namran.net') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://blog.namran.net','http://bc.namran.net');
UPDATE wp_posts SET post_content = replace(post_content, 'http://blog.namran.net', 'http://bc.namran.net');

this script is mainly to replace all the references from old hostname
to the new one.. if you just to do backup not mirroring.. then can ignore this part.

then ..create crontab entry for it..something like this ..
if want to have it running at 4.30 AM.

30 4 * * * /home/namran/.bin/today_sync > /dev/null


.. verify it.. after done.. by accessing the backup site.
it should already downloaded all the images and updated the MySQL
record accordingly..
[slider title="Backup Server for Blog.Namran.net"]
as what can be seen at http://bc.namran.net
[/slider]

that's all..

p/s : I even had dropped my database at the hosting.. due to panic after seeing a blank page at blog... not enough of that..
also removed the whole blog content from fantastico via cPanel X ..

only after a while managed to realized what was actually wrong..
Do not loop mysqldump with lock.. for every minute...~

else no page can be displayed.. and to make it worst..
"useronline" gadget tend to write to database.. each time user visit..
so .. virtually service "down" for about 1 and half hours ..!!
first few attempt to restore somehow got problem this..

Got "MySQL berkata : @0006 - MySQL server has gone away "

Luckily got it restored from backup without any data lost... hehehe 8-)