cover

MySQL® Administrator’s Bible

Sheeri Cabral

Keith Murphy

Title Page

To Mitch Cherniack, who taught me databases and inspired my love of them. In memory of Zvi Kramer and Yehoshua Kritzer—may I be able to echo their strength and spirit.—Sheeri Cabral

For Rachel, the love of my life.—Keith Murphy

About the Authors

Sheeri K. Cabral is a Database Team Lead at the Pythian Group, a remote database administration solution. She has a master's degree in computer science specializing in databases from Brandeis University and a career background in systems administration. Unstoppable as a volunteer and activist since she was 14, Sheeri founded and organizes the Boston MySQL User Group. She also produces OurSQL: The MySQL Database Podcast for the Community, By the Community and many workshop videos. These resources are freely available at www.technocation.org, the website of Technocation, Inc. Sheeri is the Treasurer of Technocation, Inc, a not-for-profit organization providing resources and educational grants for IT professionals. Sheeri currently lives in the Boston area with her husband Tony Cabral and a myriad assortment of Muppets™. When she's not working or volunteering, she reads, knits, listens to podcasts, and enjoys time with her nieces and nephews.

Keith Murphy is a MySQL database administrator who has been using MySQL server since 1998. A long-time Linux system administrator, he worked with a number of Internet companies solving systems issues. More recently he formed Paragon Consulting Services (http://www.paragon-cs.com) to provide consulting services for companies seeking training for MySQL and help with MySQL solutions ranging from everyday database administration tasks to utilizing “cloud” computing services, performance tuning, and scaling. In addition to information about the company, Keith blogs at http://blog.paragon-cs.com, which he updates as frequently as possible with tricks, tips, and general information about MySQL server.

In addition he is the editor of MySQL Magazine (http://www.mysqlzine.net), the MySQL community's digital magazine about all things MySQL. It is his pleasure to work with some of the finest people in the community in this collaborative effort to increase both the amount and quality of the available information on MySQL server.

Readers are invited to contact Keith by email at bmurphy@pargon-cs.com.

Credits

Executive Editor

Robert Elliott

 

Development Editor

Ed Connor

 

Technical Editors

Baron Schwartz

Morgan Tocker

Nicklas Westerlund

 

Production Editor

Liz Britten

 

Copy Editors

Kim Cofer

Foxxe Editorial Services

 

Editorial Manager

Mary Beth Wakefield

 

Production Manager

Tim Tate

 

Vice President and Executive Group Publisher

Richard Swadley

 

Vice President and Executive Publisher

Barry Pruett

 

Associate Publisher

Jim Minatel

 

Project Coordinator, Cover

Lynsey Stanford

 

Proofreader

Jen Larsen, Word One New York

 

Indexer

Jack Lewis

Acknowledgments

Sheeri Cabral

When I was four years old, I refused to let anyone else tie my shoes, because I wanted to do it myself. There was only one problem—I did not know how to tie my shoes yet. I went around for months with untied shoes because I made it very clear that I was going to do it. Eventually I learned to tie my shoes. I also learned that I could do anything I put my mind to.

I could not have accomplished the herculean task of writing a book had I not had the encouragement of my stubbornness. To Mom, Dad, Lymor, Noam, Josh, and Elinor, thanks for putting up with me all these years, letting me try my way so that my failures and successes were my own, and teaching me to always do a good job so I can be proud of my work. My love of databases was inspired by the man who taught me the subject at Brandeis University, Professor Mitch Cherniack. Professionally, I would not be the person I am today without the guidance and example of Tom Limoncelli, an inspirational role model, great mentor, and good friend; and Tracy Gangwer, who taught me to be a super DBA/sysadmin by confirming and triple-checking everything. A huge thank you goes to Keith for being the lead author on this project, and of course for asking me to become involved. Of course, none of this could have been done without the support of Wiley Publishing, particularly our editors Ed Connor and Bob Elliott.

I used many two-dimensional resources while writing this book, but there's a limit to how much information is in manuals and books. Special thanks are due to Brian Aker for being a constant fountain of readily available knowledge and Martin Garden for software assistance. Jay Pipes provided permission for material used in the Query Analysis chapter, Roland Bouman helped me get a firm grip on the data dictionary, and Mark Atwood answered my questions about how storage engines work. I was encouraged and supported by Carsten Turner, Ronald Bradford, Patrick Galbraith, and many fans of the OurSQL Podcast who wrote to me asking when it was coming back.

Keith Murphy

There are many people involved in any book project. Without the crew of people from Wiley we could never have finished this book. Thank you, Bob Elliott (our Executive Editor) for taking a chance on me. I hope you weren't disappointed. Without our Development Editor, Ed Connor, I suspect we would have ended up in the ditch—never to get out. And thanks to all the other people at Wiley who I didn't have the chance to meet who help make this happen.

Sheeri, I could not have done this alone. I am grateful for all the long hours and the meticulous attention to detail that you brought to the project. If I ever get the notion to write another book you will be the first person I call. Of course you don't have to answer!

And a big thanks for all those in the MySQL community. It is you who help power this crazy thing we call Open Source. Thank you for taking the time help others learn and become better.

Introduction

Welcome to the MySQL Administrator's Bible. Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical application information, as well as reference and background information that provide a context for what you are learning. This book is a comprehensive resource on MySQL database administration. It covers almost every conceivable topic of database administration from the very basics, including server installation and user management, to some fairly complex topics such as security and high availability. By the time you have completed the MySQL Administrator's Bible you will be well-prepared to perform as a database administrator.

This book fully covers MySQL 5.1 plus early features of the 6.0 release (as of the time of writing). All of the examples work in both MySQL 5.1 and MySQL 6.0, except where noted.

Who Should Read This Book

This book is designed for two groups of people. The first group of people is those who have never worked on a database server and want to learn the fundamentals of database management using the world's number one open source database server. The second group of people is those who are currently using another database server and are looking to make a transition to MySQL server. They may be experienced database administrators, but are not experienced with MySQL.

How This Book Is Organized

The MySQL Administrator's Bible is divided into four parts.

Part I makes no assumptions on the knowledge level of the reader. Chapter 1 is a brief introduction to MySQL's history. After this introduction Part I covers:

Part II covers how MySQL compares to standards and MySQL's SQL syntax:

Part III is all about MySQL administration:

Part IV highlights intermediate topics ranging from further administrative tasks to performance tuning:

Conventions and Features

There are many different organizational and typographical features throughout this book designed to help you get the most of the information.

Tips, Notes, and Cautions

Whenever the authors want to bring something important to your attention the information will appear in a Tip, Note, Caution or On the Website.

What's on the Companion Website

On the companion website (www.wiley.com/go/mysqladminbible), you will find the following:

Where To Go From Here

When you finish reading MySQL Administrator's Bible you will have a solid foundation for working as a MySQL database administrator. While it takes experience to produce a quality database administrator, knowledge and understanding of foundational principals is critical. Our goal in this book is to provide those two parts of the equation.

You will definitely want to check out the companion website (www.wiley.com/go/mysqladminbible). MySQL 6.0 is still new technology at the time of publication of this book and it will continue to evolve for some time. The world of MySQL changes quickly and the information in the book is up-to-date at time of publication. However, as more changes occur, we will write about the latest changes on the website.

In addition there are many other resources available as you grow in your knowledge of MySQL server. Many of these resources are listed in Appendix C of the book but we would point out in particular Planet MySQL (http://www.planetmysql.org), a blog aggregation site, and MySQL Magazine (http://www.mysqlzine.net), a free digital magazine with which the co-authors are involved.

Part I
First Steps with MySQL

In this Part

  1. Chapter 1 Introduction to MySQL
  2. Chapter 2 Installing and Upgrading MySQL Server
  3. Chapter 3 Accessing MySQL

Chapter 1
Introduction to MySQL

Everyone who has been involved with IT for more than a few months has at least heard of MySQL. The acquisition of MySQL AB by Sun Microsystems brought a great deal of additional attention to MySQL's database management system (DBMS). Even so, there is often more to MySQL than many people realize. They simply do not realize the full capabilities of MySQL.

Recent versions of MySQL have brought a large feature set that covers just about every imaginable need. This includes partitions, scheduled events, prepared statements, triggers, and views. MySQL has long been used in dynamic websites and applications. Whether you program in Perl, PHP, ASP, .NET or Ruby you can integrate MySQL into your environment.

MySQL Mission—Speed, Reliability, and Ease of Use

The driving force behind MySQL has been to provide a reliable, high-performance server that is easy to set up and use. These qualities are why many Internet companies in the late 1990s chose MySQL to power their websites. These same qualities are why MySQL is making strong inroads into the internal database servers of Fortune 1000 companies that have traditionally used commercial databases. MySQL did not corner the existing database market; instead, MySQL allowed the database market to expand and grow to include people all over the world. MySQL created a niche by creating the opportunity for almost anyone to be able use a database. Had there not been an easy-to-use, fast, reliable database server such as MySQL, there would be a dearth of the user-provided content and collaborative efforts that are what we expect from the Internet. Without MySQL, there would be far fewer articles, podcasts, online ’zines, tutorials, photos, forums, videos, collaborative reference material, and search engines. The World (Wide Web) as we know it would be completely different.

MySQL is not the only free database management system; it also is not the only open source database management system. One of the largest differences is the user friendliness that pervades MySQL. The friendliness, starting with the cost—free unless embedded in another product—shines through the quick installation and setup, and pleases the new database user with SQL language extensions that are nearly intuitive. For example, the SHOW DATABASES command shows a list of databases you have permission to see.

Experienced database administrators can install, configure, and bring a MySQL server online in less than fifteen minutes. If the installation process is packaged it can be done in five minutes. We look at the installation process in more detail in Chapter 2.

The reliability of MySQL played a role in MySQL's rise to become, as stated at www.mysql.com, “the world's most popular open source database.” In addition to this reliability, MySQL operates on a wide range of hardware and operating systems from laptops to multi-core servers. Though benchmark comparisons can be slanted to benefit one server or another, the various benchmarks available show that MySQL competes with and frequently beats competing database servers.

Company Background

MySQL server has been downloaded more than 100 million times. MySQL is open source software. An admittedly simple definition of open source software is software that is freely available (including source code) with free redistribution. Source code is the source of a program—the file(s) containing the original programming language code, which can be read, discussed, and learned from, just as the words of a book can. The roots of MySQL server are found in a database system called Unireg that was developed by Michael “Monty” Widenius for a Swedish company called TcX during the 1980s. The initial release of MySQL server was created in 1995 when Monty added an SQL interface to Unireg. Shortly after, David Axmark recommended MySQL server be released under a dual licensing model, where it would be available for widespread free use but could also be used in situations that require a more restrictive licensing use (such as in embedded devices). David and Monty, together with Allan Larsson, founded MySQL AB in 1995. MySQL AB was the company that, until its acquisition by Sun Microsystems in January 2008, provided support and service for the MySQL database in addition to developing most of the code.

In 2001 MySQL began supporting transactions with the integration of the BDB and InnoDB engines. This allowed for safer handling of concurrent write operations, which began the trend of adding features needed by enterprise environments.

Over the years the feature set of the MySQL server has grown to cover almost any feature needed in an enterprise database server. Some might even argue that it has too many features! Innovation has continued; within months of readily accessible cloud computing environments such as Amazon Web Services (http://aws.amzon.com), hobbyists and companies alike are deploying and using MySQL in creative environments.

Table 1.1 shows an overview of MySQL server's release history, including important pre-release (Alpha and Beta) unstable versions as well as public stable releases that were determined to be generally available (GA). In Table 1.1, only the first release notes the new features, though all features mentioned in the pre-release Beta version were also released in the GA version.

Table 1.1: MySQL Server Timeline

images/c01tnt001.jpg

Community and Enterprise Server Versions

In August of 2007 MySQL AB began offering the MySQL server in two different versions: MySQL Community and MySQL Enterprise. Although the code base of the two servers is similar, there are different levels of support for the servers. With MySQL Enterprise you purchase one of four support packages in addition to receiving the MySQL server binaries and source code. Sun has a table of available options at www.mysql.com/products/enterprise/features.html.

MySQL Community offers freely downloadable binaries with no support guarantee. When a database experiences problems the user is on his or her own to resolve those problems.

MySQL Enterprise has more frequent releases than MySQL Community Server. Owners of the source code must be allowed to redistribute the source code in full—this is required under the provisions of the GPLv2 license that governs MySQL distributions.

The MySQL Community

The MySQL community is a diverse population of both users of and contributors to MySQL, spread across the world. The large user community brings many opinions about what features are needed and discussions about the best methods to implement those features. Though this can sometimes bring about somewhat heated discussions, it also allows for honest and direct feedback that many companies may not hear.

How to Contribute

You can contribute to MySQL server in a number of ways:

  • Blogging: Many people who work on MySQL either full or part time write about their experiences online. These blogs can be valuable for learning more about the ins and outs of working with MySQL. Many of these blogs are aggregated at the Planet MySQL website (www.planetmysql.org).
  • Mailing Lists: The official public MySQL mailing lists are available at http://lists.mysql.com. The mailing lists are a great way to both contribute your knowledge and learn something new. The General Discussion list, in particular, has a wide range of topics. Dive in and take a look.
  • IRC Channels: Various IRC channels are available. These can often provide instant feedback and help with problems. One of the most popular is the #mysql channel on the Freenode IRC network (irc.freenode.net, or visit http://freenode.net for more information).
  • User Groups: Many active user groups exist around the world. It is a great experience to get together with other people who share a similar passion. Each month the user groups will have someone present on a topic relating to MySQL. One month it might be backups and recovery. The next month it might be about how to scale an application effectively. The place to look for a user group near you is www.meetup.com. If there is not a user group near you, consider starting one!
  • Documentation: MySQL has some of the best software documentation available, located at http://dev.mysql.com/doc. Working on the documentation is the easiest way to directly contribute to the MySQL project. As MySQL continues to change it is necessary to keep the documentation up to date, so this is always an ongoing project.
  • Code: The MySQL server code is complex software. However, it is somewhat modular and there are areas where an experienced C or C++ coder can help out. For smaller projects you might want to investigate the MySQL forge (http://forge.mysql.com) where many projects find a home. These projects are in many coding languages including (among others) Perl, Python, PHP, bash, C, and even Ruby.
  • MySQL Magazine: Do you like to write? Then MySQL Magazine (www.mysqlzine.net) might be your venue. Articles cover anything from coding to how-to articles on normal DBA activities. The audience is a large group of the best database administrators and developers in the world.

Reasons to Contribute

Many people never do anything other than download MySQL server, read a tutorial or two, and install the software and use it. They never contribute in any manner. That is fine, but we think that you can have a far more positive experience by becoming part of the community and contributing what you can while learning about this fascinating server software.

Though it is beyond the scope of this book to discuss the open source software philosophy in depth, openly and freely giving and receiving knowledge is the basis of this philosophy. Both code and knowledge are free to be shared. By doing so, everyone benefits from the exchange.

Summary

MySQL server has a long history of innovation. The community that has grown around MySQL provides learning experiences and teaching opportunities for database administrators old and young, veteran and newbie alike.

This chapter covered:

The MySQL Administrator's Bible covers all the topics you need to understand as a beginning MySQL database administrator. If you are a more advanced administrator switching from another database system, this book offers a unique look at where MySQL is different from the ISO SQL:2003 standard. After advancing to an intermediate level of familiarity with MySQL, the material on scaling and high availability, replication, metadata, and server tuning will continue to provide guidance.