MariaDB Tutorial / Build, Secure, Back Up, and Verify an Order Database

This final exercise builds an order database, checks an order-and-stock transaction through a restricted account, then backs up the committed state and verifies a separate restoration. Schema, business changes, privileges and recovery checks need independent confirmation rather than one generic SQL success message.

Define roles and observable completion criteria

Use a new learning database final48_lab, separate from real order or payment services. Build customers, products, orders and items, then commit new order 105 before backup. Separate the administrator, schema-capable learning account and restricted application account. A real application also needs input validation, parameters, authentication and idempotent request handling. Review transaction boundaries in Start InnoDB Transactions with COMMIT and ROLLBACK and programmatic value binding/error handling in Connect Python to MariaDB and Use Parameterized Queries. Restoration verification differs from replica-state diagnosis in Understand Replication, Lag, Errors, and Failover.

Check the MariaDB documentation for the syntax and conditions used here: mariadb dump, GRANT.

Create a dedicated database and complete initial fixture

As administrator, check that final48_lab, final48_restore and final48_app are not already in use. Choose different exercise names if necessary rather than overwrite them. Prepare the schema and rows, then grant course_user management access only to the learning database if needed.

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

Initial counts are four customers, four products, four orders and six items. Inspect foreign keys and quantity/price constraints, keeping current catalog price distinct from historical item unit_price.

Connect through an account limited to this workflow

As administrator, replace the sample password with a unique value and create this account. The defined workflow needs product reads, new order/item inserts and stock updates, not schema deletion, user management, price updates or order deletion:

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

Confirm CURRENT_USER() and a product read in the new session. An ungranted ALTER TABLE operation must be denied. Operational access can be narrower still through the actual table, column and role design.

Inspect and commit stock, order and item changes together

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

Run the next block only when reserved_rows is 1. On zero or an error, issue ROLLBACK; in this connection and do not insert the order or items.

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

For the untouched fixture, reserved_rows must be 1 and goods_total 7000.00. If stock reservation affects zero rows or any statement fails, stop before subsequent INSERTs and ROLLBACK. The known-input sequence is a supervised exercise; application code must implement those branches and cancel the whole failed attempt.

Only after the successful checks, choose COMMIT:

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

Stock is now 18, with five orders and seven items. Repeating identifier 105 causes a duplicate-key error. Design request deduplication instead of replacing it with IGNORE or REPLACE. A later ROLLBACK cannot undo this already committed operation.

Back up the committed state and prepare a separate restoration

Use a backup-capable course_user connection rather than the restricted application account. Prepare a new filename under C:/mariadb-lab, check the dump exit status and inspect the file for commands that switch databases. As administrator, create the unused restoration database and grant only its required learning access:

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

Connect to the empty target with source-file error stopping enabled, verify DATABASE and SHOW TABLES, then execute the trusted file:

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

The mariadb-dump conditions apply to transactional snapshots and concurrent DDL; the restore guide covers client compatibility and partial failure. This database dump does not restore all users or server settings. Do not automatically redirect the application account to the test restoration.

Ubuntu shell alternative: choose this or the Windows PowerShell version. The subshell confines the restrictive umask and noclobber settings to this backup operation.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

An existing destination causes failure rather than overwrite. Do not restore a partial file after a failed command; diagnose it and retry with a new filename. Size and SHA-256 provide evidence, not proof of recoverability.

For Ubuntu restoration, open the same target using the following command, then replace the SOURCE path with the actual login name and absolute file path from your backup.

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

Run only one platform’s restoration example for a target. The source-error option stops later statements in the file; it does not undo objects or committed changes created earlier. After a failure, correct the cause and retry against a newly prepared empty target.

For a TLS connection error, inspect server/client versions, certificate names, the trusted CA and any required client certificate using the official TLS connection requirements. Apply the appropriate option file or certificate options to backup and restoration clients too; do not routinely disable verification to bypass an error.

Compare source values, restored values and denied operations

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

Expected counts are 4, 4, 5 and 7, paid goods 46000.00 and product 1 stock 18. Compare keyed row values, constraints and important JOIN results, not only a success status or matching counts. Separately verify rejection of invalid foreign keys, duplicate keys and unauthorized changes.

Keep versions, dump hash, validation queries/results and privilege scope. Close exercise sessions and review ownership and retention before removing accounts or databases. This article does not automatically delete the source, backup or verification database.