How do I access my database using my favorite MySQL admin tool?

This FAQ was last modified on: Wednesday, February 17, 2016 04:19pm
Category: 

In many cases site admins prefer to use various GUI tools for adminstering their MySQL databases. This can be useful for fixing a corrupt database or updating a schema during a software upgrade. These include tools such as Navicat, MySQL Workbench, or SQLManager for MySQL.

The issue OIT faces with these tools is that they require unfiltered access to the default MySQL access port (3306). In order to allow these tools to work the firewalls would need to be open to any host on campus. This is considered a security risk so port 3306 is only allowed from the server the web applications actually run on (web-plesk1.gatech.edu in this case).

There is a work around though. This involves using SSH port forwarding to create a secure connection between the database server and the site administrators local machine through web-plesk1 using the standard site accounts that administrators are given when their site is created.  

Before doing this, you will want to ensure you are on a campus network by utilizing the Georgia Tech VPN service. Please search for "VPN" FAQs in the search bar above to find the method most suitable for your system.

Here is an example of what the SSH command would look like on a UNIX computer:

ssh -L3306:web-db1.gatech.edu:3306 foo@webHosting.gatech.edu

where "foo" is your ssh (or "Local FTP") account on webHosting. What that command does is take any network traffic sent to the local machine on port 3306 and securely forward it through webHosting.gatech.edu to web-db1.gatech.edu. To access the database simply tell your tool to connect to "localhost" on port 3306.

The equivalent using Putty looks something like this:

Putty configuration screen for connecting to the database on web-db1.gatech.eduPutty configuration for the SSH tunnel

Connecting with MySQL

How do I access my database using my favorite MySQL admin tool?

 Example of Control Panel Interface of the website you have created. From here, you will be able to get all necessary information to use to connect to MySQL database via SSH.

 websites & domains

  datatbase users

Example: Using MySQL Workbench

 

  1.    

    If you are outside of campus, make sure you connect to the campus network using VPN service

  2.   

    Open MySQL Workbench and click on Create New Connection Icon

  3. MySQL connections
  4.   

    It will open a New Connection window. Select SSH method.

  5.  ssh settings
  6. Start to input your connection info.
  7. Connection Name: [Any name that makes sense to you for this connection]
  8. Select SSH method
  9. SSH Username and Password: [This will be the username and password of the web admin you had set when you first requested for a website.)
  10. Leave SSH key file as blank
  11. MySQL Hostname: Leave it as default localhost IP which is 127.0.0.1
  12. MySQL Server Port: Leave is as default port which is 3306
  13. Database Username and Password.  If you only know the database username, but not the password.  You can find the password by FTP to your remote site and look for web config file.
    1. DRUPAL: httpdocs/sites/default/settings.php
    2. WORDPRESS: public_html/mywebsite/wp-config.php
  14. On the config file, you should database array with MySQL username and password. 
  15. user name password from config filefinish and click ok
  16. Cick ok.  MySQL Workbench will create your new connection Thumbnail on the home screen.  You can click on the thumbnail to connect to your MySQL 
  17. Done!