[ Pobierz całość w formacie PDF ]
columns the table contains, and their type. The tblName.MYD file contains all the
table data. The tblName.MYI file contains any indexes for the table (for example, it
might contain the lookup table that helps the table's primary key column speed up
queries based on it).
To check a table for errors, just run myisamchk (in the MySQL bin directory) and
provide the location of these files and the name of the table, or the name of the
table index file:
% myisamchk /usr/local/mysql/var/dbName/tblName
% myisamchk /usr/local/mysql/var/dbName/tblName.MYI
http://www.webmasterbase.com/printTemplate.php?aid=228 26-12-00
Building a Database-Driven Web Site Using PHP and MySQL - WebmasterBase.com Side 79 af 99
Either of the above will perform a check of the specified table. To check all tables
in the database, use a wildcard:
% myisamchk /usr/local/mysql/var/dbName/*.MYI
And to check all databases in all tables, use two:
% myisamchk /usr/local/mysql/var/*/*.MYI
Without any options, myisamchk performs a normal check of the table files. If you
suspect problems with a table and a normal check fails to turn up anything, you
can perform a much more thorough (but also much slower!) check using the --
extend-check option:
% myisamchk --extend-check /path/to/tblName
Checking for errors is non-destructive, which means that you don't have to worry
about making an existing problem worse by performing a check on your data files.
Repair operations, on the other hand, while usually safe, make changes to your
data files that cannot be undone. For this reason, it is strongly recommended that
you make a copy of any damaged table files before attempting to repair them. As
usual, make sure your MySQL server is shut down before making copies of the
data files.
There are three types of repair that you can try to fix a problem with a damaged
table. These should be tried in order, with fresh copies of the data files each time
(i.e. don't try the second recovery method on a set of files resulting from a failed
attempt of the first recovery method). If at any point you get an error message
indicating that a temporary file can't be created, delete the file the message refers
to and try again -- the offending file is a remnant of a previous repair attempt.
The three repair methods can be executed as follows:
% myisamchk --recover --quick /path/to/tblName
% myisamchk --recover /path/to/tblName
% myisamchk --safe-recover /path/to/tblName
The first is the quickest, and fixes the most common problems; the last is the
slowest, and fixes a few problems that the other methods do not.
Checking and Repairing MySQL Data Files (cont'd.)
If the above methods fail to resurrect a damaged table, there are a couple more
tricks you can try before giving up:
If you suspect that the table index file (*.MYI) is damaged beyond repair,
or even missing entirely, it can be regenerated from scratch and used with
your existing data (*.MYD) and table form (*.frm) files. Begin by making a
copy of your table data (tblName.MYD) file. Restart your MySQL server and
connect to it, then delete the contents of the table using the following
command:
mysql> DELETE FROM tblName;
In addition to deleting the contents of the table, this creates a brand new
index file for the table. Log out and shut down the server again, then copy
your saved data file (tblName.MYD) over the new (empty) data file. Finally,
perform a standard repair (the second method above) using myisamchk to
http://www.webmasterbase.com/printTemplate.php?aid=228 26-12-00
Building a Database-Driven Web Site Using PHP and MySQL - WebmasterBase.com Side 80 af 99
regenerate the index data based on the contents of the data and table
form files.
If your table form file (tblName.frm) is missing or damaged beyond repair,
but you know the table well enough to reproduce the CREATE TABLE
statement that defines it, you can generate a new .frm file and use it with
your existing data file and index file (if the index file is no good, use the
above method to generate a new one afterwards). Begin by making a copy
of your data and index files, and then delete the originals (removing any
record of the table from the data directory).
Start up the MySQL server and create a new table using the exact same
CREATE TABLE files over top of the new (empty files). The new .frm file
should work with them, but perform a standard table repair (the second
method above) for good measure.
Wrap-Up
Okay, I admit this part of the series hasn't been the usual non-stop, action-
packed code fest that you may have become accustomed to. But in concentrating
on the topics that we did -- backing up and restoring MySQL data, administering
the MySQL access control system, and table checking and repair -- we have
armed ourselves with the tools we'll need to be able to set up a MySQL database
server that will stand the test of time (not to mention the constant traffic that
your site will endure during that time).
In Part 9, the penultimate chapter of this series, we'll get back to the fun stuff and
[ Pobierz całość w formacie PDF ]