Remote Connect to MySQL Database Using 'MySQL Query Browser'

There was a recent requirement for giving remote access to the MySQL database to our ERP consultant for him to connect from home and do the work. Our MySQL database was running on our cloud “Omegha” and was by default restricted of any remote access.

Instead of asking the consultant to use remote SSH using PuttY, we decided to go with a better GUI client for MySQL databases.

Below are the steps used for enabling remote access to the MySQL database.

Note: - Strictly not recommended for any production environment with critical data. This is a temporary requirement and will be reverted by actual security settings once the work is done.

After writing my book on “Oracle SQL Developer 4.1”  I’ve understood non-DBAs are more comfortable with GUI clients like “Oracle SQL Developer” or “MySQL Query Browser”, In fact it is true that such beautiful free tools, which normally people don’t care about make the work quite faster with its rich features.

Step-1.  Download MySQL Query Browser using this link

Note: - Please note that development of MySQL Query Browser has been discontinued. 
MySQL Workbench provides and integrated GUI environment for MySQL database design, SQL development, administration and migration. Download MySQL Workbench »

I was ok with “MySQL Query Browser” as it served my temporary purpose

Step-2.  Start the “MySQL Query Browser” once it is installed, and create a new connection to your remote MySQL Database.

Fill in the details of your database like hostname, port, username pwd etc

Step-3.  Connect to the new connection using the login screen as shown below. Her comes my first error

Note- MySQL Error Number 2003, But my ping to the server was working fine.

Step 4.  Connected to my database using PuttY and checked if I am really able to connect to the database or not. It was perfect!!!

ubuntu@Omegha-bcp:~$ mysql -uroot -p***

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 37

Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.02 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

Step 5. Opened the my.cnf file under /etc/mysql directory as root user.

ubuntu@Omegha-bcp:~$ sudo su -

sudo: unable to resolve host bcp-instance

root@bcp-instance:~# cd /etc/mysql

root@Omegha-bcp:/etc/mysql# vi my.cnf

Step6. Changed the bind-address parameter to the public IP of the machine and commented the default entry of 127.0.0.1

#

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

#bind-address           = 127.0.0.1

bind-address            = xx.xx.xx.xxx

Step7. Restarted the mysql service

root@Omegha-bcp:/etc/mysql# service mysql restart

mysql stop/waiting

mysql start/running, process 1946

root@Omegha-bcp:/etc/mysql#

Step8. Here comes my next error when I tried connecting.

Note- MySQL Error Number 1130

                    

Step9. Again connected to my MySQL database and granted privileges to all the host (It includes any remote hosts too, Highly not recommended for prod envs) as shown below.

root@Omegha-bcp:/etc/mysql# mysql -uroot -p***

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 38

Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.* to 'root'@'%' identified by '***';

Query OK, 0 rows affected (0.03 sec)

mysql>

Step 10. Tried to make connection again using the “MySQL Query Browser” client, worked like charm J

HAPPY LEARNING!