How To Repair Mysql Table

Introduction

MySQL offers several different ways to analyze and repair databases. Errors and anomalies appear for numerous reasons, some of which include:

• A MySQL update or downgrade.
• Rapid changes in indexes or information.
• Table inconsistencies.

When checking for corrupted data and repairing tables, the steps taken to fix the errors depend on how you connect to the database, the database server state, the blazon of mistake, and the database engine used.

This commodity shows you multiple ways to troubleshoot and repair a MySQL database.

Prerequisites

• Terminal or command line with root privileges.
• MySQL version eight.0 installed and configured.
• A corrupt database or database tabular array.

How to Check MySQL Table for Errors

Running diagnostics is the first pace to troubleshooting for issues. There are two means to bank check a MySQL tabular array for errors:

• Through a MySQL Bank check Table query.
• Using the terminal plan mysqlcheck .

Bank check a Tabular array with CHECK Tabular array

The Bank check Tabular array query works in MySQL while the service is running. The full general use is:

          Cheque Tabular array <table name> [, table name, table name] [option] [pick];        

The simplest use is one tabular array without any options:

          CHECK Tabular array <table proper name>;        

The output of the Bank check Table query shows an informative table about the cheque results:

The available options when checking tables are:

• FOR UPGRADE - discovers version inconsistencies. For instance, MySQL 8.0 does not back up 2-digit years, so a table containing such values is marked for upgrade.
• QUICK - does non bank check rows for incorrect linking. This is helpful in avoiding timeouts.
• FAST - performs a bank check on tables that are not airtight properly.
• CHANGED - only checks tables with changes since the terminal check or that have not closed properly.
• MEDIUM - checks table rows to verify the validity of deleted links and performs a checksum for the rows.
• EXTENDED - does a check for consummate consistency.

Without any options, CHECK TABLE performs a MEDIUM check on MyISAM tables and views. The FOR UPGRADE and QUICK perform checks on InnoDB engine tables and views while other options are ignored.

You lot can also combine the options for a more detailed check. For example, to do a quick bank check to detect out whether a table closed adequately, run:

          CHECK Table <table name> FAST QUICK;        

Any options that do not apply to the tables are ignored.

Check a Table With mysqlcheck

The mysqlcheck control is the control-line version of CHECK Tabular array . The plan runs in the last while the MySQL service is operating. To perform the bank check:

one. As the root user, navigate to the directory where the databases are stored:

          sudo su cd /var/lib/mysql                  

2. Cheque the whole database with:

          mysqlcheck <database proper noun>        

Alternatively, check a specific table within the database by providing the table proper name as well:

          mysqlcheck <database name> <table name>        

How to Repair MySQL Database

In that location are several methods how to repair a database in MySQL. However, keep in listen that these methods are not a quick ready and tin can result in data loss. If the database tables are decadent often, identify the reason for the occurrence.

Before making whatever changes and starting repairs, create a copy of the directory:

          cp -rfv /var/lib/mysql /var/lib/mysql_copy        

Alternatively, brand a fill-in version of the database before trying any of the repair solutions. Follow our guide: How to Back Upwardly & Restore a MySQL Database.

Beneath you will observe three options how to repair a MySQL database.

Repairing MySQL Database with REPAIR TABLE Query

Afterwards scanning and locating the database and the tables with issues, the quickest way to fix the issue is with the REPAIR Table query:

If the server shuts downwards during the repair, rerun the REPAIR TABLE operation when the server starts again. Make sure to practise this before performing whatsoever other operations on the same tabular array. In normal conditions, a MyISAM tabular array will non result in data loss from using this method.

Repairing MySQL Database with mysqlcheck

The mysqlcheck control is an alternative to repairing database tables in the concluding.

i. First, navigate to the MySQL database folder as the root user:

          cd /var/lib/mysql        

2. Add the -r selection to the mysqlcheck command to repair the database table:

          mysqlcheck -r <database> <table name>        

The mysqlcheck -r command is the terminal version of the REPAIR Table query.

Repairing MySQL Database with ALTER Table

Utilize the ALTER Tabular array query to rebuild the tabular array using the same storage engine. For example, if you have an InnoDB table, run:

          Modify Tabular array <tabular array proper name> ENGINE = InnoDB;        

To rebuild a MyISAM tabular array, utilize:

          Change Tabular array <table name> ENGINE = MyISAM;        

If yous are unsure which storage engine the tabular array uses, run the following query to find out:

          Prove CREATE TABLE <tabular array name>;        

Running Engine-Specific Diagnostics and Repairs to MySQL Database

Depending on the database engine used, MySQL provides engine-specific approaches for analyzing and repairing a database tabular array.

MyISAM tables are prone to corruptions. However, most of the time the errors are easy to set up.

When working with InnoDB databases, corruptions appear when the checksum values don't lucifer. Errors typically happen due to a power outage or some other hardware upshot.

Checking and Repairing MyISAM Tables with myisamchk

MySQL provides a specific checker for MyISAM tables.

i. Shut downward the MySQL server. Open the terminal and run:

          systemctl mysql stop        

2. As the root user, navigate to the location of the database:

          cd /var/lib/mysql/<database proper name>        

3. To cheque a specific table, run:

          myisamchk <table name>        

4. Repair a table past running:

          myisamchk --recover <table name>        

5. Restart the MySQL server:

          systemctl restart mysql        

6. Lastly, check the table in MySQL afterward recovery:

          sudo mysql -u <user> -p <password> USE <database name>; SELECT * FROM <table proper noun> LIMIT four;        

Running the InnoDB Recovery Process

MySQL documentation suggests running the InnoDB recovery process for InnoDB databases.

ane. Locate and open up the global configuration file using your favorite text editor. For Ubuntu and Debian systems, the location is:

          cd /etc/mysql/my.cnf        

Note: For CentOS and Fedora, the location is:

            cd /etc/my.cnf          

2. Add together the following choice to the my.cnf file:

          [mysqld] innodb_force_recovery=4                  

Past default, the forced recovery choice is set to 0 (off) and goes up to six. Every new level includes the recovery features of the lower numbers.

three. Relieve and shut the file.

4. Restart the MySQL server to ensure the new configuration file applies:

          systemctl restart mysql        

5. In the home binder, export all the databases to a backup file with the mysqldump control:

          sudo mysqldump --all-databases --add-drib-database --add-drop-table --routines > <file name>.sql        

6. Start MySQL client and drop all the affected database tables. To drop a database table, endeavour:

          DROP TABLE IF EXISTS <table proper noun>;        

If the tabular array does not drop, try removing the database table manually. Log in as the root user and navigate to the directory where the databases are:

          sudo su        

          cd /var/lib/mysql/<database name>        

Remove the database table:

          rm -r <table proper noun>        

7. Stop the MySQL server:

          systemctl stop mysql        

eight. In the my.cnf file, comment out the innodb_force_recovery option added in pace ii to disable the recovery way. Then, save the file and shut.

9. Showtime the MySQL server up over again:

          systemctl start mysql        

10. Restore the database from the fill-in .sql file in the MySQL vanquish. Log into the MySQL customer:

          sudo mysql -u <username> -p <countersign>        

11. Run the post-obit query to import the databases:

          Utilise <database proper noun>; # Turning off auto commit is optional and speeds things up for larger databases Set autocommit=0;  SOURCE <file name>.sql;                  

12. Lastly, test the database to confirm everything works correctly. If not, restore the database from backup and effort an alternating method.

Decision

This tutorial outlines several unlike methods to repair a MySQL database. Since database corruptions are bound to happen, MySQL provides multiple ways to discover and fix errors in a database, some of which are outlined in this article.

All the same, since recovery sometimes results in information loss, the best way to protect your information is to create regular backups and follow the all-time database security practices.

Was this article helpful?

Aye No

Source: https://phoenixnap.com/kb/mysql-repair-database

Posted by: morantfaren1991.blogspot.com

Share this post