PyMySQL can't connect to MySQL on localhost

Question

I'm trying to connect to MySQL on localhost using PyMySQL:

import pymysql
conn = pymysql.connect(db='base', user='root', passwd='pwd', host='localhost')

but (both on Python 2.7 and Python 3.2) I get the error:

socket.error: [Errno 111] Connection refused

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (111)")

I'm sure mysqld is running because I can connect using mysql command or phpMyAdmin. Moreover, I can connect using MySQLdb on Python 2 with nearly the same code:

import MySQLdb
conn = MySQLdb.connect(db='base', user='root', passwd='pwd', host='localhost')

It seems that the problem is on PyMySQL side rather than MySQL but I have no idea how to solve it.

score 76 · Accepted Answer · edited Jan 16 '13 at 05:11

76

Two guesses:

Run mysqladmin variables | grep socket to get where the socket is located, and try setting up a connection like so:
```
pymysql.connect(db='base', user='root', passwd='pwd', unix_socket="/tmp/mysql.sock")
```
Run mysqladmin variables | grep port and verify that the port is 3306. If not, you can set the port manually like so:
```
pymysql.connect(db='base', user='root', passwd='pwd', host='localhost', port=XXXX)
```

edited Jan 16 '13 at 05:11

Ry-

218,210
55
464
476

answered Jan 16 '13 at 05:01

Lucas Wiman

10,021
2
37
41

3

Sort of weird that it would be needed, though. I wonder if your `my.cnf` file isn't configured correctly. I'm guessing the port isn't configured right, and MySQLdb is smart enough to try to use the socket instead of TCP. (Some discussion [here](http://dev.mysql.com/doc/refman/5.5/en//connecting.html) where they talk about `--protocol`) – Lucas Wiman Jan 16 '13 at 06:39
2

I haven't actually got a `my.cnf` file - but PHP seems to be able to figure out that `localhost` should become that. I like using `.sock`s better, though :) – Ry- Jan 16 '13 at 06:40
I am really wonder ***why*** connecting via the socket works but not `localhost`. I mean, both adminer and mysql command line work fine with the credentials… – Sardathrion - against SE abuse Sep 04 '19 at 10:01
1

Been searching for days for an answer and your answer finally solved my problem here: https://stackoverflow.com/questions/60317075/pymysql-cant-connect-do-mysql-db-hangs-at-self-sock-recv-intob – J.E.C. Feb 20 '20 at 13:17
Your second guess led the way to the answer for me. The MySQL port was set up to the, non-default, 13306 so I had set the host to be '127.0.0.1:13306' which didn't work but setting the port via the 'port' parameter and leaving the host as '127.0.0.1' made it start to work. Thanks. – glaucon May 06 '23 at 05:07

score 13 · Answer 2 · edited Jan 09 '18 at 21:24

13

Seems like changing localhost to 127.0.0.1 fixes the error, at least in my configuration. If it doesn't, I would look for errors in tcp sockets connection and, of course, post it as a bug in pymysql bugtrack.

edited Jan 09 '18 at 21:24

ballade4op52

2,142
5
27
42

answered Jul 31 '11 at 01:31

BrainStorm

2,036
1
16
23

4

This didn't fix it for me either. – gozzilli Sep 22 '12 at 22:11
I'm having similar trouble but using sqlalchemy --> pymysql --> mysql Can connect fine using commandline( mysql -h localhost ).... so it does seem to implicate pymysql – Steve G May 04 '18 at 18:57

score 8 · Answer 3 · edited Sep 30 '17 at 15:49

8

I solved the issue by replacing localhost with 127.0.0.1 and changing the password to my MYSQL database password as shown below;

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = 'XXXXXXXXX',
    db = 'mysql'
)

edited Sep 30 '17 at 15:49

dǝɥɔS ʇoıןןƎ

1,674
5
19
42

answered Jul 09 '16 at 18:01

Byron Kiriibwa

159
1
6

score 1 · Answer 4 · edited Aug 02 '19 at 07:08

1

I met the same question and my solution is as follows:

Run ssh -fN -L 3307:mysql_host:3306 ssh_user@ssh_host in my terminal.
Then input your ssh password
conn = pymysql.connect(db='base', user='root', passwd='pwd', host='localhost')

This error occurs because database does not support link directly.

edited Aug 02 '19 at 07:08

Rex5

771
9
23

answered Dec 17 '18 at 09:49

wolfog

731
7
10

score 1 · Answer 5 · answered Sep 25 '19 at 07:51

1

I asked why socket worked but not TCP and the answer was that bind-address in /etc/my.cnf was not set correctly. This could be your problem too since the socket methods works just fine but the TCP one does not.

answered Sep 25 '19 at 07:51

Sardathrion - against SE abuse

17,269
27
101
156

score 1 · Answer 6 · answered Dec 17 '19 at 08:43

1

Those who are strugging to connect localhost MySQL from dockerised flask-sqlalchemy or using pymysql, pls look into this thread, very usefull How to connect locally hosted MySQL database with the docker container

answered Dec 17 '19 at 08:43

Selvaraj S.

124
1
5

score 0 · Answer 7 · answered May 16 '20 at 05:14

This worked for me:

import pymysql

db = pymysql.connect(host="localhost",port=8889,user="root",passwd="root")
cursor=db.cursor()
cursor.execute("SHOW DATABASES")
results=cursor.fetchall()
for result in results:
    print (result)

if you want to find the port # go to mysql in terminal, and type:

SHOW VARIABLES WHERE Variable_name = 'hostname';
SHOW VARIABLES WHERE Variable_name = 'port';

score 0 · Answer 8 · edited Oct 15 '21 at 07:18

0

I had this same problem on AWS - and turns out that my security group was blocking the connection. I temporarily opened up all connections and voila! It connected!

Do you have any type of FW or host-based FW that could be blocking the connection? I thought it was my code and all was fine. Also check the port you are connecting on.

edited Oct 15 '21 at 07:18

S.B

13,077
10
22
49

answered Oct 14 '21 at 23:48

cciegirl2012

1

score 0 · Answer 9 · answered May 09 '22 at 01:23

0

If you are using Docker, you might need to use host.docker.internal instead of localhost.

answered May 09 '22 at 01:23

cdutra

587
6
15

score 0 · Answer 10 · answered Jul 23 '22 at 14:01

0

I managed to solve my issue by using the port without any quotation like so:

port = 3306,

answered Jul 23 '22 at 14:01

Rojin

1,045
9
15

score -1 · Answer 11 · edited Dec 28 '17 at 16:53

-1

You need to add the port to the connection as well. Try this and it works fine.

pymysql(Module Name).connect(host="localhost", user="root", passwd="root", port=8889, db="db_name")

edited Dec 28 '17 at 16:53

Philipp Maurer

2,480
6
18
25

answered Dec 28 '17 at 14:36

Satheesh Polu

1

5

Welcome to SO! Before answering a question, always read the existing answers. This answer has already been provided. Instead of repeating the answer, vote up the existing answer. Some guidelines for writing good answers can be found here https://stackoverflow.com/help/how-to-answer – LightBender Dec 28 '17 at 14:57

PyMySQL can't connect to MySQL on localhost

11 Answers11

Linked

Related