Coordinating with the Server | High Performance MySQL: Optimization, Backups, Replication, and More

Some types of administrative operations are performed by connecting to the server and telling it what to do. Suppose you want to perform some consistency checks or table repairs on a MyISAM table. One way to do this 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. By having the server perform the requested operations, you can let it handle any issues involved in coordinating access to the table. This means that they become non-issues as far as you're concerned and you need not think about them.

Another way to check or repair the table is to invoke the myisamchk utility, which also accesses the table files. In this case, however, the table operations are done not by the server, but by a program external to it. This 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 read 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 same issue comes up in other contexts as well. For example, 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, in which case, you have to keep the server from accessing the table at all.

One way to keep the server from interfering with you is to bring it down; clearly, if it's not running, it can't access the tables you're working with. But administrators are understandably reluctant to take the server completely offline after all, that makes all your other databases and tables unavailable as well. The procedures described in this section will help you avoid interaction between a running server and operations that you're performing external 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 other for example, to keep one client's SELECT query from being interrupted by another client's UPDATE query.
The server also can use external locking to keep 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 file system 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 should use internal locking.)

The information in this section applies when working with table types for which each table is represented by its own files, such as MyISAM, BDB, and ISAM tables. It does not apply to the InnoDB handler, which represents all InnoDB tables together within the files that make up the InnoDB 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 only read access to the table's files or read/write access. 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 a specific table. If your version of MySQL is older than 3.23.23, you'll need to use FLUSH TABLES, which does not take a table name and flushes the entire table cache.

You must perform all the LOCK, FLUSH, and UNLOCK statements from within a single mysql session. If you lock a table and then quit mysql, the lock will be released. At that point, the server will consider 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

This protocol is appropriate for operations in which you only need 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; 
```
The lock 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 out any unwritten changes that may still be cached.
With mysql sitting idle, switch to window B so you can work with the table files. For example, you can check a MyISAM table as follows:
```
 % myisamchk tbl_name  
```
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

This protocol is appropriate for operations such as table repair in which you actually 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 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. Second, you should issue another 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 you can work directly with the table files. For example, you can repair a MyISAM table as follows:
```
 % myisamchk --recover tbl_name  
```
This example is for illustration only. The particular commands you issue will depend on what you're doing. (Note that it may 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 clients that need to make updates, so you should hold the server lock no longer than necessary.

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. For example, if external locking is supported by your system, myisamchk and isamchk know 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. External locking should not be relied on for operations, such as table repair, that require read/write access. External locking is based on file locking, but repair operations performed by myisamchk and isamchk copy table files to new files as they work and then use them 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 on all systems as of MySQL 4. 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 or not the server is able to use external locking, check the appropriate server variable. The variable is named skip_external_locking as of MySQL 4 and skip_locking before that, but you can check for whichever of these your server uses by issuing the following query:

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

Depending on the value of the skip_external_locking (or skip_locking) variable, proceed as follows:

If skip_external_locking is ON, external locking is suppressed, and the server won't know when myisamchk or isamchk are 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.
If skip_external_locking is OFF, external locking is enabled and you can use myisamchk or isamchk for read-only operations, such as checking tables. The server and the utility will cooperate for table access. However, before running either utility, you should flush the table cache with 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 may need to modify them, so you can't use external locking. Use the internal locking protocol for read/write access instead.