Symptoms of corrupted tables
Corrupted tables can cause a high load, or cause MySQL to crash fairly often. If you do have a problem with corrupted tables, you should see entries in the MySQL error log indicating this. The error log is usually located at /var/lib/mysql/HOSTNAME.err.
In that filename, "HOSTNAME" indicates the base hostname of your server. You can determine your server's hostname with the following command:
hostname
The most common error message in this log file that indicates you should attempt a repair on the MySQL tables is (errno: 145), but you should also attempt a repair if you see many of the following error codes: 126, 127, 132, 134, 135, 136, 141, 144
The easy way to repair
You may have a tool on your server called "mysqlcheck". If so, you can attempt an automatic repair with the following command:
mysqlcheck -A --auto-repair
The mysqlcheck tool should be run while MySQL is running. Do not stop MySQL before attempting a repair using this method.
The advanced method
WARNING: The information below is intended for users with advanced knowledge of operating systems, control panels, and other aspects of server management. Do NOT simply copy and paste commands to resolve issues as you may severely harm your server, cause downtime, or incur billable support not covered under your DEFCON plan. Please open a support ticket if you feel uncomfortable with the suggestions provided.
Read me first
Myisamchk uses /tmp while repairing, so if a table is larger than the /tmp partition, you can't repair that one with the following steps.
If you /tmp is smaller, you can repair it utilizing a temp tmp folder... something like the following:
myisamchk -r -q database/table.MYI --tmpdir=/home/tmp
Prep work
Before you start modifying tables, you should stop MySQL or you'll get a lot of false positives on the check, and a lot of problems on the repair. So before you go any further, stop MySQL. Even before that, if you have cPanel, you should first stop chkservd:
/etc/init.d/chkservd stop
Chkservd is cPanel's service monitor. It checks every eight (8) minutes to ensure all services are running, and if it detects any problems it will attempt to restart the service.
After chkservd is stopped, or if you do not have cPanel installed, stop MySQL:
/etc/init.d/mysql stop
Potential problem with myisamchk
Myisamchk uses /tmp while repairing, so if a table is larger than the /tmp partition, you can't repair it with the following steps.
If your /tmp partition is smaller than your largest database, you will have to specify a different tmp location. Here's an example:
myisamchk -r -q database/table.MYI --tmpdir=/home/tmp
Analysis and repair
Now we need to analyze the tables to see where the corruption is. The tables are located within /var/lib/mysql/. Every subfolder represents a database. The general idea is to run `myisamchk -s ./[database]/[table]. The -s flag means silent. Without it, you will receive a lot of status information that is irrelevant to the problem at hand. Usually there are a few more than a couple databases, so doing this manually can be a pain. Run a 'for' loop:
cd /var/lib/mysql
for x in `find * | grep .MYI`; do myisamchk -s $x; done
This should print out error reports on all of the corrupt tables. For every corrupt table, run the following command:
myisamchk -r -q [table]
Once the repair has completed, you want to make sure it actually accomplished what you sent it in to do. Run another check:
for x in `find * | grep .MYI | grep -v ".bak" | grep -v ".back"`; do myisamchk -s $x; done
If that reports any problems, it means the previous repair didn't fix everything. That's not a huge deal, because we just used the most simplistic and least invasive repair method. The other methods have a fair chance of causing damage, so you'll want to back things up from here.
Make a copy of any tables you are about to repair:
cp [database]/brokentable.MYI{,.bak}
Now try running a beefier repair against the table. Don't use the -q flag.
myisamchk -r [database]/brokentable.MYI
Once you've tried to repair the tables again, run another check. If it still reports errors, try using -o instead of -r. The -o flag uses an older repair strategy that tries a few more things than the newer, but is slower.
myisamchk -o [database]/brokentable.MYI
Run one more check to be sure you've caught everything.
Wrap-up
When you are done repairing the tables, don't forget to start MySQL again:
/etc/init.d/mysql start
And, if you're running cPanel:
/etc/init.d/chkservd start
Corrupted tables can cause a high load, or cause MySQL to crash fairly often. If you do have a problem with corrupted tables, you should see entries in the MySQL error log indicating this. The error log is usually located at /var/lib/mysql/HOSTNAME.err.
In that filename, "HOSTNAME" indicates the base hostname of your server. You can determine your server's hostname with the following command:
hostname
The most common error message in this log file that indicates you should attempt a repair on the MySQL tables is (errno: 145), but you should also attempt a repair if you see many of the following error codes: 126, 127, 132, 134, 135, 136, 141, 144
The easy way to repair
You may have a tool on your server called "mysqlcheck". If so, you can attempt an automatic repair with the following command:
mysqlcheck -A --auto-repair
The mysqlcheck tool should be run while MySQL is running. Do not stop MySQL before attempting a repair using this method.
The advanced method
WARNING: The information below is intended for users with advanced knowledge of operating systems, control panels, and other aspects of server management. Do NOT simply copy and paste commands to resolve issues as you may severely harm your server, cause downtime, or incur billable support not covered under your DEFCON plan. Please open a support ticket if you feel uncomfortable with the suggestions provided.
Read me first
Myisamchk uses /tmp while repairing, so if a table is larger than the /tmp partition, you can't repair that one with the following steps.
If you /tmp is smaller, you can repair it utilizing a temp tmp folder... something like the following:
myisamchk -r -q database/table.MYI --tmpdir=/home/tmp
Prep work
Before you start modifying tables, you should stop MySQL or you'll get a lot of false positives on the check, and a lot of problems on the repair. So before you go any further, stop MySQL. Even before that, if you have cPanel, you should first stop chkservd:
/etc/init.d/chkservd stop
Chkservd is cPanel's service monitor. It checks every eight (8) minutes to ensure all services are running, and if it detects any problems it will attempt to restart the service.
After chkservd is stopped, or if you do not have cPanel installed, stop MySQL:
/etc/init.d/mysql stop
Potential problem with myisamchk
Myisamchk uses /tmp while repairing, so if a table is larger than the /tmp partition, you can't repair it with the following steps.
If your /tmp partition is smaller than your largest database, you will have to specify a different tmp location. Here's an example:
myisamchk -r -q database/table.MYI --tmpdir=/home/tmp
Analysis and repair
Now we need to analyze the tables to see where the corruption is. The tables are located within /var/lib/mysql/. Every subfolder represents a database. The general idea is to run `myisamchk -s ./[database]/[table]. The -s flag means silent. Without it, you will receive a lot of status information that is irrelevant to the problem at hand. Usually there are a few more than a couple databases, so doing this manually can be a pain. Run a 'for' loop:
cd /var/lib/mysql
for x in `find * | grep .MYI`; do myisamchk -s $x; done
This should print out error reports on all of the corrupt tables. For every corrupt table, run the following command:
myisamchk -r -q [table]
Once the repair has completed, you want to make sure it actually accomplished what you sent it in to do. Run another check:
for x in `find * | grep .MYI | grep -v ".bak" | grep -v ".back"`; do myisamchk -s $x; done
If that reports any problems, it means the previous repair didn't fix everything. That's not a huge deal, because we just used the most simplistic and least invasive repair method. The other methods have a fair chance of causing damage, so you'll want to back things up from here.
Make a copy of any tables you are about to repair:
cp [database]/brokentable.MYI{,.bak}
Now try running a beefier repair against the table. Don't use the -q flag.
myisamchk -r [database]/brokentable.MYI
Once you've tried to repair the tables again, run another check. If it still reports errors, try using -o instead of -r. The -o flag uses an older repair strategy that tries a few more things than the newer, but is slower.
myisamchk -o [database]/brokentable.MYI
Run one more check to be sure you've caught everything.
Wrap-up
When you are done repairing the tables, don't forget to start MySQL again:
/etc/init.d/mysql start
And, if you're running cPanel:
/etc/init.d/chkservd start
No comments:
Post a Comment