migrate/copy mysql databases from one machine to another

I am currently on Mac OS X( Snow leopard ) and have installed mysql using macports. I wanted to copy my databases to another machine Mac OS X ( Lion ) to avoid creating new db’s from fresh. Here are the instructions for the same.

Important: Information about databases is stored in below folder

/opt/local/var/db/mysql5

So basically you need to copy this folder onto your new machine.

1. First backup existing mysql5 folder. ( this is assuming you have already installed mysql using macports )

2. Copy mysql folder to /opt/local/var/db/, for this you need be sudo user.

3. Change owner

chown -R _mysql:_mysql /opt/local/var/db/mysql5

4. Restart your computer  ( I didn’t find the better way ) or may be try restarting mysql.

sudo /opt/local/share/mysql5/mysql/mysql.serve

5. When you copy databases, there is a good change that your INNODB engine might not work. So go to mysql terminal and fire

<mysql> show engines;

If it does not show INNODB or is disabled try following:

cd /opt/local/var/db/mysql5

mv ib_logfile0 ib_logfile0.bak

Basically move all ib_logfile* file to .bak files and then restart mysql server. Now go to mysql terminal and do show engines. INNODB should be enabled.

I think this instructions should work fine on any OS as long as you copy databases to correct location.

This saved me few hours and I hope it helps you too 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.