Installing MySQL on Mac

Despite the popular virtualization solutions like Vagrant, Micro Cloud Foundry and others all of which are perfect solutions for your development environment that would include mysql, there may be reason you need to install Mysql server locally on your machine. This covers steps how to install mysql via macports packaging distribution.

Environment: MySQL 5.5, Mac 10.7

1- Install MacPorts

http://www.macports.org/install.php

2- Update MacPorts

sudo port selfupdate

3- Install MySQL server and client binaries

sudo port install mysql55

4- Install MySQL server startup scripts

sudo port install mysql55-server

5- Initialize the Database:

sudo /opt/local/lib/mysql55/bin/mysql_install_db --user=mysql

6A- Trick to get root user work

sudo chown -R mysql:admin /opt/local/var/db/mysql55
sudo chmod u+rwx,go= /opt/local/var/db/mysql55

6B- Starting MySQL

sudo /opt/local/share/mysql55/support-files/mysql.server start

7- Setting Root password

/opt/local/lib/mysql55/bin/mysqladmin -u root password 'changepasswordhere'

8- Enable the startup script so mysql starts on boot

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

– or —

sudo port load mysql55-server

9-Creating user that can access DB from any IP address

GRANT ALL PRIVILEGES ON *.* TO db_user@'%' IDENTIFIED BY 'db_passwd';
FLUSH PRIVILEGES;

10 – Verify all working

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

11-Reinstalling

If you need to uninstall/reinstall for whatever reason, remove the database directory when you do the uninstall

sudo port uninstall mysql55-server
cd /opt/local/var/db/
sudo rm -rf mysql55/

Troubleshooting

1. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61)
I wasn’t able to connect via client such as MySQL Workbench. Part of troubleshooting was running:

mysql -u makapacs -p -h localhost
mysql -u makapacs -p -h 127.0.0.1

The last, displayed the error ‘ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61)’. Next:

○  telnet localhost 3306
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying fe80::1...
telnet: connect to address fe80::1: Connection refused
telnet: Unable to connect to remote host

Clearly, something doesn’t let connect via TCP connection
Solution:Make sure ‘skip-networking’ is commented out in configuration file(our case /opt/local/etc/mysql55/my.conf) or when starting mysql server, it does’t start with ‘–skip-networking’

In skip-networking mode, the mysql instance doesn’t “listen for TCP/IP connections at all. All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix)” from MySql Docs

2. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
This error was because, mysql server was binded on particular ip as follows (/etc/mysql/my.cnf):

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 173.255.xx.xx


So, when mysql binded on specific ip then it only listens remote connection from localhost or the ip specified. Solutions was to comment out ‘bind-address’ and restart mysql server. Afterwards, any remote connection worked

3. Warning: PDO::__construct() [pdo.–construct]: [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)
This error came out suddenly one day. It cannot find the mysql socket file. To locate

mysqladmin variables | grep 'socket'

This will display where the mysql.socket is created and placed every time mysql starts. Next, create symbolic link so php can find it

sudo ln -s /opt/local/var/run/mysql55/mysqld.sock mysql.sock

Important to note, to find out where Apache looks for mysql.sock, run in browser and look under ‘pdo_mysql’ section

4. Warning: mysql_connect(): No such file or directory
Solved by changing mysql host from ‘localhost’ to ‘127.0.0.1’. The reason for this error is because the “localhost” is a special name for the mysql driver making it use the unix socket to connect to mysql instead of the a tcp socket.

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000]: General error: 2006 MySQL server has gone away’ /includes/database/database.inc

The solution was to add following line in mysql55/my.conf file:

[mysqld]
max_allowed_packet=100M

This gave the necessary extra space for the communication between db and the app

Useful Links:

 

• http://jackal.livejournal.com/2160464.html
• http://stackoverflow.com/questions/4798464/installing-mysql-on-snow-leopard-using-macports
• http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix
• http://dba.stackexchange.com/questions/15004/how-to-initiate-mysql-on-mac-os-x-10-6-8
• http://dev.mysql.com/doc/refman/5.0/en/flush.html
• http://www.razorsql.com/docs/mysql_connection_troubleshooting.html