Allow root LOGIN to MySQL on Red Hat 7

If you’ve installed MySQL on a Linux server you’ve probably run the “mysql_secure_installation” script to lock it down.  But now you need to access this server remotely using MySQL workbench.  How do I allow remote connections to MySQL?

It isn’t as easy as throwing a switch and allowing MySQL to accept incoming connections from any source.  

Do not attempt these steps on an internet-facing server.  Make sure you have taken all other precautions to protect your machines from unauthorized access.

My first step was to open the firewall on the Red Hat server:

image

image

And don’t forget to “Options –> Runtime to Permanent” to keep these changes

image

But even then I received the error:image

The second step is in MySQL configuration.  Even though RHEL can accept incoming connections on port 3306, and MySQL is configured to allow incoming connections, root still doesn’t have permission to log in remotely

Here are the steps to configuring MySQL running on Red Hat server to allow incoming connection from root on any host.  You will have to run this in terminal as root.

# mysql –u root –p
(enter MySQL root password)

use mysql;

select user, host from user;
this shows a table of users and the host(s) the users are allowed to log in from.

+———–+———–+
| user      | host      |
+———–+———–+
| mysql.sys | localhost |
| root      | localhost |
+———–+———–+
2 rows in set (0.00 sec)

Now we update the “host” entry for root to allow login from any host.  For this we’ll use the SQL wildcard, ‘%’’

mysql> UPDATE user SET host = ‘%’ WHERE user=’root’ AND host=’localhost’;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, host from user;
+———–+———–+
| user      | host      |
+———–+———–+
| root      | %         |
| mysql.sys | localhost |
+———–+———–+
2 rows in set (0.00 sec)

You should connect now with no issues!