MySQL 5th Ed.

MySQL Fifth Edition

Developer's Library

Author: Paul DuBois

Published by: Addison Wesley

With a new database project in the planning stage I settled on MySQL for my data base manager. MySQL switched to InnoDB as the default search engine starting with version 5.5. Since InnoDB introduces some primary differences in design and performance over the previously used MyISAM, I thought the latest version of the “MySQL Bible” should be a requirement.

Indeed this change brings a major difference in the way indexes are created and stored. This could cause problems in a large complex database like I'm planning. DuBois does an excellent job of highlighting this difference and how best to accommodate it in Chapter 5 of the book. He continues with extensive treatment on using indexing for maximum benefit and minimum cost. My highlighter got a workout in this section.

The MySQL default data directory is under /usr/local or /var/lib which on my computers are on separate partitions fixed in size. This could become a problem as the database grows. My personal preference is to keep database files in a /home/sub-directory. Since I keep /home on a separate drive it is then somewhat protected against accidental deletion during installation of an updated or different version of Linux. In Chapter 11 the author goes into considerable detail on the location and structure of the data directory. Though I did have to go to a different source to find a sure-fire method for changing the default location.

My planned database will be accessible by other users so security is a major concern. Chapter 13 starts out with a section entitled “How to Steal Data” which can be rather unsettling. The author then presents detailed step-by-step instructions on setting tight permissions on all related directories and files. Many of which I would never have thought of securing. The author also covers MySQL user and administrator privileges in great detail. As with any system, it is important to note that the security is only as good as your passwords. Of course if your root password is compromised nothing is secure.

Lastly, after designing and setting up my database, much of the inputting and accessing of data will be through other software and scripts. Chapters 7, 8 and 9 cover writing programs in C, Perl and PHP respectively in considerable detail. I'm sure these pages will be thumb-worn and full of margin notes in due time. Sadly, however, the author completely ignores the widely popular and my personal favorite Python language. Though there are numerous resources on line, hopefully the author will consider include a chapter devoted to Python in later editions.

Finally Chapter 14 addresses my personal weakness by outlining the procedure for regular secure backups of your data base files. You can use either mysqldump command or with a binary backup. One piece of sage advice here is to store your backups on a separate drive or even a different computer to to avoid a catastrophic loss. The chapter is a good reference for backing up all your important files.

In the net I found the book invaluable in understanding the inner working of MySQL for designing and building my database. This I'll keep it close at hand as I flesh out the structural details and set to work at my keyboard. Later on it will serve as a primary reference on SQL syntax and query structure.