[SOLVED] Access mysql on the cloud server using Python

Pedroski · 10-10-2021, 06:54 PM

I have a little Python script to collect students' homework and online classwork scores from the table allstudentsAnswers20BE.

Works great on my old shared web-hosting webpage.

On the new Ubuntu 20.04 cloud server, I am getting connection refused.

Quote:

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

netstat says mysqld is listening on 33060 and 3306, so I allowed both in the firewall ufw

Quote:

pedro@ebs-105422:~$ sudo netstat -tlnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 657/systemd-resolve
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 2770/mysqld
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2770/mysqld
tcp 0 0 0.0.0.0:22000 0.0.0.0:* LISTEN 21908/sshd: /usr/sb
tcp6 0 0 :::22000 :::* LISTEN 21908/sshd: /usr/sb
tcp6 0 0 :::80 :::* LISTEN 27719/apache2

There must be something else that needs setting.

Any tips what that might be please, I really need this to work.

Below is from mysql on the server, confirming port 3306

Quote:

Here is the Python, works fantastic on my old webpage. I run it in bash on my laptop:

Code:

def mysqlRemoteAttn(clas): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123',
        port=3306,
        user='my_mysql_user',  
        password = 'my_mysql_pw', 
        db='allstudentsdb', 
        ) 
      
    cur = conn.cursor()

    # Select query     
    sql = f"SELECT studentnr, attn_this_week FROM allstudents{clas}"
    cur.execute(sql)
    output = cur.fetchall() 
          
    # To close the connection 
    conn.close()
    return output

# get the attendance from the webpage MySQL
results = mysqlRemoteAttn(clas)

Turbocapitalist · 10-11-2021, 11:55 AM

Hi again.

Are you trying to access it from the same system? That is to say, is the python script on the same system as the MySQL database? If so then you should have the database only listening to localhost and not the external IP address. That's kind of important. In other words, host = localhost instead of the IPv4 number.

If you are trying to access it from another system, say from your laptop for testing or development, then you can use another SSH tunnel:

Code:

ssh -L 3306:localhost:3306 you@www.example.com

Then once you are logged in, in another terminal access MySQL via localhost on port 3306 on your laptop for as long as the tunnel is open. If you already have a service on your laptop at 3306, change the first 3306 to something else and then connect to that instead.

Pedroski · 10-11-2021, 06:37 PM

@Turbocapitalist

No, I need to collect the student data from my laptop.

My Python uses pymysql and openpyxl together, collects the data, writes it to Excel in about 2 seconds for 200 + students!

Thanks for the tip! I like this ssh tunneling!

But could I run my Python data collector through that?

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. Maybe do that later, right now, I just need it working.

ssh to your cloud server.

First, open port 3306 on the cloud server.

Quote:

sudo ufw allow 3306
sudo ufw enable

Check if you like:

Quote:

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:

Quote:

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):

Quote:

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):

Quote:

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

find the line that has

Quote:

bind-address = 127.0.0.1

change this to

Quote:

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):

Quote:

sudo systemctl restart mysql

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

Like many set-up things, once you have done it a couple of times, it looks easy! But the first time!