HOW TO: Repair MySQL databases and tables

In this article, we will guide you to repair MYSQL databases and tables. Please follow the steps below:

STEP 1: BACKING UP THE DATABASES

Before you attempt to repair any database, you should back it up first. To back up all of the files from all of your databases, follow these steps:

1. Log in to your server using SSH.

2. Stop the MySQL server using the appropriate command for your Linux distribution:

- For CentOS and Fedora, type:
```
service mysqld stop
```
- For Debian and Ubuntu, type:
```
service mysql stop
```

3. Type the following command:

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

This command 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.

4. Restart the MySQL server using the appropriate command for your Linux distribution:

- For CentOS and Fedora, type:
```
service mysqld start
```
- For Debian and Ubuntu, type:
```
service mysql start
```

STEP 2: CHECKING AND REPAIRING A TABLE WITH MYSQLCHECK

After you back up your databases, you are ready to start troubleshooting. The?mysqlcheckprogram 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.

For information about how to determine which storage engine a database table is using, please see?this article.

To use?mysqlcheck, follow these steps:

1. As the root user, type the following command:

cd /var/lib/mysql

2. Type the following command, replacing DATABASE?with the name of the database that you want to check:

mysqlcheck DATABASE

The previous 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

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

4. If mysqlcheck?cannot successfully repair the table or tables, go to the following procedure.

STEP 3: RUNNING ENGINE-SPECIFIC DIAGNOSTICS

If running?mysqlcheck?does not fix the problem, the next step is to run diagnostics specific to the engine used by the database table or tables. Follow the appropriate procedure below for your table’s database storage engine.

For information about how to determine which storage engine your database tables are using, please see?this article.

Repairing MyISAM tables with myisamchk

If you are using the MyISAM storage engine for a table, you can run the myisamchkprogram to repair it. To do this, follow these steps:

Note: The myisamchk?program only works for tables that use the MyISAM storage engine. It does not work for the InnoDB engine.

Firstly, stop the MySQL server using the appropriate command for your Linux distribution:

- For CentOS and Fedora, type:
```
service mysqld stop
```
- For Debian and Ubuntu, type:
```
service mysql stop
```

2. Secondly, ype the following command:

cd /var/lib/mysql

3. Change to the directory where the database is located. For example, if the database is named customers, type?cd customers.

4. Next, ype the following command, replacing the TABLE?with the name of the table that you want to check:

myisamchk TABLE

To check all of the tables in a database, type the following command:

myisamchk *.MYI

If the previous command does not work, you can try deleting temporary files that may be preventing?myisamchk?from running correctly. To do this, change back to the?/var/lib/mysql?directory, and then type the following command:

ls */*.TMD

If there are any .TMD files listed, type the following command to delete them:

rm */*.TMD

Then try to run?myisamchk?again.

5. To try to repair a table, type the following command, replacing the TABLE?with the name of the table that you want to repair:

myisamchk --recover TABLE

6. After that, estart the MySQL server using the appropriate command for your Linux distribution:

- For CentOS and Fedora, type:
```
service mysqld start
```
- For Debian and Ubuntu, type:
```
service mysql start
```

7. Lastly, test the repaired table or tables.

Running the InnoDB recovery process

If you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process. To do this, follow these steps:

1. Firstly, use your preferred text editor to open the my.cnf?file on your server. The location of the?my.cnf?file depends on your Linux distribution:

- On CentOS and Fedora, the?my.cnf?file is located in the?/etc?directory.
- On Debian and Ubuntu, the?my.cnf?file is located in the?/etc/mysql?directory.

2. Secondly, in the my.cnf?file, locate the?[mysqld] section.

3. Add the following line to the [mysqld]?section:

innodb_force_recovery=4

4. Next, save the changes to the my.cnf?file, and then restart the MySQL server using the appropriate command for your Linux distribution:

- For CentOS and Fedora, type:
```
service mysqld restart
```
- For Debian and Ubuntu, type:
```
service mysql restart
```

5. After that, type the following command to export all of the databases to the databases.sql?file:

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

6. Start the mysql?program, and then try to drop the affected database or databases using the?DROP DATABASE?command.

If MySQL is unable to drop a database, you can delete it manually in step 8 below after you stop the MySQL server.

7. Stop the MySQL server using the appropriate command for your Linux distribution:

For CentOS and Fedora, type:
```
service mysqld stop
```
For Debian and Ubuntu, type:
```
service mysql stop
```

8. If you were unable to drop a database in step 6, type the following commands to delete it manually. Replace?DBNAME?with the name of the database that you want to delete:

cd /var/lib/mysql
rm -rf DBNAME

Make sure you do not delete the?mysql?or?performance_schemadirectories!

9. Next, use your preferred text editor to open the my.cnf?file on your server, and then comment out the following line in the?[mysqld]?section as shown:

#innodb_force_recovery=4

This disables InnoDB recovery mode.

10. Save the changes to the my.cnf?file, and then start the MySQL server using the appropriate command for your Linux distribution:

For CentOS and Fedora, type:
```
service mysqld start
```
For Debian and Ubuntu, type:
```
service mysql start
```

11. Then, type the following command to restore the databases from the backup file you created in step 5:

mysql < databases.sql

12. Lastly, test the restored database.

앱쫴먹

HOW TO: Repair MySQL databases and tables