1

I have setup a private home server using my raspberry pi 3. It has MySQL as its database. It hosts my personal website and i want to access its MySQL database remotely using python. It works perfectly fine when the database is hosted on shared hosting like GoDaddy. The main reason I'm unable to connect to the server is because my server doesn't has a static IP. I made its IP static using NoIP.

The web URL to my hosted website ends with ddns.net

This is my python code using which I,m trying to connect to the remote mysql server

import pymysql as _sql
SQL = None
def SQL_ONLINE_CONNECTION():
    try:
        connection = _sql.connect(host='URL',
                     user='USERNAME',
                     password='PASSWORD',
                     db='DBNAME',
                     charset='utf8mb4',
                     port=3306,
                     cursorclass=_sql.cursors.DictCursor)
        return connection
    except _sql.err.OperationalError as e:
        return False

def Boot():
    global SQL
    try:
        SQL = SQL_ONLINE_CONNECTION()
        if SQL is not None:
            print("[MYSQL CONNECTION ESTABLISHED!]")
        else:
            print("[FAILED!]")
    except Exception as e:
        print(e)

if __name__ == '__main__':
    Boot()

I cannot even ping to my raspberry server using its URL address.

**Please ignore my python code styling as i had to copy every line here and put 4 spaces to display them as code.

Just Stuff
  • 21
  • 2
  • 2
    Use the hostname "xyz.ddns.net" in place of the IP? But showing us some of your code and an error message would be helpful to get useful answers. – Ralf Kleberhoff Oct 14 '20 at 15:06
  • 1
    And NoIP doesn't make your IP static, it only gives you a constant domain name mapping that tracks your ever-changing dynamic IP address. – Ralf Kleberhoff Oct 14 '20 at 15:08
  • This situation has several parts: do you have access to port 3306/tcp through firewall ? Does server even listen on 3306 on that interface (server config) ? or, do you have ssh access, can you ssh tunnel to server ? Then, if you actually connect to server, do you have defined a remote user with access to that database ? Seems to me a dynamic ip address is the least of your problems. – Tuncay Göncüoğlu Oct 14 '20 at 15:11
  • yes i have ssh access and how to configure server config file to listen for connections on port 3306? – Just Stuff Oct 14 '20 at 15:12
  • Then this post might be of help: https://stackoverflow.com/questions/21903411/enable-python-to-connect-to-mysql-via-ssh-tunnelling – Tuncay Göncüoğlu Oct 14 '20 at 15:14
  • May I refer you to [PEP-8](https://www.python.org/dev/peps/pep-0008/) for Python code styling. – S3DEV Oct 14 '20 at 15:14

1 Answers1

0

By your description, this does not strike me as a python specific question nor as MySQL specific issue. But a router firewall configuration.

1.- Can you from inside of your network connect to the MySQL server using the same configuration as you are using when connecting from the outside? If yes them, most likely, it is your router that is not allowing access to that port from the internet. The changes needed are dependent on your router and assuming you have administrator access to it, this site should be able to help (https://portforward.com/router.htm)

If not, then the problem is most likely in the MySQL server configuration. if you perform a nestat -nap and look for the open ports you should be able to identify those attached to the MySQL server process. What is the Local IP address it is listening on? If it is 127.0.0.1 then that is the problem. You are only accepting connections from the Rpi machine itself. The configuration must be changed so that the server binds itself to 0.0.0.0 in order to accept connections from the outside.

If the bind address is already 0.0.0.0 can you perform a nc or telnet to the host:port of the server? Does it reply back?

If it does then I am wrong and this is a problem with python and MySQL. If it does not then it is then a MySQL problem.

PandaCheLion
  • 446
  • 5
  • 15