Lỗi cant connect to mysql server on ip address 111 năm 2024

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://{}:{}@{}/{}".format(db_user,db_pass,db_host,db_name)

When I change it and run it on server:

SQLALCHEMY_DATABASE_URI = "mysql+pymysql:///unix_socket=/{}/{}".format(db_user,db_pass,db_name,db_socket_dir,cloud_sql_connection_name)

I got:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")

wokmou

unread,

Apr 9, 2021, 9:04:55 PM4/9/21

to Google Cloud SQL discuss

Looking at some public forums [1][2] , I found some valid reasons why you may experiencing this error. Mostly related to your MySQL server only listening the localhost interface or db user’s privileges.

When I want to build connections between database and Python programs with SQLAlchemy on Google Colab, it occurs errors saying: (mysql.connector.errors.InterfaceError) 2003: Can’t connect to MySQL server on ‘localhost:3306’ (111 Connection refused) and Can’t connect to MySQL server on ‘localhost:3306’ [Errno 99] Cannot assign requested address.

Here’s my solution. It can also be understood as to how to allow remote MySQL database connection.

Step1. Modify bind-address

sudo vim /usr/local/etc/my.cnf

While the location of ‘bind-address’ in files is not fixed, it may summarize are:

1. /etc/mysql/mysql.conf.d/mysqld.cnf

2. /etc/mysql/mariadb.conf.d/50-server.cnf

3. /etc/mysql/mysql.conf.d

4. /etc/mysql/mariadb.conf.d

5. /etc/mysql/my.conf

…….

In this .cnf, the mission is to replace ‘bind-address = 127.0.0.1’ by ‘bind-address = 0.0.0.0’.

First, ‘Ctrl + v’ to enter —VISUAL BLOCK — mode. Use arrow keys to select lines that need to be adjusted.

Secondly, press ‘Shift+i’ to enter insert mode. That’s to say we will comment out the line ‘bind-address = 127.0.0.1’ with ‘#’ and insert a new line ‘bind-address = 0.0.0.0’.

After done all the modifications, press ‘ESC’ to change mode, then input:

1. :w- save files, not exit from vim

2. :w file- save the changes as a new file, not exit from vim

3. :wq- save files and exit

Before the next step, check the changes do exits in target files.

Step2. Restart MySQL Server

service mysql start

Step3. Create new users to visit remotely

mysql -u root -p

Then we will need to add a user to access the database.

grant all privileges on . to root@"%" identified by ".";

The above code, which you might find the most common, will lead to ERROR 1064 (42000). Because MySQL 8.0 hasn’t supported this method anymore.

When we try to access mysql server on Linux machine using the public IP address, it is showing error 111.

Here at Bobcares, we get requests from our customers to fix similar issues as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this issue for our customers.

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 -pbob -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 -pbob -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 -ubob -pbob -h195.168.1.100
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘195.168.1.100’ (111)

Solution for ‘Can’t connect to mysql error 111’ error

Let us see how our Support Techs resolved the 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.