[MySQL]: Administrative and Utility Programs
8.1 myisamchk ― MyISAM Table-Maintenance Utility
The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables. You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.
Note: It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss.
Invoke myisamchk like this:
shell> myisamchk [options] tbl_name …
With no options, myisamchk simply checks your table as the default operation. tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than in the database directory,
you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk does not actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.
You can name several tables on the myisamchk command line if you wish. You can also specify a table by naming its index file (the file with the .MYI suffix). This enables you to specify all tables in a directory by using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this:
shell> myisamchk *.MYI
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
shell> myisamchk /path/to/database_dir/*.MYI
You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:
shell> myisamchk /path/to/datadir/*/*.MYI
Note: You must ensure that no other program is using the tables while you are running myisamchk. The most effective means of doing so is to shut down the MySQL server while running myisamchk, or to lock all tables that myisamchk is being used on.
Otherwise, when you run myisamchk, it may display the following error message:
warning: clients are using or haven’t closed the table properly
This means that you are trying to check a table that has been updated by another program (such as the mysqld server) that hasn’t yet closed the file or that has died without closing the file properly, which can sometimes lead to the corruption of one or more MyISAM tables.
Options:
–check, -c
Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly.
–fast, -F
Check only tables that haven’t been closed properly.
–analyze, -a
Analyze the distribution of key values. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use. To obtain information about the key distribution.
–backup, -B
Make a backup of the .MYD file as file_name-time.BAK
–extend-check, -e
Do a repair that tries to recover every possible row from the data file. Normally, this also finds a lot of garbage rows. Do not use this option unless you are desperate.
–recover, -r
Do a repair that can fix almost any problem except unique keys that are not unique. If you want to recover a table, this is the option to try first. You should try –safe-recover only if myisamchk reports that the table cannot be recovered using –recover.
–safe-recover, -o
Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found. This is an order of magnitude slower than –recover, but can handle a couple of very unlikely cases that –recover cannot.
–unpack, -u
Unpack a table that was packed with myisampack.
–set-auto-increment[=value], -A[value]
Force AUTO_INCREMENT numbering for new records to start at the given value (or higher, if there are existing records with AUTO_INCREMENT values this large). If value is not specified, AUTO_INCREMENT numbers for new records begin with the largest value currently in the table, plus one.
8.2. myisampack — Generate Compressed, Read-Only MyISAM Tables
The myisampack utility compresses MyISAM tables. myisampack works by compressing each column in the table separately. Usually, myisampack packs the data file 40% to 70%.
When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.
- If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped
- After packing a table, it becomes read only.
Invoke myisampack like this:
shell> myisampack [options] file_name …
After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes.
Options:
–backup, -b
Make a backup of each table’s data file using the name tbl_name.OLD.
–tmpdir=path, -T path
Use the named directory as the location where myisampack creates temporary files.
Compressed Table Characteristics:
Compressed storage format is a read-only format that is generated with the myisampack tool. Compressed tables can be uncompressed with myisamchk.
- Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CDROMs).
- Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row in the table. Each column is compressed differently.
- Numbers with a value of zero are stored using one bit.
- If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.
- If a column has only a small set of possible values, the data type is converted to ENUM.
mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
mysql_convert_table_format converts the tables in a database to use a particular storage engine (MyISAM by default). mysql_convert_table_format is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section 2.22, “Perl Installation Notes”).
Invoke mysql_convert_table_format like this:
shell> mysql_convert_table_format [options]db_name
Options:
–host=host_name
Connect to the MySQL server on the given host.
–password=password
The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs.
–port=port_num
The TCP/IP port number to use for the connection.
–socket=path
For connections to localhost, the Unix socket file to use.
–type=engine_name
Specify the storage engine that the tables should be converted to use. The default is MyISAM if this option is not given.
–user=user_name
The MySQL user name to use when connecting to the server.
mysql_config — Get Compile Options for Compiling Clients
mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL. About port, socket, include files, libraries, version etc.