How to restore mysql database from `.ibd `files?

Member

by ryleigh , in category: MySQL , 3 months ago

How to restore mysql database from `.ibd `files?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by daisha , a month ago

@ryleigh 

To restore a MySQL database from .ibd files, you can follow these steps:

  1. Stop the MySQL service: It is important to stop the MySQL service before making any changes to the database files.
  2. Create a new empty database: Use MySQL command-line or a GUI tool like phpMyAdmin to create a new empty database with the same name as the original database.
  3. Locate the .ibd files: Find the .ibd files of the tables you want to restore. These files are typically stored in the MySQL data directory, under the subdirectory corresponding to the database name.
  4. Prepare the .ibd files: If the .ibd files are from a different MySQL installation, you may need to reset file permissions. Make sure the files belong to the user and group that MySQL runs as. Additionally, ensure the .ibd files have the correct ownership and permissions set.
  5. Import the .ibd files: Copy the .ibd files into the corresponding database directory of the new empty database you created in step 2. Replace any existing .ibd files with the same name.
  6. Start the MySQL service: Restart the MySQL service, allowing it to pick up the changes you made.
  7. Verify the restored database: Check if the restored database and its tables are accessible and contain the expected data. You can use command-line tools like mysql or GUI tools to connect to the MySQL server and verify the data.


It is important to note that this method only works for InnoDB storage engine tables. If your database contains MyISAM tables, you'll need to follow a different approach to restore those tables.