MySQL on a network

users
#1

I can connect to the MySQL server (5.5) on the same computer using localhost. But how do I connect to a MySQL server on another computer on the same network? I can not find it in the MySQL manual, or I don't know where to look in it.
      I use MySQL Workbench for administrative purposes.

--Dan

#2

Hi Dan,
When you define your MySQL-database under LO-Base, specify the hostname (or the
IP-address) of the server as well as the port (usually 3306) instead of "localhost".
I am actually using a MySQL-server over the internet. It works o.k....
Regards H

#3

More information: My laptop is 192.168.2.101 and the MySQL server is on 192.168.2.100. The database I want to access is Budget.
      I entered Budget as the database name, Server as 192.168.2.100, port is 3306. I then test the connection, enter the user name and password. The error message I get is "Host '192.168.2.101' is not allowed to connect to this MySQL server".
      I am using the MySQL JDBC driver, and when I click the "Test Class" button, I get the message "The JDBC Driver was loaded successfully."
      Any suggestions as to what I might be doing wrong?

--Dan

#4

Dan Lewis wrote:

       I can connect to the MySQL server (5.5) on the same computer
using localhost. But how do I connect to a MySQL server on another
computer on the same network? I can not find it in the MySQL manual, or
I don't know where to look in it.
      I use MySQL Workbench for administrative purposes.

--Dan

Hi Dan,
When you define your MySQL-database under LO-Base, specify the hostname (or the
IP-address) of the server as well as the port (usually 3306) instead of "localhost".
I am actually using a MySQL-server over the internet. It works o.k....
Regards H

     More information: My laptop is 192.168.2.101 and the MySQL server is on 192.168.2.100. The database I want to access is Budget.
     I entered Budget as the database name, Server as 192.168.2.100, port is 3306. I then test the connection, enter the user name and password. The error message I get is "Host '192.168.2.101' is not allowed to connect to this MySQL server".
     I am using the MySQL JDBC driver, and when I click the "Test Class" button, I get the message "The JDBC Driver was loaded successfully."
     Any suggestions as to what I might be doing wrong?

--Dan

Sounds like the server is not configured to allow you access. Since the IP addresses for the server and your computer are on the same LAN, I can assume the MySQL server is on another computer on the same LAN. You don't say if you did this in mysql:

    GRANT ALL PRIVILEGES ON *.* TO <username>@hostname IDENTIFIED BY `<password>` WITH GRANT OPTION;

Note that the "hostname" should be the IP address of your laptop, or 192.168.2.101 . Note that the IP address may need " around it due to the periods and numbers.
If not, the server will not allow you access. The laptop is not "registered" as a trusted computer. You must have done this for your other computer, but you need to do it for all computers you need access for. Or, if you are brave, you can use "%" for any host: <username>@"%". Or, better yet, you can limit access to your LAN: <username>@"192.168.2.%"

For further information, this can be found in the MySQL reference manual under "Access Control, Stage 1: Connection Verification".

Hope this helps.
Girvin Herr

#5

Hello again,
The IP-address has to be that of the SERVER, and - of course as Girvin says -
access has to be granted to you on the server as mentioned. By the way -
I tend to use single apostrophies (') instead of ", if necessary on the
GRANT statements.
Regards and good luck

#6

Hi all,

The IP-address has to be that of the SERVER, and - of course as Girvin
says -
access has to be granted to you on the server as mentioned. By the way -
I tend to use single apostrophies (') instead of ", if necessary on the
GRANT statements.
Regards and good luck

To add to what Girvin and Heinrich have written, you need to be aware that some Linux distribs forbid remote TCP access by default to a mysql server instance, because it is a potential remote attack point.

Look for the file my.cnf in /etc or /etc/mysql. In it, there should be a line that says "skip networking". Open up the file in your favorite text editor as superuser and comment out this line by putting a # as the very first character. Save and restart your mysql server instance. You should now be able to access your mysql server instance remotely via TCP.

Alex

#7

Heinrich ,

fine, in the past there where some issues about "remote" connections, but how do you manage the "security" of your connection

greetz

Fernand

#8

Hello,
Regarding security,
Well, Only running a server for a wind-band, security is maybe not QUITE
as indispensable as it might be with a large organisation. However, besides
using the capabilities available within the GRANT system, there is always
the additional security of using things such as VPN...
Regards
H.S.

#9

Here is the link to the manual:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-networking

#10

It might be....

'skip-networking' in your config (read:my.cnf) means that MySQL will not listen to any network interface. Yuou will still be able to connect to localhost, because its something 'sepecial'

http://en.wikipedia.org/wiki/Localhost
"The public releases of the MySQL database differentiate between localhost and 127.0.0.1. When using localhost from a client program, say a PHP application, then MySQL connects to the database using a Unix domain socket rather than making a direct TCP connection[7][8]. To ensure a TCP connection to the database in IPv4 then use 127.0.0.1."

#11

Luuk wrote:

       I can connect to the MySQL server (5.5) on the same computer
using localhost. But how do I connect to a MySQL server on another
computer on the same network? I can not find it in the MySQL manual, or
I don't know where to look in it.
      I use MySQL Workbench for administrative purposes.

--Dan

Here is the link to the manual:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-networking

      Thanks for the link, but I already have it in ePUB format. My
problem is trying to determine what the manual means. It is very verbose!

--Dan

It might be....

'skip-networking' in your config (read:my.cnf) means that MySQL will not listen to any network interface. Yuou will still be able to connect to localhost, because its something 'sepecial'

http://en.wikipedia.org/wiki/Localhost
"The public releases of the MySQL database differentiate between localhost and 127.0.0.1. When using localhost from a client program, say a PHP application, then MySQL connects to the database using a Unix domain socket rather than making a direct TCP connection[7][8]. To ensure a TCP connection to the database in IPv4 then use 127.0.0.1."

Umm. Are you sure about that?
I am not an expert, but it has been my experience that if "skip-networking" is enabled, then localhost will not work either. As you say, there are two ways to access the server: the network or the Unix socket. The MySQL programs generally use the socket. However, LO Base and, more specifically, the "connector" driver, use the network interface. When users complain about not being able to connect to MySQL, I suggest commenting out the skip-networking directive and when they do, they are able to connect. That implies that localhost is controlled by skip-networking. localhost may indeed be something special, but I think in this respect, it isn't. If you do an "ifconfig" while root (Linux), you will see that the lo (loopback) interface, which is 127.0.0.1, is listed along with the hardware LAN Ethernet interface(s). So it is treated at the same level as the Ethernet interface in the IP (Internet Protocol) stack. BTW, the last sentence of the wiki you quote doesn't make sense. Under *nix, the name "localhost" is defined as 127.0.0.1 (the lo interface) in the "/etc/hosts" file. Therefor, when localhost is requested, it gets translated to 127.0.0.1 by the computer, not passed on to MySQL as a special case. MySQL should have nothing to do with this translation, so it would never see the name "localhost".
That said, it seems that the "connector" driver could make that translation for MySQL. I don't know the interior details of the connector, but It could get the localhost name from Base and then process it. It could make a special case of localhost and vector subsequent requests to the socket. However, as I said, that has not been my experience.
Just my 2-cents.
Girvin Herr

#12

MySQL server 5.1 has a skip network section. This has been modified in Server 5.5 to this:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

     The reference in the MySQL Reference Manual ( "Access Control, Stage 1: Connection Verification") states what to put in place of 127.0.0.1 for using MySQL over a network. In my case, I can use 192.168.2.% or
192.168.2.0/255.255.255.0.

#13

Dan Lewis wrote:
<snip>

      MySQL server 5.1 has a skip network section. This has been modified in Server 5.5 to this:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

    The reference in the MySQL Reference Manual ( "Access Control, Stage 1: Connection Verification") states what to put in place of 127.0.0.1 for using MySQL over a network. In my case, I can use 192.168.2.% or
192.168.2.0/255.255.255.0.

That's interesting. I am using MySQL 5.0.67, so this does not yet apply to me. However, I will have to make note of this for future reference.
How did you make out? Did you get your laptop talking to the server?
Girvin Herr

#14

Luuk wrote:

       I can connect to the MySQL server (5.5) on the same computer
using localhost. But how do I connect to a MySQL server on another
computer on the same network? I can not find it in the MySQL
manual, or
I don't know where to look in it.
      I use MySQL Workbench for administrative purposes.

--Dan

Here is the link to the manual:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-networking

      Thanks for the link, but I already have it in ePUB format. My
problem is trying to determine what the manual means. It is very
verbose!

--Dan

It might be....

'skip-networking' in your config (read:my.cnf) means that MySQL will
not listen to any network interface. Yuou will still be able to
connect to localhost, because its something 'sepecial'

http://en.wikipedia.org/wiki/Localhost
"The public releases of the MySQL database differentiate between
localhost and 127.0.0.1. When using localhost from a client program,
say a PHP application, then MySQL connects to the database using a
Unix domain socket rather than making a direct TCP connection[7][8].
To ensure a TCP connection to the database in IPv4 then use 127.0.0.1."

Umm. Are you sure about that?
I am not an expert, but it has been my experience that if
"skip-networking" is enabled, then localhost will not work either. As
you say, there are two ways to access the server: the network or the
Unix socket. The MySQL programs generally use the socket. However, LO
Base and, more specifically, the "connector" driver, use the network
interface. When users complain about not being able to connect to
MySQL, I suggest commenting out the skip-networking directive and when
they do, they are able to connect. That implies that localhost is
controlled by skip-networking. localhost may indeed be something
special, but I think in this respect, it isn't. If you do an "ifconfig"
while root (Linux), you will see that the lo (loopback) interface, which
is 127.0.0.1, is listed along with the hardware LAN Ethernet
interface(s). So it is treated at the same level as the Ethernet
interface in the IP (Internet Protocol) stack. BTW, the last sentence of
the wiki you quote doesn't make sense. Under *nix, the name "localhost"
is defined as 127.0.0.1 (the lo interface) in the "/etc/hosts" file.
Therefor, when localhost is requested, it gets translated to 127.0.0.1
by the computer, not passed on to MySQL as a special case. MySQL should
have nothing to do with this translation, so it would never see the name
"localhost".
That said, it seems that the "connector" driver could make that
translation for MySQL. I don't know the interior details of the
connector, but It could get the localhost name from Base and then
process it. It could make a special case of localhost and vector
subsequent requests to the socket. However, as I said, that has not
been my experience.
Just my 2-cents.
Girvin Herr

a special adddition to this:

on my linux box:
opensuse:/home/luuk # mysql -u root -p test -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28-log Source distribution

.....

#15

Luuk wrote:

Luuk wrote:

       I can connect to the MySQL server (5.5) on the same computer
using localhost. But how do I connect to a MySQL server on another
computer on the same network? I can not find it in the MySQL
manual, or
I don't know where to look in it.
      I use MySQL Workbench for administrative purposes.

--Dan

Here is the link to the manual:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-networking

      Thanks for the link, but I already have it in ePUB format. My
problem is trying to determine what the manual means. It is very
verbose!

--Dan

It might be....

'skip-networking' in your config (read:my.cnf) means that MySQL will
not listen to any network interface. Yuou will still be able to
connect to localhost, because its something 'sepecial'

http://en.wikipedia.org/wiki/Localhost
"The public releases of the MySQL database differentiate between
localhost and 127.0.0.1. When using localhost from a client program,
say a PHP application, then MySQL connects to the database using a
Unix domain socket rather than making a direct TCP connection[7][8].
To ensure a TCP connection to the database in IPv4 then use 127.0.0.1."

Umm. Are you sure about that?
I am not an expert, but it has been my experience that if
"skip-networking" is enabled, then localhost will not work either. As
you say, there are two ways to access the server: the network or the
Unix socket. The MySQL programs generally use the socket. However, LO
Base and, more specifically, the "connector" driver, use the network
interface. When users complain about not being able to connect to
MySQL, I suggest commenting out the skip-networking directive and when
they do, they are able to connect. That implies that localhost is
controlled by skip-networking. localhost may indeed be something
special, but I think in this respect, it isn't. If you do an "ifconfig"
while root (Linux), you will see that the lo (loopback) interface, which
is 127.0.0.1, is listed along with the hardware LAN Ethernet
interface(s). So it is treated at the same level as the Ethernet
interface in the IP (Internet Protocol) stack. BTW, the last sentence of
the wiki you quote doesn't make sense. Under *nix, the name "localhost"
is defined as 127.0.0.1 (the lo interface) in the "/etc/hosts" file.
Therefor, when localhost is requested, it gets translated to 127.0.0.1
by the computer, not passed on to MySQL as a special case. MySQL should
have nothing to do with this translation, so it would never see the name
"localhost".
That said, it seems that the "connector" driver could make that
translation for MySQL. I don't know the interior details of the
connector, but It could get the localhost name from Base and then
process it. It could make a special case of localhost and vector
subsequent requests to the socket. However, as I said, that has not
been my experience.
Just my 2-cents.
Girvin Herr

a special adddition to this:

on my linux box:
opensuse:/home/luuk # mysql -u root -p test -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28-log Source distribution

.....
> \s
--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 6.2
......
Server version: 5.5.28-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
.....
>

and:
opensuse:/home/luuk # mysql -u root -p test -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.28-log Source distribution

......
> \s
--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 6.2

.....
Server version: 5.5.28-log Source distribution
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
.....
>

notice the differences in the 'Connection:'-line.........

and, of course, i DO have the line:
127.0.0.1 localhost
in my /etc/hosts file

Luuk,
Yes, I stand corrected. I have and was thinking of MySQL 5.0. I now understand that 5.5+ is different. I have already made note of this for future reference. MySQL 5.5 is using a way to circumvent the normal translation. Interesting.
Thanks.
Girvin Herr

#16

Update and corrections about what I wrote earlier:

       "MySQL server 5.1 has a skip network section. This has been modified in Server 5.5 to this in 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 = 127.0.0.1

     The reference in the MySQL Reference Manual ( "Access Control, Stage 1: Connection Verification") states what to put in place of 127.0.0.1 for using MySQL over a network. In my case, I can use 192.168.2.% or 192.168.2.0/255.255.255.0."

        What I have found from a web search is how to connect to my MySQL server 5.5: modify the above section in the my.cnf file by adding a #.

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

I have done this. I also added a user to my host 192.168.2.101 (my IP on my laptop). After restarting my MySQL server, I connected to the server on my tower. YAH!!!

--Dan