Procedure:MySQL Database Transfers

This procedure describes how to transfer data across SQL databases, or more generally from a MySQL DB to another DB (including MySQL)

Step 1 is to get the data out of the original DB. The exact command may depend on what you want; Typical examples spanning most scenarios are:

• "mysqldump -uUSER -pPASS --all-databases > databaseFile" Stores everything in a format suitable for initializing a blank DB, including user tables
• "mysqldump -uUSER -pPASS --databases db1 db2 ... > databaseFile" Like above but only stores the named set of databases db1, db2, ..., dbN.
• "mysqldump -uUSER -pPASS --all-databases --add-drop-table --add-drop-database --add-locks > databaseFile" Will instruct destination system to impose a Pax Romana before resettling the land. Add-locks will lock tables while adding data and should make the upload onto the destination box go faster.

mysqldump HALTS in the event of error and WILL leave you an incomplete dump (not that it'll actually say "don't use this, it's incomplete" - beware)

Step 2: scp databaseFile USER@NEWMACHINE:~/

Step 3 is uploading the database dump file into the new database. The dump file is a bunch of text SQL statements strung together, not a heaving mass of binary data, so in principle you can upload to anything that speaks SQL. On a virgin MySQL, one would probably use "mysql -uroot -p < databaseFile" since by default mysql has no access restrictions. If the original mysql's user DB isn't included in databaseFile you'd best either add users & restrictions now and/or check that usernames/permissions are compatible across the machines. On an existing DB, "mysql -uUSER -pPASS < databaseFile" will perform the upload (assuming that USER has the privileges to do whatever is implied by the original dump, such as create tables/DBs/users and possibly drop them and lock tables.

This all completed successfully, a "/etc/init.d/mysqld restart" will make sure all the new hotness is running, and nothing old and busted is gumming up the works. There's some command to have mysql flush everything online too.

'man mysqldump' and 'man mysql' for more information and many more options if desired.