Troubleshoot and Recover from Common MySQL Database Problems

  

 Email Article  

Tweet  

  

MySQL is the most popular open source database, but though it has a reputation for reliability and stability, it's not bulletproof. When MySQL starts throwing error messages at you, you need to be able to field them cleanly. Here's how to troubleshoot and recover from the most common MySQL problems.

The time you're most likely to see a MySQL error is when a query fails within a web application. However, debugging information is usually disabled in web applications for security reasons. You may have to check the application's log file or enable debugging in order to be able to see what query causes the failure and possibly the reason for it. Doing so often uncovers incorrect user privileges or connectivity problems.

If the application itself cannot provide enough troubleshooting information, next check MySQL's log file. By default in CentOS, the mysqld service logs all information, including warnings and errors, in the file /var/log/mysqld.log. This log shows global problems such as storage engine crashes.

If you're still stumped, go through the general troubleshooting process described in the article How to Troubleshoot Your CentOS Linux Server. As you follow this general guide, keep in mind a few MySQL-specific basics. First, mysqld runs under the privileges of the user mysql, which belongs to a group also called mysql. Second, all MySQL data is stored in the directory /var/lib/mysql/. This directory has to be owned by the mysql user in order for mysqld to work.

Once you have determined the MySQL problem, solving it may be simple; for instance, you can solve some connectivity problems by adding a firewall rule. Unfortunately some problems, and especially ones with MySQL's storage engines, are not so easily fixed.

MySQL Storage Engine Troubleshooting

MySQL keeps its database information in storage engines. The two most widely used MySQL storage engines are MyISAM, the default in MySQL for all versions before 5.5.5, and InnoDB, the current default. Just as the engines differ in features and design, so too do problems with them vary.

To begin troubleshooting problems with MySQL engines, start by checking what engines are supported on the server by running the MySQL command show engines. Some problems are caused simply by the fact that you are trying to run a query that requires a certain engine that is not supported on the server.

A storage engine is usually disabled for better performance reasons if it has not been used before. Check MySQL's configuration file /etc/my.cnf and MySQL's storage engines manual for more information.

The most common MySQL engine problems are connected with the default MyISAM engine. For example, a query may fail with the error Table 'example' is marked as crashed and should be repaired. Such an error indicates an isolated problem only with the example table. The rest of the databases, database tables, and the server as a whole continue to function properly.

You can get corrupted MyISAM tables when the mysqld process writes information to a table and the operation is interrupted before completion, as it can be for example in the event of a power failure. To repair a corrupted MyISAM table, use the Linux command /usr/bin/myisamchk. As arguments it takes the options -r for repair and the full path to MyISAM's table index file (MYI). Thus to repair the example table from the example database, run /usr/bin/myisamchk -r /var/lib/mysql/example/example.MYI.

Problems with InnoDB are more severe and complex to fix, but they arise for the same kinds of reasons as problems with MyISAM, including sudden interruption of the MySQL server while updating information, perhaps caused by faulty hardware, a software bug, or power outage.

InnoDB problems appear in the server log (/var/log/mysqld.log) starting with 'InnoDB:'. They may include a warning or error about an operation that cannot be completed because the server was not shut down properly and/or a suggestion that a table is corrupted.

To fix InnoDB errors, stop the MySQL server using the CentOS command service mysqld stop, or, if necessary, even kill -9 PID_OF_MYSQLD. You should stop the MySQL service as soon as possible after you discover InnoDB problems in order to avoid even more problems and data loss.

Next, edit MySQL's configuration file (/etc/my.cnf) and set an InnoDB recovery level by adding the line innodb_force_recovery = 4 after the [mysqld] configuration section. Level 4 means average tolerance against problems and data loss; a lower number means lower tolerance – that is, better recovery but with increased chance of failure. For information about all recovery levels, check the InnoDB recovery manual.

Restart the mysqld service and try accessing the corrupted table. You should be able to perform all MySQL operation (INSERT, UPDATE, SELECT). However, you should not work in recovery mode for long; rather, you should dump the corrupted table, drop it from the database, and import it from the dump as soon as possible.

Once you have imported back the corrupted InnoDB table, stop the server, remove the line innodb_force_recovery = 4 from /etc/my.cnf, and start the server again.

This InnoDB recovery procedure may seem tedious and risky, but it's the only one officially documented. You can also try using software such as Percona's InnoDB recovery tool at your own risk.

MySQL Full Recovery

Sometimes recovering MySQL is not as easy as fixing a corrupted table. An important file such as ibdata1, InnoDB's main data storage file, may have been deleted, and that would leave you in a situation without options for automatic recovery. Even worse, the database server could have become the victim of a human mistake in which data was deleted, and there is no such a thing as undo in MySQL.

In some cases, running MySQL in high availability mode can allow you to use a slave server while recovering the master. That won't help with manually deleted information though, because the wrong query would have been replicated immediately to the slave server.

When everything else fails, you can turn to your latest full backup in combination with MySQL's binlog file. You can use the binlog file to restore the lost information from the gap between the time of the backup and the time of the failure. The catch is that, by default, MySQL's binary logging is not enabled in CentOS. You should edit /etc/my.cnf and add log-bin to the [mysqld] section. After restarting mysqld, use MySQL's console to run the command show binary logs to confirm it is working.

When you have MySQL binary logs, full database recovery takes the following three steps:

1. Dump the events for your database since the last backup. For example, if you ran the last full backup of the example database on August 29 at 3:00 a.m., run mysqlbinlog --database=example --start-datetime="2012-08-29 03:00:00" /var/lib/mysql/mysqld-bin.000001 > example_incremental_dump.sql. Examine the example_incremental_dump.sql file and remove the incorrect data deletion command from the file.
2. Restore the latest MySQL full backup for the database example: mysql example < example.sql.
3. Import the dump from the binary log with the command mysql example < example_incremental_dump.sql

By following these simple steps you can restore one or more databases with minimum data loss, and without using expensive third-party tools or investing time in additional qualification. MySQL's reliability and recovery options are among the reasons MySQL is a favorite both in the community and in the enterprise.