Can't connect to mysql server on u ' local host'[ Error 111] connection refused

OperationalError: (2003, "Can't connect to MySQL server on u'localhost' ([Errno 111] Connection refused)")

How to troubleshoot & fix it?

1 Like

Troubleshooting

Check if you can access database for default website using

 bench mysql

If fails, then find out if MySQL/MariaDB is running by

sudo ps -Af | grep mysqld # determine process running
sudo netstat -lnp | grep mysql # determine listening port number of mysql process 
ps -Af | grep mysqld
root     10483 28913  0 21:52 pts/1    00:00:00 grep mysqld
mysql    23228     1  0 Sep22 ?        00:02:36 /usr/sbin/mysqld

OR
Output must contain, Port number 3306 as LISTEN as follows:

netstat -lnp | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      23228/mysqld        
unix  2      [ ACC ]     STREAM     LISTENING     91090    23228/mysqld         /var/run/mysqld/mysqld.sock

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

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

If your command output is showing that MySQL service is listening on port 3306 and you are still facing issue with ERPNext/bench working normally, then try following methods

Method #1

Check your db user’s privileges.
Example: cd ~/frappe-bench/

bench mysql

# Welcome to the MariaDB monitor.  Commands end with ; or \g.
# Your MariaDB connection id is xxxx
# Server version: 10.x.x-MariaDB mariadb.org binary distribution

# Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [DATABASE]> SHOW GRANTS FOR CURRENT_USER;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for whatever-db@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'USERNAME'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' |
| GRANT ALL PRIVILEGES ON `DATABASE`.* TO 'USERNAME'@'localhost'                                          |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

Run a command like below to access from all machines. (Replace USERNAME and PASSWORD by your credentials.)

MariaDB [DATABASE]> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
MariaDB [DATABASE]> FLUSH PRIVILEGES;

Run a command like below to give access from specific IP. (Replace USERNAME and PASSWORD by your credentials.

MariaDB [DATABASE]> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
MariaDB [DATABASE]> FLUSH PRIVILEGES;

Followed by Start / Restart MySQL

Note:

  • You can replace 1.2.3.4 with your servers IP address. You can run above command many times to GRANT access from multiple IPs in case you have more than 1 IP.
  • MySQL Credentials for specific site can found in ./frappe-bench/sites/YOUR-SITE-NAME/site_config.json file.

Method #2

(Not Recommended, Not very secure)
It is probably, that your MySQL/MariaDB server is only listening on localhost interface (i.e. 127.0.0.1), opening it for external connection sometimes help.

sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/my.cnf

Start / Restart MySQL as follows:

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

Method #3

First take backup of your Database and then reinstalling MariaDB server will also help resolve this issue some some cases. Do it with care, google for steps, as you might end up losing your database.

3 Likes