MySQL Server won't accept remote connections

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

46 Replies - 4140 Views - Last Post: 24 December 2013 - 06:20 PM

#1 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

MySQL Server won't accept remote connections

Posted 19 December 2013 - 06:09 PM

I'm using the C++ connector and one of the example code that goes along with it and it wont allow external connections to connect to my database when i change the host address from localhost to my computer's address where the server is running.

This connects perfectly without any problems:
con = driver->connect("tcp://127.0.0.1:3306", "Shawn", "my_pass");



But obviously this wont work when i change computers, so i set the host address to my computer where the server is running like this:
con = driver->connect("tcp://108.xxx.xxx.xxx:3306", "Shawn", "my_pass");


*x's stand for numbers to hide my IP

Why won't it connect?

I allowed Norton 360(takes over windows firewall) to accept any connections from my program.

I gave the user "Shawn" the privledges necessary to accept all hosts:
Posted Image
I get this mysql error:
Posted Image

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL Server won't accept remote connections

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,581
  • Joined: 12-June 08

Re: MySQL Server won't accept remote connections

Posted 19 December 2013 - 07:39 PM

Typically you do not connect directly to the server.. but use some sort of web service to interface with it... I mean.. it would be super duper unwise to have a db just sitting, door open, to the web.
Was This Post Helpful? 0
  • +
  • -

#3 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 19 December 2013 - 08:39 PM

But for educational purposes let's say, how would i solve this problem?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,581
  • Joined: 12-June 08

Re: MySQL Server won't accept remote connections

Posted 19 December 2013 - 08:44 PM

Again.. you would need a webservice or something to interface between your calling source and the database. Period.
Was This Post Helpful? 0
  • +
  • -

#5 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 19 December 2013 - 09:19 PM

Maybe I asked my question wrong.

I'm trying to remotely access my MySQL database? If you're trying to say this is impossible because you have to use a webservice(the safer route), then you're wrong.

MySQL provides multiple examples on accessing databases locally(mysql examples), which is what we're talking about, only remotely instead.

This, upon the multiple articles that talk about how to get mysql to accept remote connection(article#1) with no mention in any article of a "webservice" being a requirement to do this.

However, these links have still not helped me, so i came here.

Please elaborate on your definition of a webservice as it relates to being a requirement for remote mysql connections.

This post has been edited by Java Student: 19 December 2013 - 09:24 PM

Was This Post Helpful? 1
  • +
  • -

#6 zakhrov  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 23-October 13

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 12:08 AM

The trick is to edit the my.ini file in your server's installation directory. By default MySQL binds the server to the local address (127.0.0.1) You need to change the bind-address property from 127.0.0.1 to your global or local ip address if only you or a specific client needs to access it or you could set it to 0.0.0.0 to allow you to connect to it from any client computer.

View Postzakhrov, on 20 December 2013 - 12:35 PM, said:

The trick is to edit the my.ini file in your server's installation directory. By default MySQL binds the server to the local address (127.0.0.1) You need to change the bind-address property from 127.0.0.1 to your global or local ip address if only you or a specific client needs to access it or you could set it to 0.0.0.0 to allow you to connect to it from any client computer.


I forgot to mention that you should do this ONLY for a small project or for education. This method is not recommended for a full fleged commercial application. For that you will need a web service like it has been advised
Was This Post Helpful? 1
  • +
  • -

#7 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 12:52 AM

Thanks for your response Zak.

In the my.ini configuration file there is no mention of the bind-address property, only this :

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# The Pipe the MySQL Server will use
# socket=mysql

# The TCP/IP Port the MySQL Server will listen on
port=3306

...
more settings...



And when i start my server it tells me this:
Server hostname (bind-address): '*'; port: 3306



So i think by default it listens to all hosts, even though every website i visit says it doesn't by default.

And if i add the bind-address(bind-address=0.0.0.0) property under [mysqld] in the my.ini, then start the server, it gives me:
Server hostname (bind-address): '0.0.0.0'; port: 3306



And neither of these will accept my "tcp://108.xxx.xxx.xxx:3306" connection, even when the mysql error code:10060 it gives me specifically relates to mysql server's tcp/ip bindings :sleepy:

Does the MySQL server have a different IP address than my computer's and that's why it's not connecting?

This post has been edited by Java Student: 20 December 2013 - 12:57 AM

Was This Post Helpful? 0
  • +
  • -

#8 zakhrov  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 23-October 13

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 01:08 AM

OK in addition to the bind address you will need to grant acess to all hosts to your MySql login and password.
use this:
[
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY "PASSWORD"; 
FLUSH PRIVILEGES;


If you are using PHPMyAdmin or the MySql workbench to manage the database they will have proper settings for adding specific hosts

Oh and after you make the changes you will need to restart the mysql server instance
Was This Post Helpful? 0
  • +
  • -

#9 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 01:21 AM

Ok, i executed those query and restarted, but the same rejected connection unfrotunately. I believe i gave all priviledges to my user "Shawn", but i tried to connect with your "USERNAME" as well.

this is my mysql.user table with privilges outcome after your queries that shows users Shawn and USERNAME have the host="%":

Posted Image

This post has been edited by Java Student: 20 December 2013 - 01:21 AM

Was This Post Helpful? 0
  • +
  • -

#10 zakhrov  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 23-October 13

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 02:09 AM

try connecting using your local (as in private) IP address. You know the IP adress that your router assigns something like 192.168.x.x
One question. Are you using a web server like apache as well? I think that is what modi meant.
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 02:20 AM

You are using the MySQL CLI there. Are you doing this one the server, or are you connecting to the server from the local computer that is failing to connect via the code?

What is the output of these commands? They show the current loaded config for the running server.
SHOW VARIABLES LIKE '%bind_address%';
SHOW VARIABLES LIKE '%skip_networking%';



View Postzakhrov, on 20 December 2013 - 08:08 AM, said:

Oh and after you make the changes you will need to restart the mysql server instance

Only after the config changes though. Changes to MySQL user privileges don't require a restart. The FLUSH shouldn't even be necessary after a GRANT command, although it doesn't hurt either.
Was This Post Helpful? 0
  • +
  • -

#12 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 573
  • View blog
  • Posts: 2,560
  • Joined: 03-December 12

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 02:45 AM

If you created the database, did you explicitly allow for remote connections?
Was This Post Helpful? 0
  • +
  • -

#13 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 02:46 AM

View PostAtli, on 20 December 2013 - 02:20 AM, said:

You are using the MySQL CLI there. Are you doing this one the server, or are you connecting to the server from the local computer that is failing to connect via the code?

What is the output of these commands? They show the current loaded config for the running server.
SHOW VARIABLES LIKE '%bind_address%';
SHOW VARIABLES LIKE '%skip_networking%';



View Postzakhrov, on 20 December 2013 - 08:08 AM, said:

Oh and after you make the changes you will need to restart the mysql server instance

Only after the config changes though. Changes to MySQL user privileges don't require a restart. The FLUSH shouldn't even be necessary after a GRANT command, although it doesn't hurt either.


I'm not quite sure what MySQL CLI is. I'm connecting to the server from the same computer that the server is run on, except i'm not useing "localhost" as the host, i'm useing the computer's IP and Port # for a host because im trying to act as a remote connection for when i run this program on a different computer.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%bind_address%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%skip_networking%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>



View Postzakhrov, on 20 December 2013 - 02:09 AM, said:

try connecting using your local (as in private) IP address. You know the IP adress that your router assigns something like 192.168.x.x
One question. Are you using a web server like apache as well? I think that is what modi meant.


I'm doing that on the this line if that's what you mean(where 108.xxx.xxx.xxx is my router-assigned IP), which is the code that keeps giving me the error because it claims it can't connect to a MySQL server on that IP:port :
con = driver->connect("tcp://108.xxx.xxx.xxx:3306", "USERNAME", "PASSWORD");


I'm not useing Apache, just MySQL with the c++ connector.

View Postastonecipher, on 20 December 2013 - 02:45 AM, said:

If you created the database, did you explicitly allow for remote connections?


I did, i created "test2" database, but did nothing other than "CREATE DATABASE test2;", unless you're talking about privileges.

This post has been edited by Java Student: 20 December 2013 - 02:55 AM

Was This Post Helpful? 0
  • +
  • -

#14 zakhrov  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 23-October 13

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 02:58 AM

The MySQL CLI is the Command Line client that comes with the stock MySQL installer. Why dont you install and use the MySQL workbench provided by Oracle? It makes managing hosts and users simpler (unless you are a command line kind of guy)
Was This Post Helpful? 0
  • +
  • -

#15 Java Student  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 480
  • Joined: 05-February 10

Re: MySQL Server won't accept remote connections

Posted 20 December 2013 - 03:01 AM

View Postzakhrov, on 20 December 2013 - 02:58 AM, said:

The MySQL CLI is the Command Line client that comes with the stock MySQL installer. Why dont you install and use the MySQL workbench provided by Oracle? It makes managing hosts and users simpler (unless you are a command line kind of guy)


Oh no, i use the workbench in conjunction with the command line. The workbench is for reading server stats, stoping and starting server, etc... and the command line is for queries personally.
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »