Restoring a MySQL database from files is crucial for database administrators, especially when systems crash, databases get corrupted, or unexpected shutdowns occur. Knowing how to restore a MySQL database from various files can ensure that you recover your valuable data quickly and securely. In this guide, we’ll explore different file types and detailed steps for restoring MySQL databases effectively, with a focus on minimizing risks.
What files can you restore MySQL from?
MySQL databases are composed of multiple files, each playing a specific role in data storage. The following files are crucial for restoring MySQL databases:
.frm files: Contain table definitions (for MyISAM tables).
.myd files: Store data (for MyISAM tables).
.myi files: Store indexes (for MyISAM tables).
.ibd files: Contain table data and indexes (for InnoDB tables).
Log files: Enable point-in-time recovery (binary logs).
In addition to these, backup images can be used for restoring an entire MySQL instance, especially when a server crash or corruption occurs.
How to MySQL from .frm, .myd, and .myi files?
For MyISAM tables, .frm, .myd, and .myi files are the primary components to restore. These files contain the table's definition, data, and index information. Here's how to restore a database:
Steps to restore MySQL from .frm, .myd, and .myi files:
1. Stop MySQL: Shut down the MySQL service before copying files to avoid data corruption.
2. Copy files: Copy the .frm, .myd, and .myi files into the MySQL data directory.
3. Set permissions: Ensure that the files are owned by the MySQL user. On Linux, use chown -R mysql:mysql /var/lib/mysql/; on Windows, adjust folder properties via the Security tab.
4. Restart MySQL: Start the MySQL service and check if the tables are restored.
Note: With MySQL 8.0 and later, the data dictionary is serialized and no longer relies on .frm files. This change renders some methods outdated. In MySQL 8.0+, you must use alternative recovery methods, such as mysqlbackup.
How to Restore MySQL from .ibd Files?
InnoDB’s .ibd files store table data and indexes, but recovering them requires precise steps due to InnoDB’s transactional nature. Below is a unified guide for both standard and advanced recovery scenarios.
Steps to restore MySQL from .ibd files:
1. Stop the MySQL service before file operation and ensure the .ibd file matches the target table’s structure.
2. Place the .ibd file in the MySQL data directory.
3. Unlink the existing tablespace
ALTER TABLE table_name DISCARD TABLESPACE;
4. Import the .ibd file
ALTER TABLE table_name IMPORT TABLESPACE;
5. Restart MySQL
Verify table accessibility with:
SELECT * FROM table_name LIMIT 1;
There are some command for troubleshooting:
1. For data corruption errors, use the command below to fix inconsistency:
mysqlcheck --repair db_name table_name
2. If tablespace imports fail, add innodb_force_recovery=1 to my.cnf (values 1–6; higher values risk data loss).
3. For metadata mismatches, extract schema details using SHOW CREATE TABLE table_name and recreate the table if needed.
Note:
MySQL 8.0+ users: .frm files are obsolete; rely on CREATE TABLE statements.
Cross-server restores: Match InnoDB file formats.
How to restore MySQL from log files and backup images?
Binary logs and backup images can also be used for MySQL recovery. Binary logs capture all changes made to the database, allowing point-in-time recovery. Backup images, created by tools like mysqlbackup, provide a snapshot of your entire MySQL instance, enabling a full recovery.
Steps to restore MySQL from log files:
1. Enable binary logging: Ensure that binary logging is enabled on the server for point-in-time recovery.
2. Use mysqlbinlog: Run the command mysqlbinlog mysql-bin.xxxxxx | mysql -u username -p to apply the log files to the database.
3. Restore to desired point: Adjust the log files and position to recover data up to the exact point required.
Steps to restore MySQL from backup images:
1. Use mysqlbackup: Run the copy-back-and-apply-log command to restore from a backup image.
2. Configure the server: Ensure the server’s configuration matches that of the backup server, especially the innodb_data_file_path and innodb_log_group_home_dir.
3. Restore the backup: Use the following command to restore the backup:
mysqlbackup --datadir=/path/to/datadir --backup-image=/path/to/backup.mbi --backup-dir=/path/to/tmp copy-back-and-apply-log
How backup and restore MySQL with Vinchin?
Vinchin is a professional enterprise-level database backup software that supports MySQL along with many other major databases like Oracle, SQL Server, MariaDB, PostgreSQL, and PostgresPro. Vinchin provides comprehensive MySQL protection features that make backup and recovery easier, faster, and more reliable.
It provides various features for database protection like:
Incremental Backups: Save storage space with efficient strategies.
Log Backup & Any-Point Recovery: Restore MySQL databases to any moment (e.g., pre-corruption).
Data Compression & Deduplication: Reduce backup size by up to 70%.
Ransomware Protection: Protect backups from tampering by malware.
Vinchin’s web console is simple, intuitive, and easy to use. Backing up and restoring MySQL is straightforward, and can be done in just four simple steps:
1. Select MySQL database
2. Select the backup storage
3. Select the strategies
4. Submit the job
Try Vinchin today with a 60-day free trial, offering full access to all features. Click the button to get the installer and deploy easily, ensuring your MySQL database is fully protected.
MySQL database restore FAQs
Q1: Can I restore MySQL from just the .frm file?
No, the .frm file only contains the table definition. You also need the .myd and .myi files for MyISAM tables or .ibd files for InnoDB tables to restore the full database.
Q2: How do I restore MySQL after a crash?
To restore MySQL after a crash, copy your backup files into the MySQL data directory, ensuring the files are owned by the MySQL user. Then, restart MySQL and check the database.
Q3: Can I restore MySQL using backup images?
Yes, backup images are ideal for restoring MySQL. You can use the mysqlbackup tool for a full recovery from a backup image.
Conclusion
Restoring MySQL databases from files is a critical skill for database administrators. Whether you're restoring from .frm, .myd, .myi, or .ibd files, following the proper steps can help recover your data efficiently. For a more streamlined backup and restore process, consider Vinchin, a comprehensive enterprise-level solution for MySQL database protection. It offers key features such as compression, incremental backups, and any-point-in-time recovery for MySQL.
Get started with Vinchin today! Take advantage of the 60-day free trial and deploy it easily to protect your MySQL databases.
