Database

Overview

The GCCIS departments may provide database access to servers, virtual machines, etc. When using a database, it is important that they are accessed in a secure manner vs other programs that may be installed locally. Here we give examples of 2 software programs MySQL Workbench and JetBrains Datagrip for accessing a remote database.

These instructions are as of 10/2020

 

Datagrip

Database Support

  • MySQL
  • MariaDB
  • Many others

 

Creating a connection

  • Launch Datagrip (pre-installed in iSchool & IGM labs)

Datagrip instructions1.png

  • Click on the database tab on the far left margin
  • Click on the (+) sign to the right of the database tab (see above picture).
  • Under Data Source, click on MySQL (or the appropriate database that is being used)

Datagrip instructions2.png

  • In the screen that appears, enter the following information

Datagrip instructions3.png

    • Enter a name for the connection (ex. Solace)
    • Select the SSH/SSL connection and check "Use SSH Tunnel"
    • At the 3 dots to the right of SSH Tunnel, you will see any pre-created SSH tunnels you have created previously. Otherwise if this is a new connection, select the (+) sign in the top left to create a new connection.
    • Input your SSH credentials (i.e. how you access the remote SERVER -- ex. RIT username and password for solace) as shown below.

Datagrip instructions4.png

    • You can test the connection to ensure you can access the server. If this is the first time, accept the fingerprint and you should get a message saying test is successful.
    • Select OK and you should see the connection shown in the SSH Tunnel

Datagrip instructions5.png

  • Now that we can access the server, we need to setup the access to our database. Go to the General Tab and enter your MySQL username and password (it will not be the same as your RIT username and password!).

{{InfoNote| if you have not changed the MySQL credentials for solace, you will find them in the my.conf file in your home directory). Datagrip instructions6.png

  • Click apply and test your connection. Select OK and as you have tested your connections to the server and the database, you should now see this:

Datagrip instructions7.png

  • You have a solace connection and connection to the server and database

Datagrip instructions8.png

 

Use

A good tutorial is here for the basics of JetBrains Datagrip and working with databases. Additional video tutorial is here.

 

MySQL Workbench

Database Support

  • MySQL
  • MariaDB (newer versions have some limitations)

 

Creating a connection

  • Launch MySQL Workbench (pre-installed in iSchool & IGM labs)
  • Click on the plus symbol (+) to create a new connection

Mysqlworkbench instructions1.png

  • The following window will then appear

Mysqlworkbench instructions2.png

    • Add a connection name (ex. Solace DB Server)
    • Change connection method to Standard (TCP/IP) over SSH
    • Change SSH hostname to the server (ex. solace.ist.rit.edu)
    • Change SSH username to your RIT username (ex abc1234)
    • Change MySQL username to your RIT username (or whatever you were given to connect to the database with)
    • It will look like the below image after the changes have been made.

Mysqlworkbench instructions3.png

NOTE: If this is your 1st time connecting, the SSH connection may fail due to the fingerprint. It's okay, try again and it should succeed the second time

  • A new connection will then appear as shown

Mysqlworkbench instructions4.png

  • Click on the new connection and you will be prompted for your SSH Password...enter and click OK.

Mysqlworkbench instructions5.png

  • After successful SSH connection (i.e. you are able to access the server), you will be prompted to provide credentials to get to the database. This is NOT the same as your SSH password and if you have not changed it yet (you should), it can be found in the my.cnf file in your home directory on solace. Enter your MySQL password

Mysqlworkbench instructions6.png

  • If you followed these instructions and entered the correct passwords, you should now have access to your database and tables.

Use

A good tutorial is here for the basics of MySQL Workbench and working with databases. At a minimum, review the SQL Development Tool section.