MySQL Backup and Restore

Backing up a MySQL database creates a copy of the data that can be restored if the original is lost, corrupted, or accidentally deleted. Regular backups are a critical part of any database administration practice. MySQL provides several tools and methods to perform backups and restores efficiently.

Why Backup is Important

• Protection against accidental deletion or corruption
• Recovery from hardware failure or server crash
• Migration of data to a new server
• Creating a snapshot before making major changes

Types of Backups

Backing Up with mysqldump

mysqldump is the most commonly used MySQL backup tool. It creates a logical backup as a .sql file containing all the SQL statements needed to recreate the database.

Backup a Single Database

mysqldump -u root -p school_db > school_db_backup.sql

Enter the root password when prompted. The backup file school_db_backup.sql is created in the current directory.

Backup a Single Table

mysqldump -u root -p school_db students > students_backup.sql

Backup Multiple Databases

mysqldump -u root -p --databases school_db company_db > multi_backup.sql

Backup All Databases

mysqldump -u root -p --all-databases > full_backup.sql

Backup with Structure Only (No Data)

mysqldump -u root -p --no-data school_db > schema_only.sql

Backup with Data Only (No Structure)

mysqldump -u root -p --no-create-info school_db > data_only.sql

Restoring a Backup

Restoring a backup means running the .sql file against a MySQL server to recreate the database and its data.

Restore to an Existing Database

mysql -u root -p school_db < school_db_backup.sql

Restore All Databases

mysql -u root -p < full_backup.sql

Restore Inside MySQL (Alternative Method)

-- First create the database if it doesn't exist
CREATE DATABASE school_db;
USE school_db;

-- Then source the file
SOURCE /path/to/school_db_backup.sql;

Viewing the Backup File

A .sql backup file is plain text and can be opened in any text editor. It contains statements like:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

INSERT INTO students VALUES (1, 'Riya Sharma');
INSERT INTO students VALUES (2, 'Anil Joshi');

Compressed Backup

mysqldump -u root -p school_db | gzip > school_db_backup.sql.gz

Restore a Compressed Backup

gunzip < school_db_backup.sql.gz | mysql -u root -p school_db

Key Points

• mysqldump creates a logical backup as a .sql file containing SQL statements.
• Use mysqldump -u root -p database_name > file.sql to back up a database.
• Use mysql -u root -p database_name < file.sql to restore a backup.
• Backups can be limited to structure only or data only using flags.
• Schedule regular automated backups and store them in a separate location from the database server.