[KLUG Members] Moving a MySQL database....

[Jamie McCarthy]

mysqldump is what you want.

The -e option will make it a LOT faster to import (harder for
humans to read though).

If you're dumping all your databases, just give it the -A option.
And since you're going to a fresh DB that you can blow away, you'll
probably want --add-drop-table.

It sounds like you can take care of making sure that no clients
will try to alter the data while you're dumping it, but if not,
consider the --single-transaction option, which will guarantee
consistency... but if clients are trying to write at the same time
this may have less than pleasant results.

You'll probably want to dump the 'mysql' database as well as all
your data.  'mysql' contains all the permissions you've granted.

Check your new DB's my.cnf file against the old config file, to
make sure it's configured the way you want.  For example, if
you're using InnoDB tables, your original DB has its InnoDB table
space configured a certain way;  you'll definitely want to
replicate that on the copy machine so that you don't run out of
space during the import.

Pipe the result into 'mysql', the command line client.

Once that completes, do a 'FLUSH PRIVILEGES;' since you changed
your 'mysql' database... and that's it, you're done.

Now, if you have many gigabytes of data and speed is of the
essense, mysqlimport may be faster for you.  Dump tables to disk
with 'mysqldump -T' and it will create an .sql file with the
schema and a .txt file with tab-separated values.  Pipe the .sql
files to 'mysql' to create the tables, and name the .txt files on
the command line of 'mysqlimport' to pull that data into the
tables.  This can be significantly faster than sending raw SQL
commands to 'mysql', even with the extended INSERT syntax
generated by 'mysqldump -e', but it's a bit of a hassle.
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg