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.

  1. 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';
  2. Grant privileges for this new user
    GRANT ALL ON *.* TO 'myuser'@'localhost';
    GRANT ALL ON *.* TO 'myuser'@'%';
    FLUSH PRIVILEGES;
    FLUSH HOSTS;
    
  3. 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
  4. 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.

database
source: http://www.heidisql.com/screenshots.php?which=database



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
Advertisements

One thought on “Remote Access on MySQL Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s