Most Webmasters encounter 'Can't connect to mysql error 111' error when trying to access mysql server on Linux machine using the public IP address.
This error simply Indicates that your MySQL server is only listening the localhost interface.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix similar Server errors.
Cause for 'Can't connect to mysql error 111' error
Usually, this error occurs when mysqld only listens to the localhost interface.
When we try to access mysql server on Linux machine using the public IP address, it often shows error 111.
However, when localhost and 127.0.0.1 is used, it is connecting fine.
# ifconfig | grep "inet addr"
inet addr:127.0.0.1 Mask:255.0.0.0
inet addr:195.168.1.100 Bcast:195.168.1.255 Mask:255.255.255.0
# mysql -ubob -pibmimedia -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 160
Server version: 5.1.31-1ubuntu2 (Ubuntu)
# mysql -ubob -pibmimedia -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.1.31-1ubuntu2 (Ubuntu)
The error message appears as below:
# mysql -uibmimedia -pibmimedia -h100.161.1.104
ERROR 2003 (HY000): Can't connect to MySQL server on '100.161.1.104' (111)
How to fix 'Can't connect to mysql error 111' error ?
Before we proceed with the steps to fix the issue, check the MySQL status.
You can check MySQL status following below provided steps:
1.Initially, check if the MySQL is running without any issue.
# sudo ps wwaux | grep -i sql
2. If that does not output anything, try starting the MySQL service:
$ sudo service mysql start
3. Also, check hostname/port is MySQL listening on
$ sudo netstat -plutn | grep -i sql
If the MySQL is running without any issue, please go ahead with the below mentioned steps:
1. Firstly, in my.cnf configuration file, we should comment the following line in the mysqld section:
bind-address = 127.0.0.1
We can comment by adding # at the beginning of a line.
Once this is done, restart mysql:
$ sudo service mysql restart
To do this, we must have root or sudo access to the server.
2. Then, we should check the user privileges.
Login as root to mysql and add this:
CREATE USER 'root'@'100.161.1.104' IDENTIFIED BY ‘***’;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'100.161.1.104' IDENTIFIED BY ‘***’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
Finally, you can try to connect again using mysql -uibmimedia -pibmimedia -h100.161.1.104. to check its working.
[Need assistance in fixing mysql errors? We can help you. ]
Conclusion
This article covers methods to fix mysql error, 'Can't connect to mysql error 111' on Linux machine for our customers.
This can happen when there was a host IP change.
This issue can prevent connection to the database.
As it turned out if you do come accross this look in /etc/my.cnf, there is a line:
bind-address = ip.add.ress
This may be the old address for the server and this will stop connections, change this to your new address and restart MySQL/MariaDB and you should be good again.
This article covers methods to fix mysql error, 'Can't connect to mysql error 111' on Linux machine for our customers.
This can happen when there was a host IP change.
This issue can prevent connection to the database.
As it turned out if you do come accross this look in /etc/my.cnf, there is a line:
bind-address = ip.add.ress
This may be the old address for the server and this will stop connections, change this to your new address and restart MySQL/MariaDB and you should be good again.
