Score:0

What do I need to do to allow remote access to MySQL on Unbuntu 20.04 Server?

es flag

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

This works great on my old shared web-hosting webpage.

On the new Ubuntu 20.04 cloud server, I am getting connection refused. I have no experience running a cloud server.

I allowed ports 33060 and 3306 in UFW

My ip is at home is not fixed.

When I run my Python script I get this error when trying to connect to the cloud server

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

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

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

I restarted mysql: sudo systemctl restart mysql

There must be something else that needs setting, server side.

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

I also got this info (assume my cloud ip is 123.456.789.123):

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:~$

Does this mean mysqld is listening on 3530?? Or is that the PID of mysqld??

pedro@ebs-105422:~$ sudo netstat -tap | grep mysql tcp 0 0 0.0.0.0:mysql 0.0.0.0:* LISTEN 3530/mysqld
tcp6 0 0 [::]:33060 [::]:* LISTEN 3530/mysqld
pedro@ebs-105422:~$

Below is from mysql on the server, confirming port 3306

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>

in flag
Which user are you using to connect to the database? Have you confirmed that the username is externally accessible?
Pedroski avatar
es flag
How would I confirm that? All I know is, using pymsql to connect on my shared web hosting works fine, but on the cloud server, I can't connect, not matter what I try. I set UFW to allow 3306 and 33060 I thought that would do, but, alas, no joy.
Pedroski avatar
es flag
Using an ssh tunneling trick, I can open phpMyAdmin on the cloud server, login with my credentials and manipulate tables. But what I need is to grab the data each week, combined with openpyxl, I have all results for all students written to Excel in about 2 seconds!
in flag
Using an SSH tunnel, you're connecting with a local user account. Something like `'pedroski'@'localhost'` for MySQL. Connecting externally would require you to create an account like `'pedroski'@'%'`. Without knowing more about how things are set up, though, this is just a guess
Score:0
es flag

I figured it out, this answer is just to help anyone who may have the same problem. 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.

When you create a user on mysql 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 user):

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

% here apparently represents any ip

Then on the also via ssh on the server:

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 worked fine from the Idle shell:

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

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

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.