Labs · Cyber Security

Lab: Back Up and Restore a MySQL Database with mysqldump, Then Remove an Unused User

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we manage the database for a Razorpay-style payments demo app in our training batch. A teammate runs DELETE FROM products; without a WHERE and every row is gone. If we have a fresh backup and we have practised restoring it, this is a 2-minute fix. If not, it is a very bad day. We also remove the old report user, because the dashboard project has ended.

Goal of this lab

By the end you will have:

• A backup file shop-backup.sql made with mysqldump, readable only by you.
• Restored the shop database after a deliberate mistake and checked the rows.
• Listed all database users and dropped one you no longer need.

What you need (all free)

• Your EC2 server with MariaDB and the shop database from Lab 10. (No server? Do Part 1 and 2 of Lab 10 first.)
• 30–35 minutes.

Steps

1. SSH to the server and count the rows now:

   sudo mariadb -e "SELECT COUNT(*) FROM shop.products;"
   

   👁 What you should see: 5 (or more, if you added rows).

2. Make a folder only you can open, and take the backup:

   mkdir -p ~/db-backups && chmod 700 ~/db-backups
   sudo mysqldump --databases shop --single-transaction > ~/db-backups/shop-backup.sql
   chmod 600 ~/db-backups/shop-backup.sql
   ls -l ~/db-backups
   

   --databases shop adds the CREATE DATABASE line, and --single-transaction gives a consistent copy without locking the tables.

   👁 What you should see: -rw------- 1 ec2-user ec2-user ... shop-backup.sql.

3. Look inside the backup; it is plain SQL text:

   grep -E "CREATE TABLE|INSERT INTO" ~/db-backups/shop-backup.sql
   

   👁 What you should see: a CREATE TABLE line for products and one INSERT INTO line that holds all 5 rows, starting with (1,'Kurti',499).

4. Now make the mistake on purpose:

   sudo mariadb -e "DELETE FROM shop.products; SELECT COUNT(*) FROM shop.products;"
   

   👁 What you should see: 0. All rows are gone.

5. Restore from the backup:

   sudo mariadb < ~/db-backups/shop-backup.sql
   sudo mariadb -e "SELECT * FROM shop.products;"
   

   👁 What you should see: all 5 rows back, with the same ids, names and prices.

6. Compress the backup to save space, and check it can still be read:

   gzip ~/db-backups/shop-backup.sql
   zcat ~/db-backups/shop-backup.sql.gz | head -n 20
   

7. List every database user:

   sudo mariadb -e "SELECT user, host FROM mysql.user;"
   

   👁 What you should see: root, mariadb.sys, mysql (system accounts) and report from Lab 10.

8. The dashboard project has ended, so remove report:

   sudo mariadb -e "DROP USER 'report'@'localhost'; SELECT user, host FROM mysql.user;"
   

   👁 What you should see: report is no longer in the list. Do not drop root, mariadb.sys or mysql.

Common mistakes

Self-check checklist

Try-at-home challenge

Write a one-line command you could run every night that makes a dated, compressed backup like shop-2026-10-08.sql.gz. Then copy today's backup to your laptop with scp.

sudo mysqldump --databases shop --single-transaction | gzip > ~/db-backups/shop-$(date +%F).sql.gz && chmod 600 ~/db-backups/shop-$(date +%F).sql.gz

Clean up to avoid charges

If you do not need the server for Chapter 12, terminate it: EC2 → Instances → Instance state → Terminate (delete) instance. Copy any backup you want to keep to your laptop first.

Samjla ka? Backup, break, restore, verify, and remove users you do not need. Aata pudhe jaauya: Chapter 12 points a domain at our server.