Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

If you run isamchk while there are corrupted keys, isamchk may assume that the data itself is

corrupted and delete some of it.

Thus, you need to remove the keys temporarily, repair the table, and then reinstate the keys. The

following command

isamchk -rqk=0

will do just this, setting keys-used to zero (removing keys) and doing a quick check and repair. For

example

[root@tigger book]# isamchk -rqk=0 *.ISM

• check delete-chain


• recovering ISAM-table 'customers.ISM'
  Data records: 15

After this is done and you're happy with the repair job, run

isamchk -rq

to rebuild the keys.

Note After you have run an isamchk repair, it may appear that your application

appears not to be using the repaired tables. This is most likely to occur if you are

unable to shut down the MySQL server before doing reparations. Just run

mysqladmin reload if this happens.

Summary

Today, you have learned several aspects of MySQL administration:
ƒ How to back-up your database, using mysqldump to create backups in a range of
formats.
ƒ How to restore your database, using mysqlimport to read data-only text files and the
UNIX cat command to pipe both SQL and data directly into MySQL.
ƒ How to make use of MySQL's various types of logs: update logs for changes to the
database and full activity logs for watching MySQL at a greater level of detail. Update
logs are useful for making incremental backups, while full logs may help in diagnosing
application and system problems.
ƒ How to use the system functions for accessing administrative information about your
MySQL database
ƒ How to use the mysqladmin utility, with its range of options for creating and dropping
databases, getting server status and thread information, and for flushing logs and other
administrative functions.
ƒ What the underlying file structure looks like, how to get rid of wasted space, and how
to check and recover a corrupted table.

With this knowledge in hand, you should be on the way to a properly backed-up database. You now

have the ability to reach system information and to fix most problems, should they occur.

Q&A

Q:

I just want to do a simple backup of my entire database. How should I

do it?

A:

Use the following command:

mysqldump --opt database_name > /path/to/mydump.sql

This will create a relatively compact and fast backup of the whole

database with SQL statements for table CREATEs and data INSERT. It

will save it as a single file called mydump.sql that you can store in a

secure location.

Q: How do I do a full restore from this file?

A:

At a UNIX or Linux command prompt, type the following:

cat /home/mydata/mydump.sql | /path/to/mysql -u