Server bottlenecks and failures are a fact of life in any database deployment, but they don’t have to bring everything to a halt. This practical book explains replication, cluster, and monitoring features that can help protect your MySQL system from outages, whether it’s running on hardware, virtual machines, or in the cloud.
Written by engineers who designed many of the tools covered, this book reveals undocumented or hard-to-find aspects of MySQL reliability and high availability—knowledge that’s essential for any organization using this database system. This second edition describes extensive changes to MySQL tools. Versions up to 5.5 are covered, along with several 5.6 features.
Learn replication fundamentals, including use of the binary log and MySQL Replicant Library
Scale out to manage read-load increases, and use data sharding to handle large databases and write-load increases
Keep track of masters and slaves, and deal with failures and restarts, corruption, and other incidents
Foreword for the Second Edition xv
Foreword for the First Edition xix
Preface xxi
Part I. High Availability and Scalability
1 Introduction 3 (8)
What's This Replication Stuff, Anyway? 5 (2)
So, Backups Are Not Needed Then? 7 (1)
What's With All the Monitoring? 7 (1)
Is There Anything Else I Can Read? 8 (1)
Conclusion 9 (2)
2 MySQL Replicant Library 11 (12)
Basic Classes and Functions 15 (1)
Supporting Different Operating Systems 16 (1)
Servers 17 (2)
Server Roles 19 (2)
Conclusion 21 (2)
3 MySQL Replication Fundamentals 23 (28)
Basic Steps in Replication 24 (5)
Configuring the Master 25 (2)
Configuring the Slave 27 (1)
Connecting the Master and Slave 28 (1)
A Brief Introduction to the Binary Log 29 (6)
What's Recorded in the Binary Log 30 (1)
Watching Replication in Action 30 (3)
The Binary Log's Structure and Content 33 (2)
Adding Slaves 35 (7)
Cloning the Master 37 (2)
Cloning a Slave 39 (2)
Scripting the Clone Operation 41 (1)
Performing Common Tasks with Replication 42 (7)
Reporting 43 (6)
Conclusion 49 (2)
4 The Binary Log 51 (72)
Structure of the Binary Log 52 (6)
Binlog Event Structure 54 (2)
Event Checksums 56 (2)
Logging Statements 58 (28)
Logging Data Manipulation Language 58 (1)
Statements
Logging Data Definition Language 59 (1)
Statements
Logging Queries 59 (6)
LOAD DATA INFILE Statements 65 (2)
Binary Log Filters 67 (3)
Triggers, Events, and Stored Routines 70 (5)
Stored Procedures 75 (3)
Stored Functions 78 (3)
Events 81 (1)
Special Constructions 82 (1)
Nontransactional Changes and Error 83 (3)
Handling
Logging Transactions 86 (11)
Transaction Cache 87 (4)
Distributed Transaction Processing Using 91 (3)
XA
Binary Log Group Commit 94 (3)
Row-Based Replication 97 (3)
Enabling Row-based Replication 98 (1)
Using Mixed Mode 99 (1)
Binary Log Management 100(5)
The Binary Log and Crash Safety 100(1)
Binlog File Rotation 101(2)
Incidents 103(1)
Purging the Binlog File 104(1)
The mysqlbinlog Utility 105(13)
Basic Usage 106(7)
Interpreting Events 113(5)
Binary Log Options and Variables 118(3)
Options for Row-Based Replication 120(1)
Conclusion 121(2)
5 Replication for High Availability 123(30)
Redundancy 124(2)
Planning 126(2)
Slave Failures 127(1)
Master Failures 127(1)
Relay Failures 127(1)
Disaster Recovery 127(1)
Procedures 128(23)
Hot Standby 130(5)
Dual Masters 135(10)
Slave Promotion 145(4)
Circular Replication 149(2)
Conclusion 151(2)
6 MySQI Replication for Scale-Out 153(36)
Scaling Out Reads, Not Writes 155(1)
The Value of Asynchronous Replication 156(2)
Managing the Replication Topology 158(12)
Application-Level Load Balancing 162(8)
Hierarchical Replication 170(3)
Setting Up a Relay Server 171(1)
Adding a Relay in Python 172(1)
Specialized Slaves 173(4)
Filtering Replication Events 174(2)
Using Filtering to Partition Events to 176(1)
Slaves
Managing Consistency of Data 177(10)
Consistency in a Nonhierarchical 178(2)
Deployment
Consistency in a Hierarchical Deployment 180(7)
Conclusion 187(2)
7 Data Sharding 189(38)
What Is Sharding? 190(4)
Why Should You Shard? 191(1)
Limitations of Sharding 192(2)
Elements of a Sharding Solution 194(3)
High-Level Sharding Architecture 196(1)
Partitioning the Data 197(9)
Shard Allocation 202(4)
Mapping the Sharding Key 206(9)
Sharding Scheme 206(4)
Shard Mapping Functions 210(5)
Processing Queries and Dispatching 215(5)
Transactions
Handling Transactions 216(2)
Dispatching Queries 218(2)
Shard Management 220(5)
Moving a Shard to a Different Node 220(5)
Splitting Shards 225(1)
Conclusion 225(2)
8 Replication Deep Dive 227(64)
Replication Architecture Basics 228(7)
The Structure of the Relay Log 229(4)
The Replication Threads 233(1)
Starting and Stopping the Slave Threads 234(1)
Running Replication over the Internet 235(4)
Setting Up Secure Replication Using 237(1)
Built-in Support
Setting Up Secure Replication Using 238(1)
Stunnel
Finer-Grained Control Over Replication 239(9)
Information About Replication Status 239(9)
Options for Handling Broken Connections 248(1)
How the Slave Processes Events 249(8)
Housekeeping in the I/O Thread 249(1)
SQL Thread Processing 250(7)
Semisynchronous Replication 257(3)
Configuring Semisynchronous Repli
新书报道