Skip to content
Advertisement

How to connect to my remote SQL server

I have a linux ubuntu server that I rent from DigitalOcean for storing streaming real time data in MySQL by coding with python.

Problem is that I am coding not in the linux server environment but in my local computer python(personal Windows 10(not a server)). So in the coding, I need to connect to my linux server in order to feed/get the data to/from the linux MySQL server.

I know I need to use MySQLdb library in python to do this. I tried to connect to my linux server by using this MySQLdb in python, but it could not connect to the server.

A different question: For granting other ip addresses from which I am connect to the mysql server, should I do it whenever my ip address changes? for example, when I work at home I need to grant my home internet ip, and when I work other places do I need to grant that ip addresses?

Anyway, I tried granting to the ip address where I am connecting to the internet, but even after the granting I cannot access to the mysql server.4

What should I do?

Here is the code I used;

import MySQLdb


conn = MySQLdb.connect("000.000.000.0", "root", "password", "name of database")

“000.000.000.0” is the server that I rent and it is a linux server. “root” is my username of the server “password” is the password for the server as well as for the mysql. and then the name of the database I want to connect to.

c = conn.cursor()

c.execute("SELECT * FROM practice")

here I just want to see what’s in the “practice” table that I have made in the database.

rows = c.fetchall()

for eachRow in rows:
    print eachRow

I don;t know what went wrong. For Granting I used following code:

 GRANT ALL ON nwn.* TO new_username@'00.000.000.00' IDENTIFIED BY 'new_password';

The ip address 00.000.000.000 is Starbucks ip address where I am working on this project right now.I got a message like this:

 Query OK, 0 rows affected, 1 warning (0.03 sec)

After that I tried to connect in python on my local window python.And it didn’t work and got this python error:

OperationalError: (2003, "Can't connect to MySQL server on '000.000.000.0' (10061)")

What’s wrong with this?

Advertisement

Answer

Similar question have already been asked here.

  • Can you connect from your local OS to your remote Database? mysql -u XXXX -h {IP} -p

  • Have you granted your client in the Database? Digital Ocean HowTo for this

  • Create an user for the remote access

  • GRANT SELECT,DELETE,INSERT,UPDATE ON tablename.* TO 'user'@'your_local_ip';

  • FLUSH PRIVILEGES;

It’s good practice not to grant all privileges for remote users (scripts)

Your local IP should be a static IP, otherwise you have allways change ip or use a dynDNS like Service.

(EDIT) I just set up an environment:

  • If you connect to an address that does not exist or the mysql/mariadb instance is not running you get errorcode 2003.’

(2003, "Can't connect to MySQL server on 'my_hostname' (61)")

  • If you’re local host is not granted you get error 1130

(1130, "Host '213.213.213.213' is not allowed to connect to this MariaDB server")

To check if you can access your remote sql server try

mysql -h 123.123.123.123 -u username -p

on youre local windwos machine, where 123.123.123.123 is the IP Adress of your DigitalOcean Server.

To get your public IP Adress (outside of starbucks) you can visit a Site like this

If you REALLY used 0.0.0.0 as ip: 0.0.0.0 in service configuration as ip address is used to bind the service for external access. 0.0.0.0 is like a joker for every ip, but can not be used to access the server/service remotely.

Advertisement