Tuesday, March 30, 2010

Export and import database to and from remote servers through telnet

Hi, If you want to take database backup from one server and import it into another remote server use the below process.

Import command at terminal or dos
mysql -u user_login -p database_name < /path_to_the_file/my_backup.sql

Export command at terminal or dos
mysqldump -u user_login -p database_name > /path_to_the_file/my_backup.sql

Database Backup
connect to remote host with SSH from termail using below command
#ssh user@host.com
After login, crate one folder to store the backup file and make it writeble, then run the beloc ommand to take backup for the whole database
#mysqldump -u dbusername -p dbname > dbbackup.sql
It will ask you the dbpassword

After download ddbackup.sql and transfer to new host where you have to set up the database..

connect to remote host with SSH
#mysql -u dbusername -p dbname -e 'source dbbackup.sql'
It wil ask you the dbpassword.

The other way, which is even cooler, and more secure, assuming you have ssh access on both machines (this, again, is a direct dump-to-import):

mysqldump -ux -px database | ssh me@newhost "mysql -ux -px database"

This will dump the data to stdout, pipe it to ssh @ the newhost, and into mysql. Of course, replace all x's with username's and password.

If you want to keep two databases equal, you can use rsync (if your system supports it) to do it by creating a simple script:

rsync you@yourserver::share/path/to/mysql/data/* /path/on/the/other/server/

that you call with a cron job every 10 minutes/1 hour/1day/...

This way, whenever your database change, rsync will find the difference and would transfer ONLY the bytes that have changed.

Ref: http://www.webhostingtalk.com/archive/index.php/t-226992.html