Skip to main content

MySQL

Connect to MySQL​

Before accessing starting, ensure you have been provided with MySQL credentials. If you do not have the credentials or cannot see the desired database, please contact the software team.

Our MySQL server is hosted on Google Cloud and for security reasons, it is not reachable from the public internet. In order to connect to the server, you must use one of the following methods.

Morgan Solar Office or VPN​

If you are in the Morgan Solar office or connected to the Morgan Solar VPN, you don't need to do anything special to connect to the MySQL server as the IP address is white-listed. You can use any MySQL client to connect to the server but we recommend MySQL Workbench (see below for details). Configure the client as follows:

  • Hostname: mysql.morgansolar.xyz
  • Port: 3306
  • Username: provided by the software team
  • Password: provided by the software team

Google Cloud SQL Proxy​

Another option is to use the Google Cloud SQL Proxy. This is a small program that runs on your computer and creates a secure tunnel to the MySQL server. This is the preferred method if you are not in the Morgan Solar office or connected to the Morgan Solar VPN. The proxy is available for Windows, Mac, and Linux. You can download the proxy from here. For authentication, you will also need to download the Google Cloud CLI.

Installation​

  • Download and install Google Cloud CLI. Instructions here
  • Login to Google Cloud CLI using your Morgan Solar account and configure the default project and quota project
gcloud auth login
gcloud auth application-default login
gcloud config set project msi-meerkat
gcloud auth application-default set-quota-project msi-meerkat
  • Download the proxy from here
  • Unzip the proxy and move it to a safe directory
  • Open a Command Prompt or Power Shell terminal and execute the proxy using the following command
C:\path\to\cloud-sql-proxy.exe --address 0.0.0.0 msi-meerkat:northamerica-northeast1:meerkat-v12

As long as the terminal is open and the command is running, the proxy will be listening on port 3306 on your computer. You can connect to it using any MySQL client. We recommend MySQL Workbench (see below for details). Configure the client as follows:

  • Hostname: localhost
  • Port: 3306
  • Username: provided by the software team
  • Password: provided by the software team

NOTE: For convenience you can create a shortcut with target C:\Windows\System32\cmd.exe /k "C:\path\to\cloud-sql-proxy.exe --address 0.0.0.0 msi-meerkat:northamerica-northeast1:meerkat-v12"

JupyterLab​

If you've been granted access to GitHub, you can access our JupyterLab instance, which has MySQL support built in. JupyterLab is a web application that provides an interactive development environment for data science. You can find the JupyterLab instance at jupyter.morgan.solar. Credentials for MySQL are provided automatically on login, and the ipython-sql is installed by default. To access MySQL data from Jupyter, first create a Python notebook and then use the %sql magic within a cell to run queries.

%sql SELECT name, code, timezone FROM site

Queries can also be converted to pandas DataFrames for further processing in Python.

res = %sql SELECT name, code, timezone FROM site
df = res.DataFrame()
df

More documentation for ipython-sql can be found here