(re)Installing mySQL on iMac

If the problem wasn't caused by the OSX update, then it's an awful coincidence. I mean, perhaps it was something else and rebooting after the update is what revealed it. I just hope I dont see this again.

The symptom was after restarting my iMac, all my SQL databases disappeared! OUCH! My apps did not run, phpMyAdmin didn't see them. And when I looked in /urs/local/mysql/data/ they weren't there! Finally, i did a "find" on one of the table names and found my stuff inside a data/ dir under the old SQL (4.x) version, not my current 5x version. Whatever. Perhaps the symlinks got messed up somehow. Perhaps something the way i upgraded from 4 to 5 6 months ago.

Long and short of it, I decided to reinstall mySQL. The previous version was 5.0.21, the new one is 5.0.27. Not major, so the binaries should be compatible, eh?

Here's the steps I took

• Backup my databases, I copied the data/ directory I found to a temp location

# sudo cp -rp /PATH/TO/OLD/MYSQL/data ~/

use the -p option to preserve the chmod and chown settings

• Shut down myslqd if its running

Click the mySQL icon in System Preferences, and click Stop the mySQL process

or, from the command line

# mysqladmin -u root -p shutdown

Test that its not running my trying the mysql command (etc)

• Removed the "library receipt" to ensure the package is a fresh install rather than an update.

# cd /Library/Receipts

# rm mysql-[yadda-yadda]

• Download a fresh copy of mySQL package

from http://dev.mysql.com/downloads/mysql/5.0.html#downloads , find the one for OSX x86

the download automatically mounts the package and opens its window.

• Install the mySQL package

Double click on the icon and go through it.

• Install the StartupItem package too

• Start the mysqld process

From the command line:

# sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

• Run a few tests to make sure its running

# mysqladmin version

# mysqladm variables

# mysqlshow

# mysqlshow mysql

• Set a root password

In a fresh install, by default, the root password is blank.

# mysqladmin -u root password NEWPASSWORD

I'm supposed to also do

# mysqladmin -u root -h LOCALHOSTNAME password NEWPASSWORD

but i get an error: 'Host '192.168.1.4' is not allowed to connect to this MySQL server' . I'll have to look at it later, but since I can access this via phpMyAdmin, I just did it in there (maybe I needed to put quotes around my host name or something like that).

by the way, to determine your LOCALHOSTNAME, try this,

mysql> SELECT Host, User FROM mysql.user;

and look for the record that has `root' in the `User' column and something other than `localhost' in the `Host' column.

• Delete anonymous accounts

Not everyone may want to do this, but I dont have any anonymous users or apps connecting to the db on my mac.

shell> mysql -u root
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;
mysql> quit;

• Restore (copy) the old databases

Don't just copy everything from the old data/ directory, just copy each of the db subdir's. So to be safe, do it one at a time.

You can sudo this or do it as root, e.g.

# cp -rp /PATHTO/OLDMYSQL/data/myname /PATHTO/NEWMYSQL/data/