mysqldump usage - Mini Project: Build & Apply

Backup and Restore a MySQL Database Using mysqldump

📖 Scenario: You are managing a small business database. To keep your data safe, you want to create a backup of your MySQL database and later restore it if needed.

🎯 Goal: Learn how to use the mysqldump command to export a database to a file and then import it back to restore the database.

📋 What You'll Learn

Create a MySQL database named shop with a table products and some sample data.

Use mysqldump to export the shop database to a file named shop_backup.sql.

Create a new empty database named shop_restore.

Use mysqldump output file to restore data into shop_restore.

💡 Why This Matters

🌍 Real World

Database backups are essential to protect data from loss due to errors, crashes, or attacks. Using mysqldump is a common way to create logical backups of MySQL databases.

💼 Career

Database administrators and developers often need to backup and restore databases safely. Knowing mysqldump commands is a fundamental skill for managing MySQL databases.

1

DATA SETUP: Create the shop database and products table with sample data

Create a MySQL database called shop. Then create a table called products with columns id (integer primary key), name (varchar 50), and price (decimal). Insert these exact rows into products: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), (3, 'Eraser', 0.50).

MySQL

-- Create database shop
-- Create table products
-- Insert sample data

Need a hint?

Use CREATE DATABASE, CREATE TABLE, and INSERT INTO statements with exact names and values.

2

CONFIGURATION: Prepare to export the shop database using mysqldump

Write the exact mysqldump command to export the shop database to a file named shop_backup.sql. Assume the MySQL username is root and no password is needed.

MySQL

CREATE DATABASE shop;
USE shop;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.50);

-- Write mysqldump command to export shop database
# Your code here

Need a hint?

Use mysqldump -u root shop > shop_backup.sql to export the database.

3

CORE LOGIC: Create a new empty database shop_restore to prepare for restoring

Create a new MySQL database called shop_restore which will be used to restore the backup data.

MySQL

CREATE DATABASE shop;
USE shop;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.50);

mysqldump -u root shop > shop_backup.sql

-- Create new database shop_restore
# Your code here

Need a hint?

Use CREATE DATABASE shop_restore; to create the new database.

4

COMPLETION: Restore the shop_backup.sql file into the shop_restore database

Write the exact command to import the shop_backup.sql file into the shop_restore database using MySQL command line. Use username root and no password.

MySQL

CREATE DATABASE shop;
USE shop;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.50);

mysqldump -u root shop > shop_backup.sql

CREATE DATABASE shop_restore;

-- Restore shop_backup.sql into shop_restore
# Your code here

Need a hint?

Use mysql -u root shop_restore < shop_backup.sql to restore the backup.