Thursday, December 29, 2011

How to Restore MySQL database from Program files?

In this article, we will discuss about a scenario wherein a database administrator has tape backup of C drive only and production server had compromised by virus attack which was hosted in a third party hosting company. It is very frustrating for every database administrator if they have not schedule MySQL database export to a SQL backup file.

Restore MySQL database without SQL dump: To do this you have to follow the below steps.

  • Install MySQL server database
  • Stop the service of MySQL database
  • Copy data folder to new data folder
  • Restart the MySQL service
  • Check MySQL database table
  • Repair all the database table
  • Backup & restore to new production server

Install MySQL Server Database: Install the same version of MySQL database as you used before.

Stop the service of MySQL Database: Stop the service of MySQL database for adding a new data folder to the database.

Copy data folder to new data folder: Take the required database data folder that contains program files (.frm, .myi, .myd) from tape backup of C drive and paste them to new MySQL database data folder.

Restart the MySQL Service: After adding the data folder in new database then restart the MySQL service, hope all goes well without any problem.

Check MySQL Database Table: Run MYSQLCHECK <db_name> or CHECK <table_name> to check the consistency of the database. It depends upon your MySQL versions.

Repair all the Database Table: This step is not necessary but helps in ensuring that database is free from any corruption. You can use “REPAIR <your_table_name>” to repair MySQL database.

Backup & Restore to a new production server: Now export the mysql database to a SQL dump or SQL file.

Notification: Above discuss method works with MyISAM tables only, it does not work with InnoDB database storage engine and Tape backup of C drive is necessary.

No comments:

Post a Comment