?️ Restore Database in MySQL

? Quick Overview

Restoring a database in MySQL means importing data and database structure from an existing SQL backup file (.sql) back into a MySQL database. This operation is essential after accidental data deletion, server failure, system migration, or when setting up a database in a new environment.

The restore process replays SQL commands stored in the backup file, allowing MySQL to rebuild tables, indexes, and data exactly as they were at the time of backup.

? Key Concepts

• Restore operations use logical SQL dump files
• The target database must exist unless the dump creates it
• Restore can be full (schema + data) or partial (selected tables)
• Executed via MySQL client or inside MySQL shell
• Errors stop execution unless handled explicitly

? Syntax / Theory

During a restore, MySQL reads SQL statements line by line from the backup file and executes them in sequence. These statements may include:

• CREATE TABLE for structure
• INSERT INTO for data
• ALTER TABLE for constraints and indexes

If the dump file does not contain a CREATE DATABASE statement, the database must be created manually before restoring.

? Restore Database from SQL File (Command Line)

This is the most common and fastest way to restore a database using a backup file.

-- Restore a MySQL database using command-line client
mysql -u root -p mydatabase < backup.sql

? Restore Using MySQL SOURCE Command

The SOURCE command is executed from inside the MySQL shell and is useful when working interactively.

-- Restore database using SOURCE inside MySQL shell
SOURCE C:/backup/backup.sql;

? Restore After Creating Database

When restoring on a fresh server, the database often does not exist yet. In such cases, create the database first and then load the backup.

-- Create database and restore data
CREATE DATABASE mydatabase;
USE mydatabase;
SOURCE C:/backup/backup.sql;

? Restore Specific Database from Full Backup

When a dump file contains multiple databases, you can restore only one by selecting the appropriate database before execution.

-- Restore a single database from a full backup
mysql -u root -p --one-database mydatabase < alldb_backup.sql

? Live Output / Explanation