MySQL High Availability

نویسنده :Charles Bell, Mats Kindahl, and Lars Thalmann

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Part I. Replication
1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What’s This Replication Stuff Anyway? 5
So, Backups Are Not Needed Then? 6
What’s with All the Monitoring? 7
Is There Anything Else I Can Read? 8
Conclusion 8
2. MySQL Replication Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Basic Steps in Replication 12
Configuring the Master 13
Configuring the Slave 15
Connecting the Master and Slave 15
A Brief Introduction to the Binary Log 17
What’s Recorded in the Binary Log 17
Watching Replication in Action 18
The Binary Log’s Structure and Content 20
Python Support for Managing Replication 23
Basic Classes and Functions 25
Operating System 26
Server Class 26
Server Roles 28
Creating New Slaves 30
Cloning the Master 31
Cloning the Slave 33

Scripting the Clone Operation 35
Performing Common Tasks with Replication 36
Reporting 37
Conclusion 43
3. The Binary Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Structure of the Binary Log 46
Binlog Event Structure 48
Logging Statements 50
Logging Data Manipulation Language Statements 50
Logging Data Definition Language Statements 51
Logging Queries 51
LOAD DATA INFILE Statements 57
Binary Log Filters 59
Triggers, Events, and Stored Routines 61
Stored Procedures 66
Stored Functions 69
Events 71
Special Constructions 71
Nontransactional Changes and Error Handling 72
Logging Transactions 75
Transaction Cache 76
Distributed Transaction Processing Using XA 79
Binary Log Management 81
The Binary Log and Crash Safety 82
Binlog File Rotation 83
Incidents 85
Purging the Binlog File 86
The mysqlbinlog Utility 87
Basic Usage 88
Interpreting Events 94
Binary Log Options and Variables 98
Conclusion 100
4. Replication for High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Redundancy 104
Planning 106
Slave Failures 106
Master Failures 106
Relay Failures 107
Disaster Recovery 107
Procedures 107
Hot Standby 111

Dual Masters 115
Semisynchronous Replication 124
Slave Promotion 127
Circular Replication 142
Conclusion 146
5. MySQL Replication for Scale-Out . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Scaling Out Reads, Not Writes 149
The Value of Asynchronous Replication 150
Managing the Replication Topology 152
Example of an Application-Level Load Balancer 155
Hierarchal Replication 159
Setting Up a Relay Server 160
Adding a Relay in Python 161
Specialized Slaves 162
Filtering Replication Events 162
Using Filtering to Partition Events to Slaves 164
Data Sharding 165
Shard Representation 168
Partitioning the Data 170
Balancing the Shards 171
A Sharding Example 173
Managing Consistency of Data 184
Consistency in a Nonhierarchal Deployment 185
Consistency in a Hierarchal Deployment 187
Conclusion 193
6. Advanced Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Replication Architecture Basics 196
The Structure of the Relay Log 196
The Replication Threads 200
Starting and Stopping the Slave Threads 201
Running Replication over the Internet 202
Setting Up Secure Replication Using Built-in Support 204
Setting Up Secure Replication Using Stunnel 204
Finer-Grained Control over Replication 206
Information About Replication Status 206
Options for Handling Broken Connections 214
How the Slave Processes Events 215
Housekeeping in the I/O Thread 216
SQL Thread Processing 217
Slave Safety and Recovery 222
Syncing, Transactions, and Problems with Database Crashes 222

Rules for Protecting Nontransactional Statements 225
Multisource Replication 226
Row-Based Replication 229
Options for Row-Based Replication 230
Mixed-Mode Replication 231
Events for Handling Row-Based Replication 232
Event Execution 236
Events and Triggers 238
Filtering 240
Conclusion 241
Part II. Monitoring and Disaster Recovery
7. Getting Started with Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Ways of Monitoring 246
Benefits of Monitoring 247
System Components to Monitor 247
Processor 248
Memory 249
Disk 250
Network Subsystem 251
Monitoring Solutions 252
Linux and Unix Monitoring 253
Process Activity 253
Memory Usage 259
Disk Usage 261
Network Activity 265
General System Statistics 266
Automated Monitoring with cron 268
Mac OS X Monitoring 268
System Profiler 268
Console 271
Activity Monitor 273
Microsoft Windows Monitoring 276
The Windows Experience 277
The System Health Report 278
The Event Viewer 281
The Reliability Monitor 283
The Task Manager 285
The Performance Monitor 285
Monitoring as Preventive Maintenance 288
Conclusion 288

8. Monitoring MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
What Is Performance? 292
MySQL Server Monitoring 292
How MySQL Communicates Performance 293
Performance Monitoring 293
SQL Commands 294
The mysqladmin Utility 300
MySQL GUI Tools 302
MySQL Administrator 302
MySQL Query Browser 312
Server Logs 313
Third-Party Tools 316
The MySQL Benchmark Suite 318
Database Performance 319
Measuring Database Performance 320
Database Optimization Best Practices 331
Best Practices for Improving Performance 339
Everything Is Slow 340
Slow Queries 340
Slow Applications 340
Slow Replication 341
Conclusion 341
9. Storage Engine Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
MyISAM 344
Optimizing Disk Storage 344
Tuning Your Tables for Performance 345
Using the MyISAM Utilities 345
Storing a Table in Index Order 347
Compressing Tables 347
Defragmenting Tables 348
Monitoring the Key Cache 348
Preloading Key Caches 349
Using Multiple Key Caches 350
Other Parameters to Consider 351
InnoDB 352
Using the SHOW ENGINE Command 354
Using InnoDB Monitors 357
Monitoring Logfiles 359
Monitoring the Buffer Pool 360
Monitoring Tablespaces 363
Using INFORMATION_SCHEMA Tables 363
Other Parameters to Consider 365

Conclusion 366
10. Replication Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Getting Started 367
Server Setup 368
Inclusive and Exclusive Replication 368
Replication Threads 371
Monitoring the Master 372
Monitoring Commands for the Master 373
Master Status Variables 376
Monitoring Slaves 376
Monitoring Commands for the Slave 377
Slave Status Variables 380
Replication Monitoring with MySQL Administrator 381
Other Items to Consider 383
Networking 383
Monitor and Manage Slave Lag 383
Causes and Cures for Slave Lag 384
Conclusion 386
11. Replication Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
What Can Go Wrong 388
Problems on the Master 388
Problems on the Slave 393
Advanced Replication Problems 398
Tools for Troubleshooting Replication 399
Best Practices 401
Know Your Topology 401
Check the Status of All of Your Servers 403
Check Your Logs 404
Check Your Configuration 404
Conduct Orderly Shutdowns 404
Conduct Orderly Restarts After a Failure 405
Manually Execute Failed Queries 405
Common Procedures 406
Reporting Replication Bugs 407
Conclusion 407
12. Protecting Your Investment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
What Is Information Assurance? 410
The Three Practices of Information Assurance 410
Why Is Information Assurance Important? 411
Information Integrity, Disaster Recovery, and the Role of Backups 411

High Availability Versus Disaster Recovery 412
Disaster Recovery 413
The Importance of Data Recovery 419
Backup and Restore 420
Backup Utilities and OS-Level Solutions 424
The InnoDB Hot Backup Application 425
Physical File Copy 428
The mysqldump Utility 430
XtraBackup 432
Logical Volume Manager Snapshots 432
Comparison of Backup Methods 437
Backup and MySQL Replication 438
Backup and Recovery with Replication 438
PITR 439
Automating Backups 446
Conclusion 449
13. MySQL Enterprise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
Getting Started with MySQL Enterprise 452
Subscription Levels 453
Installation Overview 454
MySQL Enterprise Components 456
MySQL Enterprise Server 456
MEM 456
MySQL Production Support 459
Using MySQL Enterprise 460
Installation 460
Fixing Monitoring Agent Problems 462
Monitoring 463
Query Analyzer 470
Further Information 473
Conclusion 473
Part III. High Availability Environments
14. Cloud Computing Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
What Is Cloud Computing? 478
Cloud Architectures 480
Is Cloud Computing an Economical Choice? 483
Cloud Computing Use Cases 484
Cloud Computing Benefits 485
Cloud Computing Vendors 486

AWS 487
A Brief Overview of Technologies 488
How Does It All Work? 492
Amazon Cloud Tools 492
Getting Started 496
Working with Disk 511
Where to Go from Here 516
MySQL in the Cloud 517
MySQL Replication and EC2 517
Best Practices for Using MySQL in EC2 520
Open Source Cloud Computing 522
Conclusion 523
15. MySQL Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525
What Is MySQL Cluster? 526
Terminology and Components 526
How Does MySQL Cluster Differ from MySQL? 527
Typical Configuration 527
Features of MySQL Cluster 528
Local and Global Redundancy 530
Log Handling 531
Redundancy and Distributed Data 531
Architecture of MySQL Cluster 532
How Data Is Stored 533
Partitioning 536
Transaction Management 537
Online Operations 537
Example Configuration 539
Getting Started 539
Starting a MySQL Cluster 541
Testing the Cluster 546
Shutting Down the Cluster 546
Achieving High Availability 547
System Recovery 550
Node Recovery 551
Replication 552
Achieving High Performance 557
Considerations for High Performance 557
High Performance Best Practices 558
Conclusion 561
Appendix: Replication Tips and Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563