X

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

This will then dump the current mysql record into /home/user/dbbackup/
everyday.

Careful when doing this, as if you got it wrong. It might locked up the mysql record and preventing write to it while backup is performed.

It is not a good practice to run mysqldump over a loop of every 1minute.
Else will ended up, locking up the mysql server from serving the user.

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 " https://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, 'https://blog.namran.net', 'http://bc.namran.net') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'https://blog.namran.net','http://bc.namran.net');
UPDATE wp_posts SET post_content = replace(post_content, 'https://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..
Backup Server for Blog.Namran.net»

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-)


as what can be seen at http://bc.namran.net
Namran Hussin: a soft spoken guy... with exceptional interest in computers and technology. I love to learn new thing and also love to break thing for the sake of learning.. but I do abide to the self-imposed limitation or certain thing such as social thing in life, thing can be done and thing that must be avoided at whatever cost such as drug,illegal tracking, smoke,illicit activity..etc.muahahaha let's share what we had in this short term of the life.! make it worth of the living.~

View Comments (3)

  • I was wondering if I can use this for my wordpress 2.9.2 site. I simply want to mirror/sync/backup all my newly UPLOADED files to a new BACKUP website. (I don't want to wait for the cron job to kick in)

  • I love what you guys are up too. Such clever work and exposure! Keep up the good works guys I've added you guys to blogroll.

Related Post
Leave a Comment