I Drop the Mysql Database; How Can I Repair in Linux

Summary: Read this web log to find out how to repair MySQL database in Linux manually or by using MySQL database repair software. Employ the software to recover all the database components without affecting the original database file. You tin can download the free demo version of the software to preview all the recoverable database components.

When running the MySQL database on a Linux machine, the database tin can become corrupt due to several reasons, such every bit virus assault on the machine, hardware failure, etc. You can attempt restoring the database from an updated fill-in. However, if the backup isn't available, y'all can try repairing the MySQL database.

Methods to Repair MySQL Database in Linux

Method 1 – Repairing MySQL Database Manually

Note: The manual pace-wise approach to repair MySQL database may differ depending on the storage engine you lot're using: InnoDB or MyISAM. Thus, troubleshooting database corruption by executing different manual steps requires fourth dimension, increasing the database downtime. Employ a MySQL database repair tool to rapidly repair corrupt database and restore it to the original course.

Here, we volition hash out stride-wise process to repair a corrupt MySQL database on a Linux-based system:

Step 1: Support your Database

Brand sure to back up your database before attempting to repair information technology. Doing and then will prevent your database from further loss. To back up your MySQL database files, follow these steps:

1 Log in to your server via SSH

two. Stop your MySQL server by using any of the following commands based on your Linux distribution:

For CentOS and Fedora, enter:

service mysqld stop

For Ubuntu and Debian, enter:

service mysql stop

3. To dorsum upwardly your database file, type:

cp -r /var/lib/mysql /var/lib/mysql_backup

4. Restart the MySQL server by running the post-obit command in your Linux arrangement:

service mysqld start

Footstep two: Run "mysqlcheck" to Check and Repair MySQL Databases

Notation: The mysqlcheck command is useful for users who want to repair MySQL database and tables without stopping MySQL service.

In one case you've backed upwardly your databases, run the mysqlcheck command to check and repair database and tables for MyISAM or InnoDB database engines. Follow these steps to use mysqlcheck for repairing the database:

1. Every bit the root user, enter the beneath command:

cd /var/lib/mysql

2. Check the database and all its tables for abuse by typing the command:

mysqlcheck database_name

To check a specific database tabular array for errors, type the command:

mysqlcheck database_name table_name

3. If the table is non corrupted, an OK message is displayed. However, if the database tabular array displays any errors, you need to repair it by using the post-obit control:

mysqlcheck –r database_name table_name

If running the mysqlcheck command does non ready the issue, proceed to the adjacent step.

Stride 3: Perform Engine-specific Diagnostics

Run diagnostics specific to the storage engine (InnoDB or MyISAM) used by the database and tables. Follow the advisable procedure below for your table's database storage engine.

Repairing MyISAM tables with myisamchk

When your database runs on MyISAM storage engine, run the myisamchk command to repair it. To practice this, follow these steps:

Note: The myisamchk command works for the MyISAM storage engine only.

1. Stop your server using whatsoever of the below commands for your Linux distribution:

For CentOS and Fedora, use:

service mysqld stop

For Debian and Ubuntu, employ:

service mysql end

2. Type the following:

cd /var/lib/mysql

Change the above directory with the directory where your corrupt database is located. For example, if the database is named db1, type cd db1.

3. Check decadent tables in the database, by using the following command:

myisamchk table_name

To check all of the database tables, type the post-obit command:

myisamchk *.MYI

4. Once you accept identified the corrupted tables in the database, utilize the mysqlchk control to repair the tables past following this command:

myisamchk –recover tabular array

v. Restart the server:

For CentOS and Fedora, type:

service mysqld start

For Debian and Ubuntu, type:

service mysql start

Running the InnoDB Recovery Process

To repair db running on InnoDB storage engine, follow these steps:

1. Open up MySQL configuration file "my.cnf". The location of the my.cnf file volition vary depending on your Linux OS. On CentOS and Fedora, you can observe the file in the '/etc' directory. And on Debian and Ubuntu, the configuration file is located in the '/etc/mysql' directory.
2. Once you've located my.cnf file, find the [mysqld] section.
3. In the [mysqld] section, add the following line:

innodb_force_recovery=4

iv. Save the changes to the MySQL configuration file, and then restart the MySQL server:

For CentOS and Fedora:

service mysqld restart

For Debian and Ubuntu:

service mysql restart

5. Run the beneath control to consign all of the databases to the databasesbkp.sql file:

mysqldump –all-databases > databasesbkp.sql

6. Start the mysql service, then apply the Drib DATABASE command to effort to drop the affected database.

Note: If you're unable to driblet a database, delete it manually after stopping the MySQL server.

7. Stop MySQL Server.

8. Type the following command to delete the database manually:

cd /var/lib/mysql rm -rf database

Replace 'database' with the proper name of your database that you lot desire to repair.

9. Open my.cnf file over again, and annotate out the following line:

#innodb_force_recovery=iv

This will disable the InnoDB recovery mode.

10. Save the changes yous've fabricated to my.cnf file, and then kickoff your MySQL Server.

11. Endeavour restoring the database from the fill-in created in Step v:

mysql < databasesbkp.sql

Now cheque the restored database. If the problem persists, utilise a specialized MySQL database repair software to restore the database.

Method 2 – Repairing MySQL Database with Stellar Repair for MySQL

Stellar Repair for MySQL is a powerful MySQL database repair software recommended by DBAs to repair severely corrupted databases and restore all its objects. The software allows repairing MySQL database on Linux besides as Windows system.

To repair a corrupted MySQL database created on a Linux organisation, follow these steps:

Step 1: Download and install the software. For software installation, open the Linux Terminal, and then enter the post-obit command:

$ sudo yum install app_name

Note: Here "app_name" is the path of Stellar Repair for MySQL software package that you need to install.

Stride 2: Launch the software. The software main interface opens with an Instruction window. Click OK to proceed.

Step iii: In Select Data Binder window, select the version of your MySQL installation, so browse and select the data folder containing the corrupt database.

Step iv: The list of the databases stored in the selected information folder is displayed. Select all or specific database that you desire to repair, and then click Repair.

Step 5: Click OK when the 'Repair Complete' dialog box appears.

Step 6: The software displays preview of the recoverable MySQL database components.

Stride 7: Click Save on File menu for saving the repaired MySQL database file.

Step 8: In the 'Save Database' dialog box, choose MySQL under Save As, and and so enter the required details to connect to your MySQL server. Click Save for saving the repaired database file in MySQL Server.

Step 9: A 'Save Complete' bulletin box is displayed when the repaired file saving process is consummate.

Afterward executing these steps, the MySQL database volition be repaired.

Conclusion

Repairing MySQL database in Linux-based system using the transmission method does non guarantee to restore all the database components. It might work to ready minor database problems but fail to set up a heavily damaged MySQL database. Using Stellar Repair for MySQL software can assist you repair a severely corrupt database and recover all its components, preserving the original file structure and contents.

mcdermottfition.blogspot.com

Source: https://www.stellarinfo.com/blog/repair-mysql-database-table-on-linux/

Share this post