MacPorts mysql with GUI tools

A friend had some problems installing MySql with MacPorts and asked me if I had problems installing it.  Our problems were totally unrelated but thinking back, I remembered I had problem with MySQL Query Browser.  I couldn’t get it to connect even though I could connect via the command line.  It turned out to just have the wrong path for the socket file.

Click the triangle next to the “More Options” label on the connection string an change the “Connect Using Socket” field to the path of your socket file.

To determine where your socket file is, type:

netstat -ln | grep mysql

Uninstall MySQL on Mac

Somehow, I either forgot my password, or I did something to mess up my MySQL instance.  it is a development machine, so I don’t really care about the databases on it.  I just wanted to remove it completely from my machine.  I actually had two copies of mysql installed.  One from Mac Ports and the other was the package directly from MySQL for the Mac.
I used the following commands to completely remove mysql from my system.

sudo port uninstall mysql5-server
sudo port uninstall mysql5

sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

edit /etc/hostconfig and remove the line MYSQLCOM=-YES-

rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*

Thanks to Rob Allen.

Determine mysql socket

netstat -ln | grep mysql

Install MySql5 on Snow Leopard using MacPorts

Install MacPorts before you do anything.

Install MySQL5

sudo port install mysql5
sudo port install mysql5-server 
sudo -u mysql mysql_install_db5

Secure MySQL5

To secure your MySQL5 installation, you can either change the password manually like so:

/opt/local/lib/mysql5/bin/mysqladmin -u root password {new-password}

or on a production system, you can run the secure script and not only change the password, but remove anonymous access and prevent remote root login.

/opt/local/lib/mysql5/bin/mysql_secure_installation

To start the MySQL daemon, type:

sudo /opt/local/lib/mysql5/bin/mysqld_safe &

or to make sure MySQL5 is launched at startup every time:

sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

Create a symbolic link

sudo ln -s /opt/local/bin/mysql5 /opt/local/bin/mysql

Test the installation

mysql -u root -p

It should prompt you for a password and you should see the “mysql>” prompt

Login failed after restoring database on SQL Server 2008

After restoring a production database backup on SQL Server 2008, to my development machine, I got a “Login failed for user xyz”. The database user was not associated with a user and was marked as “Without login”. This happens when you do not have a login for the database user.  You can create that user but the SIDs do not match.  SQL Server provides a stored procedure to check for this scenario.

sp_change_users_login ‘report’

To correct it, use the same stored procedure with a different command.

sp_change_users_login ‘update_one’, ‘{username}’, ‘{login}’

This will make sure that your username and login have the same SID.