30.3. Client and Utility Programs for Table Maintenance


The table-maintenance SQL statements discussed in the preceding sections can be issued from within the mysql client program or from other applications that send statements to the server. By using these statements, you can write your own administrative applications that perform table check and repair operations.

Some MySQL client programs provide a front end for issuing table-maintenance statements:

  • MySQL Administrator offers a point-and-click interface for table check, repair, and optimize operations. When you select one of these operations, MySQL Administrator sends the corresponding SQL statement to the server.

  • mysqlcheck can check, repair, analyze, and optimize tables. It determines which options were given on the command line, and then sends appropriate SQL statements to the MySQL server to perform the requested operation.

The myisamchk utility for MyISAM tables also performs table maintenance. However, it takes a different approach from MySQL Administrator and mysqlcheck. Rather than sending SQL statements to the server, myisamchk directly reads and modified the table files. For this reason, it's necessary when using myisamchk to ensure that the server does not access the tables at the same time.

30.3.1. The mysqlcheck Client Program

mysqlcheck checks, repairs, analyzes, and optimizes tables. It can perform all these operations on MyISAM tables, and can perform some of them on InnoDB tables. It provides a command-line interface to the various SQL statements that instruct the server to perform table maintenance, such as CHECK TABLE and REPAIR TABLE.

mysqlcheck has properties that in some contexts make it more convenient than issuing SQL statements directly. For example, if you name a database, it determines what tables the database contains and issues statements to process them all. You need not name each table explicitly. Also, because mysqlcheck is a command-line program, it can easily be used in jobs that perform periodic scheduled maintenance.

mysqlcheck has three general modes of operation, depending on the arguments with which you invoke it:

  • By default, mysqlcheck interprets its first non-option argument as a database name and checks all the tables in that database. If any other arguments follow the database name, mysqlcheck TReats them as table names and checks just those tables. For example, the first of the following commands checks all the tables in the world database; the second checks just the City and Country tables in that database:

     shell> mysqlcheck world shell> mysqlcheck world City Country 

  • With the --databases (or -B) option, mysqlcheck interprets its non-option arguments as database names and checks all the tables in each of the named databases. The following command checks the tables in both the world and test databases:

     shell> mysqlcheck --databases world test 

  • With the --all-databases (or -A) option, mysqlcheck checks all tables in all databases:

     shell> mysqlcheck --all-databases 

mysqlcheck also supports options that indicate which operation to perform on the specified tables. --check, --repair, --analyze, and --optimize perform table checking, repair, analysis, and optimization. The default is to check tables if none of these options is given.

For some operations, mysqlcheck supports options that modify the basic action to be performed. A recommended table-checking strategy is to run mysqlcheck with no options. If any errors occur, run mysqlcheck again, first with the --repair and --quick options to attempt a quick repair. If that fails, run mysqlcheck with --repair for a normal repair, and then if necessary with --repair and --force.

30.3.2. The myisamchk Utility

The myisamchk utility performs table maintenance on MyISAM tables. Conceptually, myisamchk is similar in purpose to mysqlcheck, but the two programs do differ in certain ways:

  • Both programs can check, repair, and analyze MyISAM tables. mysqlcheck also can optimize MyISAM tables, as well as check InnoDB tables. There are certain operations that myisamchk can perform that mysqlcheck cannot, such as disabling or enabling indexes, although these operations aren't discussed in this study guide.

  • The two programs differ significantly in their mode of operation. mysqlcheck is a client program that communicates with the MySQL server over a network connection. This means that mysqlcheck requires the server to be running, but it also means that mysqlcheck can connect to remote servers. In contrast, myisamchk isn't a client program. It's a utility that operates directly on the files that represent MyISAM tables. This means that you must run myisamchk on the server host where those files are located. In addition, you need filesystem read privileges on those files for table check operations, and write privileges for table repair operations.

  • The two programs also differ in their relationship with the server while they're running. With mysqlcheck, there's no problem of interaction with the server because mysqlcheck asks the server itself to do the work of checking and repairing the tables. With myisamchk, you need to make sure that the server doesn't have the tables open and isn't using them at the same time. It's possible to get incorrect results or even to cause table damage if table files are used by myisamchk and the server simultaneously. The most certain way to avoid conflict while running myisamchk is to stop the server first. It's also possible to leave the server running and lock the tables while checking or repairing them with myisamchk, but the procedure is not described here. You can find the details in the MySQL Reference Manual.

Because you must avoid using tables at the same time the server might be accessing them, the procedure for using myisamchk differs from that for using mysqlcheck. Perform table maintenance with myisamchk as follows:

1.

Ensure that the server will not access the tables while you're working with them. One way to guarantee this is to stop the server.

2.

From a command prompt, change location into the database directory where the tables are located. This will be the subdirectory of the server's data directory that has the same name as the database containing the tables you would like to check. (The reason for changing location is to make it easier to refer to the table files. You can skip this step if you like, but you'll have to specify to myisamchk the directory where the tables are located.)

3.

Invoke myisamchk with options indicating the operation you want performed, followed by arguments that name the tables on which myisamchk should operate. Each of these arguments can be either a table name or the name of the table's index file. An index filename is the same as the table name, plus an .MYI suffix. Thus, you can refer to a table either as table_name or as table_name.MYI.

4.

Restart the server.

The default myisamchk operation is to check tables. If that's what you want to do, no options are necessary and you need only name the table or tables to be checked. For example, to check a table named City, use either of these commands:

 shell> myisamchk City shell> myisamchk City.MYI 

To repair a table, use the --recover option:

 shell> myisamchk --recover City 

If a repair operation performed with --recover encounters problems that it cannot fix, try using the --safe-recover option. --safe-recover can fix some problems that --recover cannot. (--safe-recover is much slower than --recover, which is why you try --recover first.)

30.3.3. Options for mysqlcheck and myisamchk

mysqlcheck and myisamchk both take many options to control the type of table maintenance operation performed. The following list summarizes some of the more commonly used options. For the most part, the list contains options that are understood by both programs.

Where that isn't the case, it's noted in the relevant option description.

  • --analyze or -a

    Analyze the distribution of key values in the table. This can improve performance of queries by speeding up index-based lookups.

  • --auto-repair (mysqlcheck only)

    Repair tables automatically if a check operation discovers problems.

  • --check or -c

    Check tables for problems. This is the default action if no other operation is specified.

  • --check-only-changed or -C

    Skip table checking except for tables that have been changed since they were last checked or tables that haven't been properly closed. The latter condition might occur if the server crashes while a table is open.

  • --fast or -F

    Skip table checking except for tables that haven't been properly closed.

  • --extended (for mysqlcheck), --extend-check (for myisamchk), or -e (for both programs)

    Run an extended table check. For mysqlcheck, when this option is given in conjunction with a repair option, a more thorough repair is performed than when the repair option is given alone. That is, the repair operation performed by mysqlcheck --repair --extended is more thorough than the operation performed by mysqlcheck --repair.

  • --medium-check or -m

    Run a medium table check.

  • --quick or -q

    For mysqlcheck, --quick without a repair option causes only the index file to be checked, leaving the data file alone. For both programs, --quick in conjunction with a repair option causes the program to repair only the index file, leaving the data file alone.

  • --repair (for mysqlcheck), --recover (for myisamchk), or -r (for both programs)

    Run a table repair operation.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net