Build a Reproducible Course Lab with Sample Data, Accounts, and Safety Conventions

Learning outcomes

A database course becomes risky when every exercise uses root, every schema has a generic name such as test, and no one knows how to reset the environment. The final lesson of Chapter 1 fixes that before later chapters introduce locks, backups, replication, failover, and destructive administration exercises.

The course domain: ServiceHub

We will use ServiceHub, a fictional equipment-maintenance platform, as the MySQL course domain. Multiple technicians work at customer sites, assets require maintenance, dispatchers create work orders, and applications/reporting jobs connect to a central MySQL server. That makes the domain useful later for transactions, indexes, deadlocks, backup, replication, observability, and high availability.

Start with only three tables. The schema will evolve as later chapters introduce richer types, constraints, generated columns, indexing, JSON, stored programs, and operational metadata.

Separate owner/admin work from application work

Using root for the application makes every authorization test meaningless and increases blast radius. Instead, use a privileged bootstrap account only to create the lab boundary. Then create two dedicated accounts:

• servicehub_lab_admin — a local course administrator with broad privileges only inside the servicehub_lab schema, used for schema/migration exercises.
• servicehub_app — an application account limited to the data operations the fictional application needs.

In production, privileges should be designed from real tasks and often assigned through roles. For Chapter 1, schema-scoped accounts make the principle visible without introducing the entire MySQL privilege system at once.

Create the lab schema and accounts

Connect with a privileged local bootstrap account. Replace the angle-bracket password markers with disposable lab passwords when you execute the statements; do not commit those secrets to Git.

CREATE DATABASE servicehub_lab  CHARACTER SET utf8mb4  COLLATE utf8mb4_0900_ai_ci;CREATE USER 'servicehub_lab_admin'@'127.0.0.1'  IDENTIFIED BY '<ADMIN_LAB_PASSWORD>';CREATE USER 'servicehub_app'@'127.0.0.1'  IDENTIFIED BY '<APP_LAB_PASSWORD>';GRANT ALL PRIVILEGES ON servicehub_lab.*  TO 'servicehub_lab_admin'@'127.0.0.1';GRANT SELECT, INSERT, UPDATE, DELETE  ON servicehub_lab.*  TO 'servicehub_app'@'127.0.0.1';SHOW GRANTS FOR 'servicehub_lab_admin'@'127.0.0.1';SHOW GRANTS FOR 'servicehub_app'@'127.0.0.1';

The application account has no CREATE, ALTER, or DROP privilege. The lab administrator is powerful inside one disposable schema but is not granted global *.* privileges. This is a simple blast-radius reduction.

Create the first schema version

Run the next script as servicehub_lab_admin. The definitions are intentionally conservative; Chapter 3 will revisit MySQL data types, keys, constraints, SQL modes, and generated columns with much more precision.

USE servicehub_lab;CREATE TABLE sites (  site_id BIGINT PRIMARY KEY AUTO_INCREMENT,  site_name VARCHAR(120) NOT NULL,  city VARCHAR(80) NOT NULL) ENGINE=InnoDB;CREATE TABLE assets (  asset_id BIGINT PRIMARY KEY AUTO_INCREMENT,  site_id BIGINT NOT NULL,  asset_tag VARCHAR(64) NOT NULL,  asset_type VARCHAR(80) NOT NULL,  CONSTRAINT uq_assets_asset_tag UNIQUE (asset_tag),  CONSTRAINT fk_assets_site    FOREIGN KEY (site_id) REFERENCES sites(site_id)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY AUTO_INCREMENT,  asset_id BIGINT NOT NULL,  summary VARCHAR(200) NOT NULL,  status VARCHAR(24) NOT NULL DEFAULT 'OPEN',  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  CONSTRAINT fk_work_orders_asset    FOREIGN KEY (asset_id) REFERENCES assets(asset_id)) ENGINE=InnoDB;SHOW TABLES;SHOW CREATE TABLE work_orders;

Specifying ENGINE=InnoDB makes the lab’s storage-engine assumption explicit even though InnoDB is the default in current MySQL. The schema uses foreign keys so later exercises can observe transaction and integrity behavior on related data.

Seed with small, inspectable data

Good course data is large enough to express relationships but small enough that you can manually reason about every row in Chapter 1. Later performance chapters will generate larger datasets deliberately.

USE servicehub_lab;INSERT INTO sites (site_name, city) VALUES  ('North Plant', 'Baku'),  ('Harbor Workshop', 'Baku');INSERT INTO assets (site_id, asset_tag, asset_type) VALUES  (1, 'PUMP-001', 'Centrifugal Pump'),  (1, 'MOTOR-014', 'Induction Motor'),  (2, 'CRANE-003', 'Workshop Crane');INSERT INTO work_orders (asset_id, summary, status) VALUES  (1, 'Inspect seal leakage', 'OPEN'),  (2, 'Measure bearing vibration', 'OPEN'),  (3, 'Quarterly safety inspection', 'PLANNED');SELECT * FROM sites ORDER BY site_id;SELECT * FROM assets ORDER BY asset_id;SELECT * FROM work_orders ORDER BY work_order_id;

If you rerun this seed file without resetting the schema, the auto-increment values and duplicate logical data will change. That is why a course needs an explicit reset contract rather than “just run the inserts again.”

Positive authorization test: prove the app can do its job

Open a new session as the application account. Use -p so the client prompts for the password.

mysql -h 127.0.0.1 -P 3306 -u servicehub_app -p servicehub_lab

SELECT CURRENT_USER(), DATABASE();SELECT w.work_order_id, a.asset_tag, w.summary, w.statusFROM work_orders AS wJOIN assets AS a ON a.asset_id = w.asset_idORDER BY w.work_order_id;UPDATE work_ordersSET status = 'IN_PROGRESS'WHERE work_order_id = 1;SELECT work_order_id, statusFROM work_ordersWHERE work_order_id = 1;

The successful SELECT and UPDATE prove those actions are authorized for this account in this schema. They do not prove the account is generally “secure”; network exposure, password policy, TLS, secret storage, host matching, and role design remain separate concerns.

Negative authorization test: failure is part of verification

Now deliberately ask the application account to perform schema administration it was not granted.

DROP TABLE work_orders;

On a correctly configured lab, MySQL should reject the statement with a permission error indicating that DROP is denied for the application account. The exact text can vary with version and connection details, so verify the SQLSTATE/error category and then prove the table still exists.

SHOW TABLES;SELECT COUNT(*) AS work_order_count FROM work_orders;SHOW GRANTS;

This is a powerful security testing pattern: define an allowed action and a forbidden action, execute both, and verify state afterward. “We issued a GRANT statement” is weaker evidence than positive and negative authorization tests.

Observe identity and transport before trusting the lab

Every future lab should begin with a small environment check when the consequences matter. This prevents you from running a destructive script against the wrong server or account.

SELECT VERSION() AS server_version,       @@hostname AS server_hostname,       @@port AS server_port,       CONNECTION_ID() AS connection_id,       USER() AS client_identity,       CURRENT_USER() AS authenticated_account,       DATABASE() AS current_schema,       @@default_storage_engine AS default_storage_engine;SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW GRANTS;SELECT COUNT(*) AS sites FROM servicehub_lab.sites;SELECT COUNT(*) AS assets FROM servicehub_lab.assets;SELECT COUNT(*) AS work_orders FROM servicehub_lab.work_orders;

A non-empty Ssl_cipher value indicates this session negotiated TLS. An empty value can occur in local configurations or local socket use. Do not infer a production transport-security policy from one development session; Chapter 11 builds a proper TLS/authentication/authorization model.

Reset, backup/export, and cleanup conventions

Use predictable filenames so later chapters can automate setup and failure recovery without guessing. The directory belongs to the course workspace, not the MySQL data directory.

mysql-course/  environment.md  Chapter01/    00_bootstrap.sql    01_schema.sql    02_seed.sql    03_verify_environment.sql    90_reset.sql    99_cleanup.sql  exports/  backups/  logs/  notes/

For now, “reset” means drop and recreate only the disposable servicehub_lab schema. Later backup chapters replace ad-hoc exports with explicit consistency and restore requirements.

DROP DATABASE IF EXISTS servicehub_lab;CREATE DATABASE servicehub_lab  CHARACTER SET utf8mb4  COLLATE utf8mb4_0900_ai_ci;-- Then rerun 01_schema.sql and 02_seed.sql.

DROP DATABASE IF EXISTS servicehub_lab;DROP USER IF EXISTS 'servicehub_app'@'127.0.0.1';DROP USER IF EXISTS 'servicehub_lab_admin'@'127.0.0.1';

Before executing cleanup, verify @@hostname, @@port, CURRENT_USER(), and the object names. A safe lab makes destructive intent obvious.

A wrong approach: “grant everything so the tutorial works”

When a permission error blocks a lab, the fastest-looking fix is GRANT ALL ON *.*. That destroys the security model and teaches the wrong diagnostic habit. A permission failure should trigger a question: what exact operation is the account supposed to perform, on which objects, and at what scope?

For ServiceHub, the app reads and modifies rows; schema migration is an administrative concern. Keeping those responsibilities separate lets you detect accidental DDL from application code and reduces the impact of a compromised application credential.

Chapter 1 lab acceptance checklist

Production judgment: lab safety scales into operational safety

The habits in this small lab are production habits in miniature: dedicated identities, least privilege, environment verification, explicit object scopes, reproducible migrations, seed/test data separation, version records, safe cleanup, and proof that denied actions are actually denied. Production adds secret managers, TLS policy, role governance, audit, backup/restore, change control, monitoring, HA, and incident response—but the reasoning is the same.

Chapter 2 now has a stable target: one known MySQL server and one disposable ServiceHub database. You will use it to understand how mysqld starts, where configuration comes from, how sessions consume state, and how server defaults become operational policy.

References

• MySQL 8.4 — CREATE USER
• MySQL 8.4 — GRANT
• MySQL 8.4 — SHOW GRANTS
• Specifying MySQL account names
• Connection verification and access control