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

I use the following command:

mysql -u root -h 127.0.0.1 -p

And the error message is:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
开发者_如何学Go

How can I fix it?

If you are using Ubuntu, you have to use the following steps to avoid this error (if there is no replication enabled):

1. run the command vim /etc/mysql/my.cnf
2. comment bind-address = 127.0.0.1 using the # symbol
3. restart your MySQL server once.

In Step 1, if you cannot find bind-address in the my.cnf file, look for it in /etc/mysql/mysql.conf.d/mysqld.cnf file.

Update in case of MySQL replication enabled

Try to connect MySQL server on the IP address for which MySQL server is bound in file my.cnf instead of localhost or 127.0.0.1.

Try localhost instead of 127.0.0.1 to connect or in your connection-config. It worked for me on a Debian 6.0 (Squeeze) Server.

In my case (remote connection), it helped turning off the firewall on the server:

service iptables stop

This happens when you forget to start the database before connecting to it:

mysql.server start

Then

mysql -u root -p -h 127.0.0.1

On Windows, this problem may occur because your MySQL server is not installed and running.

To do that, start a command prompt as administrator and enter the command:

"C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\mysqld" --install

If you get "service successfully installed" message then you need to start the MySQL service. To do that: go to Services window (Task Manager → Services → Open Services). Search for MySQL and start it from the top navigation bar. Then if trying to open mysql.exe, it will work.

Look at the my.cnf file. If it contains a [client] section, and the port is other than the real listen port (default 3306), you must connect the server with an explicit parameter, -P 3306, e.g.

mysql -u root -h 127.0.0.1 -p -P 3306

If you are here with the same error message and you use Docker - try to use the name of the database service as a host.

services:
  app:
    blablabla
  db:
    blablabla

I mean, instead of:

127.0.0.1 or localhost

use:

db

If you are running Ubuntu via WSL (Windows Subsystem for Linux) and wish to connect to the MySQL instance on the host machine, you will need to use the host machine's IPv4 address e.g. 192.X.X.X, not 127.0.0.1 or localhost.

$ mysql -u <user> -h 127.0.0.1 -p -P 3306

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

$ mysql -u <user> -h 192.X.X.X -p -P 3306

Welcome to the MySQL monitor...Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To check your IPv4 address, go to Settings -> Network $ Internet -> Properties -> IPv4 address.

I got the same error configuring MySQL URI for apache airflow as:

mysql+mysqlconnector://<user>:<password>@localhost:3306/<database>

(mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (111)

or

mysql+mysqlconnector://<user>:<password>@127.0.0.1:3306/<database>

(mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

Fixed the error configuring the URI as:

mysql+mysqlconnector://<user>:<password>@192.X.X.X:3306/<database>

If you have tried all of the above and it still did not work. Check firewall.

I was trying to connect from a windows machine to a MySql Server 5.7.32 installed on a VirtualBox machine with CentOs7. I have tried everything and it was not working, even though i could ping the machine i couldn't connect to the MySql Server.

On CentOs7 the commands to check the firewall are:

Step 1: Check the status of your firewall service:

systemctl status firewallid.service

Step 2: Disable the firewall service

systemctl stop firewallid.service

You need to change the bind-address parameter to 127.0.0.1 in the MySQL configuration file (my.ini or my.cnf) or use the one that is defined there.

If that doesn't work you should check that the MySQL service is actually running.

In case you are running on a non-default port, you may try using --port=<port num> provided --skip-networking is not enabled.

I was also facing the same issue.

The following helped me fix the problem

Go to Control Panel → Administrative Tools → Services. Inside this you will most certainly see the MySQL service: right click and say start (force start).

I just have this problem... running in Windows 7 and WAMP server ... after reading this.

I found that Antivirus Firewall had caused the problem.

For Docker users - When trying to connect to the local SQL database server using mysql -u root -h 127.0.0.1 -p and your database is running in a Docker container, make sure the MySQL service is up and running (verify using docker ps and also check that you are in the right port as well). If the container is down you'll get connection error.

The best practice is to set the IP addresses in /etc/hosts on your machine:

127.0.0.1 db.local

And running it by mysql -u root -h db.local -p.

Check if it is open port 3306 (more here):

nmap -p3306 127.0.0.1

If you receive something like:

Starting Nmap 7.92 ( https://nmap.org ) at 2022-01-07 11:55 CET
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000082s latency).

PORT     STATE  SERVICE
3306/tcp closed mysql

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds

then open port 3306:

sudo iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Or sudo ufw allow 3306 if you use UFW.

Check: netstat -lnp | grep mysql you should get something like this:

cp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2048/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      2048/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     514961   2048/mysqld          /var/run/mysqld/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     514987   2048/mysqld          /var/run/mysqld/mysqlx.sock

I had this problem when I tried to connect to the MySQL server in the Docker container

I fixed by following the steps below.

1. Go inside the container:

docker exec -it mysql bash

2. Run this command:

echo "bind-address           = 0.0.0.0" >> /etc/mysql/conf.d/mysql.cnf 

3. Exit from the container

exit

4. Restart the container

docker restart mysql

5. Go inside the container

docker exec -it mysql bash

6. Connect to the MySQL server, and enter your password

mysql -u root -p

Make sure that, your password doesn't contain the '@' character. As an example, If your password is Jane@123, then the following error will be displayed.

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '123@localhost' ([Errno -2] Name or service not known)")

In my case, with Centos 8 and MYSQL 8.0.26. I fixed it by opening the port from the firewall. You can run the below command if [3306] is the MySQL port and [public] is the active zone (default configuration)

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

Also, I looked for bind-address=127.0.0.1 but did not configure anywhere in the configuration./etc/my.cnf /etc/my.cnf.d/*

This happens when connecting to RDS on AWS when the database has run out of storage capacity. You have to increase the amount of storage capacity available to fix this.

I changed the installation directory on re-install, and it worked.

I just restarted my MySQL server and the problem was solved.

On Windows, net stop MySQL, and then net start MySQl.

On Ubuntu (Linux): sudo service start mysql

Please make sure your MySQL server is running on localhost.

On Linux

To check if MySQL server is running:

sudo service mysql status

To run MySQL server:

sudo service mysql start

On Windows

To check if MySQL server is running:

net start

If MySQL is not in list, you have to start/run MySQL.

To run MySQL server:

net start mysql