| Foreword for the Second Edition |
|
xv | |
| Foreword for the First Edition |
|
xix | |
| Preface |
|
xxi | |
| Part I. High Availability and Scalability |
|
|
|
|
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) |
|
|
|
9 | (2) |
|
2 MySQL Replicant Library |
|
|
11 | (12) |
|
Basic Classes and Functions |
|
|
15 | (1) |
|
Supporting Different Operating Systems |
|
|
16 | (1) |
|
|
|
17 | (2) |
|
|
|
19 | (2) |
|
|
|
21 | (2) |
|
3 MySQL Replication Fundamentals |
|
|
23 | (28) |
|
Basic Steps in Replication |
|
|
24 | (5) |
|
|
|
25 | (2) |
|
|
|
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) |
|
|
|
35 | (7) |
|
|
|
37 | (2) |
|
|
|
39 | (2) |
|
Scripting the Clone Operation |
|
|
41 | (1) |
|
Performing Common Tasks with Replication |
|
|
42 | (7) |
|
|
|
43 | (6) |
|
|
|
49 | (2) |
|
|
|
51 | (72) |
|
Structure of the Binary Log |
|
|
52 | (6) |
|
|
|
54 | (2) |
|
|
|
56 | (2) |
|
|
|
58 | (28) |
|
Logging Data Manipulation Language Statements |
|
|
58 | (1) |
|
Logging Data Definition Language Statements |
|
|
59 | (1) |
|
|
|
59 | (6) |
|
LOAD DATA INFILE Statements |
|
|
65 | (2) |
|
|
|
67 | (3) |
|
Triggers, Events, and Stored Routines |
|
|
70 | (5) |
|
|
|
75 | (3) |
|
|
|
78 | (3) |
|
|
|
81 | (1) |
|
|
|
82 | (1) |
|
Nontransactional Changes and Error Handling |
|
|
83 | (3) |
|
|
|
86 | (11) |
|
|
|
87 | (4) |
|
Distributed Transaction Processing Using XA |
|
|
91 | (3) |
|
|
|
94 | (3) |
|
|
|
97 | (3) |
|
Enabling Row-based Replication |
|
|
98 | (1) |
|
|
|
99 | (1) |
|
|
|
100 | (5) |
|
The Binary Log and Crash Safety |
|
|
100 | (1) |
|
|
|
101 | (2) |
|
|
|
103 | (1) |
|
|
|
104 | (1) |
|
|
|
105 | (13) |
|
|
|
106 | (7) |
|
|
|
113 | (5) |
|
Binary Log Options and Variables |
|
|
118 | (3) |
|
Options for Row-Based Replication |
|
|
120 | (1) |
|
|
|
121 | (2) |
|
5 Replication for High Availability |
|
|
123 | (30) |
|
|
|
124 | (2) |
|
|
|
126 | (2) |
|
|
|
127 | (1) |
|
|
|
127 | (1) |
|
|
|
127 | (1) |
|
|
|
127 | (1) |
|
|
|
128 | (23) |
|
|
|
130 | (5) |
|
|
|
135 | (10) |
|
|
|
145 | (4) |
|
|
|
149 | (2) |
|
|
|
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) |
|
|
|
170 | (3) |
|
Setting Up a Relay Server |
|
|
171 | (1) |
|
|
|
172 | (1) |
|
|
|
173 | (4) |
|
Filtering Replication Events |
|
|
174 | (2) |
|
Using Filtering to Partition Events to Slaves |
|
|
176 | (1) |
|
Managing Consistency of Data |
|
|
177 | (10) |
|
Consistency in a Nonhierarchical Deployment |
|
|
178 | (2) |
|
Consistency in a Hierarchical Deployment |
|
|
180 | (7) |
|
|
|
187 | (2) |
|
|
|
189 | (38) |
|
|
|
190 | (4) |
|
|
|
191 | (1) |
|
|
|
192 | (2) |
|
Elements of a Sharding Solution |
|
|
194 | (3) |
|
High-Level Sharding Architecture |
|
|
196 | (1) |
|
|
|
197 | (9) |
|
|
|
202 | (4) |
|
|
|
206 | (9) |
|
|
|
206 | (4) |
|
|
|
210 | (5) |
|
Processing Queries and Dispatching Transactions |
|
|
215 | (5) |
|
|
|
216 | (2) |
|
|
|
218 | (2) |
|
|
|
220 | (5) |
|
Moving a Shard to a Different Node |
|
|
220 | (5) |
|
|
|
225 | (1) |
|
|
|
225 | (2) |
|
|
|
227 | (64) |
|
Replication Architecture Basics |
|
|
228 | (7) |
|
The Structure of the Relay Log |
|
|
229 | (4) |
|
|
|
233 | (1) |
|
Starting and Stopping the Slave Threads |
|
|
234 | (1) |
|
Running Replication over the Internet |
|
|
235 | (4) |
|
Setting Up Secure Replication Using Built-in Support |
|
|
237 | (1) |
|
Setting Up Secure Replication Using Stunnel |
|
|
238 | (1) |
|
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) |
|
|
|
250 | (7) |
|
Semisynchronous Replication |
|
|
257 | (3) |
|
Configuring Semisynchronous Replication |
|
|
258 | (1) |
|
Monitoring Semisynchronous Replication |
|
|
259 | (1) |
|
Global Transaction Identifiers |
|
|
260 | (8) |
|
Setting Up Replication Using GTIDs |
|
|
261 | (2) |
|
|
|
263 | (1) |
|
Slave Promotion Using GTIDs |
|
|
264 | (2) |
|
|
|
266 | (2) |
|
Slave Safety and Recovery |
|
|
268 | (7) |
|
Syncing, Transactions, and Problems with Database Crashes |
|
|
268 | (2) |
|
Transactional Replication |
|
|
270 | (4) |
|
Rules for Protecting Nontransactional Statements |
|
|
274 | (1) |
|
|
|
275 | (3) |
|
Details of Row-Based Replication |
|
|
278 | (11) |
|
|
|
280 | (2) |
|
The Structure of Row Events |
|
|
282 | (1) |
|
|
|
283 | (1) |
|
|
|
284 | (2) |
|
Filtering in Row-Based Replication |
|
|
286 | (2) |
|
|
|
288 | (1) |
|
|
|
289 | (2) |
|
|
|
291 | (42) |
|
|
|
292 | (6) |
|
Terminology and Components |
|
|
292 | (1) |
|
How Does MySQL Cluster Differ from MySQL? |
|
|
293 | (1) |
|
|
|
293 | (1) |
|
Features of MySQL Cluster |
|
|
294 | (2) |
|
Local and Global Redundancy |
|
|
296 | (1) |
|
|
|
297 | (1) |
|
Redundancy and Distributed Data |
|
|
297 | (1) |
|
Architecture of MySQL Cluster |
|
|
298 | (8) |
|
|
|
300 | (3) |
|
|
|
303 | (1) |
|
|
|
304 | (1) |
|
|
|
304 | (2) |
|
|
|
306 | (8) |
|
|
|
306 | (2) |
|
|
|
308 | (5) |
|
|
|
313 | (1) |
|
Shutting Down the Cluster |
|
|
314 | (1) |
|
Achieving High Availability |
|
|
314 | (10) |
|
|
|
317 | (1) |
|
|
|
318 | (1) |
|
|
|
319 | (5) |
|
Achieving High Performance |
|
|
324 | (4) |
|
Considerations for High Performance |
|
|
325 | (1) |
|
High Performance Best Practices |
|
|
326 | (2) |
|
|
|
328 | (5) |
| Part II. Monitoring and Managing |
|
|
10 Getting Started with Monitoring |
|
|
333 | (46) |
|
|
|
334 | (1) |
|
|
|
335 | (1) |
|
System Components to Monitor |
|
|
335 | (5) |
|
|
|
336 | (1) |
|
|
|
337 | (1) |
|
|
|
338 | (1) |
|
|
|
339 | (1) |
|
|
|
340 | (1) |
|
Linux and Unix Monitoring |
|
|
341 | (15) |
|
|
|
342 | (5) |
|
|
|
347 | (3) |
|
|
|
350 | (3) |
|
|
|
353 | (2) |
|
General System Statistics |
|
|
355 | (1) |
|
Automated Monitoring with cron |
|
|
356 | (1) |
|
|
|
356 | (9) |
|
|
|
357 | (2) |
|
|
|
359 | (2) |
|
|
|
361 | (4) |
|
Microsoft Windows Monitoring |
|
|
365 | (12) |
|
|
|
366 | (1) |
|
|
|
367 | (2) |
|
|
|
369 | (3) |
|
|
|
372 | (2) |
|
|
|
374 | (1) |
|
|
|
375 | (2) |
|
Monitoring as Preventive Maintenance |
|
|
377 | (1) |
|
|
|
377 | (2) |
|
|
|
379 | (68) |
|
|
|
380 | (1) |
|
|
|
381 | (26) |
|
How MySQL Communicates Performance |
|
|
381 | (1) |
|
|
|
382 | (1) |
|
|
|
383 | (6) |
|
|
|
389 | (2) |
|
|
|
391 | (11) |
|
|
|
402 | (3) |
|
The MySQL Benchmark Suite |
|
|
405 | (2) |
|
|
|
407 | (2) |
|
|
|
409 | (12) |
|
|
|
410 | (2) |
|
|
|
412 | (8) |
|
Using Performance Schema to Diagnose Performance Problems |
|
|
420 | (1) |
|
MySQL Monitoring Taxonomy |
|
|
421 | (2) |
|
|
|
423 | (21) |
|
Measuring Database Performance |
|
|
423 | (12) |
|
Best Practices for Database Optimization |
|
|
435 | (9) |
|
Best Practices for Improving Performance |
|
|
444 | (2) |
|
|
|
444 | (1) |
|
|
|
444 | (1) |
|
|
|
445 | (1) |
|
|
|
445 | (1) |
|
|
|
446 | (1) |
|
12 Storage Engine Monitoring |
|
|
447 | (30) |
|
|
|
448 | (19) |
|
Using the SHOW ENGINE Command |
|
|
450 | (3) |
|
|
|
453 | (4) |
|
|
|
457 | (1) |
|
Monitoring the Buffer Pool |
|
|
458 | (2) |
|
|
|
460 | (1) |
|
Using INFORMATION_SCHEMA Tables |
|
|
461 | (1) |
|
Using PERFORMANCE_SCHEMA Tables |
|
|
462 | (1) |
|
Other Parameters to Consider |
|
|
463 | (1) |
|
Troubleshooting Tips for InnoDB |
|
|
464 | (3) |
|
|
|
467 | (8) |
|
|
|
467 | (1) |
|
|
|
468 | (1) |
|
Using the MyISAM Utilities |
|
|
468 | (2) |
|
Storing a Table in Index Order |
|
|
470 | (1) |
|
|
|
471 | (1) |
|
|
|
471 | (1) |
|
|
|
471 | (1) |
|
|
|
472 | (1) |
|
Using Multiple Key Caches |
|
|
473 | (1) |
|
Other Parameters to Consider |
|
|
474 | (1) |
|
|
|
475 | (2) |
|
13 Replication Monitoring |
|
|
477 | (24) |
|
|
|
477 | (1) |
|
|
|
478 | (1) |
|
Inclusive and Exclusive Replication |
|
|
478 | (3) |
|
|
|
481 | (2) |
|
|
|
483 | (4) |
|
Monitoring Commands for the Master |
|
|
483 | (4) |
|
|
|
487 | (1) |
|
|
|
487 | (6) |
|
Monitoring Commands for the Slave |
|
|
487 | (5) |
|
|
|
492 | (1) |
|
Replication Monitoring with MySQL Workbench |
|
|
493 | (2) |
|
|
|
495 | (4) |
|
|
|
495 | (1) |
|
Monitor and Manage Slave Lag |
|
|
496 | (1) |
|
Causes and Cures for Slave Lag |
|
|
497 | (1) |
|
|
|
498 | (1) |
|
|
|
499 | (2) |
|
14 Replication Troubleshooting |
|
|
501 | (30) |
|
|
|
502 | (1) |
|
|
|
503 | (6) |
|
Master Crashed and Memory Tables Are in Use |
|
|
503 | (1) |
|
Master Crashed and Binary Log Events Are Missing |
|
|
503 | (2) |
|
Query Runs Fine on the Master but Not on the Slave |
|
|
505 | (1) |
|
Table Corruption After a Crash |
|
|
505 | (1) |
|
Binary Log Is Corrupt on the Master |
|
|
506 | (1) |
|
Killing Long-Running Queries for Nontransactional Tables |
|
|
507 | (1) |
|
|
|
507 | (2) |
|
|
|
509 | (8) |
|
Slave Server Crashed and Replication Won't Start |
|
|
510 | (1) |
|
Slave Connection Times Out and Reconnects Frequently |
|
|
510 | (1) |
|
Query Results Are Different on the Slave than on the Master |
|
|
511 | (1) |
|
Slave Issues Errors when Attempting to Restart with SSL |
|
|
512 | (1) |
|
Memory Table Data Goes Missing |
|
|
513 | (1) |
|
Temporary Tables Are Missing After a Slave Crash |
|
|
513 | (1) |
|
Slave Is Slow and Is Not Synced with the Master |
|
|
513 | (1) |
|
Data Loss After a Slave Crash |
|
|
514 | (1) |
|
Table Corruption After a Crash |
|
|
514 | (1) |
|
Relay Log Is Corrupt on the Slave |
|
|
515 | (1) |
|
Multiple Errors During Slave Restart |
|
|
515 | (1) |
|
Consequences of a Failed Transaction on the Slave |
|
|
515 | (1) |
|
|
|
515 | (1) |
|
SQL Thread Problems: Inconsistencies |
|
|
516 | (1) |
|
Different Errors on the Slave |
|
|
517 | (1) |
|
Advanced Replication Problems |
|
|
517 | (3) |
|
A Change Is Not Replicated Among the Topology |
|
|
517 | (1) |
|
Circular Replication Issues |
|
|
518 | (1) |
|
|
|
518 | (1) |
|
The HA_ERR_KEY_NOT_FOUND Error |
|
|
519 | (1) |
|
|
|
519 | (1) |
|
Tools for Troubleshooting Replication |
|
|
520 | (1) |
|
|
|
521 | (7) |
|
|
|
521 | (2) |
|
Check the Status of All of Your Servers |
|
|
523 | (1) |
|
|
|
523 | (1) |
|
|
|
524 | (1) |
|
Conduct Orderly Shutdowns |
|
|
525 | (1) |
|
Conduct Orderly Restarts After a Failure |
|
|
525 | (1) |
|
Manually Execute Failed Queries |
|
|
526 | (1) |
|
Don't Mix Transactional and Nontransactional Tables |
|
|
526 | (1) |
|
|
|
526 | (2) |
|
Reporting Replication Bugs |
|
|
528 | (1) |
|
|
|
529 | (2) |
|
15 Protecting Your Investment |
|
|
531 | (52) |
|
What Is Information Assurance? |
|
|
532 | (1) |
|
The Three Practices of Information Assurance |
|
|
532 | (1) |
|
Why Is Information Assurance Important? |
|
|
533 | (1) |
|
Information Integrity, Disaster Recovery, and the Role of Backups |
|
|
533 | (14) |
|
High Availability Versus Disaster Recovery |
|
|
534 | (1) |
|
|
|
535 | (6) |
|
The Importance of Data Recovery |
|
|
541 | (1) |
|
|
|
542 | (5) |
|
Backup Tools and OS-Level Solutions |
|
|
547 | (23) |
|
|
|
548 | (11) |
|
MySQL Utilities Database Export and Import |
|
|
559 | (1) |
|
|
|
560 | (2) |
|
|
|
562 | (2) |
|
Logical Volume Manager Snapshots |
|
|
564 | (5) |
|
|
|
569 | (1) |
|
Comparison of Backup Methods |
|
|
569 | (1) |
|
Backup and MySQL Replication |
|
|
570 | (9) |
|
Backup and Recovery with Replication |
|
|
571 | (1) |
|
|
|
571 | (8) |
|
|
|
579 | (2) |
|
|
|
581 | (2) |
|
16 MySQL Enterprise Monitor |
|
|
583 | (28) |
|
Getting Started with MySQL Enterprise Monitor |
|
|
584 | (6) |
|
|
|
585 | (1) |
|
Anatomy of MySQL Enterprise Monitor |
|
|
585 | (1) |
|
|
|
586 | (4) |
|
MySQL Enterprise Monitor Components |
|
|
590 | (7) |
|
|
|
591 | (3) |
|
|
|
594 | (1) |
|
|
|
594 | (1) |
|
|
|
595 | (2) |
|
|
|
597 | (1) |
|
Using MySQL Enterprise Monitor |
|
|
597 | (12) |
|
|
|
599 | (6) |
|
|
|
605 | (3) |
|
|
|
608 | (1) |
|
|
|
609 | (2) |
|
17 Managing MySQL Replication with MySQL Utilities |
|
|
611 | (64) |
|
Common MySQL Replication Tasks |
|
|
612 | (6) |
|
|
|
612 | (3) |
|
|
|
615 | (2) |
|
|
|
617 | (1) |
|
|
|
618 | (3) |
|
|
|
618 | (1) |
|
Using the Utilities Without Workbench |
|
|
619 | (1) |
|
Using the Utilities via Workbench |
|
|
619 | (2) |
|
|
|
621 | (23) |
|
Comparing Databases for Consistency: mysqldbcompare |
|
|
621 | (3) |
|
Copying Databases: mysqldbcopy |
|
|
624 | (1) |
|
Exporting Databases: mysqldbexport |
|
|
625 | (3) |
|
Importing Databases: mysqldbimport |
|
|
628 | (1) |
|
Discovering Differences: mysqldiff |
|
|
629 | (3) |
|
Showing Disk Usage: mysqldiskusage |
|
|
632 | (3) |
|
Checking Tables Indexes: mysqlindexcheck |
|
|
635 | (1) |
|
Searching Metadata: mysqlmetagrep |
|
|
636 | (1) |
|
Searching for Processes: mysqlprocgrep |
|
|
637 | (2) |
|
Cloning Servers: mysqlserverclone |
|
|
639 | (2) |
|
Showing Server Information: mysqlserverinfo |
|
|
641 | (1) |
|
Cloning Users: mysqluserclone |
|
|
642 | (1) |
|
Utilities Client: mysqluc |
|
|
643 | (1) |
|
|
|
644 | (6) |
|
Setting Up Replication: mysqlreplicate |
|
|
644 | (2) |
|
Checking Replication Setup: mysqlrplcheck |
|
|
646 | (2) |
|
Showing Topologies: mysqlrplshow |
|
|
648 | (2) |
|
High Availability Utilities |
|
|
650 | (13) |
|
|
|
650 | (1) |
|
|
|
651 | (4) |
|
|
|
655 | (8) |
|
Creating Your Own Utilities |
|
|
663 | (10) |
|
Architecture of MySQL Utilities |
|
|
663 | (1) |
|
|
|
664 | (9) |
|
|
|
673 | (2) |
| A Replication Tips and Tricks |
|
675 | (18) |
| B A GTID Implementation |
|
693 | (12) |
| Index |
|
705 | |
Lisainfo e-raamatute kohta