Checking health of MySQL tables, repairing & rebuilding indexes
Corruption of data leads to loss of data integrity in a database which is definitely a thing to avoid. Fortunately we can perform regular sanity checks in order to identify issues that would stay unnoticed otherwise. Data corruption may occur when i.e. we perform a large update or insert and our database is shutdown unexpectedly. This scenario is simulated below, we’ll simply add some characters into the database files.
Prerequisites
Of course there should be a MySQL database installed on your machine. If you’re missing it, get one here. We’re also going to create some procedures which along with shown data corruption means the below-described test shall not be applied in a production environemnt.
The test itself - Data Corruption
As mentioned above, let’s connect to the database and let’s create our procedure and tables.
#ensure that your MySQL database is running, execute the command below
ps -ef | grep mysql
--we should see more than one line, if the process hasn’t been started yet,
execute the command below (valid for openSUSE/SUSE Linux)
service mysql start
#login to mysql console and enter password when prompted
mysql -u root -p -A
--now connect to the database test
use test;
--now we’re going to create a table with 1K of rows and
--we’re also going to show our datadir variable
--issue the command below, so we have our procedure created
delimiter $$
CREATE PROCEDURE `test_corruption`()
begin
declare i int Default 0 ;
declare random char(20) ;
myloop: loop
set random=conv(floor(rand() * 99999999999999), 20, 36) ;
INSERT INTO `test_corruption` VALUES ( NOW() , random );
set i=i+1;
if i=1000 then
SHOW VARIABLES LIKE 'datadir';
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;
--now let’s create our table which will be corrupted
CREATE TABLE `test_corruption` (
`datecol` datetime NOT NULL,
`name` varchar(25) NOT NULL,
KEY `datecol` (`datecol`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--let’s call the procedure, be ready your database goes down!
call test_corruption();
--the procedure will give something like following:
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
--now open another terminal and go to the datadir directory as root by executing following:
su
#now enter password and press ENTER
#once you’re logged as root, go to MySQL datadir (path can be different on your machine,
#it’s shown as procedure output)
cd /var/lib/mysql/
cd test #because we’re using database called test
#now search for files that belong to test_corruption table
ll test_corruption*
#OK, let’s simulate index corruption - execute following command (still as root)
cat test_corruption.MYD >> test_corruption.MYI
#now return to the terminal where you’re logged in MySQL and execute following command:
check table test_corruption;
--you will see a warning message, in order to fix this you have to issue following SQL command:
repair table test_corruption;
--our index is repaired, let’s simulate data corruption
--return to the terminal where you’re logged as root and where you’re in MySQL datadir
#now execute following:
cat test_corruption.MYI >> test_corruption.MYD
#data is now corrupted, this can be checked by returning to the MySQL console and executing:
check table test_corruption;
--now we should see more than warning, let’s repair it
repair table test_corruption;
--imagine you’ve lost the MYI file, this can be simulated by executing following command in
--your OS shell (you should be in MySQL datadir)
rm test_corruption.MYI -r -f
#now get back to the MySQL console and execute following command:
check table test_corruption;
--we’ll get an error and we have to restore the MYI file from FRM; execute following:
repair table test_corruption USE_FRM;
--once finished, our MYI file is restored and corruption is gone
How to rebuild an index
Indexes can be rebuilt using various commands, they’re explained below:
--you should be logged in the MySQL console as it’s described at the top of this page
--it’s also recommended to have one table for testing purposes, above-mentioned test_corruption is ideal
--in order to rebuild indexes, execute following:
REPAIR table test_corruption;
--this will lock table until it’s rebuilt. Even SELECT statements will wait for unlock!
--another way how to rebuild indexes is rebuilding entire table
--find out engine of your table by executing following SQL statement:
SHOW create table test_corruption;
--current engine should be MyISAM, rebuild of entire table is done when following command is being issued:
ALTER table test_corruption engine=MyISAM;
--alter table usually finishes sooner than repair table
Cleanup
If everything is clear, execute following commands in order to cleanup all above.
--drop the table
DROP table test.test_corruption;
--remove the procedure
DROP procedure test.test_corruption;
Conclusion
Rebuild of indexes may consume lots of resources especially when there is plenty of rows in a table (100M+). This action should be used when there’s little traffic on the database, or in case it takes too long to read data from our table and further waiting is not acceptable. Bear in mind partitioning should be used if your database contains enourmous amounts of data.
Further reading & help
If you need help with your MySQL database, drop me a line. You’re also welcome to visit other articles devoted to MySQL - they’re listed below.
- Performing regular backups and point-in-time recovery
- Enabling logging of issued SQL statements
- Understanding indexes
- Memory tuning & filesystem tuning & data optimisation
- Moving datafiles between two databases
- Difference between MYISAM and InnoDB engines
- Partitioning
- Enabling remote access to a MySQL database
- How to setup replication
- Rebuilding indexes and checking health of tables