When you're dealing with MySQL's MyISAM storage engine (the default for many older MySQL installations), the database stores tables in three key files:
table_name.frm - stores table structure/definition
table_name.MYD - contains the actual data (MyISAM Data)
table_name.MYI - contains indexes (MyISAM Index)
The good news is yes - if you have all three files (.frm, .MYD, .MYI) for a MyISAM table, you can recover the entire table without needing SQL dump files. Here's how to do it properly:
1. First, ensure MySQL is stopped during the file transfer:
sudo systemctl stop mysql
2. Copy your table files to the new MySQL data directory (typically /var/lib/mysql/database_name/):
sudo cp table1.* /var/lib/mysql/your_database/
sudo chown -R mysql:mysql /var/lib/mysql/your_database
3. Restart MySQL:
sudo systemctl start mysql
• Storage Engine Matters: This only works for MyISAM tables. For InnoDB, you'd need the .ibd files and a more complex process.
• File Permissions: MySQL needs proper ownership of the files (usually mysql:mysql).
• Database Directory: The files must be placed in a directory with the exact same name as the original database.
After restarting MySQL, check if your tables are accessible:
mysql -u root -p
USE your_database;
SHOW TABLES;
SELECT * FROM table1 LIMIT 5;
For a more reliable method before re-imaging, consider using mysqlhotcopy:
mysqlhotcopy your_database /path/to/backup
This creates a complete copy of all MyISAM tables that can be easily restored.
If you encounter issues, try these repair commands:
REPAIR TABLE table1;
# Or at the system level:
myisamchk -r /var/lib/mysql/your_database/table1.MYI
Remember that while this method works for MyISAM, modern MySQL installations often use InnoDB by default, which requires a different approach.
When dealing with MyISAM tables in MySQL, three critical files constitute the complete table structure:
table_name.frm - Contains the table definition (schema)
table_name.MYD - Stores the actual data (MYData)
table_name.MYI - Holds index information (MYIndex)
To restore your database from these files:
- Install the same MySQL version on your new server
- Stop the MySQL service:
sudo service mysql stop - Copy the files to the appropriate datadir location (typically /var/lib/mysql)
- Ensure proper file permissions:
chown -R mysql:mysql /var/lib/mysql - Restart MySQL:
sudo service mysql start
Version Compatibility: The MySQL version must match or be newer than the original. Downgrading may cause corruption.
Storage Engine: This method works primarily for MyISAM tables. For InnoDB, you'll need the entire database directory.
After restoration, check your tables:
mysql> CHECK TABLE table_name;
mysql> REPAIR TABLE table_name; # If needed
For more complex scenarios, consider using mysqlhotcopy or creating symbolic links:
ln -s /path/to/original/data/table1.* /var/lib/mysql/db_name/
Always maintain proper backups using:
mysqldump -u root -p --all-databases > full_backup.sql