1

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

1
  • 1
    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, 2010 at 8:17

4 Answers 4

1

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.

1

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.

0

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.

-1

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

You must log in to answer this question.