This website use cookies to help you have a superior and more admissible browsing experience on the website.
Share This Article：
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.
| File Type | Created By | Best For | Restore Method |
|---|---|---|---|
| .sql / .dump | mysqldump, mysqlpump | Migrations, small-to-medium databases | MySQL command line |
| .sql.gz | mysqldump + compression | Large databases, remote transfers | Decompress, then import |
| .ibd | InnoDB engine | Single-table recovery | Manual file replacement |
| .ibdata | InnoDB engine | Full InnoDB system tablespace recovery | Manual file replacement |
| .frm | MySQL 5.7 and older | Table structure metadata | Manual file replacement |
| .MYD / .MYI | MyISAM engine | MyISAM table data and index recovery | Manual file replacement |
- 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.
Note: Avoid copying physical files between significantly different MySQL versions (e.g., 5.7 to 8.0) without testing. Schema formats changed between major releases and can cause startup failures.
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"
Tip: Piping directly avoids writing a large temporary file to disk, which matters when storage is limited.
Common Pitfalls
- Access denied errors: Make sure the MySQL user has
ALL PRIVILEGESon the target database before running the import. - Import fails on large rows: If restoring tables with blobs or long text fields, increase
max_allowed_packetin 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 andSET 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
.sqlfile 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/mysqldirectory.
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
Note: If you’re restoring InnoDB tables, the ibdata1 and ib_logfile* files must come from the same backup as your .ibd files. Mismatched system files will cause tablespace ID errors and prevent MySQL from starting.
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.
Tip: Check the MySQL error log immediately after restarting — look for hostname.err on Windows or run
sudo journalctl -u mysql on Linux. Any tablespace mismatch or permission issue will show up here before it causes bigger problems.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;
Tip: If you recorded row counts at the time the backup was taken, compare them now. A lower number usually points to an incomplete import or a
max_allowed_packet error during the restore.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.
Note: If the connection fails, check your MySQL user permissions — specifically the
HOST value. A user defined as 'user'@'localhost' won’t work for remote connections; use 'user'@'%' if the application connects from a different host.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.
{{ author_info.name }}
{{author_info.introduction || "No brief introduction for now"}}
More Related Articles
Table of Contents:
Stay Updated on Latest Tips
Subscribe to our newsletter for the latest insights, news, exclusive content. You can unsubscribe at any time.
Ready to Enhance Business Data Security?
Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
Please fill out the form and submit it, our customer service representative will contact you soon.
By submitting this form, I confirm that I have read and agree to the Privacy Notice.
