Persisting Database States (MySQL)

Search…
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:
1
sudo chown -R mysql:mysql /var/lib/mysql/
2
sudo systemctl restart mysql
Copied!
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:
1
CREATE DATABASE company_db;
2
​
3
USE company_db;
4
​
5
CREATE TABLE jobs (
6
  job_code        VARCHAR(3),
7
  job_description VARCHAR(25),
8
  PRIMARY KEY(job_code)
9
);
10
INSERT INTO jobs VALUES('1','Programmer');
11
INSERT INTO jobs VALUES('2','Systems Analyst');
Copied!
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!