Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unable to access Mariadb (Centos 7) with MySQL Workbench

Tags:

I have installed Mariadb on Centos 7 and I am trying to access it with MYSQL Workbench.

I have done the following:

Start MariaDB execute the following command:

 systemctl start mariadb.service

Autostart MariaDB execute the following command:

 systemctl enable mariadb.service

After you started MariaDB (do this only once), execute the following command:

  /usr/bin/mysql_secure_installation

when running this I removed the anonymous user login

We also need to change the port: /etc/my.cnf.d/server.cnf # Mariadb network settings

[mysqld]
# comment out the bind address
#bind_address=127.0.0.1 

now I will get an error

[root@localhost ~]# mysql -u root -p mcellblock -h 192.168.159.163 -P 3306
Enter password:
ERROR 1130 (HY000): Host '192.168.159.163' is not allowed to connect to this MariaDB server

I then enabled the connection privileges:

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
  GRANT ALL PRIVILEGES ON *.* TO 'mcb'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
  FLUSH PRIVILEGES;

  MariaDB [mcellblock]> select User, Host, password from mysql.user where Host <> 'localhost';
  +------+-----------------------+-------------------------------------------+
  | User | Host                  | password                                  |
  +------+-----------------------+-------------------------------------------+
  | root | localhost.localdomain | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
  | root | 127.0.0.1             | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
  | root | ::1                   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
  | root | %                     | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
  | mcb  | %                     | *A071D903A1ABA9752B05C16C573A095C80A7AFAD |
  +------+-----------------------+-------------------------------------------+
  5 rows in set (0.00 sec)

Now when I try to access via the terminal it works:

  $ mysql -u mcb -p mcellblock -h 192.168.159.163 -P 3306
  Enter password:
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 28
  Server version: 5.5.47-MariaDB MariaDB Server

But when I try to access via MySQL Workbench I get an error:

 Please check
  1) Check that mysql is running on server 192.168.159.163  ** IT IS
  2) Check that mysql is running on port 3306 ** I can connect to it via the terminal so I assume it is
  3) Check mcb has the rights to connect to 192.168.159.163 from your address ** It should as its setup for %
  4) Make sure you are both providing a password if needed and using the correct password for 192.168.159.163 ** the passwords are the same

When I install MySQL Workbench locally it works but not remotely.

Does anyone know what I am missing or how to trouble shoot this?

like image 493
ALM Avatar asked Apr 21 '16 16:04

ALM


1 Answers

It turns out it was a firewall issue on the centos machine. After checking with telnet IP_ADDRESS 3306. I was unable to access via my other VM nor Windows. After disabling the firewall it worked.

@Alex I had previously checked the port as well and it was listening correctly. Thank you for your input.

like image 153
ALM Avatar answered Sep 28 '22 01:09

ALM