1

I am trying to allow connections using mysql.connector from a specific machine to a MariaDB server, within the local network.

Ideally, I would like to create an account limited to a single IP and DB.

For simplicity, I have tried the following wildcard approach:

CREATE USER 'foo'@'%' IDENTIFIED BY 'foobar';
GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' WITH GRANT OPTION;

SELECT user,host FROM mysql.user WHERE user='foo';

+--------+-----------+
| user   | host      |
+--------+-----------+
| foo    | %         |
+--------+-----------+

Using this basic, Python test script:

import mysql.connector

class DBConnect:
  def __init__(self):
    config={
      'user':'foo',
      'password':'foobar',
      'host':'192.168.x.xxx',
      'port':3306,
      'database':'foo',
      'raise_on_warnings':True
    }
    self.__db=self.__connect(config)

  def __connect(self,config):
    try:
      return mysql.connector.connect(**config)
    except mysql.connector.Error as err:
      print(str(err))
      return False

db=DBConnect()

I receive the following error:

1130: Host '192.168.x.xxx' is not allowed to connect to this MariaDB server

I have read about changing the bind-address and followed various setup guides such as that in the MySQL documentation for adding new users but I still cannot get a successful connection.

The bind-address can be found in the 50-server.cnf file. It is set to 127.0.0.1 but if changed to, for example, 0.0.0.0 the server restart fails:

systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE systemd[1]: Failed to start MariaDB 10.1.37 database server

skip-networking is not present in any of the config files

With the bind-address set to 127.0.0.1 running netstat -anp | grep 3306 returns:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1790/mysqld
tcp 0 0 192.168.x.xxx:3306 0.0.0.0:* LISTEN 19490/mysqld

This leads me to believe the database server is indeed listening for connections within the LAN?

Any help is much appreciated!

macourtney7
  • 111
  • 4

1 Answers1

0

Stopping both of the mysqld processes will allow for the 50-server.cnf file to be modified.

The mysql process can then be successfully restarted. Remote connections will now be accepted.

Credit to MichaelHampton

macourtney7
  • 111
  • 4