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.jsonfile.
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.
4 Likes