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
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!
Thanks Kevin for the catching that. I have updated the post
I also wrote a guide for setting up a local mySQL server on OSX and thought I would share with others who may be interested. I have outlined two different ways to install, the more traditional way and a method utilizing the package manager Homebrew.
Setup Local MySQL Server
Thank You Dal for sharing
Thank you so much! Was searching for hours!
I am glad it was useful. Thanks for mentioning
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.
Pingback: Post-It » Blog Archive » Upgrading PHP on Mac and Ubuntu