How do I migrate a running database to a new instance?

Answer

Follow the steps below to transfer the database from host A to host B.  Important:  These instructions assume that the base MySQL database directory is /mnt/mysql.  Change your directory structure accordingly.

1. Stop B's mysql server:
   service mysqld stop
    
2. Delete or move B's DB directory:
   mv /mnt/mysql /mnt/mysql.old
    
3. Create a new dir for B:
   mkdir /mnt/mysql
   chown mysql:mysql /mnt/mysql
    
4. Stop A's mysql server:
   service mysqld stop
    
5. Tar up A's complete mysql dir:
   cd /mnt/mysql
   tar cf /mnt/mydb.tar
    
6. Copy the tar from A to B:
   scp /mnt/mydb.tar root@B:/mnt/
    
7. Untar the DB at the root in B:
   cd /mnt/mysql
   tar xf /mnt/mydb.tar
    
8. Start B's db:
   sevice mysqld start

Done!

NOTE: There's a MySQL bug that sometimes appears if it has to do log replay at startup.  To avoid this problem, it's best if you restart B's MySQL again.  That is, perform a start (step 8) and then do a restart.