Can't connect to Remote MySQL Server (10061)

Question

Localhost connection is enabled in MySQL.

But Remote(My laptop) access is disabled

Can't connect to MySQL server on "host" (10061)`.

My port always open 3306.

Here is my config file (/etc/mysql/my.cnf) :

#bind-address 0.0.0.0
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

And MySQL status is :

mysql start/running, process 15204

This is my error message. here `Can't connect to MySQL server on "host" (10061)`. Yes i am connecting mysql server remotely. — Hax0r, Jun 20 '16 at 07:55
Permit to your user for your ip.Take a look `http://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address` — Mohammad Sayeed, Jun 20 '16 at 07:55

score 36 · Accepted Answer · answered Jun 20 '16 at 07:58

36

To allow remote access to MySQL, you have to comment out bind-address (you did) and skip-networking in the configuration file.

Next, you have to make sure the user is allowed remote access. Check your user with this:

SELECT User, Host FROM mysql.user;

If your user here has '127.0.0.1' or 'localhost' listed as host, you don't have remote access.

Change this with:

UPDATE mysql.user SET HOST='%' WHERE User='__here_your_username';

Flush privileges:

FLUSH PRIVILEGES;

The '%' is a wildcard for 'all hosts'.

answered Jun 20 '16 at 07:58

Keugels

790
5
15

3

I am Update 'localhost' to `%`.. and Flush privileges. But still access is disabled – Hax0r Jun 20 '16 at 08:24
1

Did you comment out both bind-address and skip-networking? What error are you getting back when you try to connect with the MySQL server from the terminal (remotely)? mysql -h host -u username -p – Keugels Jun 20 '16 at 09:09
Yes. I am comment out `bind-address` and `skip-networking`. If i Connection Method SSH(On remote MySQL server) enabled.. But My Laptop to request is disabled. Here is my command line : "mysql -u NICKNAME -h HOST -p" then always output "ERROR 2003 (HY000): Can't connect to MySQL server on 'HOST' (111) " Thanks.. – Hax0r Jun 20 '16 at 09:23
Trying `HOST`... telnet: Unable to connect to remote host: Connection refused – Hax0r Jun 20 '16 at 09:31
1

Then your port 3306 is blocked by the firewall. :) – Keugels Jun 20 '16 at 09:34
Really? Blocked by the firewall?? How can i disabled my firewall? I am using ubuntu(`Ubuntu 14.04.4 LTS`) on AWS – Hax0r Jun 20 '16 at 09:36
1

I'm only using Fedora and CentOS myself, so I can't help you with that, but here is a useful link: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-14-04 – Keugels Jun 20 '16 at 10:09
1

Oh, btw.. if you're using AWS, you will need to open the port in the security group as well. That might probably be it: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html – Keugels Jun 20 '16 at 10:11
1

After creating or updating the user, we might have to restart MySQL server if it does not take effect immediately – Sai Manoj Jan 27 '19 at 14:59

score 30 · Answer 2 · edited Jun 01 '23 at 10:47

30

To Allow remote access to MySQL installed on a Ubuntu, you will have to access the file at this location:

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

There, you comment out the following line: bind-address = 127.0.0.1

basically to change: bind-address = 127.0.0.1 to: #bind-address = 127.0.0.1

Now you either restart the computer or just the mySQL service using the follwing command: sudo /etc/init.d/mysql restart

edited Jun 01 '23 at 10:47

Ahmed Ashour

5,179
10
35
56

answered Feb 27 '18 at 19:30

Nesan Mano

1,892
2
26
43

This bind-address may also be found in /etc/mysql/mariadb.conf.d/50-server.cnf. – kloddant Jan 06 '23 at 18:32

score 8 · Answer 3 · answered Oct 14 '19 at 12:54

The following worked for me.

   SELECT User, Host FROM mysql.user;
   UPDATE mysql.user SET HOST='%' WHERE User='root';
   UPDATE mysql.user SET HOST='%' WHERE User='administrator';
   FLUSH PRIVILEGES;

Then,

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

Change

bind-address = 127.0.0.1 to: #bind-address = 127.0.0.1

save the file. reboot server/restart the MySQL

score 5 · Answer 4 · answered May 13 '19 at 05:20

In my case, installed LAMP stack on Oracle VM of Ubuntu 18.04

Here's my updateto mysql config file: /etc/mysql/mysql.conf.d/mysqld.cnf

Before:

bind-address          = 127.0.0.1

After:

# bind-address          = 127.0.0.1
# comment out bind-address to test remote access

Ensure your user can access from remote host sudo mysql -u root -p Enter your password, then issue the command

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

This works for me, good luck.

jadki · Answer 5 · 2020-08-31T02:56:22.367

4

For anyone else who is hosting the MySQL on a Raspberry Pi 3 the file you are looking for is in

etc/mysql/mariadb.conf.d/50-server.cnf

You still need to follow the prior steps like Nesan Mano stated above though by commenting out

bind-address = 127.0.0.1

Hopefully this helps someone else from spending as much as time as I did for what appeared to be a missing line.

edited Aug 31 '20 at 02:56

answered Feb 03 '19 at 01:32

jadki

482
1
8
15

score 4 · Answer 6 · answered Dec 11 '19 at 03:29

4

That bind-address = 127.0.0.1 config option means that your mysql server only accepts connections from the localhost, which is your actual CentOS machine. Make sure to set bind-address = 0.0.0.0

answered Dec 11 '19 at 03:29

emily blunt

63
4

Can't connect to Remote MySQL Server (10061)

6 Answers6