pysql connection to cloud server database times out

[Pedroski55]

I have only ever used shared web hosting for my little webpages. That makes things easy for people like me who are not so computer-fit.

Now I have rented a cloud server, using Ubuntu 20.04 Server.

I haven't actually transferred my domain name to the new ip yet, this week I need it on the old hosting.

This bit of ssh tunneling allows me to open phpMyAdmin on the new cloud server from home on my laptop.

# do this on the local machine
ssh -L 9980:localhost:80 -p 22000 -i ~/.ssh/my_cloud_ed25519 pedro@123.456.789.123

Put this:

http://localhost:9980/phpmyadmin

in the browser on my laptop and I get my login for phpMyAdmin on the cloud server. I can open phpMyAdmin and manipulate tables.

Normally, the function below connects me to my database and gets the information I want, but I on the new cloud server, I just get a timeout error:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '123.456.789.123' (timed out)")

Is there some setting I need to adjust on the cloud server??
EDIT The port assigned to me by the cloud server company is 22000 Maybe I need to tell pymysql that??
EDIT: I tried setting pymysql to port=22000 but then I get:

pymysql.err.InternalError: Packet sequence number wrong - got 45 expected 0

Without port set, pymysql will use port=1443 and I get the timeout error.

def mysqlRemoteAttn(clas): 
        # To connect remote MySQL database 
        conn = pymysql.connect( 
            host='123.456.789.123', 
            user='my_sql_user_name',  
            password = 'my_sql_password', 
            db='allstudentsdb', 
            ) 
          
        cur = conn.cursor()
    
        # Select query 
        #cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'")
        #cursor.execute("SELECT spam FROM eggs WHERE lumberjack = ?", (lumberjack,))
        #sql = f"SELECT studentnr, attendance FROM allstudents{clas}"
        # should be SELECT studentnr, has_been_inc got that wrong in Week 2
        #sql = f"SELECT studentnr, has_been_inc FROM allstudents{clas}"
        sql = f"SELECT studentnr, attn_this_week FROM allstudents{clas}"
        cur.execute(sql)
        output = cur.fetchall() 
          
        #for i in output: 
            #print(i) 
          
        # To close the connection 
        conn.close()
        return output

    # get the attendance from the webpage MySQL
    results = mysqlRemoteAttn(clas)
    # put the results in a dictionary
    studentsAttn = {}

[ndc85430]

EDIT The port assigned to me by the cloud server company is 22000 Maybe I need to tell pymysql that??

Given your SSH command line above, that looks like the port for the SSH server. What port is the database server running on? If not the default, then you'll need to specify it. Is there some firewall on the server (or in front of it) that's not allowing the connection, perhaps?

[Pedroski55]

The default port for the mysql server is 1443, according to the pymysql docs.

I opened 1443 in ufw on the server, but I just get a timeout.

Today I will ask the company register the domain name with the ip for the cloud server. Maybe that is the problem.

You gave me the idea to check the port for mysqld on the cloud server.

I checked with netstat -tlnp:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2770/mysqld

So I allowed port 3306 in ufw, but I am now getting connection refused.

[bowlofred]

1443 is the default port for SQL Server (the Microsoft product).
3306 is the default port for MySQL.

Can you point to the pymysql documentation that references 1443 for mysql server?

[Pedroski55]

Didn't know that! I just saw somewhere that the default port was 1443. Can't find that again right now!

Anyway, I used this query in mysql on the server to check the port:

mysql> show variables where variable_name in ('hostname','port');
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| hostname | ebs-105422 |
| port | 3306 |
+---------------+------------+
2 rows in set (0.01 sec)

mysql>

So at least 1 port is 3306

But I am getting:

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

So, there must be some other server setting I am not aware of.

Any clues??

Like I said, this works like a charm on my old shared hosting website.

[bowlofred]

Seems like a mysql question or a network question, not a python thing. Connection error is at the network layer. From the same machine, can you do nc 123.456.789.123 3306 < /dev/null?

Some servers default to only allowing connections from localhost, not from other machines until changed, but I have no idea if that's the case here.

[Pedroski55]

This is what I get from nc

pedro@pedro-HP:~$ nc -v -w 2 123.456.789.123 3306
nc: connect to 123.456.789.123 port 3306 (tcp) failed: Connection refused
pedro@pedro-HP:~$

There must be something I need to set on the server, but what??

[bowlofred]

https://superuser.com/questions/702907/m...-localhost

[Pedroski55]

Connecting to the cloud server via ssh, I ran:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

I changed the line bind-address to 0.0.0.0 (I also tried *)

pedro@ebs-105422:~$ nc -v -w 2 123.456.789.123 3306
Connection to 123.456.789.123 3306 port [tcp/mysql] succeeded!
pedro@ebs-105422:~$

But I still get :

pymysql.err.OperationalError: (1130, "183.206.16.30' is not allowed to connect to this MySQL server")

[Pedroski55]

Like bowlofred said, this is not really a Python problem, but without this, pymysql will not work from a remote machine.

Just in case anyone else has this problem, a solution:

This way is probably not so secure, with 3306 open, but a) I only have homework b) you still need the user name and password. There is a more secure way using mysql rsa key encryption I read.

ssh to your cloud server.

First, open port 3306 on the cloud server.

sudo ufw allow 3306
sudo ufw enable

Check if you like:

sudo ufw status

When you create a user on mysql as root, it looks something like this, also when you do it via ssh on the server:

CREATE USER 'peter'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON some_db.* TO 'peter'@'localhost';

If you want remote access, you need to change the user data (or make a new, remote user):

RENAME USER 'peter'@'localhost' TO 'peter'@'%';
GRANT ALL ON somedb.* TO 'peter'@'%';
FLUSH PRIVILEGES;

% here apparently represents any ip

Then on the also via ssh on the server (Ubuntu uses nano mostly, there are other editors):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

find the line that has

bind-address = 127.0.0.1

change this to

bind-address = 0.0.0.0

ctrl X to quit nano, nano asks if you want to save, press y, then enter to save in the same place you opened /etc/mysql/mysql.conf.d/mysqld.cnf

Then (on the server):

sudo systemctl restart mysql

After that, my Python data collector using pymysql worked fine from the Idle shell.