Last week I’ve got a brand new server to play with ;). The mission was clear (I’m going over there, I’m going to do the mission…) - upgrade, install all essential stuff, prepare and migrate the data from the old machine. So here’s what I’ve done so far (except setting up system accounts, ssh access and all that basic stuff).

mysqldump --add-drop-database --add-drop-table --extended-insert --quick --create-options --log-error=dump_error.log --user=USER --password=PASSWORD --databases database1 database2 database3 | pv | ssh -C login@newserver "mysql --user=USER --password=PASSWORD"

You execute this command on old server, pipe it to pv (for some neat progress bar) and then to the new server. Here description of options I decided to go with (tweak it as you please for your needs):

--add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement.

--add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the –all-databases or –databases option because no CREATE DATABASE statements are written unless one of those options is specified.

--databases Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.

--extended-insert Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

--log-error=file_name Log warnings and errors by appending them to the named file. The default is to do no logging.

--quick This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out (this may be already set as default in your my.cfg file).

--create-options Include all MySQL-specific table options in the CREATE TABLE statements.