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:
Change the user for the content to
Start tracking the
Add a startup script
Set up your database with the starting state
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!
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
This will address a number of permission errors you would encounter if you used the
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!
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.
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.
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');
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!
There are a few other things you may want to consider for your database content.
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.
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.
Finally, we recommend you increase the size of your sandboxes for MySQL content, as it consumes a large amount of memory.
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!