1

I've been scratching my head for a day and probably read so many articles and on how can I allow my MariaDB to listen to remote connections. Unfortunately getting the below error.

Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MariaDB server.

I also read this StackOverflow question, anotherQuestion and successfully able to create the new user and grant all permission with following MySQL query.

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

And below is my ahsensaeed user grants.

MariaDB [mysql]> show grants for 'ahsensaeed'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ahsensaeed@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ahsensaeed'@'%' IDENTIFIED BY PASSWORD '*F794ABE2A12665587C6B6D8B61E2F7E987711AFA' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In the end, I just flush the privileges and after that, I go to my MariaDB configuration file and edit it. Below is the path where my MariaDB conf file placed.

/etc/mysql/mariadb.conf.d/50-server.cnf

The following shows my MariaDB file block.

[mysqld]

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
.....
.....

and then I just restart the mysql service via /etc/init.d/mysql restart. I also open the 3306 port on my client for mysql.

When the above solution not works I also add the bind-address = 0.0.0.0 in /etc/mysql/conf.d/mysql.cnf file, but still it failed with error.

The following shows how I requesting for MariaDB database from my server.

-> mysql -uahsensaeed -p -h hostIp

and then I got the below error.

ERROR 1130 (HY000): Host 'hostIp' is not allowed to connect to this MariaDB server

Edit Added the host and user data.

MariaDB [(none)]> select User,Host from mysql.user;
+------------+-----------+
| User       | Host      |
+------------+-----------+
| ahsensaeed | %         |
| root       | localhost |
+------------+-----------+
2 rows in set (0.00 sec)

Any help would be appreciated.

Ahsan Saeed
  • 701
  • 10
  • 22

1 Answers1

0

This worked for me (I`m using Ubuntu 18.04 as virtual machine and Windows 10 with Vagrant):

sudo mysql -e "CREATE DATABASE {db_name};"
sudo mysql -e "CREATE USER {user_name}@localhost IDENTIFIED BY '{password}';"
sudo mysql -e "UPDATE mysql.user SET Host='%' WHERE User='{user_name}';"
sudo mysql -e "GRANT ALL PRIVILEGES ON {db_name}.* TO '{user_name}'@'%';"
sudo mysql -e "FLUSH PRIVILEGES;"
sudo systemctl restart apache2
sudo systemctl restart mariadb

After that, you should edit the file:

/etc/mysql/mariadb.conf.d/50-server.cnf

And set bind-address property to your hostIP address or just comment this line out.

Finally, run commands:

sudo systemctl restart apache2
sudo systemctl restart mariadb

I hope this will suit your needs

Ozzy
  • 1