Sunday, December 21, 2014

Book Review: MySQL Admin Cookbook

MySQL is reputed to be among the most popular open source databases. It is frequently used as the database component of the LAMP software stack. From the beginning of its development in 1994 to its acquisition in 2008 by Sun Microsystems which itself is now a subsidiary of Oracle Corporation, MySQL has come a long way.

The book explains 99 recipes which can be used by the developer/ administrator using the database. These cover main topics such as backup and recovery, configuring MySQL, MySQL user management and managing schemas.

One of the unique features of MySQL is concept of storage engine. This additional layer allows the user/developer to choose how and where a database table is to be stored. Four of the most common storage engines for MySQL are MyISAM,InnoDB,Memory and NDB. The default storage engine is MyISAM.

Before starting with the recipes for indexing, there is comparison of indexes between InnoDB and MyISAM as the main two storage engines for MySQL. One of the differences mentioned is MyISAM structures all indexes identically, whereas InnoDB makes a distinction between the primary key and additional secondary ones. There is always a trade-off between between increased space requirements and maintenance costs on index updates. There are suggestions for making the optimal choice out of these. 

InnoDB tables do not support fulltext indexing whereas MyISAM does. One of the workarounds for InnoDB tables to use MyISAM is via replication as explained in one of the recipes. One of the usual tips about full-text indexes repeated here is dropping it during bulk load to avoid expensive update operations.

Among the recipes in this book are steps for replication in mysql via scripts and configuration settings, manually configuring loadbalancing functionality on MyQL, using the blackhole storage engine for limiting network and slave I/O load in heavy write scenarios, exporting and importing data from different file formats, tips on choosing a storage engine. 

To summarize, this is a useful book with useful tips and some good recipes for the developer/administrator using MySQL in practical situations.

No comments: