Home / ubuntu / Questions / 1368572
In Process
Pedroski
Asked: 2021-10-12 01:15:06 +0800 CST

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

  • 772

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>

server

1 Answers

  1. 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)
    • 0