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.
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:
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:
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.
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:
Locking All Databases for Read Access
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.
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: