How to repair MySQL databases and tables

How to repair MySQL databases and tables

To repair MySQL databases and tables there are below steps

1. Backing up the databases

Step 1: Stop mysql
         service mysqld stop

Step 2: Use Below command to copies all of the files from all of your databases to a directory name based on the current time (more precisely, the number of seconds elapsed since January 1, 1970). This ensures that each database backup is stored in a directory that has a unique name. For added protection, you can (and should) back up the database files to a remote location not on the server.

      cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

Step 3: Start mysql
            service mysqld start

2. Checking and repairing a table with mysqlcheck 

   After you back up your databases, you are ready to start troubleshooting. The mysqlcheck program enables you to check and repair databases while MySQL is running. This feature is useful when you want to work on a database without stopping the entire MySQL service.

Additionally, mysqlcheck works on tables that use the MyISAM or InnoDB database engines.

Step 1. To use mysqlcheck  As the root user

           cd /var/lib/mysql

Step 2. Replacing DATABASE with the name of the database that you want to check:

           mysqlcheck DATABASE

The above command checks all of the tables in the specified database. Alternatively, to check a specific table in a database, type the following command. Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:

           mysqlcheck DATABASE TABLE

Step 3: Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table. However, if mysqlcheck reports an error for a table, type the following command to try to repair it. Replace DATABASE with the database name, and TABLE with the table name:

mysqlcheck -r DATABASE TABLE