1

I want to connect to MySQL Server hosted on a Linux Machine from my Windows Machine using Command Prompt with the following command:

prompt>mysql -u root -p -h remote_hostname

Connections, ports, user accounts and credentials are all fine. But it is throwing the below error:

ERROR 1130 (HY000): Host 'my_hostname' is not allowed to connect to this MySQL server

I am wondering is it possible to communicate with Linux Shell from Windows Command Prompt? or is it not at all in the very design of prompt to connect to Linux Shell for any remote operations? Or is it special in case of MySQL.

I observed that I can connect from Linux to Windows but the reverse is not possible when I am trying just like I explained above.

Anybody know the theoretical explanation to this?

2

You state that "Connections, ports, user accounts and credentials are all fine.", but I have my doubts.

Mentioning the exact error from the documentation:

[The error] occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host

Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:

SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';

If that doesn't return a result, you'll need to add that user. If it does return a result, can you add that to your question?

  • Hi DTest, Thanks for the reply! If i create a user for example 'user'@'%' then it should be able to connect to the host MySQL server from any remote machine. I am using the account similar to what i mentioned now with '%' wildcard as the host value. – V R Murthy Korimilli Oct 9 '12 at 14:17
  • What value are you using as the remote_hostname in -h remote_hostname? is it a machine name, or the IP? Highly recommend using IP.. – Derek Downey Oct 9 '12 at 15:11
  • Yes i have tried using the IP address only. But the same result! :( – V R Murthy Korimilli Oct 9 '12 at 15:23
  • Have you connected to Linux Machine from Windows Prompt anytime? Is it possible actually? – V R Murthy Korimilli Oct 9 '12 at 15:24
  • Not personally, but I have to think it's definitely possible and this is just a configuration issue. What is the value of your 'bind-address' option on the linux machine ? – Derek Downey Oct 9 '12 at 15:43
1

5 years late to the party, but the only answer appearing here while accurate left me feeling unsatisfied.

The reason is that the OP in a comment asked " if i have to specify a single ip address to connect from, whats the point in creating a user with wildcard "%"? Am i not restricting the server to allow connections from a specified host against to the given privilege of user that he can connect virtually from anywhere?"

There seems to be some confusion regarding several issues including the purpose of the bind address and the difference between connecting FROM vs connecting TO.

From the docs... "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces."

Simply put, the server binds to one and only one address. The client can only connect where the server is listening for a connection attempt. The client connects TO the server. The server receives connection attempts FROM clients. If the client is connecting FROM client.com, then the server must allow that host - client.com - in the grant tables if the connection is to succeed.

That is why Derek Downey stated:

Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:

SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';

If that doesn't return a result, you'll need to add that user.

So "lo" is an interface and it's the interface associated with localhost/127.0.0.1. localhost is mapped to 127.0.0.1 in /etc/hosts - though some distributions add some new constructs which I won't go into here. eth0 was an ethernet interface at one time, now it's something like enp5s0 (at least on ubuntu). That interface is associated with your other address such as 192.168.0.2 or 101.25.45.107 and is generally associated with your physical Network Interface Card (NIC).

When a client wants to connect, it specifies where it wants to connect TO - what address and port. The server is listening on the bind address and the configured port (3306 by default). Since it is possible for an address to map onto multiple network interfaces, the server can listen on multiple IP addresses that are associated with said interfaces.

Next, there's the grant tables. The grant tables specify which clients are allowed to connect - and that's where you add your client. If only user@localhost is allowed, then remote connections will fail. The OP stated he tried user@% as a test, so that indicates the OP's issue is somewhere else. I'm not a security expert, but I think that's probably safe (secure as the user's password) as long as you don't use root or another privileged user as your "user". Otherwise, someone can beat on your account remotely. Best to specify specific hosts to allow if you're security conscious.

I would guess that you have an anonymous user in your table and your client matches that first. Solution: Delete that user. Use "SELECT User, Host FROM mysql.user" to see what users are in the table. You might be surprised at what you see. Make sure you understand why each row is in the table.

This post is far too long at this point, so look elsewhere to secure your installation. Here's a good starting point though now that it's 5 years later, it is of course a later version than the OP was using: https://dev.mysql.com/doc/refman/5.7/en/default-privileges.html It provides more details on the mentioned anonymous user. Below copied from https://dev.mysql.com/doc/refman/5.7/en/server-options.html

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. To specify an address, use the --bind-address=addr option at server startup, where addr is an IPv4 or IPv6 address or a host name. If addr is a host name, the server resolves the name to an IP address and binds to that address.

The server treats different types of addresses as follows:

If the address is *, the server accepts TCP/IP connections on all server host IPv6 and IPv4 interfaces if the server host supports IPv6,

or accepts TCP/IP connections on all IPv4 addresses otherwise. Use this address to permit both IPv4 and IPv6 connections on all server interfaces. This value is the default.

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.

If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format.

For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.

If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address. 

If you intend to bind the server to a specific address, be sure that the mysql.user grant table contains an account with administrative privileges that you can use to connect to that address. Otherwise, you will not be able to shut down the server. For example, if you bind the server to *, you can connect to it using all existing accounts. But if you bind the server to ::1, it accepts connections only on that address. In that case, first make sure that the 'root'@'::1' account is present in the mysql.user table so you can still connect to the server to shut it down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.