Thanks in advance for your help.
I am facing an issue with mysql server which is the title of this topic, by using the command:
mysql -u myuser -pmypass -h `hostname` db_name
I get the 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 "bind address" and "skip-networking" although I tried adding them and restarting which did not change anything.
Besides that there is no error in the sql logs, and we can (with HeidiSQL) connect to the server remotely knowing that the user is myuser"@"%
. Mysql listens on the port 3306, so it's ok there
Also, users created with with @'localhost', works fine with the command line (without the -h option).
What is more intriguing is that other servers that look exactly the same work both locally and remotely with the first command...
The server runs with CentOS 6.2
So if anyone has an idea on this matter I would be glad to hear it
P.S : It's my first time posting here, so if there are formatting issues, please forgive me
Simply check your my.cnf and change from
bind-address = 0.0.0.0
to
bind-address = 127.0.0.1
if you don't have that parameter just add it.
Binding to the 0.0.0.0 let your mysql being available on every IP configured cause you can't bind just on two or three IP on the server, the config can be: localhost or everything.
Then check your /etc/hosts file and be sure that the line
127.0.0.1 localhost
contains also your server hostname, as example: my hostname is "db01", my /etc/hosts is
127.0.0.1 localhost db01
Keep in mind that after the install process via yum (I don't know if CentOS do this automagically for you, I just know that Gentoo does not) you have to execute mysql_install_db and then configure the password for the root user, be absolutely sure that you'd set up a password for:
'root'@'localhost'
'root'@'hostname'
'root'@'127.0.0.1'
'root'@'%'
(for security issue be also sure to remove from mysql.user those entry with user='')
hope this helps
Belated response, but I believe that error message means that the server you are trying to connect to is not listening on that interface.
If you change the config to:
bind-address = 127.0.0.1
as suggested above, what you are doing is configuring the server to reject all connections except those from localhost (itself).
bind-address = 0.0.0.0
is in most cases sufficient, and makes mysql listen on all interfaces on the system, which makes it accessible via all the addresses that the host is accessible at.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With