Advanced MySQL Administration
 
Maintenance of MyISAM Tables
 
MyISAM tables are stored in the files dbname/tablename.MYD (data) and dbname/tablename.MYI (indexes). This allows the simple copying and moving of tables and databases. However, such operations are allowed only if the server is not using the databases (the best procedure is to stop the server for this purpose).
 
In rare cases, it may be necessary to work with the MyISAM table files directly, such as to restore indexes or repair damaged files. This section offers the tools that you will need.
 
myisamchk
 
The command myisamchk is, in a sense, a universal tool for the maintenance of MyISAM tables. With this command you can accomplish the following:
 
. Check the integrity of MyISAM tables.
. Repair damaged MyISAM table files (e.g., after a power outage).
. Release unused storage space in MyISAM files.
. Recreate the index to MyISAM tables (for example, after a change in the sort order of the MySQL server).
 
Instead of myisamchk,you can also use the following SQL commands:
 
. ANALYZE TABLE provides information about internal index management.
. CHECK TABLE tests the table file for errors in consistency.
. OPTIMIZE TABLE optimizes the use of storage space in tables.
. REPAIR TABLE attempts to repair defective tables.
 
These commands currently work only for MyISAM tables. However, it is possible that their effectiveness will someday extend to other types of tables. The advantage of the SQL commands over myisamchk is that you do not need to worry about the MySQL server and myisamchk interfering with each other.
 
The disadvantage is that the MySQL server must be running (which may be problematic after a crash), that under some circumstances not all errors can be corrected, that there are fewer options for control of the process, and that the SQL commands are somewhat slower in their execution.
 
An extension to myisamchk is the command myisampack, with which MyISAM tables can be compressed. In this way, a great deal of space can be saved. However, only read access to such tables is then possible. At the end of this section we shall have more to say about myisampack.
 

 

Pointer 

A reference to all options of myisamchk and myisampack can be found in Chapter 19. Further information on the use of myisamchk, particularly for the repair of defective table files, can be found in the MySQL documentation: 

http://www.mysql.com/doc/en/Table_maintenance.php
 
Using myisamchk
 
The syntax of the myisamchk command is as follows:
 
myisamchk [options] tablename1 tablename2 ...
 
The table names are given as complete file names, either with or without the ending *.MYI (but, surprisingly, not with *.MYD). Depending on the options specified, however, both MyISAM files, that is, name.MYD (data) and name.MYI (indexes), are analyzed or changed.
 
To check on the integrity of all tables in the database mydatabase you should execute the following command. (You must, of course, replace /var/lib/mysql with your actual database directory.)
 
root# myisamchk /var/lib/mysql/mydatabase/*.MYI
 
You can use myisamchk independently of the MySQL server (the server may be running, but it does not have to be). If the server is running, then mysqladmin flush-tables or the SQL command FLUSH TABLES must be executed first.
 

 

Caution 

If myisamchk actually changes MyISAM files and not just checks them, it must be ensured that the MySQL server does not change any data during this time.
Therefore, you must execute if necessary the SQL command LOCK TABLES with mysql, followed by myisamchk, and then, finally, UNLOCK TABLES. You must not leave mysql during this time, since otherwise, the LOCKswould end.

 
Speed Optimization, Memory Usage
 
In the case of large tables, the analysis, and even more the repair, of tables is a very costly operation. The speed of myisamchk depends greatly on the amount of available RAM.
 
The memory usage of myisamchk is set by four variables. In the default setting, myisamchk requires about 3 megabytes of RAM. If you have more memory to squander, then you should raise the values of the appropriate variables, since then myisamchk will execute much more quickly for large tables. The MySQL documentation recommends the following values:
 
root# myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M
 
Here x should represent about one-fourth of available RAM (e.g., 64 MB on a 256-MB computer).
 
Furthermore, for repairing database files myisamchk requires a huge amount of space on the hard drive (among other reasons, because a copy of the database file is first made). A copy of the file is placed in the directory specified by the environment variable TMPDIR.You can also specify this directory via --tmpdir.
 
Shrinking and Optimizing MyISAM Tables
 
The MyISAM table driver attempts, normally, to keep table files as small as possible. However, if you delete a large number of records from your tables or if you often carry out changes to records with columns of variable size (VARCHAR, xxxTEXT, xxxBLOB), then the optimization algorithm runs up against its limit. In the worst case, the database files are significantly larger than necessary. Moreover, the data are scattered throughout the file, which slows down access to the database.
 
The following command provides some assistance. It regenerates the database file and optimizes the index file in view of providing the speediest access to the database. With the option --set-character-set the character set is specified for the sort order. (You must be dealing with the same character set with which the MySQL server is running.) The effect of --check-only-changed is that only those tables are processed that were changed since the last processing by myisamchk.
 
root# myisamchk --recover --check-only-changed --sort-index \ --analyze --set-character-set=xxx databasepath/*.MYI
 
Word of advice

If myisamchk is unable to find the character set files, you must specify it explicitly with --character-set-dir.UnderWindows, the files can be found under a standard installation in C:\[mysql_install_dir]\share\charsets.
 
Repairing MyISAM Tables
 
For me, this section is largely of a theoretical nature, because fortunately, I have thus far had no problems with corrupt MyISAM tables. Corrupted files can arise when the database is stopped by a power failure, when MySQL or the operating system crashes, or if MySQL or the MyISAM table driver contains errors (which is rather unlikely, however).;
 
Damaged MyISAM files make themselves known in MySQL service by error messages like Index-file/Record-file/Table is crashed or No more room in index/record file. In such a case, myisamchk will not, of course, be able to work a miracle. Data that for some reason are no longer available or have been overwritten cannot be restored. However, myisamchk can repair the database to the extent that at least all other records can again be read:
 
root# myisamchk --recover --set-character-set=xxx databasepath/*.MYI
 
If you suspect that only the index file has been affected, then execute myisamchk with the additional option --quick (which is considerably faster). In this case myisamchk regenerates the index file.
 
In particularly difficult cases, that is, when myisamchk --recover fails, you can attempt recovery with --safe-recover. However, that will take much longer than --recover.
 
If MySQL is running with a character set other than the standard latin1, you must specify the character set for sorting with the option --character-sets-dir.
 
Restoring or Creating a New MyISAM Index
 
If you change the character set of the MySQL server (for which a restart is necessary), then you must generate new indexes for all your tables. The command for doing so looks like this:
 
root# myisamchk --recover --quick --set-character-set=xxx
databasepath/*.MYI
 
Compressing MyISAM Tables (myisampack)
 
If you exclusively read (but do not change) large tables, then it is a good idea to compress your files. Not only does this save space, but in general, it speeds up access (since larger portions of the table can reside in the file buffer of the operating system):
 
root# myisampack databasepath/*.MYI
 
Although with myisampack the identifier *.MYI is specified for the index file, the command changes only the data file *.MYD.To uncompress compressed table files, you should execute myisamchk with the option --unpack.