Home arrow MySQL arrow Page 2 - Disaster Prevention and Recovery with the MySQL Database

4.6.2.2 General Options for myisamchk - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss the issues of disaster prevention and recovery. The fifth of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:
  1. Disaster Prevention and Recovery with the MySQL Database
  2. 4.6.2.2 General Options for myisamchk
  3. 4.6.2.4 Repair Options for myisamchk
  4. 4.6.2.7 Using myisamchk for Crash Recovery
  5. 4.6.2.9 How to Repair Tables
  6. 4.6.3 Setting Up a Table Maintenance Schedule
  7. 4.6.4 Getting Information About a Table
By: Sams Publishing
Rating: starstarstarstarstar / 7
June 22, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The options described in this section can be used for any type of table maintenance operation performed by myisamchk. The sections following this one describe options that pertain only to specific operations, such as table checking or repairing.

  • --help, -?

    Display a help message and exit.

  • --debug=debug_options, -# debug_options

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

  • --silent, -s

    Silent mode. Write output only when errors occur. You can use -s twice (-ss) to make myisamchk very silent.

  • --verbose, -v

    Verbose mode. Print more information. This can be used with -d and -e. Use -v multiple times (-vv, -vvv) for even more output.

  • --version, -V

    Display version information and exit.

  • --wait, -w

    Instead of terminating with an error if the table is locked, wait until the table is unlocked before continuing. Note that if you are running mysqld with the --skip-external-locking option, the table can be locked only by another myisamchk command.

You can also set the following variables by using --var_name=value options:

Variable

Default Value

decode_bits

9

ft_max_word_len

version-dependent

ft_min_word_len

4

key_buffer_size

523264

myisam_block_size

1024

read_buffer_size

262136

sort_buffer_size

2097144

sort_key_blocks

16

write_buffer_size

262136


It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. However, this syntax is deprecated as of MySQL 4.0.

The possible myisamchk variables and their default values can be examined with myisamchk --help:

sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover.

key_buffer_size is used when you are checking the table with --extend-check or when the keys are repaired by inserting keys row by row into the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

  • You use --safe-recover.

  • The temporary files needed to sort the keys would be more than twice as big as when creating the key file directly. This is often the case when you have large key values for CHAR, VARCHAR, or TEXT columns, because the sort operation needs to store the complete key values as it proceeds. If you have lots of temporary space and you can force myisamchk to repair by sorting, you can use the --sort-recover option.

Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the key_buffer_size and sort_buffer_size variables to about 25% of your available memory. You can set both variables to large values, because only one of them is used at a time.

myisam_block_size is the size used for index blocks. It is available as of MySQL 4.0.0.

The ft_min_word_len and ft_max_word_len variables are available as of MySQL 4.0.0. ft_stopword_file is available as of MySQL 4.0.19.

ft_min_word_len and ft_max_word_len indicate the minimum and maximum word length for FULLTEXT indexes. ft_stopword_file names the stopword file. These need to be set under the following circumstances.

If you use myisamchk to perform an operation that modifies table indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt using the default full-text parameter values for minimum and maximum word length and the stopword file unless you specify otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by the server. They are not stored in MyISAM index files. To avoid the problem if you have modified the minimum or maximum word length or the stopword file in the server, specify the same ft_min_word_len, ft_max_word_len, and ft_stopword_file values to myisamchk that you use for mysqld. For example, if you have set the minimum word length to 3, you can repair a table with myisamchk like this:

shell> myisamchk --recover
--ft_min_word_len=3
tbl_name.MYI

To ensure that myisamchk and the server use the same values for full-text parameters, you can place each one in both the [mysqld] and [myisamchk] sections of an option file:

[mysqld]
ft_min_word_len=3
[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, or ALTER TABLE. These statements are performed by the server, which knows the proper full-text parameter values to use.

4.6.2.3 Check Options for myisamchk

myisamchk supports the following options for table checking operations:

  • --check, -c

    Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly.

  • --check-only-changed, -C

    Check only tables that have changed since the last check.

  • --extend-check, -e

    Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table.

    If you are using --extend-check and have plenty of memory, setting the key_buffer_size variable to a large value will help the repair operation run faster.

  • --fast, -F

    Check only tables that haven't been closed properly.

  • --force, -f

    Do a repair operation automatically if myisamchk finds any errors in the table. The repair type is the same as that specified with the --repair or -r option.

  • --information, -i

    Print informational statistics about the table that is checked.

  • --medium-check, -m

    Do a check that is faster than an --extend-check operation. This finds only 99.99% of all errors, which should be good enough in most cases.

  • --read-only, -T

    Don't mark the table as checked. This is useful if you use myisamchk to check a table that is in use by some other application that doesn't use locking, such as mysqld when run with the --skip-external-locking option.

  • --update-state, -U

    Store information in the .MYI file to indicate when the table was checked and whether the table crashed. This should be used to get full benefit of the --check-only-changed option, but you shouldn't use this option if the mysqld server is using the table and you are running it with the --skip-external-locking option.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: