Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am using mysqldump for a large database (several GB) and import the result from a pipe, please see commands below, does it do incremental pipe, or wait until the first one finishes then import? is this a good way of importing large db across servers? I know you can export gz it, then pscp it then import. Quick alternative are welcome

mysqldump -u root -ppass -q mydatabase | mysql -u root -ppass --host=xxx.xx.xxx.xx --port=3306 -C mydatabase

share|improve this question

migrated from stackoverflow.com Jan 11 '10 at 8:42

This question came from our site for professional and enthusiast programmers.

    
several GB is not "large" .. the optimal approach taken depends very much on DB size, available CPU power (for compression and de-compression) bandwidth and transmission cost. –  lexu Jan 11 '10 at 8:17

4 Answers 4

It does it "incrementally"*. Specifically, the second process will execute a mysql statement as soon as it sees it and the first will send a mysql statement each time it encounters a newline \n character.

* depending on how you define incremental.

share|improve this answer

mysqldump outputs SQL commands that drop tables (if they exist), create them, and then populate the tables one row at a time. Each statement is executed as soon as it is read; this includes updating indexes appropriately after each statement and doing any logging you have set up. It's extremely robust and portable, and thus wonderful for backup, but it's about as inefficient a transfer mechanism as you could come up with.

If you're coping the entire set of databases, you can short-circuit this process by simply copying the database files (usually found in /var/lib/mysql). The binary format for the more popular storage engines is portable across architectures, making a file copy relatively robust. However, make sure you take steps to keep the data from changing as you copy, either by locking the tables or by simply shutting down MySQL until the copy (or archiving) process is done.

Note that MyISAM stores all of its files within the schema's subdirectory, so /var/lib/mysql/mydb/ will contain everything you need for the schema ("database") called mydb. Just copy that subdir to the other server and you're done. However, InnoDB tables by default store their data in /var/lib/mysql/ibdata* and /var/lib/mysql/ib_logfile*, sharing those files between all the other various InnoDB tables on the server. So if you're using InnoDB, mysqldump is your only really safe option, unless you're transferring everything.

share|improve this answer

If you want to import a compressed database you can use this command.

zcat mydatabase.sql.gz | mysql -u root -ppass --host=xxx.xx.xxx.xx --port=3306 -C mydatabase
share|improve this answer

To quickly copy database to remote host over ssh you can use:

mysqldump | gzip | ssh remotehost sh -c 'gunzip | mysql'

Also it is generally bad practice to specify passwords directly on command line.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.