Manually transfer MySQL databases

Solution 1:

I figured it out!

For posterity, here's what I did:

1. I killed the MySQL server
2. I copied /usr/local/mysql/support-files/my-small.cnf to /etc/my.cnf

3. I opened my.cnf and added this line in the [mysqld] section:

   # point the datadir to the old stuff on the external hard drive
   datadir = /Volumes/Clone/usr/local/mysql/data
   

4. I saved and closed my.conf
5. I restarted the MySQL server. The MySQL pane in System Preferences had a warning about how "/Volumes/Clone/usr/local/mysql/data" didn't have the proper ownership, but I figured this was OK to ignore. It was also reassuring to see that MySQL was looking in the right place for data.
6. I opened PHPMyAdmin and logged in.
7. I exported the databases I cared about as .sql.gz files
8. I re-killed the MySQL server and deleted the /etc/my.cnf file
9. I re-started the MySQL server and logged in to PHPMyAdmin
10. I imported my databases, and everything is looking great!

Solution 2:

First thing you should do is this:

chown -R mysql:mysql /usr/local/mysql/data

You should find out how big the InnoDB files are

• ibdata1
• ib_logfile0
• ib_logfile1

If ibdata1 is < 20M, and ib_logfile0 = 5M, then mysql was running without a my.cnf in place. You should be able to startup mysql. If they are different sizes, you need to create my.cnf with the following

[mysqld]
innodb_log_file_size=???? (file of ib_logfile0 in M)

I hope this is a good start