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 🙂