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>
При попытке подключиться к MySQL или Percona сервер с настройками по-умолчанию с другой рабочей станции или сервера, можно получить ошибку
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘xx.xx.xx.xx’ (111)
Далее описаны 3 шага, которые нужно пройти для установления соединения.
The first thing we can check is to see if the user from the remote host is allowed.
1. Login as root on mysql server
mysql -u root -p
2. Select database and show users.
select * from mysql.userG
**A vertical list will be displayed. Look at the first two fields for each entry. If the user is only allowed to connect from localhost, this may be the problem.
Example:
Host: localhost
User: mydbuser
A user will have to be defined with the same parameters as mydbuser for the remote host (or hosts)
Here’s where your documentation will come in handy (or you can hope the old query exists in the mysql buffer!)
3. Allow remote hosts to connect
grant select,insert,update,delete,create,drop,index,alter on mydbname.* to mydbuser@’192.168.1.%’ identified by ‘mydbpassword’ ;
flush privileges;
Note: if you only want to allow a certain host, specify the IP instead of the wildcard.
The second issue that may cause this error is a MySQL configuration.
1. Open MySQL config file
nano /etc/my.cnf
2. Ensure that the following are commented out.
#skip-external-locking
#skip-networking
#bind-address = xx.xx.xx.xx
Save and exit
3. Restart mysql service
service mysqld start
The third issue that may contribute to this error may be the security configuration rejecting incoming requests on the server.
1. Login as root on db server
2. Add rule to iptables
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp —destination-port 3306 -j ACCEPT
** this grants access to the entire subnet, use a specific IP where applicable.
service iptables save
3. Restart iptables service
service iptables restart
Test from remote host by using the following:
mysql -h 192.168.my.dbip -u mydbuser -p
When you’re done installing a MySQL server application on Windows operating system, you can try to login to your MySQL server by using the mysql command from the Command Line.
But sometimes you may get the MySQL 2003 error saying it can’t connect to MySQL server as shown below:
mysql --user=[your username] --password=[your password]
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)
The error above means that your request to connect to the MySQL server has been refused.
There are two things you need to check to fix this error:
- Make sure that your MySQL service is actually running
- Check that you are attempting to connect to the right port number
This tutorial will help you to do both, starting with checking MySQL service status.
Make sure that your MySQL service is actually running
To fix this error, you need to make sure that your MySQL server service is actually running in the Services panel.
First, open the Windows Start menu and search for the Services panel to see the result below:
Then, scroll through the services list until you reach the services that start with "M" to look for MySQL services.
Usually, you have the MySQL version number attached to the service name.
The MySQL version installed on my computer is MySQL 8.0.26 so I have MySQL80 service listed as shown below:
If you have MySQL version 7, then you may have MySQL70 listed on the Services panel.
As you can see from the picture above, the status of MySQL80 service is empty, meaning that it’s not currently running.
If you see the same status, you can run the service by clicking the Start the service link on the left pane.
Now you can try to connect again to your MySQL server from the Command Line.
MySQL service is missing from the Services panel
If you don’t find MySQL service from the Services panel, then you may not have installed MySQL as a Windows service.
It’s always recommended to install MySQL as a Windows service so that you can start and stop a running MySQL program easily.
To install the MySQL service, you can run the mysqld --install command from the command line as follows:
mysqld --install
Service successfully installed.
Please note that you need to have Administrator access to perform the installation above.
Alternatively, you can re-run the MySQL installer program and choose to reconfigure the installed MySQL server as shown below:
In the reconfigure wizard panel, click on Next until you get into the Windows Service section.
You should check the box that says Configure MySQL Server as a Windows Service as follows:
When you’re done, click on Next and then click on Execute to run the changes.
Now you should be able to find MySQL under the Services panel.
Check that you are attempting to connect to the right port number
When you run a MySQL service on your computer, the service will start MySQL server and allows you to connect to the server under a specific port number.
By default MySQL uses port 3306 but you can check the one you are currently using in your my.ini file.
The my.ini file is a MySQL configuration file used to properly configure the running MySQL services.
When you install MySQL using the official installer, then you should have a my.ini file located under your MySQL installation folder.
For example, mine was located under C:ProgramDataMySQLMySQL Server 8.0 folder as shown below:
Once you find the file, open it and find the [client] section.
You should find the port number assigned under the section as shown below:
[client]
# pipe=
# socket=MYSQL
port=3306
[mysql]
no-beep
Once you find the port number, and the number to the mysql command when you try to login as follows:
mysql --user=[your username] --password=[your password] --port=3306
Now you should be able to connect to your local MySQL server. Nice work! 👍