Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Tuesday, May 17, 2016

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!


No comments:

Post a Comment

Thanks for you valuable comments !