Cannot connect to mysql db on another host in same zone

Installing and Using OpenWrt Network and Wireless Configuration
1

I think this question is about the OpenWrt firewall and enabling communication between two hosts on the same LAN.

I have two Raspberry Pis.
Pi A - Running mysql db server on Raspbian (192.168.1.124)
Pi B - Running OpenWrt (192.168.1.254)

Both Pis are in the lan zone (eth0) of OpenWrt... i.e: The same subnet.

The OpenWrt Pi has a temp sensor attached. I want to send sensor readings from it to a database running on Pi A.

I installed a mysql client on the OpenWrt Pi.

opkg update
opkg install python3-pymysql

Seems to be set up:

root@OpenWrt:~/python# python3
Python 3.9.0 (default, Dec 06 2020, 15:24:04)
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

I am trying to get this example working. So I created the "users" table in an existing database (called: am2302) I have in MySQL server on the Pi A. I copied the python code to a file on OpenWrt and updated the db connection details for my db server.

This is the script:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='192.168.1.124',user='root',password='root',db='am2302',port=3306,charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO users (email, password) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT id, password FROM users WHERE email=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

When I run the script I get this error:

root@OpenWrt:~/python# python dbtest.py
Traceback (most recent call last):
  File "/root/python/dbtest.py", line 4, in <module>
    connection = pymysql.connect(host='192.168.1.124',user='root',password='root',db='am2302',port=3306,charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
  File "/usr/lib/python3.9/site-packages/pymysql/__init__.py", line 94, in Connect
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 327, in __init__
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 587, in connect
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 969, in _get_server_information
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet
  File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error
  File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
pymysql.err.OperationalError: (1130, "OpenWrt.lan' is not allowed to connect to this MariaDB server")

I ran these commands from the MySQL server on Pi A:

CREATE USER 'root'@'192.168.1.124' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.124';
SET PASSWORD FOR 'root'@'192.168.1.124' = PASSWORD('root');
FLUSH PRIVILEGES;

Here are those commands in the MySQL server...

image
image900×638 28.4 KB

I added bind 0.0.0.0 to /etc/mysql/mariadb.conf.d/50-server.cnf on NCP Pi.
I can ping 192.168.1.124 (Pi A) from OpenWrt (Pi B)
MySQL server is listening on port 3306

root@NEXTCLOUDPI:/etc/mysql/mariadb.conf.d# sudo netstat -pant | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      31177/mysqld

Why am I getting "OpenWrt.lan' is not allowed to connect to this MariaDB server"? Do I need to make a change to the OpenWrt firewall to open port 3306 or something? What other things can I try?

Cheers,

Flex

2

I got it working...
I was specifying the wrong ip address in the MySQL Grant All Privileges command.

The MySQL client is on 192.168.1.254 trying to connect to MySQL server on 192.168.1.124 so the grants to enter into MySQL server should be:

CREATE USER 'root'@'192.168.1.254' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.254';
SET PASSWORD FOR 'root'@'192.168.1.254' = PASSWORD('root');
FLUSH PRIVILEGES;

Cheers,

Flex

3 Likes