2005 by Michael Kruckenberg and Jay Pipes
Contents at a Glance
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
PART 1 ¦ ¦ ¦ Design and Development
¦CHAPTER 1 Analyzing Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
¦CHAPTER 2 Index Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
¦CHAPTER 3 Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
¦CHAPTER 4 MySQL System Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
¦CHAPTER 5 Storage Engines and Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
¦CHAPTER 6 Benchmarking and Profiling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
¦CHAPTER 7 Essential SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
¦CHAPTER 8 SQL Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
¦CHAPTER 9 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
¦CHAPTER 10 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
¦CHAPTER 11 Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
¦CHAPTER 12 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
¦CHAPTER 13 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
PART 2 ¦ ¦ ¦ Administration
¦CHAPTER 14 MySQL Installation and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 469
¦CHAPTER 15 User Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
¦CHAPTER 16 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
¦CHAPTER 17 Backup and Restoration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
¦CHAPTER 18 Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585
¦CHAPTER 19 Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
¦CHAPTER 20 Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
¦CHAPTER 21 MySQL Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
¦INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 iii
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
PART 1 ¦ ¦ ¦ Design and Development
¦CHAPTER 1 Analyzing Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . 3
The Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Common Team Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Importance of Team Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
From Concept to Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Textual Object Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Modeling Approaches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
A Database Blueprint (Baseline Model) . . . . . . . . . . . . . . . . . . . . . . . . 30
Database Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Surveying the Landscape . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Why Choose MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Your Environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
On Hosting Companies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Commercial Web Software Development . . . . . . . . . . . . . . . . . . . . . . 35
On Controlled Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
¦CHAPTER 2 Index Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Data Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
The Hard Disk: Persistent Data Storage. . . . . . . . . . . . . . . . . . . . . . . . 40
Memory: Volatile Data Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Pages: Logical Data Representation . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
How Indexes Affect Data Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Computational Complexity and the Big “O” Notation. . . . . . . . . . . . . 44
Data Retrieval Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Analysis of Index Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 v
Clustered vs. Non-Clustered Data and Index Organization . . . . . . . . . . . . 55
Index Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The B-Tree Index Layout. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The R-Tree Index Layout. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
The Hash Index Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
The FULLTEXT Index Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
General Index Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Clustering Key Selection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Query Structuring to Ensure Use of an Index . . . . . . . . . . . . . . . . . . . 66
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
¦CHAPTER 3 Transaction Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Transaction Processing Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Transaction Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
The ACID Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Ensuring Atomicity, Consistency, and Durability. . . . . . . . . . . . . . . . . . . . . . 76
The Transaction Wrapper and Demarcation . . . . . . . . . . . . . . . . . . . . 76
MySQL’s Autocommit Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Checkpointing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Implementing Isolation and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Locking Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Isolation Levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Locking and Isolation Levels in MySQL: Some Examples . . . . . . . . . 92
Multiversion Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Identifying Your Transaction Control Requirements . . . . . . . . . . . . . . . . . . 102
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
¦CHAPTER 4 MySQL System Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
The MySQL Source Code and Documentation . . . . . . . . . . . . . . . . . . . . . . 106
The Source Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
The MySQL Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
TEXI and texi2html Viewing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
MySQL Architecture Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
MySQL Server Subsystem Organization . . . . . . . . . . . . . . . . . . . . . . 112
Base Function Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
vi ¦CONTENTS
Process, Thread, and Resource Management . . . . . . . . . . . . . . . . . . . . . . 114
Thread-Based vs. Process-Based Design . . . . . . . . . . . . . . . . . . . . . 114
Implementation Through a Library of Related Functions . . . . . . . . 115
User Connection Threads and THD Objects. . . . . . . . . . . . . . . . . . . . 117
Storage Engine Abstraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Key Classes and Files for Handlers. . . . . . . . . . . . . . . . . . . . . . . . . . . 118
The Handler API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Caching and Memory Management Subsystem . . . . . . . . . . . . . . . . . . . . 121
Record Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Key Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Table Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Hostname Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Privilege Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Other Caches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Network Management and Communication . . . . . . . . . . . . . . . . . . . . . . . . 128
Access and Grant Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Log Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Query Parsing, Optimization, and Execution . . . . . . . . . . . . . . . . . . . . . . . . 135
Parsing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
The Query Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
A Typical Query Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
¦CHAPTER 5 Storage Engines and Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . 153
Storage Engine Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
The MyISAM Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
MyISAM File and Directory Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
MyISAM Record Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
The .MYI File Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
MyISAM Table-Level Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
MyISAM Index Choices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
MyISAM Limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
The InnoDB Storage Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Enforcement of Foreign Key Relationships . . . . . . . . . . . . . . . . . . . . 164
InnoDB Row-Level Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
ACID-Compliant Multistatement Transaction Control. . . . . . . . . . . . 166
The InnoDB File and Directory Layout . . . . . . . . . . . . . . . . . . . . . . . . 166
¦CONTENTS vii
InnoDB Data Page Organization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Internal InnoDB Buffers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
InnoDB Doublewrite Buffer and Log Format . . . . . . . . . . . . . . . . . . . 170
The Checkpointing and Recovery Processes . . . . . . . . . . . . . . . . . . 172
Other Storage Engines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
The MERGE Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
The MEMORY Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
The ARCHIVE Storage Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
The CSV Storage Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
The FEDERATED Storage Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
The NDB Cluster Storage Engine. . . . . . . . . . . . . . . . . . . . . . . . . 178
Guidelines for Choosing a Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . 178
Data Type Choices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Numeric Data Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
String Data Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Temporal Data Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Spatial Data Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
SET and ENUM Data Considerations . . . . . . . . . . . . . . . . . . . . . . . . . 184
Boolean Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Some General Data Type Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . 185
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
¦CHAPTER 6 Benchmarking and Profiling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
What Can Benchmarking Do for You? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Conducting Simple Performance Comparisons . . . . . . . . . . . . . . . . 191
Determining Load Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Testing an Application’s Ability to Deal with Change . . . . . . . . . . . . 192
Finding Potential Problem Areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
General Benchmarking Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Setting Real Performance Standards . . . . . . . . . . . . . . . . . . . . . . . . . 193
Being Proactive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Isolating Changed Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Using Real Data Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Making Small Changes and Rerunning Benchmarks . . . . . . . . . . . 197
Turning Off Unnecessary Programs and the Query Cache . . . . . . . 197
Repeating Tests to Determine Averages . . . . . . . . . . . . . . . . . . . . . . 197
Saving Benchmark Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Benchmarking Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
MySQL’s Benchmarking Suite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
MySQL Super Smack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
viii ¦CONTENTS
MyBench . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
ApacheBench (ab) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
httperf . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
What Can Profiling Do for You? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
General Profiling Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Profiling Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
The SHOW FULL PROCESSLIST Command . . . . . . . . . . . . . . . . . . . . 219
The SHOW STATUS Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
The EXPLAIN Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
The Slow Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
The General Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Mytop. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
The Zend Advanced PHP Debugger Extension . . . . . . . . . . . . . . . . . 229
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
¦CHAPTER 7 Essential SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
SQL Style. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Theta Style vs. ANSI Style. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Code Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Specific and Consistent Coding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
MySQL Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
The Inner Join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
The Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
The Cross Join (Cartesian Product). . . . . . . . . . . . . . . . . . . . . . . . . . . 253
The Union Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
The Natural Join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
The USING Keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
EXPLAIN and Access Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
The const Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
The eq_ref Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
The ref Access Type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
The ref_or_null Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
The index_merge Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
The unique_subquery Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . 267
The index_subquery Access Type. . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
The range Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
The index Access Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
The ALL Access Type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Join Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
The STRAIGHT_JOIN Hint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
¦CONTENTS ix
The USE INDEX and FORCE INDEX Hints . . . . . . . . . . . . . . . . . . . . . . 277
The IGNORE INDEX Hint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Subqueries and Derived Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
¦CHAPTER 8 SQL Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Handling OR Conditions Prior to MySQL 5.0 . . . . . . . . . . . . . . . . . . . . . . . . 300
Dealing with Duplicate Entries and Orphaned Records . . . . . . . . . . . . . . 303
Identifying and Removing Duplicate Entries . . . . . . . . . . . . . . . . . . . 304
Identifying and Removing Orphaned Records . . . . . . . . . . . . . . . 307
Dealing with Hierarchical Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Understanding the Nested Set Model. . . . . . . . . . . . . . . . . . . . . . . . . 313
Finding the Depth of a Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Finding All Nodes Under a Specific Parent. . . . . . . . . . . . . . . . . . . . . 317
Finding All Nodes Above a Specific Node. . . . . . . . . . . . . . . . . . . . . . 317
Summarizing Across the Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Inserting a Node into the Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Removing a Node from the Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Retrieving Random Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Calculating Distances with Geographic Coordinate Data . . . . . . . . . . . . . 328
Understanding the Distance Calculation Formula . . . . . . . . . . . . . . 329
Calculating the Distance Between Two Points . . . . . . . . . . . . . . . . . 331
Determining Zip Codes Within a Given Radius . . . . . . . . . . . . . . . . . 334
Generating Running Sums and Averages . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
¦CHAPTER 9 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Stored Procedure Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
The Debate Over Using Stored Procedures . . . . . . . . . . . . . . . . . . . . 350
Other Considerations in Using Stored Procedures . . . . . . . . . . . . . . 352
Stored Procedures in MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Building Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
The CREATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
The Procedure Body . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Using Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Calling Procedures from the MySQL Client . . . . . . . . . . . . . . . . . . . . 366
Calling Procedures from PHP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
x ¦CONTENTS
Managing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Viewing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Altering and Removing Stored Procedures . . . . . . . . . . . . . . . . . . . . 371
Editing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Stored Procedure Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
¦CHAPTER 10 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Database Function Uses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Functions Compared with Other Database Tools . . . . . . . . . . . . . . . . . . . . 377
Stored Functions vs. Stored Procedures . . . . . . . . . . . . . . . . . . . . . . 377
Functions vs. Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Functions vs. Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Functions in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Creating Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
CREATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Function Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Input Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Return Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
The Function Body. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Managing Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Viewing Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Changing and Removing Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Function Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Performance of Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
¦CHAPTER 11 Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Database Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Server and Client Cursors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Cursor Movement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Data Behind the Cursor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Read and Write Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Cursors in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Creating Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
DECLARE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
OPEN Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
¦CONTENTS xi
FETCH Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
CLOSE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Using Cursors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
¦CHAPTER 12 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Database View Uses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Views in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Creating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
The CREATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Algorithm Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
View Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
Column Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
The SELECT Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Check Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
Creating Updatable Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
Defining Views of Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
Managing Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Displaying Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Changing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Removing Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
View Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440
Performance of Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440
Running Performance Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Using EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
¦CHAPTER 13 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Database Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
The Debate Over Using Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Trigger Advantages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Trigger Disadvantages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Triggers in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Creating MySQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
The CREATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Trigger Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Activation Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
Event for Activation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
xii ¦CONTENTS
Table to Activate the Trigger. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Trigger Body Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Using Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
Managing Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Viewing Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Modifying and Removing Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Trigger Permissions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Trigger Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
PART 2 ¦ ¦ ¦ Administration
¦CHAPTER 14 MySQL Installation and Configuration . . . . . . . . . . . . . . . . . . . . 469
Using an Existing Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Installing Prebuilt Binaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Supported Operating Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
MySQL Build Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Manual Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Windows Installer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
RPMs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
Mac OS X Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476
Building from Source or the Development Source Tree . . . . . . . . . . . . . . 477
Manually Installing the Source Tarball on Unix . . . . . . . . . . . . . . . . . 477
Installing from the Development Source Tree on Unix. . . . . . . . . . . 481
Starting and Stopping MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Unix Startup and Shutdown . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Windows Startup and Shutdown. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Mac OS X Startup and Shutdown . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
Performing Post-Installation Setup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
Configuring MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Location of Configuration Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Configuration Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
Configuration Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
Upgrading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Uninstalling MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Running Multiple Database Servers on a Single Machine . . . . . . . . . . . . 495
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496
¦CONTENTS xiii
¦CHAPTER 15 User Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
MySQL Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Granting and Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Understanding Privilege Scope Levels . . . . . . . . . . . . . . . . . . . . . . . . 498
Granting All Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Viewing User Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
How MySQL Controls Access and Verifies Privileges. . . . . . . . . . . . . . . . . 510
How MySQL Authenticates Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
How MySQL Verifies User Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . 513
The Purpose of the host Grant Table. . . . . . . . . . . . . . . . . . . . . . . . . . 515
Managing User Accounts from the Command Line . . . . . . . . . . . . . . . . . . 516
Adding User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516
Restricting User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517
Removing Accounts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Effecting Account Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Using the MySQL Administrator GUI Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . 519
Connecting to the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520
Navigating User Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521
Adding a New User Account. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Viewing and Editing User Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Removing an Account . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
Thinking in Terms of User Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Practical Guidelines for User Administration. . . . . . . . . . . . . . . . . . . . . . . . 531
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
¦CHAPTER 16 Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
Understanding Security Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Why Aren’t We Secure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Where’s the Security Threat?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
What Does It Mean to Be Secure? . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
Building a Security Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Using Security Incidents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
Keeping Good Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
Getting Started: A MySQL Security Quick List . . . . . . . . . . . . . . . . . . . . . . 540
Setting Up Database Security: An Example. . . . . . . . . . . . . . . . . . . . . . . . . 541
Locking Up the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
Locking Down Network Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
Controlling Access to Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
xiv ¦CONTENTS
Adding Remote Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
Adding Servers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
Securing Your Whole System: Seven Major Areas . . . . . . . . . . . . . . . . . . . 545
Physical Access to the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
Operating System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
Files, Directories, and Processes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
User Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549
Application Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
Data Storage and Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
¦CHAPTER 17 Backup and Restoration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
Why Do We Create Backups? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
Creating a Backup and Restore Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
Developing Your Backup Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557
Developing Your Restore Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Backing Up a Database: A Quick Example . . . . . . . . . . . . . . . . . . . . . . . . . 559
Using MySQL Tools to Make and Restore Backups . . . . . . . . . . . . . . . . . . 560
Using mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Using mysqlhotcopy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
Creating Backups of InnoDB Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
Using MySQL Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
Using Binary Logs for Up-to-Date Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 581
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583
¦CHAPTER 18 Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585
What Is Replication? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586
Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586
Synchronous vs. Asynchronous Replication . . . . . . . . . . . . . . . . . . . 587
One-Way vs. Merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588
Why Replicate Data?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Geographic Diversity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Limited Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Redundancy and Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Storage Engine and Index Optimization . . . . . . . . . . . . . . . . . . . . . . . 590
What Isn’t Solved with Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
¦CONTENTS xv
Planning for Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
How MySQL Implements Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Binary Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Replication Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
Relay Log. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
info Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
master.info File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
Initial Replication Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
Adjust Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
Create Replication Account . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
Schema and Data Snapshot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
Start Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
Configuration Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Core Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Other Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
How Does MySQL Decide What to Replicate? . . . . . . . . . . . . . . . . . 605
Monitoring and Managing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
SHOW MASTER STATUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
SHOW SLAVE HOSTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
SHOW SLAVE STATUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
CHANGE MASTER. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609
START SLAVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610
STOP SLAVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610
RESET SLAVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
Replication Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
Hardware. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Database Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Record Size. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Amount of Data and Types of Queries . . . . . . . . . . . . . . . . . . . . . . . . 612
Replication Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Simple Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Multiple Slaves. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Daisy Chain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
Other . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
xvi ¦CONTENTS
¦CHAPTER 19 Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
What Is Clustering? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
MySQL’s Cluster Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
Nodes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620
Node Arrangement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621
Calculating the Number of Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
Using the Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Limitations of MySQL Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Installing MySQL Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Initial Configuration and Startup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Management Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Storage Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
SQL Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
Check Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
Cluster Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
Management Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
Storage Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
Management Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
Configuration File Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
Management Server Configuration File . . . . . . . . . . . . . . . . . . . . . . . 636
Storage Node Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
SQL Node Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
Managing the Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
Management Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
Single User Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
Backing Up and Restoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Cluster Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Node Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
¦CHAPTER 20 Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
Troubleshooting Toolkit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
Error Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
General Query Log. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
Slow Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
MySQL Server Thread List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
Operating System Process List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Monitoring Programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
¦CONTENTS xvii
Commonly Encountered Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Troubleshooting User, Connection, and Client Issues . . . . . . . . . . . 653
Troubleshooting Start, Restart, and Shutdown Issues . . . . . . . . . . . 661
Resolving Data Corruption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 666
How to Report MySQL Bugs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
Support Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668
¦CHAPTER 21 MySQL Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
Benefits of a Standardized Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
Adherence to Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
Using SELECT to Retrieve Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . 670
More Information than SHOW Commands. . . . . . . . . . . . . . . . . . . . . 671
The INFORMATION_SCHEMA Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
INFORMATION_SCHEMA.SCHEMATA . . . . . . . . . . . . . . . . . . . . . . . . . 673
INFORMATION_SCHEMA.TABLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
INFORMATION_SCHEMA.TABLE_CONSTRAINTS . . . . . . . . . . . . . . . 676
INFORMATION_SCHEMA.COLUMNS . . . . . . . . . . . . . . . . . . . . . . . . . . 678
INFORMATION_SCHEMA.KEY_COLUMN_USAGE . . . . . . . . . . . . . . . 680
INFORMATION_SCHEMA.STATISTICS . . . . . . . . . . . . . . . . . . . . . . . . . 682
INFORMATION_SCHEMA.ROUTINES . . . . . . . . . . . . . . . . . . . . . . . . . . 684
INFORMATION_SCHEMA.VIEWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 686
INFORMATION_SCHEMA.CHARACTER_SETS . . . . . . . . . . . . . . . . . . 687
INFORMATION_SCHEMA.COLLATIONS . . . . . . . . . . . . . . . . . . . . . . . . 688
INFORMATION_SCHEMA.COLLATION_CHARACTER_
SET_APPLICABILITY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
INFORMATION_SCHEMA.SCHEMA_PRIVILEGES . . . . . . . . . . . . . . . . 690
INFORMATION_SCHEMA.USER_PRIVILEGES . . . . . . . . . . . . . . . . . . . 691
INFORMATION_SCHEMA.TABLE_PRIVILEGES . . . . . . . . . . . . . . . . . . 692
INFORMATION_SCHEMA.COLUMN_PRIVILEGES. . . . . . . . . . . . . . . . 693
Usage Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 694
Example 1: Gathering Selectivity Numbers on Indexes . . . . . . . . . . 694
Example 2: Summarizing Table Sizes by Engine . . . . . . . . . . . . . . . 697
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 698
Цена: 200руб.