An Easy Guide To Repair Multiple MySQL Databases

It is common to face corruption issues while working with multiple MySQL databases in a production environment. The corruption in MySQL databases can occur due to several reasons. If the databases get corrupted, you may fail to access data like primary keys, foreign keys, unique keys, tables, and relations stored in the database file.

Sometimes, you can receive random errors like Tablethatwasworking.frm is locked”, “Can’t find tablethatshouldbethere.MYI file, “Unexpected end of file,” “Got error ### from table handler,” etc. Explore this Guide To Repair multiple MySQL Databases and learn effective methods to fix and restore your MySQL databases for optimal performance.

When multiple databases get corrupted at a time, it impacts workflow, leading to downtime. In this guide, you will learn methods to restore and repair multiple MySQL databases. We’ll also mention an efficient MySQL recovery software to help you recover all data from corrupt MySQL databases quickly and in one go.

Primary Reasons behind corruption in MySQL Databases:

Following are some major reasons behind the corruption in MySQL database:

• Hardware glitches on the system hosting the MySQL database
• Bugs in MySQL software
• Sudden termination of MySQL Service
• MS SQL Server crashes 
• The database closes abruptly in the middle of writing data
• Viruses in system residing MySQL database.

Guide to repair multiple MySQL databases:

You might have multiple databases running on your MySQL Server, and if some of them have become corrupted or damaged, then you can repair them to minimize downtime. Following are the solutions to repair multiple databases in MySQL Server:

Method 1-Restore your Backup file:

If you have an updated and readable backup (dump file), you can use it to restore the MySQL database. To restore the database, use the mysqldump utility. The tool allows you to dump an entire database or multiple databases. To use the tool, first make sure you have all permissions and privileges on the Dumped tables. The steps are below:

• Use the below command to drop and recreate the MySQL database:

mysql > drop db_namemysql > create db_name’

• Next, run the following command to restore the database:

mysql -u root -p db_name < dump.sql

Note: It can restore multiple MySQL databases. However, repairing large-sized MySQL databases larger than 10 GB using the mysqldump utility is time-consuming. It does not support INFORMATION_SCHEMA, and InnoDB CREATE TABLESPACE statements.

Method 2-Use mysqlcheck repair command

You can repair the MySQL database using the repair statement in the mysqlcheck client. To use this utility, first check whether you have SELECT and INSERT privileges on the table. Next, run the below command to repair all tables in a MySQL database:

mysqlcheck –repair –all-databases

Method 3-Run myisamchk utility:

You can use the myisamchk utility to repair multiple MySQL databases created in MyISAM storage engine. Here’s the command:

myisamchk --silent --force --fast --update-state \

          --key_buffer_size=64M --myisam_sort_buffer_size=64M \

          --read_buffer_size=1M --write_buffer_size=1M \

          /path/to/datadir/*/*.MYI

Note: This method only supports MyISAM tables in the MySQL database. 

Method 4-Use Dump and Reload method

You can use dump and reload technique to rebuild InnoDB tables in MySQL database. To use this method, your MySQL Server should be in a running state. But the InnoDB engine crashes when it detects corrupt InnoDB tables. So first, you need to restart the MySQL Server by forcefully modifying the Innodb_force_recovery settings to edit the startup process. You can find this setting on the configuration file on your system by following the path /etc/mysql/my.cnf., enable this setting by using the below statement. 

[mysqld]

Innodb_force_recovery=1

service mysql restart

Next, customize these Innodb_force_recovery settings from 0-6. However, if you set the value beyond 4, it may lead to data loss. So make sure you have created the backup of the damaged database table.

It will change the startup settings and allow the server to start even if it detects corrupt tables. Now, you can easily run the MySQL server. 

Once you enabled the innodb_force_recovery, you can run the below commands:

mysqldump -u user -p database_name table_name > single_dbtable_dump.sql

mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql

Next, try restarting the MySQL server and then use  the DROP DATABASE command to drop your database.

Next, from the configuration file, disable the InnoDB recovery mode by using # sign as shown below 

#innodb_force_recovery=…

Next, save all the updates in the my.cnf file and then restart your MySQL Server

Method 5 – Use a Professional MySQL Database Recovery Software

To repair multiple tables at once or the entire database quickly and without any data loss, you can opt for a professional MySQL database recovery software, like Stellar Repair for MySQL.

It is a reliable tool to repair both InnoDB and MyISAM database tables and recover data from damaged MySQL database files. It can help restore tables and all other database objects from corrupt databases with no file size limitations. It can even support repairing partitioned tables, CREATE TABLESPACE statements, and deleted elements.

Also, the tool maintains data integrity and precision while performing the recovery operation. The tool is compatible with all Windows versions, including the latest Windows 11. It can repair the  MySQL database file in just a few simple steps. 

Highlighting Features of Stellar Repair for MySQL:

• Repairs corrupt multiple MySQL databases with absolute precision 
• Repairs MySQL database tables of both the storage engines – InnoDB and MyISAM
• Repairs MariaDB databases 
• Resolves complex MySQL corruption errors
• Offers preview of recoverable database objects before saving
• Supports selective recovery of database objects
• Saves repaired files in multiple formats – MySQL, SQL Script, MariaDB, SQL Script, HTML, CSV, and XLS
• Compatible with both Windows and Linux operating systems 

Conclusion

SQL Server database corruption is a concern for its administrator. So whenever the administrator detects any symptoms of corruption in the database, they start repairing it as soon as possible to prevent data loss.

The major concern is when multiple databases get corrupted, and an instant solution is required. In such a case, it is a good practice to keep a professional MySQL Repair tool handy to assist in repairing multiple MySQL databases simultaneously and without any file size limitations. It also restores deleted data from MySQL databases without losing originality.