ERROR 2003 (HY000): unable to connect to MySQL server on "hostname" (111)

Question

Thanks in advance for your help.

I encountered a problem with the mysql server, which is the heading of this section, using the command:

mysql -u myuser -pmypass -h `hostname` db_name 

I get an error

 ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111) 

So, I checked the my.cnf file and I have no lines with the “binding address” and “skip-networking”, although I tried to add them and restart, which didn’t change anything.

In addition, there are no errors in the SQL logs, and we (with HeidiSQL) can connect to the server remotely, knowing that the user myuser"@"% . Mysql listens on port 3306, so it's good there

In addition, users created using @localhost work fine on the command line (without the -h option).

What is more intriguing is that other servers that look the same work both locally and remotely with the first command ...

Server works with CentOS 6.2

So, if anyone has an idea about this, I would be glad to hear that.

PS: This is my first post here, so if there are formatting issues, please forgive me

Answer 1

Just check your my.cnf and change it with

 bind-address = 0.0.0.0 

to

 bind-address = 127.0.0.1 

if you do not have this option, just add it.

Binding with 0.0.0.0 allows your mysql to be available with each configured IP call, because you cannot bind only two or three IP addresses on the server, the config can be: localhost or all.

Then check the file / etc / hosts and make sure the line

 127.0.0.1 localhost 

also contains your server name, for example: my host name is "db01", my / etc / hosts -

 127.0.0.1 localhost db01 

Keep in mind that after the installation process through yum (I don’t know if CentOS does this automatically for you, I just know that Gentoo doesn’t do this) you need to execute mysql_install_db and then configure the password for the root user, make sure that you set a password for:

 'root'@'localhost' 'root'@'hostname' 'root'@'127.0.0.1' 'root'@'%' 

(for security reasons, be sure to also delete this entry from userq = `` from mysql.user)

hope this helps

Answer 2

The response is slow, but I think the error message means that the server you are trying to connect to is not listening on this interface.

If you change the configuration to:

bind-address = 127.0.0.1

as suggested above, what you do is configure the server to reject all connections except those specified in localhost (itself).

bind-address = 0.0.0.0

in most cases, it’s enough and makes mysql listen to all the interfaces in the system, which makes it accessible through all the addresses where the host is available.

Answer 3

Enable the port in the firewall and try ...

in the terminal, execute the commands as root .

 firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload 

Answer 4

There is a reason why I came across the fact that your MySQL server is conflicting with the IP address of another server on your local network.