MySQL DBA - Tips and Techniques Magazine

11 Nov 2014

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

No comments:

Post a comment