5^th Practical Class:
MySQL Connection for Local Backend Development

For local development of backend you will need access to MySQL database.

Local development means not using 4IT580 server and Atom with Remote FTP plugin, but running Node.js and backend code on your computer.

To access MySQL have two basic options:

• Option 1: Install local MySQL database
• Option 2: Connect to MySQL database on 4IT580 server

Option 1: Install local MySQL database

This option could be a bit more work and requires some setup. Advantage is that you will be able to work offline.

• install MySQL
  • download for Windows
  • on macOS / Linux use your favorit package manager
  • alternative installation is using Docker, but this is out of scope of this guide
• create new database, user and password and set it properly in your backend/.env file (see section Edit .env file below)

Option 2: Connect to MySQL database on 4IT580 server

This option requires no installation on local computer and no configuration. So it's a bit faster and easier to setup.

4IT580 server is behind firewall, so it's not possible to access MySQL directly.

Following connection is not possible:

But you can create SSH tunnel that will forward connection from local port on your computer to remote port on 4IT580 server.

This way you can connect to MySQL securely and go around limitations of firewall. Connection will look like this:

SSH tunel from local to remote server (Option 2 setup)

On macOS or Linux

Go to terminal and run following command:

(replace USERNAME with your username)

Now your are connected to SSH on server and tunnel is created in the background.

SSH tunnel is working as long as this SSH session is connected to 4IT580 server.

Windows

Use PuTTY.

• go to Connection -> SSH -> Tunnels
• add new forwarded port with following settings:
  see screenshot (Source port in screenshot is different, use 4242!):
  
• connect to 4IT580 server in PuTTY as usual

Now your are connected to SSH on server and tunnel is created in the background.

SSH tunnel is working as long as this PuTTY session is connected to 4IT580 server.

Edit .env file (Option 1&2, all platforms)

Create backend/.env.local based on backend/.env file.

• don't change PORT value in .env.local (this may break the server)
• structure of backend/.env.local should look like this:
• if you are using
  • SSH tunnel
    • set DB_NAME, DB_USER, and DB_PASSWORD to value you received in email
    • also use following:
  • local installation of MySQL
    • set DB_NAME, DB_USER, and DB_PASSWORD to value you created
    • also use following:

Remeber to kill and start again the backend server when you change .env values (it will not auto-refresh when .env is changed)!

For more info about backend server see it's README.md file.

Now you can work on backend from your computer!

Edit MySQL Data

• for local MySQL server:
  • use MySQL Workbench or any other tool you like
• for MySQL on 4IT580 server:
  • use 4IT580 phpMyAdmin
  • it works for both personal and team project databases
  • if you don't like phpMyAdmin, you can use MySQL Workbench too
    • it has direct support for SSH tunnle
    • you have to select Connection Method: Standard TCP/IP over SSH