高可用性MySQL 第2版 英文PDF|Epub|txt|kindle电子书版本网盘下载

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

Part Ⅰ．High Availability and Scalability3

1．Introduction3

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

Conclusion9

2．MySQL Replicant Library11

Basic Classes and Functions15

Supporting Different Operating Systems16

Servers17

Server Roles19

Conclusion21

3．MySQL Replication Fundamentals23

Basic Steps in Replication24

Configuring the Master25

Configuring the Slave27

Connecting the Master and Slave28

A Brief Introduction to the Binary Log29

What's Recorded in the Binary Log30

Watching Replication in Action30

The Binary Log's Structure and Content33

Adding Slaves35

Cloning the Master37

Cloning a Slave39

Scripting the Clone Operation41

Performing Common Tasks with Replication42

Reporting43

Conclusion49

4．The Binary Log51

Structure of the Binary Log52

Binlog Event Structure54

Event Checksums56

Logging Statements58

Logging Data Manipulation Language Statements58

Logging Data Definition Language Statements59

Logging Queries59

LOAD DATA INFILE Statements65

Binary Log Filters67

Triggers,Events,and Stored Routines70

Stored Procedures75

Stored Functions78

Events81

Special Constructions82

Nontransactional Changes and Error Handling83

Logging Transactions86

Transaction Cache87

Distributed Transaction Processing Using XA91

Binary Log Group Commit94

Row-Based Replication97

Enabling Row-based Replication98

Using Mixed Mode99

Binary Log Management100

The Binary Log and Crash Safety100

Binlog File Rotation101

Incidents103

Purging the Binlog File104

The mysqlbinlog Utility105

Basic Usage106

Interpreting Events113

Binary Log Options and Variables118

Options for Row-Based Replication120

Conclusion121

5．Replication for High Availability123

Redundancy124

Planning126

Slave Failures127

Master Failures127

Relay Failures127

Disaster Recovery127

Procedures128

Hot Standby130

Dual Masters135

Slave Promotion145

Circular Replication149

Conclusion151

6．MySQL Replication for Scale-Out153

Scaling Out Reads,Not Writes155

The Value of Asynchronous Replication156

Managing the Replication Topology158

Application-Level Load Balancing162

Hierarchical Replication170

Setting Up a Relay Server171

Adding a Relay in Python172

Specialized Slaves173

Filtering Replication Events174

Using Filtering to Partition Events to Slaves176

Managing Consistency of Data177

Consistency in a Nonhierarchical Deployment178

Consistency in a Hierarchical Deployment180

Conclusion187

7．Data Sharding189

What Is Sharding?190

Why Should You Shard?191

Limitations of Sharding192

Elements of a Sharding Solution194

High-Level Sharding Architecture196

Partitioning the Data197

Shard Allocation202

Mapping the Sharding Key206

Sharding Scheme206

Shard Mapping Functions210

Processing Queries and Dispatching Transactions215

Handling Transactions216

Dispatching Queries218

Shard Management220

Moving a Shard to a Different Node220

Splitting Shards225

Conclusion225

8．Replication Deep Dive227

Replication Architecture Basics228

The Structure of the Relay Log229

The Replication Threads233

Starting and Stopping the Slave Threads234

Running Replication over the Internet235

Setting Up Secure Replication Using Built-in Support237

Setting Up Secure Replication Using Stunnel238

Finer-Grained Control Over Replication239

Information About Replication Status239

Options for Handling Broken Connections248

How the Slave Processes Events249

Housekeeping in the I/O Thread249

SQL Thread Processing250

Semisynchronous Replication257

Configuring Semisynchronous Replication258

Monitoring Semisynchronous Replication259

Global Transaction Identifiers260

Setting Up Replication Using GTIDs261

Failover Using GTIDs263

Slave Promotion Using GTIDs264

Replication of GTIDs266

Slave Safety and Recovery268

Syncing,Transactions,and Problems with Database Crashes268

Transactional Replication270

Rules for Protecting Nontransactional Statements274

Multisource Replication275

Details of Row-Based Replication278

Table_map Events280

The Structure of Row Events282

Execution of Row Event283

Events and Triggers284

Filtering in Row-Based Replication286

Partial Row Replication288

Conclusion289

9．MySQL Cluster291

What Is MySQL Cluster?292

Terminology and Components292

How Does MySQL Cluster Differ from MySQL?293

Typical Configuration293

Features of MySQL Cluster294

Local and Global Redundancy296

Log Handling297

Redundancy and Distributed Data297

Architecture of MySQL Cluster298

How Data Is Stored300

Partitioning303

Transaction Management304

Online Operations304

Example Configuration306

Getting Started306

Starting a MySQL Cluster308

Testing the Cluster313

Shutting Down the Cluster314

Achieving High Availability314

System Recovery317

Node Recovery318

Replication319

Achieving High Performance324

Considerations for High Performance325

High Performance Best Practices326

Conclusion328

Part Ⅱ．Monitoring and Managing333

10．Getting Started with Monitoring333

Ways of Monitoring334

Benefits of Monitoring335

System Components to Monitor335

Processor336

Memory337

Disk338

Network Subsystem339

Monitoring Solutions340

Linux and Unix Monitoring341

Process Activity342

Memory Usage347

Disk Usage350

Network Activity353

General System Statistics355

Automated Monitoring with cron356

Mac OS X Monitoring356

System Profiler357

Console359

Activity Monitor361

Microsoft Windows Monitoring365

The Windows Experience366

The System Health Report367

The Event Viewer369

The Reliability Monitor372

The Task Manager374

The Performance Monitor375

Monitoring as Preventive Maintenance377

Conclusion377

11．Monitoring MySQL379

What Is Performance?380

MySQL Server Monitoring381

How MySQL Communicates Performance381

Performance Monitoring382

SQL Commands383

The mysqladmin Utility389

MySQL Workbench391

Third-Party Tools402

The MySQL Benchmark Suite405

Server Logs407

Performance Schema409

Concepts410

Getting Started412

Using Performance Schema to Diagnose Performance Problems420

MySQL Monitoring Taxonomy421

Database Performance423

Measuring Database Performance423

Best Practices for Database Optimization435

Best Practices for Improving Performance444

Everything Is Slow444

Slow Queries444

Slow Applications445

Slow Replication445

Conclusion446

12．Storage Engine Monitoring447

InnoDB448

Using the SHOW ENGINE Command450

Using InnoDB Monitors453

Monitoring Logfiles457

Monitoring the Buffer Pool458

Monitoring Tablespaces460

Using INFORMATION_SCHEMA Tables461

Using PERFORMANCE_SCHEMA Tables462

Other Parameters to Consider463

Troubleshooting Tips for InnoDB464

MyISAM467

Optimizing Disk Storage467

Repairing Your Tables468

Using the MyISAM Utilities468

Storing a Table in Index Order470

Compressing Tables471

Defragmenting Tables471

Monitoring the Key Cache471

Preloading Key Caches472

Using Multiple Key Caches473

Other Parameters to Consider474

Conclusion475

13．Replication Monitoring477

Getting Started477

Server Setup478

Inclusive and Exclusive Replication478

Replication Threads481

Monitoring the Master483

Monitoring Commands for the Master483

Master Status Variables487

Monitoring Slaves487

Monitoring Commands for the Slave487

Slave Status Variables492

Replication Monitoring with MySQL Workbench493

Other Items to Consider495

Networking495

Monitor and Manage Slave Lag496

Causes and Cures for Slave Lag497

Working with GTIDs498

Conclusion499

14．Replication Troubleshooting501

What Can Go Wrong502

Problems on the Master503

Master Crashed and Memory Tables Are in Use503

Master Crashed and Binary Log Events Are Missing503

Query Runs Fine on the Master but Not on the Slave505

Table Corruption After a Crash505

Binary Log Is Corrupt on the Master506

Killing Long-Running Queries for Nontransactional Tables507

Unsafe Statements507

Problems on the Slave509

Slave Server Crashed and Replication Won't Start510

Slave Connection Times Out and Reconnects Frequently510

Query Results Are Different on the Slave than on the Master511

Slave Issues Errors when Attempting to Restart with SSL512

Memory Table Data Goes Missing513

Temporary Tables Are Missing After a Slave Crash513

Slave Is Slow and Is Not Synced with the Master513

Data Loss After a Slave Crash514

Table Corruption After a Crash514

Relay Log Is Corrupt on the Slave515

Multiple Errors During Slave Restart515

Consequences of a Failed Transaction on the Slave515

I/O Thread Problems515

SQL Thread Problems: Inconsistencies516

Different Errors on the Slave517

Advanced Replication Problems517

A Change Is Not Replicated Among the Topology517

Circular Replication Issues518

Multimaster Issues518

The HA_ERR_KEY_NOT_FOUND Error519

GTID Problems519

Tools for Troubleshooting Replication520

Best Practices521

Know Your Topology521

Check the Status of All of Your Servers523

Check Your Logs523

Check Your Configuration524

Conduct Orderly Shutdowns525

Conduct Orderly Restarts After a Failure525

Manually Execute Failed Queries526

Don't Mix Transactional and Nontransactional Tables526

Common Procedures526

Reporting Replication Bugs528

Conclusion529

15．Protecting Your Investment531

What Is Information Assurance?532

The Three Practices of Information Assurance532

Why Is Information Assurance Important?533

Information Integrity,Disaster Recovery,and the Role of Backups533

High Availability Versus Disaster Recovery534

Disaster Recovery535

The Importance of Data Recovery541

Backup and Restore542

Backup Tools and OS-Level Solutions547

MySQL Enterprise Backup548

MySQL Utilities Database Export and Import559

The mysqldump Utility560

Physical File Copy562

Logical Volume Manager Snapshots564

XtraBackup569

Comparison of Backup Methods569

Backup and MySQL Replication570

Backup and Recovery with Replication571

PITR571

Automating Backups579

Conclusion581

16．MySQL Enterprise Monitor583

Getting Started with MySQL Enterprise Monitor584

Commercial Offerings585

Anatomy of MySQL Enterprise Monitor585

Installation Overview586

MySQL Enterprise Monitor Components590

Dashboard591

Monitoring Agent594

Advisors594

Query Analyzer595

MySQL Production Support597

Using MySQL Enterprise Monitor597

Monitoring599

Query Analyzer605

Further Information608

Conclusion609

17．Managing MySQL Replication with MySQL Utilities611

Common MySQL Replication Tasks612

Checking Status612

Stopping Replication615

Adding Slaves617

MySQL Utilities618

Getting Started618

Using the Utilities Without Workbench619

Using the Utilities via Workbench619

General Utilities621

Comparing Databases for Consistency: mysqldbcompare621

Copying Databases:mysqldbcopy624

Exporting Databases:mysqldbexport625

Importing Databases:mysqldbimport628

Discovering Differences:mysqldiff629

Showing Disk Usage:mysqldiskusage632

Checking Tables Indexes:mysqlindexcheck635

Searching Metadata:mysqlmetagrep636

Searching for Processes:mysqlprocgrep637

Cloning Servers:mysqlserverclone639

Showing Server Information:mysqlserverinfo641

Cloning Users:mysqluserclone642

Utilities Client:mysqluc643

Replication Utilities644

Setting Up Replication:mysqlreplicate644

Checking Replication Setup:mysqlrplcheck646

Showing Topologies:mysqlrplshow648

High Availability Utilities650

Concepts650

mysqlrpladmin651

mysqlfailover655

Creating Your Own Utilities663

Architecture of MySQL Utilities663

Custom Utility Example664

Conclusion673

A．Replication Tips and Tricks675

B．AGTID Implementation693

Index705