Can't connect to remote MySQL server with error 61

Question

When I tried to connect the remote MySQL server by using command line mysql -h <remote-ip> -u any_existing_users -p or any other mysql client such as phpmyadmin, it didn't work and the error hint was

ERROR 2003 (HY000) Can't connect to MySQL server on '<remote-ip>' (61)

But, when I tried ssh <remote-ip> and connected the MySQL locally by mysql -u root -p, there is no problem.

Here is part of the user table(SELECT User, Host FROM mysql.user;):

+------------------+----------------+
| User             | Host           |
+------------------+----------------+
| root             | %              |
| other_users      | <remote-ip>    |
| root             | localhost      |
+------------------+----------------+

Here is the iptable:

Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination         

Chain RH-Firewall-1-INPUT (2 references)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255 
3    ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0           
4    ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0           
5    ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353 
6    ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631 
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631 
8    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
9    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
10   REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 
11   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:3306

So, what's the problem?

Please check my post here. It might help you: http://stackoverflow.com/a/22637763/676508 — Yoosaf Abdulla, Mar 25 '14 at 14:48

score 83 · Accepted Answer · answered Apr 23 '13 at 06:07

83

Check if your mysql server is listening on a socket with netstat:

netstat -tulpen

and search for 3306.

If not or if only on localhost, check my.cnf and search the bind-address line and change it to:

bind-address = 0.0.0.0

then restart the server and try again.

answered Apr 23 '13 at 06:07

Kaffee

1,563
11
21

A line shows ```tcp 0 0 :::3306 :::* LISTEN 100 1```. What does ```:::``` mean here? Does that mean mysql binds on an IPv6 address? – Wenhao Ji Apr 23 '13 at 06:16
Seems like it, but I don't know cent os to be sure. Did you try to connect via ip on localhost? Connect via ssh and do "mysql -u user -p -h 127.0.0.1" or the public ip address of your server and test if you can connect. A simple "mysql -u user -p" could also connect through a socket file. – Kaffee Apr 23 '13 at 06:22
```mysql -u user -p -h 127.0.0.1```, ```mysql -u user -p -h 0.0.0.0``` and ```mysql -u user -p -h ``` all work fine.@Kaffee – Wenhao Ji Apr 23 '13 at 06:28
yeah ok so mysql server seems to be fine. now its either a firewall on your host, client or in the network. – Kaffee Apr 23 '13 at 06:30
Yes, it is. The network supports ipv6 yesterday, and the ```iptable``` just accepts request via ipv4 address. And the problem is solved. Thank you. – Wenhao Ji Apr 23 '13 at 06:59
bind-address = [your server's public IP address, or an IP address that your remote client can access via a private network] – Joe Hyde Aug 02 '13 at 17:25
bind-address = 0.0.0.0 works too. What does 0.0.0.0 do? Does it act as a wildcard for allowing the mysql server to bind to ANY ip address? Are there advantages to either, security or otherwise? – Joe Hyde Aug 02 '13 at 17:58
If you're getting `start: Job failed to start` when changing the bind-address to `0.0.0.0`, try commenting out that line entirely. – Cooper Maruyama Jan 30 '14 at 21:04
If getting blank '.cnf' files, verify the location of the '.cnf' file with: `mysql --help` as shown here: https://devdocs.magento.com/guides/v2.3/install-gde/prereq/mysql_remote.html Then, do the same for `bind-address` as above. – CodeFinity Jul 12 '19 at 02:31
You may also need to add the `[mysqld]` header (in case it's missing) before you add the `bind-address = 0.0.0.0` line. Otherwise you'll get an error like `mysql: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 20.` – Aamnah Nov 23 '21 at 13:15
`netstat -tulpen` does not work on Windows 11, it just displays the options for arguments, am I doing something wrong? – Paul Aug 13 '23 at 15:51

score 32 · Answer 2 · edited Oct 04 '17 at 12:31

32

Check status:

netstat -tulpen

Modify your configuration:

nano /etc/mysql/my.cnf
# Edit:
bind-address = 0.0.0.0

Enter mysql and give privileges:

mysql -umyuser -pmypassword
# Run:
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

Restart mysql

/etc/init.d/mysql restart

edited Oct 04 '17 at 12:31

dǝɥɔS ʇoıןןƎ

1,674
5
19
42

answered Jan 29 '14 at 01:29

Haimei

12,577
3
50
36

3

While accurate, I would be more careful when granting privileges without a warning – Purefan Feb 25 '16 at 23:32
1

this worked for me. but I just had to use sudo for step 2 and step 4 – Srichakradhar Jun 16 '17 at 18:04
1

What can I do, when I don't have data in my.cnf file ? https://stackoverflow.com/questions/56517064/remote-connection-to-mysql-with-public-ip-running-on-ubuntu-18-04-vps-from-pc – Jun 09 '19 at 18:07

ulou · Answer 3 · 2021-04-14T11:28:27.267

32

With MySql 5.7 they changed files so bind-address is now in:

/etc/mysql/mysql.conf.d/mysqld.cnf

instead of:

/etc/mysql/my.cnf

edited Apr 14 '21 at 11:28

answered Oct 09 '16 at 14:35

ulou

5,542
5
37
47

same as above ^ – CybeX Sep 24 '17 at 07:01

score 18 · Answer 4 · answered Apr 12 '14 at 16:35

18

If you run MAMP, do not forget to allow access (mySQL panel, check "Allow network access to MySQL")

answered Apr 12 '14 at 16:35

user3127882

482
5
12

@andyp why do you think so? It looks like a (very short, but still) anwer to me. – John Dvorak Apr 12 '14 at 17:42
@Jan: you're correct, I was too quick to dismiss this as a comment earlier – andyp Apr 12 '14 at 17:44
How to do this equivalent in MAMP 3.4? There is no MySQL panel anymore – baisong Oct 19 '15 at 04:14

score 13 · Answer 5 · answered Oct 25 '15 at 17:30

I had this problem just after installing Centos 7 on a server. I could´t access to it through Mysql Workbench in a remote computer.

The problem was in the firewall configuration. Eventually, the solution came when doing:

sudo firewall-cmd --zone=public --permanent --add-service=mysql

And then, restart the firewall:

sudo systemctl restart firewalld

score 1 · Answer 6 · edited Aug 27 '15 at 04:17

1

It can be a problem related to firewall or try this:

GO to Server Admin -> MySQL -> Settings -> check: Allow Network Connections

Then restart MySQL

edited Aug 27 '15 at 04:17

Raptor

53,206
45
230
366

answered Apr 23 '13 at 05:55

Beth Coder

11
1
5

1

I'm using CentOS, how to allow the network connections for MySQL – Wenhao Ji Apr 23 '13 at 05:58
centos using the `firewall-cmd` on 7.0+ try doing this on command line: `firewall-cmd --zone=public --add-port=3306/tcp` @WenhaoJi make sure you have the `root` or adding `sudo` – Soma Hesk Oct 25 '22 at 06:41

score 1 · Answer 7 · answered Apr 23 '13 at 06:02

1

Take a look at the "Causes of Access-Denied Errors" from MySQL.

http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

Possibile is there a failed configuration on your server or a other program is using the same port? Or is the server bind on "127.0.0.1"? Try changing the the my.cnf file.

answered Apr 23 '13 at 06:02

Zaziki

418
2
12

There is no ```bind-address``` in my.cnf. When I add ```bind-address=```, it doen't work at all. – Wenhao Ji Apr 23 '13 at 06:07
Try giving at the start of the MySQL-Server a specific ip: http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address – Zaziki Apr 23 '13 at 06:27
1

Link rot! Link rot! – tumultous_rooster Nov 20 '18 at 04:44

Richard · Answer 8 · 2020-01-05T07:05:12.993

1

For MacOS:

mysql -uroot -p

mysql> use mysql;

# View user's host
mysql> select 'host' from user where user='root';
# Update host to '%'
mysql> update user set host='%' where user='root';

mysql> flush privileges;
mysql> quit

vim /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
bind-address = 0.0.0.0

# Restart and reconnect MySQL
mysql.server restart
mysql -uroot -h0.0.0.0 -p

edited Jan 05 '20 at 07:05

answered Jan 05 '20 at 06:58

Richard

11
2

Can't connect to remote MySQL server with error 61

8 Answers8

Linked