Hide menu

TDDD12 Database Technology

Setting up the Lab Environment


Step 1: Account and Password for the Database Server

To access the database server that we are providing for you to work on the lab assignments you need to have an account on that server. Notice that this database account is a separate account, different from your LiU account that you use to log into the computers of the university (or into Lisam).

Normally such a database account is created for you when you register for the course and you should be notified via e-mail. If you already have such a database account from a previous course, this account can be used also for this course.

If you have forgotten the password for your database account (or you do not anymore have the aforementioned notification email), you can request a new password (note that you need to be connected via VPN if you want to access that page from a computer that is not on the university campus). The lab assistant and the examiner cannot help you with password issues.

Step 2: Access the Database Server using a MySQL client program

You can use one of the following two programs to access the database server and interact with your database (issue SQL commands and run SQL scripts):

There are several different options for you to use any of these two programs, where the recommended ones are the options to start these programs on a computer in an SU room at the university (Options 1 and 4). If you are using your own computer, you need to be connected via VPN.

Option 1: MySQL Shell, if you are at a computer in an SU room at the university

Open a terminal window on the university computer and issue the following command to start the MySQL Shell and connect to the database server:

    mysql -h mariadb.edu.liu.se -u xxxyyy123 -p xxxyyy123
where xxxyyy123 has to be replaced by the login name of your database account, which should be your LiU ID. For instance, if your login name is dagso769 you would write:   mysql -h mariadb.edu.liu.se -u dagso769 -p dagso769

After issuing the command, you should see a password prompt. The password that you have to use here is the password for your database account (see Step 1 above). After entering your password, you should get access to the command line interface for writing SQL commands as illustrated below.

    Enter password: ********
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 110096 to server version: 5.5.32

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql>  

Option 2: MySQL Shell on your own computer

If you want to work on your own computer, you may download and install the MySQL Shell and use it to connect to the database server via an SSH tunnel. (Note, if you do not get this to work for some reason, Option 3 below may be an alternative)

Assuming you are connected via VPN, the SSH tunnel can be established in SSH GUI tools like Putty, or in the command line terminal like this:

    ssh -L 3307:mariadb.edu.liu.se:3306 xxxyyy123@ssh.edu.liu.se
where you have to replace xxxyyy123 by your LiU ID and, when asked for a password, you enter the password of your LiU account (i.e., not the password of your database account).

Next, open a second command line terminal on your computer (for Windows users, to open multiple command prompt windows in Windows 10, right-click the command prompt window icon in the Windows taskbar and select Command Prompt).

In this second terminal, you can start your MySQL Shell and connect to the database server through the SSH tunnel as follows:

    mysql -h 127.0.0.1 -P 3307 -u xxxyyy123 -p xxxyyy123
where xxxyyy123 has to be replaced by the login name of your database account, which may be your LiU ID. Now, you should see a password prompt. The password that you have to use here is the password for your database account (see Step 1 above). After entering your database password, you should get access to the command line interface for writing SQL commands.

If the above mysql command does not work on your computer because the mysql program is not available on your computer (which may be the case on Windows computers I have heard), then try the following command instead:

    mysqlsh --host 127.0.0.1 --port=3307 --user= xxxyyy123
where, again, xxxyyy123 has to be replaced by the login name of your database account. In this case, when you have arrived at the command line prompt of the MySQL Shell program, you need to first enter the command  \sql  in order to switch into SQL editing mode.

When you are done, don't forget to close the SSH tunnel (i.e., exit the SSH shell that was opened by the aforementioned SSH command with which you have established the tunnel).

Option 3: MySQL Shell on a university server if you are working on your own computer

If you want to work on your own computer by running MySQL Shell on a university server (because you cannot use Option 2 for whatever reason), then you have to open a remote access to a university server first. This can be done either by using ThinLinc or by using SSH (assuming you are connected via VPN). For instance, in the command line terminal you can use SSH with the following command:

    ssh xxxyyy123@ssh.edu.liu.se
where you have to replace xxxyyy123 by your LiU ID and, when asked for a password, you enter the password of your LiU account (i.e., not the password of your database account).

Once you are in a terminal on a university server (or in the SSH shell), you can execute the mysql command exactly as described under Option 1.

Option 4: MySQL Workbench, if you are at a computer in an SU room at the university

Start MySQL Workbench (for instance, by executing the command mysql-workbench in a terminal) and do the following.

  1. Open the "Database" menu in the menubar and select the menu item "Manage Connections"
  2. In the dialog window that pops up, click on "New" (in the bottom-left corner of the dialog), flip from the "Remote Management" tab to the "Connection" tab, and make sure that the "Connection Method" is set to "Standard (TCP/IP)" (which should be the case by default).
  3. Next, set the following configuration options.
    Hostname:   mariadb.edu.liu.se
    Username: xxxyy123 (put the name of your database account here, see Step 1 above)
    Default Schema:   xxxyy123 (put the name of your database account here as well)
  4. When you are done, click "Close". Now, there should be a new button for your newly configured connection in the main window of MySQL Workbench. After clicking on that button, you will be asked for a password to connect to the database server (mariadb.edu.liu.se). This is the password for your database account (see Step 1 above).
  5. Now, you might get a "Connection Warning" with a message such as the following: "Incompatible/nonstandard server version or connection protocol detected." If that's the case, ignore this warning and click "Continue Anyway."

Now you should see the main window of MySQL Workbench in which you can interact with your database.

Option 5: MySQL Workbench on your own computer

If you want to work on your own computer, you may install MySQL Workbench on your computer and use it to connect to the database server via an SSH tunnel. This tunnel is established automatically by MySQL Workbench (in contrast to the case of Option 6 below, in which you have to establish the SSH tunnel manually). To this end, download and install MySQL Workbench, start it, and do the following. (Note, if you do not get this to work for some reason, Option 6 below may be an alternative)

  1. Make sure you are connected via VPN.
  2. Open the "Database" menu in the menubar and select the menu item "Manage Connections".
  3. In the dialog window that pops up, click on "New" (in the bottom-left corner of the dialog), flip from the "Remote Management" tab to the "Connection" tab, and change the "Connection Method" to "Standard TCP/IP over SSH".
  4. Next, set the following configuration options.
    SSH Hostname:   ssh.edu.liu.se
    SSH Username: xxxyy123 (put your LiU ID here)
    MySQL Hostname:   mariadb.edu.liu.se
    Username: xxxyy123 (put the name of your database account here, which should be your LiU ID as well)
    Default Schema:   xxxyy123 (put the name of your database account here as well)
  5. When you are done, click "Close". Now, there should be a new button for your newly configured connection in the main window of MySQL Workbench. After clicking on that button, you will be asked for a password to connect to the SSH server (ssh.edu.liu.se). Use the password for you LiU account here; that is, the password that you typically use to log into the computers of the university (or into Lisam).
  6. Next, you will be asked for a password to connect to the database server (mariadb.edu.liu.se). This is the password for your database account (see Step 1 above).
  7. Now, you might get a "Connection Warning" with a message such as the following: "Incompatible/nonstandard server version or connection protocol detected." If that's the case, ignore this warning and click "Continue Anyway."

Now you should see the main window of MySQL Workbench in which you can interact with your database.

Option 6: MySQL Workbench on a university server if you are working on your own computer

If you want to work on your own computer by running MySQL Workbench on a university server (because you cannot use Option 5 for whatever reason), then you have to open a remote access to a university server first. This can be done either by using ThinLinc or by tunneling X11 through SSH (assuming you are connected via VPN).

  • Via ThinLinc: Inside ThinLinc you can start MySQL Workbench exactly like how you would do it on a computer in the SU rooms; for instance, by executing the command mysql-workbench in a terminal. Next, you can do exactly the same as described in Option 4 above.
  • Via SSH: You need to have a local X server running on your computer (which should be the case for Mac and Linux computers). Establish an SSH connection to the server ssh.edu.liu.se with the X11 forwarding option. This may be done by using an SSH GUI tool like Putty or a command line SSH client. For instance, the SSH command line client may be started like this:
      ssh -X xxxyyy123@ssh.edu.liu.se
    where you have to replace xxxyyy123 by your LiU ID and, when asked for a password, you enter the password of your LiU account (i.e., not the password of your database account). Once you have established the SSH connection (with X11 forwarding), you can start MySQL Workbench using the command mysql-workbench and, now, you can do exactly the same as described in Option 4 above.

Step 3: Test whether it works

You are now ready to execute SQL commands to interact with your database in the database server. To test that everything is working correctly you may try create a table "test" with an "id" field. This would be done a bit differently, depending on whether you are using MySQL Workbench or MySQL Shell.

If you use MySQL Workbench

Enter the following SQL statement in the main editor window (the big white one):

CREATE TABLE test(id int);
Next, execute this statement by clicking the icon with the little symbol of a lightning/thunderbolt; as an alternative to clicking this icon, you may use the menu item "Execute (All or Selection)" in the "Query" menu, or you simply use the corresponding keyboard shortcut <Shift>+<Control>+<Return>.

If you use MySQL Shell with the mysql command

Input the bold text below at the mysql> prompt:

mysql> CREATE TABLE test(id int);
Query OK, 0 rows affected (0.01 sec)

If you get the error message "No database selected," simply input the bold text below into the MySQL prompt:

mysql> use <your login name> ;
where <your login name> has to be replaced by the login name of your database account, which should be your LiU ID.

If you use MySQL Shell with the mysqlsh command

Input the bold text below at the mysql> prompt:

mysql> \sql
mysql> CREATE TABLE test(id int);
Query OK, 0 rows affected (0.01 sec)

If you get the error message "No database selected," do the same as described in the previous section.

Troubleshooting

If you have accidentally dropped your database and have trouble connecting to it again when using MySQL Workbench, you can restore the database by performing the following steps:

  1. Log into a university Linux system (either locally in one of the SU rooms, or remotely by using SSH or ThinLinc)
  2. Start a terminal (not needed if you log in remotely via SSH)
  3. Enter the following command:
    /opt/drs/restore_db.sh
This command starts a script that (re)creates a database with the same name as your user name (which is typically you LiU ID). When asked for a password, you need to provide the password for your database account (see Step 1 above).


Page responsible: Olaf Hartig
Last updated: 2020-03-28