Best Methods to repair corrupted large MySQL MyISAM tables

Usually the easiest way to repair a corrupted table is:
Repair table [table name] quick; [provided corruption occurred in index file]
This repair method attemps to “repair by key cache”. The approach is okay if you are dealing with small tables. However, this can take forever for large MyISAM tables. The best approach to repair large multi-gigabyte MyISAM tables is using sort method. Following methods repairs table via sort mechanism:

USING MYISAMCHK:
1. Create a copy of corrupted file and run following command :
myisamchk --sort-recover --sort-buffer-size=2G /table_name

This would repair the table using sort method. You can also specify tmpdir at command prompt i.e
myisamchk --sort-recover --sort-buffer-size=2G /table_name –tmpdir=/tmp/repairdir

Make sure tmpdir has enough space available.

Once repaired, repair corrupted file with this repaired file.


As a good practice, always keep a backup of original file.


USING myisam_max_sort_file_size:
This variable specifies maximum size of the temporary file that MySQL can use to repair a table. If the file size will be larger, repair is done using key cache method. Set it to a real high value and then use the repair command:
1. set global myisam_max_sort_file_size=223372036853727231;
2. repair table table_name;


Repairing tables (for example 20GB+) can be a big pain. It is very important to understand various repair options available, and choose the right strategy

How to install MySQL in an enterprise manner

I am not a big fan of default MySQL installations. Just like any other enterprise rdbms installation, I prefer installing MySQL under a dedicated db mount. Following steps talk about installing MySQL in an "enterprise" manner:

1. Get rid of /etc/my.cnf. This cnf has the highest priority and I have seen many individuals running in issue just because they cannot identify the my.cnf setting being picked my MySQL engine

2. Ask system admin to create user mysql with dba group. Also ask for a separate DB mount. For example: /MYDB with permission granted to mysql:dba

3. set up following environment variables (preferrably in .bash_profile):
MYSQL_DFLT_DB=TESTDB;export MYSQL_DFLT_DB
MYSQL_HOME=/MYDB/$MYSQL_DFLT_DB/product/5.1.xx;export MYSQL_HOME
PATH=$PATH:$MYSQL_HOME/bin;export PATH
MYSQL_DATA=$MYSQL_HOME/data;export MYSQL_DATA
MYSQL_UNIX_PORT=$MYSQL_HOME/data/mysql.sock;export MYSQL_UNIX_PORT
MYSQL_TCP_PORT=3306; export MYSQL_TCP_PORT

4. confirm that your environment is setup correctly:
env | grep -i mysql

5. Now is time to download the software
if you are mysql customer, download latest appropriate binary release from http://enterprise.mysql.com (preferred). If you are not a customer, and intend to use gpl license sofware, download the community software from : http://dev.mysql.com/downloads/
Make sure to download binaries that match your operating system

6. install the software (using a generic install example):
cd /MYDB/TESTDB/product
unzip MySQL-server-5.1.30-0.glibc23.i386.rpm
rename folder to 5.1.30
cd $MYSQL_HOME
scripts/mysql_install_db --user=mysql
Check the output of this command. This step completes binary installation

7. post configuration tasks:
Configure MySQL instance:
cd $MYSQL_DATA
copy my.cnf from support folders on to this folder. Change the settings as per your requirements
create appropriate folders for archival redo (for example /MYDB/TESTDB/arch etc)

8. Now you are all set to start the database:
cd $MYSQL_HOME
bin/safe_mysqld --user=mysql &

9. Confirm that instance is running
ps -ef | grep mysqld
check logfile

10. logging for the first time:
mysql -u root -p
for password press enter

11. Change mysql root password:
grant all on *.* to 'root'@'localhost' identified by 'test123'
or
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test123');


Now you are all set to reap benefits of wonderful MySQL. I think it is always to follow appropriate structures for install MySQL (no matter how small your database is going to be). Companies grow and so do their databases. Following standard approaches just makes your life easier... more to come