Linux Advanced 09 — MariaDB Service Accounts and Restore Verification

Linux course index · Linux Advanced category · Lesson 9 of 9

AI disclosure — AI-generated content, posted as is. AI can make mistakes. This lesson draws on the linked Open Education posts and adds original explanations or lab examples where useful. The newly written explanations and examples may be freely reused. Linked source material retains its own terms.

Learning goal

Check a Linux-hosted MariaDB service, identify least-privilege database roles, and test a logical restore.

Concepts and setup

The archive’s RHCE notes cover MariaDB packages, service operation, configuration, accounts, and backup. The server is a daemon, while the client sends it queries. Distribution packages vary in service names and default authentication. Use only necessary packages and keep the database off untrusted networks.

Use a disposable VM and its authorized local administration account. Real applications should use dedicated accounts with only required privileges. This fixture uses two unused schemas and InnoDB. It is not a full server backup.

Check the service and create a test database

systemctl is-active mariadb
sudo mariadb <<'SQL'
CREATE DATABASE oe_linux_demo;
CREATE TABLE oe_linux_demo.checks (
  id INT PRIMARY KEY,
  status VARCHAR(15) NOT NULL
) ENGINE=InnoDB;
INSERT INTO oe_linux_demo.checks VALUES (1,'ready'),(2,'verified');
SELECT id, status FROM oe_linux_demo.checks ORDER BY id;
SQL

Possible output

active
id  status
1   ready
2   verified

If the distribution does not enable socket authentication for sudo mariadb, use the lab’s authorized local authentication path. Do not put credentials into command history or lab reports.

Define a read-only role without creating a login

sudo mariadb <<'SQL'
CREATE ROLE oe_linux_reader;
GRANT SELECT ON oe_linux_demo.checks TO oe_linux_reader;
SHOW GRANTS FOR oe_linux_reader;
SQL

Possible output

Grants for oe_linux_reader
GRANT USAGE ON *.* TO `oe_linux_reader`
GRANT SELECT ON `oe_linux_demo`.`checks` TO `oe_linux_reader`

The exact display varies. The role defines scoped read access but is not itself the application’s login. In a real deployment, configure an approved application account, grant the role, and verify both an allowed SELECT and a denied write from that account. Remove the lab role when retiring the two fixture schemas.

Export locally and verify a separate restore

lab=$(mktemp -d)
( umask 077; sudo mariadb-dump --single-transaction oe_linux_demo > "$lab/demo.sql" )
sudo mariadb -e 'CREATE DATABASE oe_linux_restore;'
sudo mariadb oe_linux_restore < "$lab/demo.sql"
sudo mariadb -N -e 'SELECT id,status FROM oe_linux_restore.checks ORDER BY id;'
sudo mariadb -N -e 'SELECT COUNT(*) FROM oe_linux_restore.checks;'

Possible output

1  ready
2  verified
2

Stop at any error. The transaction helps for InnoDB data, not all engines and object types. Avoid concurrent schema changes. After verification, retire only your fixture schemas and securely handle the dump.

Questions only

Quick-check questions

  1. Why restore into a different schema?
  2. Can a running service prove the dump is valid?

Assessment questions

  1. Name two checks that provide evidence of recovery beyond a dump file’s size.
  2. What rights would you give a reporting application account?
  3. Why can –single-transaction be insufficient for a mixed-engine database?

Questions and answers

Quick-check answers

  1. Why restore into a different schema?
    Answer — It avoids modifying the original and gives a separate verification target.
  2. Can a running service prove the dump is valid?
    Answer — No. Test an actual restore and query it.

Assessment answer key

  1. Name two checks that provide evidence of recovery beyond a dump file’s size.
    Answer — Compare expected row counts and known rows or checksums, then test representative application queries.
  2. What rights would you give a reporting application account?
    Answer — Only the required SELECT rights on specified tables or schemas, with appropriate account location and authentication restrictions.
  3. Why can –single-transaction be insufficient for a mixed-engine database?
    Answer — Nontransactional engines do not share the same consistent transaction snapshot. Concurrent changes to schema definitions can also invalidate assumptions.

Source articles on this site

Continue the course

Previous lesson 08 — Apache Virtual Hosts and HTTPS. This is the last lesson in this level. See the course index for the next level.

Linux course index · Linux Advanced category · Lesson 9 of 9

Leave a Reply