This step only apply if you’re using linux-based webhosting service.
1. Create a file to be run periodically in cpanel.
the content of file is such as below :
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#!/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)" email@example.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/
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
#!/bin/sh remote_host=namran.com 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://namran.com/blog/wp-content/uploads/2009 # DOne.. echo "########## COmpleted ##########"
the content of “f.ftp” is as below :
quote USER [email protected]
quote PASS userpassword
and the content of “/home/namran/src/blog_namran_net/sql/update_sql.sql” is as below.
1 2 3
UPDATE wp_options SET option_value = replace(option_value, 'https://namran.com', 'http://bc.namran.net') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = replace(guid, 'https://namran.com','http://bc.namran.net'); UPDATE wp_posts SET post_content = replace(post_content, 'https://namran.com', '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
[slider title=”Backup Server for Blog.Namran.net”] as what can be seen at http://bc.namran.net
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..
Luckily got it restored from backup without any data lost… hehehe 😎