高可用性MySQL 第2版 英文PDF电子书下载

Part Ⅰ．High Availability and Scalability 3

1．Introduction 3

What's This Replication Stuff,Anyway? 5

So,Backups Are Not Needed Then? 7

What's With All the Monitoring? 7

Is There Anything Else I Can Read? 8

Conclusion 9

2．MySQL Replicant Library 11

Basic Classes and Functions 15

Supporting Different Operating Systems 16

Servers 17

Server Roles 19

Conclusion 21

3．MySQL Replication Fundamentals 23

Basic Steps in Replication 24

Configuring the Master 25

Configuring the Slave 27

Connecting the Master and Slave 28

A Brief Introduction to the Binary Log 29

What's Recorded in the Binary Log 30

Watching Replication in Action 30

The Binary Log's Structure and Content 33

Adding Slaves 35

Cloning the Master 37

Cloning a Slave 39

Scripting the Clone Operation 41

Performing Common Tasks with Replication 42

Reporting 43

Conclusion 49

4．The Binary Log 51

Structure of the Binary Log 52

Binlog Event Structure 54

Event Checksums 56

Logging Statements 58

Logging Data Manipulation Language Statements 58

Logging Data Definition Language Statements 59

Logging Queries 59

LOAD DATA INFILE Statements 65

Binary Log Filters 67

Triggers,Events,and Stored Routines 70

Stored Procedures 75

Stored Functions 78

Events 81

Special Constructions 82

Nontransactional Changes and Error Handling 83

Logging Transactions 86

Transaction Cache 87

Distributed Transaction Processing Using XA 91

Binary Log Group Commit 94

Row-Based Replication 97

Enabling Row-based Replication 98

Using Mixed Mode 99

Binary Log Management 100

The Binary Log and Crash Safety 100

Binlog File Rotation 101

Incidents 103

Purging the Binlog File 104

The mysqlbinlog Utility 105

Basic Usage 106

Interpreting Events 113

Binary Log Options and Variables 118

Options for Row-Based Replication 120

Conclusion 121

5．Replication for High Availability 123

Redundancy 124

Planning 126

Slave Failures 127

Master Failures 127

Relay Failures 127

Disaster Recovery 127

Procedures 128

Hot Standby 130

Dual Masters 135

Slave Promotion 145

Circular Replication 149

Conclusion 151

6．MySQL Replication for Scale-Out 153

Scaling Out Reads,Not Writes 155

The Value of Asynchronous Replication 156

Managing the Replication Topology 158

Application-Level Load Balancing 162

Hierarchical Replication 170

Setting Up a Relay Server 171

Adding a Relay in Python 172

Specialized Slaves 173

Filtering Replication Events 174

Using Filtering to Partition Events to Slaves 176

Managing Consistency of Data 177

Consistency in a Nonhierarchical Deployment 178

Consistency in a Hierarchical Deployment 180

Conclusion 187

7．Data Sharding 189

What Is Sharding? 190

Why Should You Shard? 191

Limitations of Sharding 192

Elements of a Sharding Solution 194

High-Level Sharding Architecture 196

Partitioning the Data 197

Shard Allocation 202

Mapping the Sharding Key 206

Sharding Scheme 206

Shard Mapping Functions 210

Processing Queries and Dispatching Transactions 215

Handling Transactions 216

Dispatching Queries 218

Shard Management 220

Moving a Shard to a Different Node 220

Splitting Shards 225

Conclusion 225

8．Replication Deep Dive 227

Replication Architecture Basics 228

The Structure of the Relay Log 229

The Replication Threads 233

Starting and Stopping the Slave Threads 234

Running Replication over the Internet 235

Setting Up Secure Replication Using Built-in Support 237

Setting Up Secure Replication Using Stunnel 238

Finer-Grained Control Over Replication 239

Information About Replication Status 239

Options for Handling Broken Connections 248

How the Slave Processes Events 249

Housekeeping in the I/O Thread 249

SQL Thread Processing 250

Semisynchronous Replication 257

Configuring Semisynchronous Replication 258

Monitoring Semisynchronous Replication 259

Global Transaction Identifiers 260

Setting Up Replication Using GTIDs 261

Failover Using GTIDs 263

Slave Promotion Using GTIDs 264

Replication of GTIDs 266

Slave Safety and Recovery 268

Syncing,Transactions,and Problems with Database Crashes 268

Transactional Replication 270

Rules for Protecting Nontransactional Statements 274

Multisource Replication 275

Details of Row-Based Replication 278

Table_map Events 280

The Structure of Row Events 282

Execution of Row Event 283

Events and Triggers 284

Filtering in Row-Based Replication 286

Partial Row Replication 288

Conclusion 289

9．MySQL Cluster 291

What Is MySQL Cluster? 292

Terminology and Components 292

How Does MySQL Cluster Differ from MySQL? 293

Typical Configuration 293

Features of MySQL Cluster 294

Local and Global Redundancy 296

Log Handling 297

Redundancy and Distributed Data 297

Architecture of MySQL Cluster 298

How Data Is Stored 300

Partitioning 303

Transaction Management 304

Online Operations 304

Example Configuration 306

Getting Started 306

Starting a MySQL Cluster 308

Testing the Cluster 313

Shutting Down the Cluster 314

Achieving High Availability 314

System Recovery 317

Node Recovery 318

Replication 319

Achieving High Performance 324

Considerations for High Performance 325

High Performance Best Practices 326

Conclusion 328

Part Ⅱ．Monitoring and Managing 333

10．Getting Started with Monitoring 333

Ways of Monitoring 334

Benefits of Monitoring 335

System Components to Monitor 335

Processor 336

Memory 337

Disk 338

Network Subsystem 339

Monitoring Solutions 340

Linux and Unix Monitoring 341

Process Activity 342

Memory Usage 347

Disk Usage 350

Network Activity 353

General System Statistics 355

Automated Monitoring with cron 356

Mac OS X Monitoring 356

System Profiler 357

Console 359

Activity Monitor 361

Microsoft Windows Monitoring 365

The Windows Experience 366

The System Health Report 367

The Event Viewer 369

The Reliability Monitor 372

The Task Manager 374

The Performance Monitor 375

Monitoring as Preventive Maintenance 377

Conclusion 377

11．Monitoring MySQL 379

What Is Performance? 380

MySQL Server Monitoring 381

How MySQL Communicates Performance 381

Performance Monitoring 382

SQL Commands 383

The mysqladmin Utility 389

MySQL Workbench 391

Third-Party Tools 402

The MySQL Benchmark Suite 405

Server Logs 407

Performance Schema 409

Concepts 410

Getting Started 412

Using Performance Schema to Diagnose Performance Problems 420

MySQL Monitoring Taxonomy 421

Database Performance 423

Measuring Database Performance 423

Best Practices for Database Optimization 435

Best Practices for Improving Performance 444

Everything Is Slow 444

Slow Queries 444

Slow Applications 445

Slow Replication 445

Conclusion 446

12．Storage Engine Monitoring 447

InnoDB 448

Using the SHOW ENGINE Command 450

Using InnoDB Monitors 453

Monitoring Logfiles 457

Monitoring the Buffer Pool 458

Monitoring Tablespaces 460

Using INFORMATION_SCHEMA Tables 461

Using PERFORMANCE_SCHEMA Tables 462

Other Parameters to Consider 463

Troubleshooting Tips for InnoDB 464

MyISAM 467

Optimizing Disk Storage 467

Repairing Your Tables 468

Using the MyISAM Utilities 468

Storing a Table in Index Order 470

Compressing Tables 471

Defragmenting Tables 471

Monitoring the Key Cache 471

Preloading Key Caches 472

Using Multiple Key Caches 473

Other Parameters to Consider 474

Conclusion 475

13．Replication Monitoring 477

Getting Started 477

Server Setup 478

Inclusive and Exclusive Replication 478

Replication Threads 481

Monitoring the Master 483

Monitoring Commands for the Master 483

Master Status Variables 487

Monitoring Slaves 487

Monitoring Commands for the Slave 487

Slave Status Variables 492

Replication Monitoring with MySQL Workbench 493

Other Items to Consider 495

Networking 495

Monitor and Manage Slave Lag 496

Causes and Cures for Slave Lag 497

Working with GTIDs 498

Conclusion 499

14．Replication Troubleshooting 501

What Can Go Wrong 502

Problems on the Master 503

Master Crashed and Memory Tables Are in Use 503

Master Crashed and Binary Log Events Are Missing 503

Query Runs Fine on the Master but Not on the Slave 505

Table Corruption After a Crash 505

Binary Log Is Corrupt on the Master 506

Killing Long-Running Queries for Nontransactional Tables 507

Unsafe Statements 507

Problems on the Slave 509

Slave Server Crashed and Replication Won't Start 510

Slave Connection Times Out and Reconnects Frequently 510

Query Results Are Different on the Slave than on the Master 511

Slave Issues Errors when Attempting to Restart with SSL 512

Memory Table Data Goes Missing 513

Temporary Tables Are Missing After a Slave Crash 513

Slave Is Slow and Is Not Synced with the Master 513

Data Loss After a Slave Crash 514

Table Corruption After a Crash 514

Relay Log Is Corrupt on the Slave 515

Multiple Errors During Slave Restart 515

Consequences of a Failed Transaction on the Slave 515

I/O Thread Problems 515

SQL Thread Problems: Inconsistencies 516

Different Errors on the Slave 517

Advanced Replication Problems 517

A Change Is Not Replicated Among the Topology 517

Circular Replication Issues 518

Multimaster Issues 518

The HA_ERR_KEY_NOT_FOUND Error 519

GTID Problems 519

Tools for Troubleshooting Replication 520

Best Practices 521

Know Your Topology 521

Check the Status of All of Your Servers 523

Check Your Logs 523

Check Your Configuration 524

Conduct Orderly Shutdowns 525

Conduct Orderly Restarts After a Failure 525

Manually Execute Failed Queries 526

Don't Mix Transactional and Nontransactional Tables 526

Common Procedures 526

Reporting Replication Bugs 528

Conclusion 529

15．Protecting Your Investment 531

What Is Information Assurance? 532

The Three Practices of Information Assurance 532

Why Is Information Assurance Important? 533

Information Integrity,Disaster Recovery,and the Role of Backups 533

High Availability Versus Disaster Recovery 534

Disaster Recovery 535

The Importance of Data Recovery 541

Backup and Restore 542

Backup Tools and OS-Level Solutions 547

MySQL Enterprise Backup 548

MySQL Utilities Database Export and Import 559

The mysqldump Utility 560

Physical File Copy 562

Logical Volume Manager Snapshots 564

XtraBackup 569

Comparison of Backup Methods 569

Backup and MySQL Replication 570

Backup and Recovery with Replication 571

PITR 571

Automating Backups 579

Conclusion 581

16．MySQL Enterprise Monitor 583

Getting Started with MySQL Enterprise Monitor 584

Commercial Offerings 585

Anatomy of MySQL Enterprise Monitor 585

Installation Overview 586

MySQL Enterprise Monitor Components 590

Dashboard 591

Monitoring Agent 594

Advisors 594

Query Analyzer 595

MySQL Production Support 597

Using MySQL Enterprise Monitor 597

Monitoring 599

Query Analyzer 605

Further Information 608

Conclusion 609

17．Managing MySQL Replication with MySQL Utilities 611

Common MySQL Replication Tasks 612

Checking Status 612

Stopping Replication 615

Adding Slaves 617

MySQL Utilities 618

Getting Started 618

Using the Utilities Without Workbench 619

Using the Utilities via Workbench 619

General Utilities 621

Comparing Databases for Consistency: mysqldbcompare 621

Copying Databases:mysqldbcopy 624

Exporting Databases:mysqldbexport 625

Importing Databases:mysqldbimport 628

Discovering Differences:mysqldiff 629

Showing Disk Usage:mysqldiskusage 632

Checking Tables Indexes:mysqlindexcheck 635

Searching Metadata:mysqlmetagrep 636

Searching for Processes:mysqlprocgrep 637

Cloning Servers:mysqlserverclone 639

Showing Server Information:mysqlserverinfo 641

Cloning Users:mysqluserclone 642

Utilities Client:mysqluc 643

Replication Utilities 644

Setting Up Replication:mysqlreplicate 644

Checking Replication Setup:mysqlrplcheck 646

Showing Topologies:mysqlrplshow 648

High Availability Utilities 650

Concepts 650

mysqlrpladmin 651

mysqlfailover 655

Creating Your Own Utilities 663

Architecture of MySQL Utilities 663

Custom Utility Example 664

Conclusion 673

A．Replication Tips and Tricks 675

B．AGTID Implementation 693

Index 705