How to Restore MySQL Database from SQL Dumps & Physical Files

Type of MySQL Backup Files

Before restoring, you need to know what type of backup file you have. MySQL backups fall into two categories: logical backups (SQL scripts) and physical backups (raw data files). Using the wrong restore method for your file type will cause errors — or worse, silent data corruption.

• SQL dump files are text-based. They contain SQL statements (CREATE TABLE, INSERT INTO, etc.) that rebuild your database from scratch. Easy to read, easy to transfer, and compatible across MySQL versions.
• Physical data files are the raw binary files MySQL uses on disk. They restore faster for large datasets, but require matching MySQL versions and correct file permissions to work properly.

Restore MySQL Database from SQL Dump Files

SQL dump files are the most common MySQL backup format. They’re portable, human-readable, and compatible across most MySQL versions — making them the standard choice for migrations and routine restores.

Before you start: Most dump files don’t include a CREATE DATABASE statement. If the target database doesn’t exist yet, create it first:

CREATE DATABASE your_database_name;

Restore from a .sql File

Use the mysql command-line client to pipe the file directly into your target database:

mysql -u username -p database_name < backup.sql

You’ll be prompted for your password. MySQL will then execute every statement in the file and rebuild your tables and data.

Restore from a .dump File

A .dump file is structurally identical to a .sql file — just a different extension. The command is exactly the same:

mysql -u username -p database_name < backup.dump

Restore from a Compressed Backup (.sql.gz)

Large databases are often stored as .sql.gz files to save space. You don’t need to decompress the file first — pipe it directly into MySQL in one step.

Linux / macOS:

gunzip < backup.sql.gz | mysql -u username -p database_name

Windows (PowerShell):

cmd /c "gunzip -c backup.sql.gz | mysql -u username -p database_name"

Common Pitfalls

• Access denied errors: Make sure the MySQL user has ALL PRIVILEGES on the target database before running the import.
• Import fails on large rows: If restoring tables with blobs or long text fields, increase max_allowed_packet in your my.cnf (Linux) or my.ini (Windows).
• Foreign key errors: If tables import out of order, add SET FOREIGN_KEY_CHECKS=0; at the top of your SQL file and SET FOREIGN_KEY_CHECKS=1; at the bottom.

Restore MySQL Database from Physical Data Files

Physical data files — sometimes called “flat files” or “cold backups” — are the raw binary files MySQL uses to store data on disk. Restoring from these files is faster than replaying a SQL dump, but it requires more care around file permissions and version compatibility.

When to Use Physical File Restoration

This method is best suited for the following scenarios:

• Server crash: The MySQL service won’t start and you can’t run a logical export.
• Very large databases: Importing a .sql file for a multi-terabyte database could take days. Physical restoration is significantly faster.
• Full server or snapshot restore: You’re recovering from a full system backup or a snapshot of the /var/lib/mysql directory.

Steps to Restore Physical MySQL Data Files

Follow these steps in order. Mistakes in file placement or ownership are the most common reasons a physical restore fails.

Step 1: Stop the MySQL Service

Never move or replace data files while MySQL is running. Doing so risks dirty writes and file corruption.

• Linux: sudo systemctl stop mysql
• Windows: Open services.msc and stop the MySQL service.

Step 2: Replace the Database Files

Copy your backup files into the MySQL data directory:

• Linux: /var/lib/mysql
• Windows: C:\ProgramData\MySQL\MySQL Server X.X\Data

Step 3: Adjust File Permissions

On Linux, MySQL must own all files in the data directory. This is the most frequently missed step in a physical restore.

sudo chown -R mysql:mysql /var/lib/mysql

sudo chmod -R 750 /var/lib/mysql

Windows handles file permissions differently — as long as the MySQL service account has read/write access to the data folder, no additional changes are needed.

Step 4: Restart MySQL

Start the service and watch for errors.

• Linux: sudo systemctl start mysql
• Windows: Start the service via services.msc.

Verify the Restored MySQL Database

A restore isn’t complete until you’ve confirmed the data is intact. Skipping verification can lead to silent failures — the database appears to be running, but the application crashes when it hits a missing table or corrupted row.

Step 1: Check the Database Structure

Confirm that all expected tables were recreated. Log into the MySQL shell and run:

USE your_database_name;

SHOW TABLES;

Compare the output against your original schema or documentation. Any missing tables likely indicate an interrupted import.

Step 2: Verify Table Row Counts

Tables can exist but be empty if the restore was cut short. Check the row counts for your most critical tables:

SELECT COUNT(*) FROM users;

SELECT COUNT(*) FROM orders;

Step 3: Spot-Check Data Integrity

Run a few queries on recently updated records to confirm the data is readable:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

Pay attention to any garbled text, which can indicate a character encoding mismatch — a common issue when moving files between different operating systems or MySQL versions.

Step 4: Test Application Connectivity

Restart your application or web server and perform a basic operation — logging in, loading a dashboard, or running a report. This confirms the application can reach the database and query it successfully.

Simplify MySQL Backup & Recovery with i2Backup

Manual restore procedures work — but they depend on having a clean, complete backup file ready when you need it. In practice, ad hoc backups are easy to forget, hard to verify, and often incomplete by the time a real recovery is needed. A dedicated backup solution removes that uncertainty by automating the entire process from scheduling to recovery.

i2Backup is an enterprise backup platform that lets you back up and restore MySQL databases through a visual web console, with support for physical servers, virtual machines, and cloud environments — all managed from one place.

Key Feature of i2Backup

• Scheduled and real-time database backup: Configure backup jobs to run on any schedule — hourly, daily, weekly — or capture changes continuously for near-zero RPO.
• Centralized web-based management: A single browser-based console to schedule backups, monitor status, and manage recovery jobs — no additional software required.
• Redo log and archive log capture: i2Backup continuously captures MySQL redo logs and archive logs, enabling accurate point-in-time recovery to any moment before a failure occurred.
• Multiple backup destinations: Send backups to local storage, NAS, or cloud targets simultaneously to eliminate single points of failure.
• Broad platform compatibility: Supports physical servers, VMware, Hyper-V, and other mainstream virtualization platforms — no need for separate tools across different environments.

The steps in this guide give you full control over manual MySQL restores. But a reliable recovery starts long before a failure happens — with consistent, verified backups. i2Backup automates that foundation, so when you do need to restore, the process is straightforward rather than a scramble.

FAQ

Q1: How do I recover a MySQL database from data files on Windows?

Stop the MySQL service via services.msc. Copy your .ibd, ibdata1, and .frm files into C:\ProgramData\MySQL\MySQL Server X.X\Data. Make sure the MySQL service account has full read/write permissions on the new files, then restart the service.

Q2: How do I restore MySQL from binary logs?

Use the mysqlbinlog utility to convert the log file into SQL statements and pipe them into MySQL:

mysqlbinlog binlog.000001 | mysql -u username -p

Binary log recovery is used on top of a full backup — always restore your base backup first, then replay the binlogs to recover changes up to the point of failure.

Q3: How do I restore a MySQL database using files from the data folder?

Shut down MySQL, then move the database folder into the data directory. Make sure ibdata1 comes from the same backup as your table files. On Linux, reset file ownership with chown -R mysql:mysql before restarting.

Q4: Can I restore a single table from a .sql dump file?

Restore the full dump into a temporary staging database, then export just the table you need:

mysqldump -u username -p staging_db table_name > single_table.sql

mysql -u username -p target_db < single_table.sql

Q5: What should I do if I get a “Table already exists” error?

Drop and recreate the target database before importing:

DROP DATABASE your_database_name;

CREATE DATABASE your_database_name;

Conclusion

Restoring a MySQL database comes down to knowing your backup type. SQL dump files are straightforward to import with the mysql command line. Physical data files are faster for large datasets but require careful attention to file permissions and tablespace consistency. Either way, always verify the restore before pointing your application back at the database.

The best time to test your recovery process is before you actually need it. Consider a dedicated solution like i2Backup to automate your MySQL backups and ensure you always have a clean, verified restore point ready.