Chapter 22. MySQL

MySQL is a very popular open-source database with a large user community behind it. From a user perspective, interacting with MySQL is similar to interacting with any other RDBMS. From a DBA perspective, MySQL is definitely a horse of a different color.

The big difference between MySQL and other databases is its concept of storage engines, where a storage engine does the job of actually storing the data on disk. MySQL has a pluggable storage engine architecture. That is, it can support any storage engine that interacts with its API. As of this writing, MySQL has more than 10 supported storage engines in, including MyISAM, InnoDB, BDB, Memory, Merge, Archive, Federated, NDB, CSV, BlackHole, and Example. There are also a few storage engines in the works as of this writing, including PBXT, SolidDB, and one code-named Falcon.

MySQL's pluggable storage engine architecture also makes writing a chapter on backup and recovery of MySQL somewhat difficult. Many major storage engines have their own backup and recovery method, creating many options and "if then" statements. I strongly encourage you to read the documentation for the storage engine you are using to ensure that you are very familiar with backup and recovery of that storage engine. Also, when you're just starting out, make sure you join the mailing list and ask for any usual first-timer mistakes that you can avoid.

The statements in this chapter have been tested only with MySQL versions 5.0 and 5.1. They may or may not work on previous or later versions. I also cover only the MyISAM, InnoDB, and NDB storage engines in this chapter. MyISAM is the default storage engine, InnoDB has the most ACID-compliant features as of this writing, and NDB (also ACID-compliant) is the memory-resident storage engine used with MySQL cluster. (ACID compliance is covered in more detail in Chapter 15.)