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

This FAQ was last modified on: Monday, March 12, 2018 05:19pm
Category: 

While you may always access your database from the Plesk panel with phpMyAdmin, sometimes site admins prefer  to use a graphical user interface tool for administering their MySQL databases. This can be useful when fixing a corrupt database or updating a schema during a software upgrade. These include tools llike Navicat, MySQL Workbench, or SQLManager for MySQL. You may also connect from the command line.

  1. ‚ÄčHow do I access my database using my favorite MySQL admin tool?
    1.  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.
      1.  websites & domains
      2.   datatbase users
    2. Example: Using MySQL Workbench
      1.   

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

    3.   

      Open MySQL Workbench and click on Create New Connection Icon

    4. MySQL connections
    5.   

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

    6.  ssh settings
    7. Start to input your connection info.
      1. Connection Name: [Any name that makes sense to you for this connection]
      2. Select SSH method
      3. 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.)
      4. Leave SSH key file as blank
      5. MySQL Hostname: Leave it as default localhost IP which is 127.0.0.1
      6. MySQL Server Port: Leave is as default port which is 3306
      7. 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
    8. In the config file, you should find database array with MySQL username and password. 
      1. user name password from config file
      2. finish and click ok
      3. Click ok.  MySQL Workbench will create your new connection Thumbnail on the home screen.  You can click on the thumbnail to connect to your MySQL 
      4. Done!
  2. How do I access my database from the command line in ssh sessions? (Advanced):
    1. The command to set up the forwarding from the command line would be like the following from an ssh session:
      1. ssh -L 3306:127.0.0.1:3306 s1nan@s1.nan.oit.gatech.edu -N
        1. Replace "s1nan@" with "<the sftp/system user name for your site>@" & "s1.nan.oit.gatech.edu" with <your site host name>
    2. That will leave the ssh command still running, but you won't get a command prompt.
    3. You will need to have mysql installed on your local machine, (but do not run the mysql server locally), and then in a new command line window, run the following command to connect to the database:
      1. mysql -h 127.0.0.1 -P 3306 -D drux_s1nanoi -u drux_s1nanoi8 -p
        1. Replace "drux_s1nanoi" with <your site's database name>
        2. Replace "drux_s1nanoi8" with <your site's database user name>
      2. Enter the password and you will be at the mysql prompt for your database
      3. When you're finished, go back to the window where you have the ssh command running, and ctrl+C to quit it.