ERROR 2003 (HY000): Can't connect to MySQL server on 15.29.92.100 (61)

Question

I have installed mysql on mac os sierra using the instructions given here

https://gist.github.com/nrollr/a8d156206fa1e53c6cd6

after the installation.

1 I disabled the mac firewall.
2 I did the following:

mysqladmin -v -uroot -pXXXX
mysql -uroot -p
create user 'foo'@'%' identify by 'YYYYY';
flush privileges;

3 restarted mysql service

I can very easily connect to mysql using user foo locally. but when i try to connect remotely. I get the error

ERROR 2003 (HY000): Can't connect to MySQL server on '15.20.95.200' (61)

Searched the internet and there are a million answers to this problem which have been solved by messing around with my.cnf. But on mac OS Sierra. there is no my.cnf file.

I do have a file

/usr/local/Cellar/mysql/5.7.18_1/support-files/mysql.server

I copied this file to /etc/my.cnf and added line

[mysqld] 
bind-address=0.0.0.0

restarted mysql brew services restart mysql

but still i get the same problem.

Edit:: My IP address is not wrong. I can ping the machine remotely. I can use the remote desktop and connect to the machine running mysql using the Same IP address. in fact right now I am connected to the machine using remote desktop and working on mysql locally. using the same login id foo.

its just the mysql command line fails.

https://stackoverflow.com/questions/10757169/mysql-my-cnf-location-on-os-x — Michael, May 09 '17 at 15:08
I have already mentioned that in my thread. the file is present at `/usr/local/Cellar/mysql/5.7.18_1/support-files/mysql.server ` but it doesn't have the settings people play around with. — Knows Not Much, May 09 '17 at 15:29
So copy it as `/etc/my.cnf` and insert a string `bind-address = 0.0.0.0` — Michael, May 09 '17 at 15:33

score 4 · Accepted Answer · answered May 15 '17 at 05:07

After many days of enormous suffering. I finally solved the problem.

On "MAC OSX" you have to look for a file called homebrew.mxcl.mysql.plist

I found this file in /usr/local/Cellar/mysql/5.7.18_1/homebrew.mxcl.mysql.plist

This file will look like

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.mysql</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql/bin/mysqld_safe</string>
    <string>--datadir=/usr/local/var/mysql</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql</string>
</dict>
</plist>

You have to change this file to

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.mysql</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql/bin/mysqld_safe</string>
    <string>--datadir=/usr/local/var/mysql</string>
    <string>--bind-address=0.0.0.0</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql</string>
</dict>
</plist>

Now restart mysql by brew services restart mysql Remote connections work perfectly now.

All the tomfoolery with my.cnf does not work on MAC OSX when you use HOMEBREW to install mysql.

ERROR 2003 (HY000): Can't connect to MySQL server on 15.29.92.100 (61)

1 Answers1