Migrate a running database to a new instance

If you have live database and it's not running through RightScale's MySQL Manager, you may need to move your DB from one machine to another.

Follow the steps below to transfer the DB from host A to host B.  These instructions assume that the base MySQL db 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.  i.e. Do a start (step 8) and then do a restart.