May 24, 2021
Free Two Methods to Repair MySQL Database (InnoDB & MyISAM)
In this blog we will discuss how to repair mySQL Database. Also we will provide the two methods innoDB & MYISAM. Both are standard methods to repair mysql databases.
In this we discussed about , how to backup the database , how we run mysql check, how to run- engine specific diagnostic -
- Repairing MYISAM tables with myisamchk
- Running the InnoDB recovery process.
We will provide the manual strategies as well as the use of advanced SQL Database recovery tool for the effortless recovery process.
How To Repair MYSQL Database
Before we start to attempt repair any database , first we try to backup our database, we need to backup all the files from our database, we follow some steps:-
- Firstly, we login to our server using SSH.
- After performing the above step, stop the MYSQL server using the appropriate command on our Linux operating system.
- For Fedora and Centos Type of Operating System
Service mysql Stop
- For Ubuntu and Debian Type
Service mysql stop
Now, we type the following command-
cp-rfv/var/lib/mysql/var/lib/mysql$/(date+%s)
After performing the above step we restart the MYSQL server by using the appropriate command of our Linux operating system.
- For Fedora and Centos Type operating system
Service mysqld start
- For Ubuntu & Debian Type Operating System
Service mysql start
How to Repair mysql Table
We are ready to troubleshoot. Mysql check program enables and also helps us to repair our database, MYSQL is running. This is a very useful feature to want our database without stopping the entire MYSQL service.
MYSQL check works on two tables that use the two methods MYISAM and InnoDB database engine.
We follow these steps to use mysql check-
- We follow the following command as the root user.
cd/var/lib/mysql
2. We perform the following command to replace our database with the name of the database that we want to check.
mysql check database
3. In mysql, mysqlcheck checks the specified database and tables. We pass the check in the table, we display OK on mysql check, we report an error for a table in mysqlcheck. We perform the following command to repair. We replace our database with database name and then perform table with table name.
mysqlcheck -r database table
We go through the following procedure when mysqlcheck cannot successfully
Repair the table and tables.
Steps To Running Engine — Specific Diagnostics :
If our running mysqlcheck does not have the problem. The next step is to run specific to the engine used by the database table or tables. The following procedure below for your table’s database storage engine.
How to repair MYISAM tables with myisamchk :
If we are using the MYISAM storage engine for a table we can run the myisamchk program to repair it. We follow the following steps:-
- Firstly, we stop the MYSQL server using appropriate commands for the Linux operating System.
- For Fedora and Centos Type Operating System
Service mysqld stop
- For Ubuntu and Debian Type Operating System
Service mysql stop
2. We perform the following command -
cd/var/ lb/mysql
3. Now we change the directory , where our database is located. If our database name is customers, we type cd customers.
4. We type the following command replacing table, with the name of the table that we want to check-
Myisamchk table
5. If we try to repair a table we follow the following command replacing table with the name of the table that we want to repair-
my isamchk — — recover table
6. We start the mysql server using the appropriate Linux operating system.
- For Fedora and Centos type Operating System
Service mysqld start
- For Ubuntu and Debian type Operating System
service mysql start
7. Now we test repaired tables.
Procedures to InnoDB Recovery Process :
If we are using InnoDB storage engine for the database table we can run the InnoDB recovery process. We follow this following steps:
- We use our preferred text editor and then open my.cnf file on our server. This file location is for our linux operating system. The File location is my.cnf.
- On Fedora and Centos, the my.cnf file is located/any directory.
- On Ubuntu and Debian, the my.cnf is located in the/any/mysql directory.
2. We can locate the [mysqld] section in my.cnf file.
3. Now, we add the following line to the (mysqld section).
4. Now, here we save changes to the my.cnf file and then we restart our MYSQL server using the appropriate command for our linux os.
- For Fedora and Centos type OS
service mysqld restart
- For Ubuntu and Debian Type
service mysql restart
5. These are the following commands where we export all of the database to the databases.sql/file.
Mysqldump — all databases — -add-drop-database — add-drop-table — routines.
6. Here we start the mysql/program and then we try to drop the affected database or databases using the DROP DATABASE command.
7. Here we stop the MYSQL server using the following appropriate command for our linux operating system.
- For Fedora and Centos type operating system
Service mysqld stop
- For Ubuntu and Debian type operating system
Service mysql stop
8. If we are unable to drop a database in the above step which is step 6, then we follow the following command to delete it manually. Now we replace the database with the name of the database that we want to delete-
cd/var/lib/mysql
rm-rf-database
9. Now we perform the preferred text editor to open the my.cnf file on our server and then we comment out the following line in the [mysqld] section.
#innodb_force_recovery=4
10. Now we save the changes to the my.cnf file and then start the MYSQL server using the following appropriate command for our linux os.
- For Fedora and Centos Type
service mysqld start
- For Ubuntu and Debian type
service mysql start
11. We have to follow the following command to restore the database from the backup file which was created in step 5.
mysql< database.sql
12. Now we test the restored database.
DRS SQL Database Recovery Tool
As an alternative, users can opt for the outstanding SQL Database Recovery Tool by Data Recovery Solutions (DRS). The software is able to recover the entire inaccessible SQL database files. Also it allows the recovery of all the database objects such as records, tables, functions, rules, etc. It facilitates with a preview option to view the recovered database prior recovery. The utility supports all the versions of Windows OS. There is also a demo version available to allow users to evaluate the performance of the software for free.
Conclusion
In this blog above, we have discussed the methods to repair mysql databases. We can now conclude that manual methods have several limitations and risk of data loss. So it is suggested to use the advanced SQL Database Recovery Tool which provides the complete recovery without any hassle.
