Fix: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)

This tutorial is intended to explain the necessary steps for solving the “ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)” which might occur when you try to access the MySQL database...

Spread the love

This tutorial is intended to explain the necessary steps for solving the “ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)” which might occur when you try to access the MySQL database server.

Before moving any further, if you are a Linux user who is new to MySQL/MariaDB, then you may consider learning MySQL / MariaDB for Beginners – Part 1 and 20 MySQL (Mysqladmin) Commands for Database Administration in Linux as well.

On the other hand, if you are already a intermediate/experienced MySQL user, you can master these 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips.

Note: For this tutorial, it is assumed that you have already installed mysql database server.


Coming back to the point of focus, what are some of the possible causes of this error?

  1. Network failure especially if mysql database server is running on remote host.
  2. No mysql server is running on the mentioned host.
  3. Firewall blocking TCP-IP connection or other related reasons.

Below are the essential steps to deal with it.

1. If database server is on a remote machine, then try to test the client-server connectivity using ping command, for instance:

$ ping server_ip_address
Ping Host Machine

Ping Host Machine

Once there is connectivity, use the ps command below which shows information about a selection of the active processes, together with a pipe and grep command, to check that the mysql daemon is running on your system.

$ ps -Af | grep mysqld

where the option:

  1. -A – activates selection of all processes
  2. -f – enables full format listing
Check MySQL Process

Check MySQL Process

If there is no output from the previous command, start the mysql service as follows:

$ sudo systemctl start mysql.service
$ sudo systemctl start mariadb.service
OR
# sudo /etc/init.d/mysqld start

After starting mysql service, try to access the database server:

$ mysql -u username -p -h host_address 

2. If you still get the same error, then determine the port (default is 3306) on which the mysql daemon is listening by running the netstat command.

$ netstat -lnp | grep mysql

where the options:

  1. -l – displays listening ports
  2. -n – enables display of numerical addresses
  3. -p – shows PID and name of the program owning the socket
Find MySQL Port Number

Find MySQL Port Number

Therefore use the -P option to specify the port you see from the output above while accessing the database server:

$ mysql -u username -p -h host_address -P port

3. If all the above commands run successfully, but you still see the error, open the mysql config file.

$ vi /etc/mysql/my.cnf
OR
$ vi /etc/mysql/mysql.conf.d/mysqld.cnf 

Look for the line below and comment it out using the # character:

bind-address = 127.0.0.1 

Save the file and exit, afterwards restart the mysql service like so:

$ sudo systemctl start mysql.service
$ sudo systemctl start mariadb.service
OR
# sudo /etc/init.d/mysqld start

However, if you have firewallD or Iptables running try to review firewall services and open the mysql port, assuming it is firewall blocking TCP-IP connections to your mysql server.

That’s all! Do you know other methods or have suggestions for solving the MySQL connection error above? Let us know by dropping a comment via the feedback form below.

Facebook Comments
Spread the love

Posted by News Monkey