Obtaining the sampdb Sample Database Distribution | Mysql: the Definitive Guide to Using, Programming, and Administering Mysql 4.1 and 5.0

Performing Database Maintenance with the Server Running

Some administrative operations are performed by connecting to the server and telling it what to do. If you want to perform consistency checks or table repairs on a MyISAM table, one way to do so is to issue a CHECK TABLE or REPAIR TABLE statement and let the server do the work. In this case, the server will access the .frm, .MYD, and .MYI files that represent the table. In general, this is the best approach to take if possible: When the server performs the requested operations, it handles any issues involved in coordinating access to the table so that you need not think about them.

Another way to check or repair a table is to invoke the myisamchk utility, which accesses the table files directly without going through the server. In this case, the table operations are done by a program external to the server, which raises the issue of table access coordination: While myisamchk is working with the table, it's necessary to prevent the server from trying to change it at the same time. If you don't do that, it's possible that the competing efforts to access the table will damage it and make it unusable. It's obviously a bad thing for the server and myisamchk both to be writing to the table at the same time, but even having one of them reading while the other program is writing isn't good, either. The program doing the reading can become confused if the table is being changed by the other program at the same time.

The need to prevent the server from accessing tables comes up in other contexts as well:

Compressing a MyISAM table with myisampack.
Relocating the data file or index file of a MyISAM table.
Relocating a database.
Some backup techniques involve making copies of the table files. It's necessary to keep the server from changing the tables during the backup procedure to ensure consistent backup files.
Some recovery methods are based on replacing damaged tables with good backup copies. You must not allow the server to access the table at all while you're replacing it.

One way to prevent the server from interfering with you is to stop it. Clearly, if the server is not running, it can't access the tables you're working with. But administrators are understandably reluctant to take the server completely offlineafter all, that makes other databases and tables unavailable as well. The procedures described in this section enable you to avoid stopping the server while at the same time preventing problems of interaction between a running server and operations that you're performing externally to the server.

To coordinate with the server, use a locking protocol. The server has two kinds of locking:

It uses internal locking to keep requests from different clients from getting mixed up with each otherfor example, to keep one client's SELECT query from being interrupted by another client's UPDATE statement. By using internal locking, you can prevent clients from accessing a table while you are working with the table externally to the server.
The server also can use external locking to prevent other programs from modifying table files while it's using them. This is based on the locking capabilities available for your operating system at the filesystem level. Normally, the reason the server uses external locking is for cooperation with programs like myisamchk during table checking operations. However, external locking doesn't work reliably on some systems, in which case you can't depend on it and should use an internal locking protocol instead. Also, external locking is useful only for operations that require read-only access to table files. You should not use it if you require read/write access. For example, if you want to repair a table and not just check it, you must use internal locking rather than external locking.

The locking techniques described here apply when working with table files for storage engines such as MyISAM and BDB that represent each table with its own unique files. They do not apply to the InnoDB storage engine, which by default represents all InnoDB tables together within the files that make up the InnoDB shared tablespace. (Even when configured to use individual per-table tablespaces, InnoDB still records some information about each table in its data dictionary, which is stored in the shared tablespace.)

Preventing Interactions Using Internal Locking

The locking protocols described here use the server's internal locking mechanism to prevent it from accessing a table while you work on it. The general idea is that you connect to the server with mysql and issue a LOCK TABLE statement for the table you want to use. Then, with mysql idle (that is, sitting there not doing anything with the table except keeping it locked), you do whatever you need to do with the table files. When you're done, switch back to your mysql session and release the lock to tell the server it's okay to use the table again.

The locking protocol to use depends on whether you need read-only access or read/write access to the table's files:

For operations that just check or copy the files, read-only access is sufficient.
For operations that modify the files, such as table repair or replacing damaged files with good ones, you'll need read/write access.

The locking protocols use the LOCK TABLE and UNLOCK TABLE statements to acquire and release locks. They also use FLUSH TABLE to tell the server to flush any pending changes to disk and as a means of informing the server that it will need to reopen the table when next it accesses it. The examples use the named-table form of FLUSH TABLE that takes a table name argument and flushes just that specific table.

You must perform all the LOCK, FLUSH, and UNLOCK statements from within a single session with the server. For example, if you connect to the server with the mysql program, lock a table, and then quit mysql, the server will release the lock automatically. At that point, the server considers itself free to use the table again, with the result that it is no longer safe for you to work with the table files.

It's easiest to perform the locking procedures if you keep two windows open, one for running mysql and the other for working with the table files. This allows you to leave mysql running while you do your work. If you're not using a windowing environment, you'll need to suspend and resume mysql using your shell's job control facilities while you work with the table.

Locking a Table for Read-Only Access

The read-only locking protocol is appropriate for operations that need only to read a table's files, such as making copies of the files or checking them for inconsistencies. It's sufficient to acquire a read lock in this case; the server will prevent other clients from modifying the table, but will allow them to read from it. This protocol should not be used when you need to modify a table.

In window A, invoke mysql and issue the following statements to obtain a read lock and flush the table:
```
 % mysql db_name mysql> LOCK TABLE tbl_name READ; mysql> FLUSH TABLE tbl_name; 
```
LOCK TABLE acquires a lock that prevents other clients from writing to the table and modifying it while you're checking it. The FLUSH statement causes the server to close the table files, which flushes any unwritten changes that might still be cached in memory.
With mysql sitting idle, switch to window B so that you can work with the table files. For example, you can check a MyISAM table like this:
```
 % myisamchk tbl_name 
```
This example is for illustration only. The particular commands you issue will depend on what you're doing.
When you're done working with the table, switch back to the mysql session in window A and release the table lock:
```
 mysql> UNLOCK TABLE; 
```

It's possible that your work with the table will indicate that further action is necessary. For example, if you check a table with myisamchk, it may find problems that need correction. The corrective procedure will require read/write access, which you can obtain safely using the protocol described next.

Locking a Table for Read/Write Access

The read/write locking protocol is appropriate for operations such as table repair that need to modify a table's files. To do this, you must acquire a write lock to completely prevent all server access to the table while you're working on it.

The locking procedure for repairing a table is similar to the procedure for checking it, with two differences. First, you must obtain a write lock rather than a read lock. You'll be modifying the table, so you can't let the server access it at all, not even to read it. Second, you should issue a second FLUSH TABLE statement after working with the table. Some operations, such as repairing a table with myisamchk, build a new index file, and the server won't notice that unless you flush the table cache again. To lock a table for read/write access, use this procedure:

Invoke mysql in window A and issue the following statements to obtain a write lock and flush the table:

 % mysql db_name mysql> LOCK TABLE tbl_name WRITE; mysql> FLUSH TABLE tbl_name;

With mysql sitting idle, switch to window B so that you can work directly with the table files. For example, you can repair a MyISAM table like this:

 % myisamchk --recover tbl_name

This example is for illustration only. The particular commands you issue will depend on what you're doing. (It might be prudent to make copies of the table files first, in case something goes wrong.)

When you're done working with the table, switch back to the mysql session in window A, flush the table again and release the table lock:

 mysql> FLUSH TABLE tbl_name; mysql> UNLOCK TABLE;

Locking All Databases for Read Access

A convenient way to prevent clients from making any changes to any table is to place a read lock on all tables in all databases at once. To do this, issue the following statement:

 mysql> FLUSH TABLES WITH READ LOCK;

To release the lock, do this:

 mysql> UNLOCK TABLES;

While the tables are locked this way, other clients can read from them but cannot make changes. This is a good way to make the server quiescent for operations such as making copies of all your database directories. On the other hand, it's unfriendly to all clients that need to make updates, so you should hold the server lock no longer than necessary. It is also insufficient for operations such as making a binary backup of all tables managed by a transactional storage engine because the engine might have outstanding transactions pending and only partly flushed to its log files. Operations like that require that you stop the server to make sure everything is flushed and all files are closed.

Preventing Interactions Using External Locking

In some cases, you can use external locking to coordinate with the server while you're working directly with table files. If your system supports the external locking capability, myisamchk knows how to use it to cooperate with the server. However, this should be used only for activities that require read-only access, such as table checking. You should not rely on external locking for operations that require read/write access, such as table repair. External locking is based on file locking, but repair operations performed by myisamchk copy table files to new files as they work, and then use the new files to replace the originals. The server knows nothing of the new files, which renders useless any attempt at coordinating access by means of file locks.

External locking is disabled by default. You can enable it if you're certain that it works correctly on your system, but in general, it's better to avoid it and use internal locking instead.

To determine whether the server is able to use external locking, check the skip_external_locking system variable by issuing the following statement:

 mysql> SHOW VARIABLES LIKE 'skip_external_locking'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | skip_external_locking | ON    | +-----------------------+-------+

Depending on the value of the skip_external_locking variable, proceed as follows:

If skip_external_locking is OFF, external locking is enabled and you can use myisamchk for read-only operations like checking tables. The server and myisamchk will cooperate for table access. However, before running myisamchk, you should flush the table cache with FLUSH TABLES or mysqladmin flush-tables and you must make sure that no one attempts to update the tables until you're done with them. To repair tables, you might need to modify them, so you can't use external locking. Stop the server or use the internal locking protocol for read/write access instead.
If skip_external_locking is ON, external locking is suppressed, and the server won't know when myisamchk is accessing a table. If you need to leave the server up while working directly with the table files, it will be necessary to use internal locking to tell the server to leave the table alone. Use the protocol for read-only or read/write access as appropriate for what you're doing.