Persisting Database States (MySQL)

When creating content for teaching databases or SQL, it may not initially be clear how the state of the database can be persisted between sessions. Since Next Tech does not persist the state of an entire sandbox automatically, there's a little configuration required to get things up and running!

To persist the state of a database, we will do the following in this guide:

  1. Change the user for the content to root

  2. Start tracking the /var/lib/mysql directory

  3. Add a startup script

  4. Set up your database with the starting state

  5. Save your database state

We'll also cover a few other things you may want to consider when creating content for teaching databases.

Let's dive right in!

Initial Setup

Changing the User

To get started, create a content and select a MySQL stack (version 8 is recommended).

On the same screen, scroll down to the Change User section and select the root user:

This will address a number of permission errors you would encounter if you used the nt-user user.

As you can see, using the root user results in slightly slower sandbox load times. This is because the default user (nt-user) needs to be changed to the new user, which takes a few seconds.

Once you've confirmed the change, the page will reload automatically and load a sandbox for you with the root user set up!

Tracked Directories

Next up, navigate to the settings sidebar and scroll down to the Directories section. Add a new one for /var/lib/mysql. This is the default "data directory" for MySQL, and where the changes made to a database are stored.

Each time you click Save at the top right of the page, the current state of the database will be saved. We'll cover this more in a moment.

Startup Script

Further up the settings sidebar, you'll find the field for the startup script. Add the following to it:

sudo systemctl restart mysql

This is required for your user's databases to work correctly. Otherwise, the files from your database and the current state of their MySQL system process will not align.

That wraps up the initial set up! Let's move on to the initial set up of your database.

Database Starting State

Set Up

Now that you've created your database, you can add some data to it. The recommended way to do this is using the mysql prompt. To get started, type mysql into the terminal:

From there, you can run any SQL code you'd like to set up your database. For example, the following will create a database, a table, and then add a couple records to the table:

CREATE DATABASE company_db;
USE company_db;
CREATE TABLE jobs (
job_code VARCHAR(3),
job_description VARCHAR(25),
PRIMARY KEY(job_code)
);
INSERT INTO jobs VALUES('1','Programmer');
INSERT INTO jobs VALUES('2','Systems Analyst');

Saving

Once you've done this, it's time to save the database state! Just click the Save button at the top right of the screen and wait for it to finish. It'll take a little longer than usual because it's backing up the entire database.

To test that it's worked, just click the arrow at the top right of the screen to launch a preview of your content. If everything works, you'll be able to enter mysql in the terminal and run some commands, for example:

If you're running into issues, we're here to help!

Other Considerations

There are a few other things you may want to consider for your database content.

Terminal Set Up

If you'd like to drop your users directly into a MySQL shell when they launch their project, simply change the terminal's start command to mysql. You can do this by clicking the three blue sliders at the bottom of the terminal tab.

If your users are only going to be interacting with MySQL via the terminal, you may want to use the single pane layout as well.

SQL Results Viewer

If you want to avoid having your users use the command line, you can combine the SQL Results Viewer with a SQL file to give them a more user friendly interface.

Larger Sandboxes

Finally, we recommend you increase the size of your sandboxes for MySQL content, as it consumes a large amount of memory.

Conclusion

Giving your users a predictable database state is important so they don't waste valuable learning time with set up. This guide outlines a way to do this. If you have any questions, drop us a note!