| |
| |
| Foreword | |
| |
| |
| About the Authors | |
| |
| |
| About the Technical Reviewer | |
| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| Design and Development | |
| |
| |
| |
| Analyzing Business Requirements | |
| |
| |
| The Project | |
| |
| |
| From Concept to Model | |
| |
| |
| Database Selection | |
| |
| |
| Your Environment | |
| |
| |
| Summary | |
| |
| |
| |
| Index Concepts | |
| |
| |
| Data Storage | |
| |
| |
| How Indexes Affect Data Access | |
| |
| |
| Clustered vs. Non-Clustered Data and Index Organization | |
| |
| |
| Index Layouts | |
| |
| |
| Compression | |
| |
| |
| General Index Strategies | |
| |
| |
| Summary | |
| |
| |
| |
| Transaction Processing | |
| |
| |
| Transaction Processing Basics | |
| |
| |
| Ensuring Atomicity, Consistency, and Durability | |
| |
| |
| Implementing Isolation and Concurrency | |
| |
| |
| Identifying Your Transaction Control Requirements | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL System Architecture | |
| |
| |
| The MySQL Source Code and Documentation | |
| |
| |
| MySQL Architecture Overview | |
| |
| |
| Process, Thread, and Resource Management | |
| |
| |
| Storage Engine Abstraction | |
| |
| |
| Caching and Memory Management Subsystem | |
| |
| |
| Network Management and Communication | |
| |
| |
| Access and Grant Management | |
| |
| |
| Log Management | |
| |
| |
| Query Parsing, Optimization, and Execution | |
| |
| |
| The Query Cache | |
| |
| |
| A Typical Query Execution | |
| |
| |
| Summary | |
| |
| |
| |
| Storage Engines and Data Types | |
| |
| |
| Storage Engine Considerations | |
| |
| |
| The MyISAM Storage Engine | |
| |
| |
| The InnoDB Storage Engine | |
| |
| |
| Other Storage Engines | |
| |
| |
| Guidelines for Choosing a Storage Engine | |
| |
| |
| Data Type Choices | |
| |
| |
| Summary | |
| |
| |
| |
| Benchmarking and Profiling | |
| |
| |
| What Can Benchmarking Do for You? | |
| |
| |
| General Benchmarking Guidelines | |
| |
| |
| Benchmarking Tools | |
| |
| |
| What Can Profiling Do for You? | |
| |
| |
| General Profiling Guidelines | |
| |
| |
| Profiling Tools | |
| |
| |
| Summary | |
| |
| |
| |
| Essential SQL | |
| |
| |
| SQL Style | |
| |
| |
| MySQL Joins | |
| |
| |
| EXPLAIN and Access Types | |
| |
| |
| Join Hints | |
| |
| |
| Subqueries and Derived Tables | |
| |
| |
| Summary | |
| |
| |
| |
| SQL Scenarios | |
| |
| |
| Handling OR Conditions Prior to MySQL 5.0 | |
| |
| |
| Dealing with Duplicate Entries and Orphaned Records | |
| |
| |
| Dealing with Hierarchical Data | |
| |
| |
| Retrieving Random Records | |
| |
| |
| Calculating Distances with Geographic Coordinate Data | |
| |
| |
| Generating Running Sums and Averages | |
| |
| |
| Summary | |
| |
| |
| |
| Stored Procedures | |
| |
| |
| Stored Procedure Considerations | |
| |
| |
| Stored Procedures in MySQL | |
| |
| |
| Building Stored Procedures | |
| |
| |
| Using Stored Procedures | |
| |
| |
| Managing Stored Procedures | |
| |
| |
| Stored Procedure Permissions | |
| |
| |
| Summary | |
| |
| |
| |
| Functions | |
| |
| |
| Database Function Uses | |
| |
| |
| Functions Compared with Other Database Tools | |
| |
| |
| Functions in MySQL | |
| |
| |
| Creating Functions | |
| |
| |
| Using Functions | |
| |
| |
| Managing Functions | |
| |
| |
| Function Permissions | |
| |
| |
| Performance of Functions | |
| |
| |
| Summary | |
| |
| |
| |
| Cursors | |
| |
| |
| Database Cursors | |
| |
| |
| Cursors in MySQL | |
| |
| |
| Creating Cursors | |
| |
| |
| Using Cursors | |
| |
| |
| Summary | |
| |
| |
| |
| Views | |
| |
| |
| Database View Uses | |
| |
| |
| Views in MySQL | |
| |
| |
| Creating Views | |
| |
| |
| Creating Updatable Views | |
| |
| |
| Defining Views of Views | |
| |
| |
| Managing Views | |
| |
| |
| View Permissions | |
| |
| |
| Performance of Views | |
| |
| |
| Summary | |
| |
| |
| |
| Triggers | |
| |
| |
| Database Triggers | |
| |
| |
| The Debate Over Using Triggers | |
| |
| |
| Triggers in MySQL | |
| |
| |
| Creating MySQL Triggers | |
| |
| |
| Using Triggers | |
| |
| |
| Managing Triggers | |
| |
| |
| Trigger Permissions | |
| |
| |
| Trigger Performance | |
| |
| |
| Summary | |
| |
| |
| |
| Administration | |
| |
| |
| |
| MySQL Installation and Configuration | |
| |
| |
| Using an Existing Installation | |
| |
| |
| Installing Prebuilt Binaries | |
| |
| |
| Building from Source or the Development Source Tree | |
| |
| |
| Starting and Stopping MySQL | |
| |
| |
| Performing Post-Installation Setup | |
| |
| |
| Configuring MySQL | |
| |
| |
| Upgrading MySQL | |
| |
| |
| Uninstalling MySQL | |
| |
| |
| Running Multiple Database Servers on a Single Machine | |
| |
| |
| Summary | |
| |
| |
| |
| User Administration | |
| |
| |
| MySQL Privileges | |
| |
| |
| How MySQL Controls Access and Verifies Privileges | |
| |
| |
| Managing User Accounts from the Command Line | |
| |
| |
| Using the MySQL Administrator GUI Tool | |
| |
| |
| Thinking in Terms of User Roles | |
| |
| |
| Practical Guidelines for User Administration | |
| |
| |
| Summary | |
| |
| |
| |
| Security | |
| |
| |
| Understanding Security Requirements | |
| |
| |
| Building a Security Plan | |
| |
| |
| Getting Started: A MySQL Security Quick List | |
| |
| |
| Setting Up Database Security: An Example | |
| |
| |
| Securing Your Whole System: Seven Major Areas | |
| |
| |
| Summary | |
| |
| |
| |
| Backup and Restoration | |
| |
| |
| Why Do We Create Backups? | |
| |
| |
| Creating a Backup and Restore Plan | |
| |
| |
| Backing Up a Database: A Quick Example | |
| |
| |
| Using MySQL Tools to Make and Restore Backups | |
| |
| |
| Using Binary Logs for Up-to-Date Tables | |
| |
| |
| Summary | |
| |
| |
| |
| Replication | |
| |
| |
| What Is Replication? | |
| |
| |
| Why Replicate Data? | |
| |
| |
| What Isn't Solved with Replication | |
| |
| |
| Planning for Replication | |
| |
| |
| How MySQL Implements Replication | |
| |
| |
| Initial Replication Setup | |
| |
| |
| Configuration Options | |
| |
| |
| Monitoring and Managing | |
| |
| |
| Replication Performance | |
| |
| |
| Replication Examples | |
| |
| |
| Summary | |
| |
| |
| |
| Cluster | |
| |
| |
| What Is Clustering? | |
| |
| |
| MySQL's Cluster Implementation | |
| |
| |
| Installing MySQL Cluster | |
| |
| |
| Initial Configuration and Startup | |
| |
| |
| Cluster Processes | |
| |
| |
| Configuration File Options | |
| |
| |
| Managing the Cluster | |
| |
| |
| Log Files | |
| |
| |
| Security | |
| |
| |
| Summary | |
| |
| |
| |
| Troubleshooting | |
| |
| |
| Troubleshooting Toolkit | |
| |
| |
| Commonly Encountered Issues | |
| |
| |
| How to Report MySQL Bugs | |
| |
| |
| Support Options | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Data Dictionary | |
| |
| |
| Benefits of a Standardized Interface | |
| |
| |
| The INFORMATION_SCHEMA Views | |
| |
| |
| Usage Examples | |
| |
| |
| Summary | |
| |
| |
| Index | |