Database – Dev Lite Journal

While working on console is simple and fast, having a flexibility to visualize data through some kind of workbench is also powerful. Following is the steps to enable remote access on MySQL database.

Create new user ‘myuser’ with password ‘mypass’

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

For older MySQL version, like 4.0.15, you need to use the following command and skip the grant command in step 2.

GRANT ALL ON * to myuser@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON * to myuser@'%' IDENTIFIED BY 'mypass';

Grant privileges for this new user

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;
FLUSH HOSTS;

Modify the MySQL config (somewhere like etc/my.cnf or /etc/my.cnf.d/server.cnf) to enable the incoming connection from a certain ip address, or perhaps any IP address by setting it to 0.0.0.0 (not recommended for production).
```
#Replace xxx with your IP Address 
bind-address = xxx.xxx.xxx.xxx
```
Don’t forget to restart mysql!
```
service mysql restart
```
Opening the port by adding the following line in the iptables (/etc/sysconfig/iptables). This needs root access.
```
-A INPUT -p tcp -m state --state NEW,ESTABLISHED -m tcp --dport 3306 -j ACCEPT
```
Don’t forget to restart the iptables!
```
service iptables restart
```

If all goes well, you can should be able to see your new user in the following table.

MariaDB [(none)]> select Host,User from  mysql.user;
+-----------------------+-------------+
| Host                  | User        |
+-----------------------+-------------+
| %                     | root        |
| 127.0.0.1             | root        |
| ::1                   | root        |
| localhost             | myuser      |
| localhost             | root        |
| localhost.localdomain |             |
| localhost.localdomain | root        |
+-----------------------+-------------+

To check the current user, we can check with the following command.

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

From the other machine, you can can now access the sql using CLI or workbench.

mysql -h (ip-address) -u myuser -p

Personally, I like to use HeidiSQL in my Windows machine for accessing MySQL database remotely.

Access denied for user: 'root@xxx.xxx.xxx.xxx' (Using password: YES)

If you still cannot connect to you MySQL server, you may want to check the connection to your remote machine via telnet.

telnet (ip-address) 3306

Dev Lite Journal

Category: Database

Remote Access on MySQL Database