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.