| Part of administering a database, besides maintaining backups of the data, is checking the integrity of the database files. To check for and repair corrupted files, one option is to use the myisamchk utility. This only works on MyISAM tables, though, and is really my least favorite of the three options for maintaining tables. The second option is to use SQL commands within the mysql client or other interface. These commands are discussed in the sidebar. The final option is to use our new friend the MySQL Administrator. To check and repair your tables: 1. | Open the MySQL Administrator and connect to the MySQL server.
You should connect as an administrative user.
| 2. | Back up all of the databases you'll be checking.
Since you could be affecting the data files themselves in this sequence of steps, it's always smart to back up everything first. To do so, use one of the methods discussed earlier in the chapter.
| 3. | Click Catalogs (Windows or Mac, Figure 13.18).
Figure 13.18. View, edit, and maintain your databases in the Catalogs pane. 
This area of the application is for managing the database files.
| | | 4. | Click a database in the Schemata listing to select it (Figure 13.19).
Figure 13.19. An overview of the tables in the accounting database. 
You can now see all of the tables, views, and stored procedures in this database. You can also see each table's storage engine, how many rows each has, the total size of each table's data and index, and the last time the table was updated. At the bottom of the window is a synopsis of the entire database.
| 5. | Select a table from the display on the right, and then click Maintenance (Windows) or select Check Table from the Table Actions menu (Mac, Figure 13.20).
Figure 13.20. On Mac OS X, the MySQL Administrator places the maintenance options within a drop-down menu. 
You can also select multiple tables, if you'd like. On Windows, this brings up the Table Maintenance dialog box (Figure 13.21), in which you should choose Check Tables and click Next.
Figure 13.21. On Windows, choose a maintenance option within this box. 
| | | 6. | Select a Check method, and then click Check Tables (Windows) or Continue (Mac, Figure 13.22).
Figure 13.22. Choose what type of check you would like to perform on the already-selected tables. 
Each option is well documented, so you shouldn't need to guess as to which is appropriate. Just read the descriptions and factor in things like:
How much data there is How often you perform checks How active the databases are How busy the server currently is
For example, if you regularly check your tables, you might want to go with Changed here. If the server isn't being used much at the moment and it's been a long time since you've done a check, opt for Extended.
| 7. | If any problems are reported by the check, repeat Steps 3 and 4, choosing Repair Tables from the Table Maintenance dialog box (Windows) or Table Actions menu (Mac).
Again, you'll be given choices as to what kind of repair to make (Figure 13.23).
Figure 13.23. Have MySQL repair any tables that were marked as problematic when analyzed. 
|
 Tips
If you select a table and then click Edit Table (Windows) or select Table Actions > Edit Table (Mac), you'll bring up the Table Editor (Figure 13.24). This is a wonderful tool that's kind of hidden in the MySQL Administrator. With it you can do anything that you might otherwise use an ALTER command for. For example, you can: modify columns; change the storage engine, charset, or collation; add, edit, or drop indexes; and much more. Figure 13.24. You can modify every aspect of a table using the Table Editor.  Similarly, the ability to edit stored procedures within the MySQL Administrator is really convenient, particularly when you consider how cumbersome it is to add and edit them within the mysql client. On Windows you can even run the stored procedure from the editing window.
An SQL Equivalent, Part 3 Once again, you have the option of performing maintenance using SQL within the mysql client rather than the dedicated utility. To quickly analyze a table, use CHECK TABLE tablename
Unlike myisamchk, this query will work on both MyISAM and InnoDB table types. You can change the level of checking performed by changing the SQL to CHECK TABLE tablename EXTENDED
If the CHECK TABLE query indicates a problem, run REPAIR TABLE tablename EXTENDED
The commands ANALYZE TABLE tablename and OPTIMIZE TABLE tablename can also be used to test and improve the performance of your tables. |
|
