Friday, July 30, 2010

Auto update Database from one server to another or developement database to live database

The below shell script will take the database backup and uploaded to the svn server. Call this script through cron job so that it will auto execute and send the dbbackup to the svn repo where ever you want. From svn you should auto deploy code to the sever, so run the cron tab in the target server and execute a script which empty the current database and update with this new one. Mostly usefull in while working with drupal, In drupal many things are stored in database instead of files, so that while you developing application, database also updated daily for each module addtion and upgradation etc., So developement database and live database are varies frequently. You can update the files easily using svn but can't update db because we don't know the every change in db while working. In this situation use the below shell script it will update the live database with your development database.

#!/bin/bash
#
# Configuration
#

# Database connection information
dbname="db_name"
dbhost="localhost" # leave as is unless have specific reason to change
dbuser="db_user"
dbpw="db_pass"

# Dump associated information
dump_directory="/path/to/sql/directory" # path to dump dir NO FILE NAME, note end slash
dump_name="dumpname.sql"
logfile=$dump_directory"dumpandcommitt.txt" # name of log file to use

#
# dump db and log on screen and in log file
#
cd $dump_directory
mysqldump --user=$dbuser --password=$dbpw --add-drop-table $dbname > $dump_name
echo " Dumped drupal database, using user:$dbuser; database:$dbname host:$dbhost"
echo " Dumped drupal database, using user:$dbuser; database:$dbname host:$dbhost" >> $logfile

#
# commit the newly dumped db - note you'll need to svn add to repository first before this will work
#
svn commit -m "auto committed db"
echo " Committed new db"
echo " Committed new db" >> $logfile

endtime=`date`
echo "Backup completed $endtime" >> $logfile

Save the above script as db_import_upload.sh
In terminal call as
$ ./db_import_upload.sh

0 comments: