Copying a remote database !!!

AIM: To copy a remote database locally and importing all the tables.

FILES REQUIRED: db_sync.sh (bash), alter_dbs.sql (mysql)

FLOW: db_sync.sh calls alter_dbs.sql

REQUIREMENTS: BASH & MYSQL

AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com

COMPANY: As of now Persistent System LTD, www.persistentsys.com

CREATED: Sunday 18 January 2009 07:00:29 IST

LAST CHANGED: Sunday 18 January 2009 07:00:29 IST

VERSION: 1.0

REVISION: ---

EXPLANATION:
I have designed my own website "www.paragkalra.com" using LAMP - LINUX, APACHE, MYSQL & PHP. The database is located remotely. Many times I require a copy of remote database on my local setup. So I have prepared a small project which uses two files - The bash script with the help of mysql script first drops the existing databases, recreates the databases, exports the remote databases and finally imports the latest database dump.

#!/bin/bash
#===============================================================================
#
# FILE: db_sync.sh
#
# USAGE: ./db_sync.sh
#
# DESCRIPTION: To sync the remote database
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: -----
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Sunday 18 January 2009 12:44:49 IST IS
# REVISION: ---
#===============================================================================

#-------------------------------------------------------------------------------
# Storing username, password & logfile in a variable
#-------------------------------------------------------------------------------
myuser=some_user
mypasswd=some_password
mylog=/var/log/projects/db_sync.log


#-------------------------------------------------------------------------------
# Executing a SQL file to create & drop local databases.
#-------------------------------------------------------------------------------
echo "Executing SQL file - `date`" > $mylog
mysql --verbose --user=$myuser --password=$mypasswd --host=localhost < /data/projects/mysql/alter_dbs.sql


echo "Dumping remote mysql databases- `date`" >> $mylog
mysqldump --verbose --user=$myuser --password=$mypasswd --host=www.paragkalra.com some_database > /data/databases/pkc_db_`date +%F`.sql


#-------------------------------------------------------------------------------
# Importing the database
#-------------------------------------------------------------------------------
echo "Importing the database - `date`" >> $mylog
mysql --verbose --user=$myuser --password=$mypasswd some_database < /data/databases/pkc_db_`date +%F`.sql echo "Export - Import of database complete - `date`" >> $mylog




#===============================================================================
#
# FILE: alter_dbs.sql
#
# USAGE: ---
#
# DESCRIPTION: To drop and recreate the databases
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Sunday 18 January 2009 07:00:29 IST IST
# REVISION: ---
#===============================================================================


#-------------------------------------------------------------------------------
# Dropping the databases if they exists
#-------------------------------------------------------------------------------
DROP DATABASE IF EXISTS `some_database `;

#-------------------------------------------------------------------------------
# Creating databases
#-------------------------------------------------------------------------------
CREATE DATABASE `some_database `;

Under Construction

Coming soon...