2

I'm trying to connect my Jupyter Notebook, I use Google Colab, with my local MySQL database. When I run this script in PyCharm, my preferred IDE, it works no problem. Only when I run it in Google Colab or Project Jupyter, both get the same error, does this happen.

import pymysql
import pandas as pd

mySQLuser = 'username'
mySQLpasswd = 'password'
mySQLhost = '127.0.0.1'
mySQLport = '3306'
mySQLdatabase = 'databasename'
  
connection = pymysql.connect(user = mySQLuser, passwd = mySQLpasswd, host = mySQLhost, port = mySQLport, db = mySQLdatabase)

I've also tried the same with sqlalchemy(create_engine) and mysql.connector, but get the same error.

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

I've also tried granting all privileges to the user and that didn't change anything. Another suggestion I've seen online is to null or change the bind-address in the configuration file (my.ini), but that didn't affect anything either.

Andrew Smith
  • 21
  • 1
  • 2

2 Answers2

1

Are you sure your colab/jupyter instances are run locally?
If that's not the case, you will not be able to access the SQL database on localhost (127.0.0.1) until you make your it accessible remotely (which implies several steps: making it accessible, changing the mySQLhost address in your code, authorizing connections from the host server in your SQL settings).

FlackOverstow
  • 28
  • 4
  • To check weather your instance is running locally `import socket socket.gethostbyname(socket.gethostname())` you might be able to change it to run it locally. – HackLab Jun 23 '20 at 21:27
  • I'm running them on the same computer, so I thought it's local, but because it's not working, fair to assume it's not ran locally. Do you know a video or a web page that would have all those steps for windows and MySQL 8.0? I've seen things similar to what you're saying but they're for ealier versions of MySQL or aren't on windows. – Andrew Smith Jun 23 '20 at 21:32
  • @HackLab, the ip isn't 127.0.0.1, so it's not local? – Andrew Smith Jun 23 '20 at 21:40
  • @AndrewSmith no this method gets you the remote address of the machine it is running on. If it is identical to your local machine then it is running on your local machine. The easiest way to check your remote address is a site like https://whatismyipaddress.com/ – HackLab Jun 23 '20 at 22:05
  • @HackLab, okay, it's not running locally, I changed the host ip address to my local machine's ip, but I'm now getting a timed out error – Andrew Smith Jun 23 '20 at 22:42
  • @AndrewSmith A couple of things to keep in mind: 1) You might need to allow remote connections in the MySQL settings first, which might be a two-step process: make MySQL listen to connections from outside 127.0.0.1, then authorize ROOT or whatever user you plan on using to connect from a remote IP ([see here maybe?](https://stackoverflow.com/a/14779244)) 2) For your local machine's IP, make sure not to use your local network IP (usually in the form of 192.168.x.x, 10.0.x.x, etc) but your actual public IP 3) You might need to setup port forwarding on your router – FlackOverstow Jun 24 '20 at 17:43
  • @FlackOverstow 1) I added 'bind-address = 0.0.0.0' to the mysqld section of the configuration file, is that what you mean by allowing remote connections? 2) I've authorized a user to connect remotely 3) I got my machine's public ip address from this site: https://whatismyipaddress.com/ 4) I set up port forwarding on my router, for mySQLport should I have it equal to the port specified in the port forward or still 3306? It's still giving me the same error, I just put 3306 for the port. – Andrew Smith Jun 25 '20 at 00:28
  • @AndrewSmith 4) You can leave 3306 in the MySQL settings and on your router, setup forwarding for port 3306 to your local ip's 3306 – FlackOverstow Jun 25 '20 at 20:14
  • @FlackOverstow, I did that and I'm still getting the connection refused error. I thought I'd try setting the bind-address to my public ip in the configuration file and mySQL didn't like that, the service would stop running, so I have to set bind-address to 0.0.0.0. But I did all those things and it's still the same error. I've tried setting mySQLhost to my network's ip address but that causes a timed out error. I don't know what else to try. – Andrew Smith Jun 25 '20 at 22:37
  • @FlackOverstow I also changed my firewall settings to allow access to port 3306 – Andrew Smith Jun 25 '20 at 22:50
  • @AndrewSmith Glad it worked! I would appreciate getting an accepted answer if that worked. (also you posted your last message as another answer instead of a comment - you might want to delete that) – FlackOverstow Jun 26 '20 at 23:16
0

Got it to work. The service provider was rejecting the requests despite me configuring the network to allow it, but I sorted it out.

Andrew Smith
  • 21
  • 1
  • 2