Read time 7 minutes
Recovering lost/deleted data from MySQL databases could be complicated if you don’t know the suitable approaches to do it, or you don’t have enough expertise in using MySQL commands. That’s why it is crucial to have a regular backup of your MySQL databases.
Introduction to MySQL
MySQL is an open-source RDBMS system that uses SQL. It is used for various purposes like data warehousing, logging applications, and e-commerce applications. With MySQL database, you can even store a single record of information or an entire inventory of products.
Also, MySQL database files are compatible with many file formats, such as .sql, .cnf, .ddl, .arm, .qbquery, etc. However, the data and tables in the MySQL database are stored in different file formats. Losing these files can cause much harm to your business. Some of these file formats are:
- .frm –It contains the table structure data in a MySQL database. It defines the fields and structure of the table. It is generated when the table is created in MySql, and they have the same name as the table.
- .myd – .myd is a file extension, and MyISAM table data is stored in this file format. These files are saved with a corresponding .frm file that contains the table structure and a .myi file, which is stored in the database index.
- .ibd – In this file format, InnoDB table data and indexes are stored. It is created by default when MYSQL innodb_file_per_table option is enabled.
- Db.opt –. Database characteristics are stored in the db.opt file in the database directory.
- .myi – The MyISAM table indexes are stored in this file and define the structure of the table. It stands for MySQL MyISAM Index file. It works as a control mechanism to test the integrity of tables.
Try automated tool Kernel for MySQL Database Repair to fix all MySQL corruption issues and recover complete Database objects with ease..
In this article, we have mentioned some practical methods to backup as well as restore the lost/deleted MySQL databases.
Backup with mysqldump command
The mysqldump command is helpful in dumping the database with the SQL statements, which are required to rebuild the database. This command can also be used to backup your MySQL database with the following syntax:
In the above command, ‘user’ specifies the username, ‘database_name’ specifies the file path, and ‘>’ specifies the output.
Using this command, you can easily backup the specified MySQL database. However, if you want to back up the entire database management system, then use the following command:
Now, let’s move to the methods to recover MySQL database.
Methods to recover MySQL databases
Recovering MySQL database is not that difficult, but it depends on various conditions. For instance, if you’re using MySQL Workbench, then there are options for Data Export and Data Import/Restore. Using any of these features, you can easily restore your MySQL database.
Use MySQL dump to restore database
You can also create a backup for MySQL database, and then restore it using the mysqldump command in cmd. There is one other method that can be used to restore MySQL database to a new MySQL server if you have a backup for the previous data. Let’s have a more clear look at this method.
Note: This method only works for MyISAM tables and doesn’t restore InnoDB tables.
- Download and Install MySQL Server on your system. You can also install a similar version if you want to restore data to the existing version.
- Now, stop the MySQL service to add the data folders.
- Copy the backup data folder into the new MySQL–>data–>folder.
- After adding the required data to the new MySQL folder, restart the MySQL service.
- Now, check and repair database tables – You can check the database tables using mysqlcheck command followed by REPAIR. Performing these steps ensures that the database tables are not corrupt.
- After repairing any corrupt data, you can export the database to a SQL dump or SQL file.
That’s it! Your MySQL database has been restored.
Limitations while using manual methods
Now, the manual approach can be followed only if backups are available and you are technically good. If you’ve lost your database completely, then the manual method is not beneficial in restoring it. So, we have an alternate option that can restore even permanently deleted data.
- Manual methods are a little bit risky and time-consuming.
- Manual methods cannot provide genuine results.
- You may lose your data while trying these manual methods.
Quick solution to recover MySQL database
Kernel for MySQL Database is a great tool for repairing and restoring data from corrupt MySQL databases. It allows users to preview every object of the repaired MySQL database before saving it. It comes with some advanced features, such as the option to restore data online or local disk in offline mode, fix corruption errors of the databases, multiple saving options, etc.
Let’s understand how this extensive tool works in restoring MySQL databases.
- Download and install Kernel for MySQL Database Repair tool on your system.
- Launch the tool, click the Browse button to select the specific database file, and hit the Recover button.
- The database will be added to the tool; you can preview the objects, keys, and tables by selecting them.
- To save the database, select the table(s) and the property(s) by marking the checkboxes and clicking the Save button.
- The saving mode will appear on the screen. Now, you can choose whether you want to restore the database online or save it on your local computer.
- To restore data to MySQL, select the MySQL settings mode. Enter the details for the required fields, such as ServerHost, Port, User, and Password. You can check the connection by clicking the Test Connection button.
- Now, select whether you want to create a new database or restore it to an existing database, and then click Save.
- The saving process will start; once it is complete, a window will appear on the screen displaying Process Complete, click OK to end the process.
Similarly, you can save the database in the batch file mode, and then restore it to MySQL server using cmd on your Windows operating system. Here is a preview screenshot of how it will work.
Conclusion
Restoring corrupt MySQL databases can be difficult with manual methods, but it is necessary to retrieve data from corrupt databases. So, here we have mentioned an effective solution that will help you repair and restore the MySQL database to the MySQL Server.
