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

 

9 thoughts on “Installing MySQL on Mac

  1. Seems you need to “comment” out the skip-networking setting to get things enabled again on the network. Your post says to “uncomment” it. JFYI, thank you!

  2. I am trying to get MAMP PRO to work and was receiving # 2 error you list
    2. ERROR 2003 (HY000): Can’t connect to MySQL server on ’127.0.0.1′ (111)
    Solutions was to uncomment ‘bind-address’ and restart mysql server.
    To be clear do I uncomment bind-address or comment it out?
    Neither seems to work, but I am working on MAMP PRO and your suggestions are so far the most helpful on the web, which I thank you.
    Do you suggest I ditch MAMP PRO and simply setup localhost directly on my mac?
    Most appreciated.

    • Thank you Louis. I am sorry it is “comment it out”. I have updated the post

      In re MAMP PRO, I am unable to comment since I don’t have experience. I did have all my setup run on local mac for long time till the troubles begun. It went bad one day, 12 month later it was stolen. Either case, I got to wast a lot of time reinstalling and reconfiguring my development to the local machine. Today, I have virtualbox(see post – http://margotskapacs.com/2014/10/virtualbox-gotchas/) that i keep backup. it also gives me chance to version my development environment. If it helps, I am happy to share my virtualbox image, so you don’t have to install/configure servers…send me an email makapacs@hotmail.com in that case.

  3. Pingback: Post-It » Blog Archive » Upgrading PHP on Mac and Ubuntu

Leave a Reply

Your email address will not be published. Required fields are marked *