Extracting data from one database into another

Method 1

 

a) dump the database using mysqldump & ftp the file across

b)Here's the simple command to restore the database using the file you dumped in the first step:

mysql -u USER -p DBNAME < dump.sql

 

Method 2

 

The way that is described above is indeed a very sloppy way!

If you have access to the other box from that machine (if they're on

the same network/ on the 'net) you can use the following - this

essentially pipes the output from mysqldump directly into the other

database - very handy indeed - instead of just dumping it into a file

and then manually ftp it to the other box the mysql < .... it in.

The first host is where you want to copy FROM and the second is where

its going TO.

mysqldump --opt --compress --user=USERHERE --password=PWHERE

--host=SOURCE.HOST.HERE SOURCE_DB_NAME | mysql --user=USERHERE

--password=PWHERE --host=TARGET.HOST.HERE -D TARGET_DB_NAME -C

TARGET_DB_NAME