MySQL High Availability. Tools for Building Robust Data Centers. 2nd Edition (e-book) Oświęcim

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 LibraryHandle failing components through redundancyScale out to manage read-load increases, and use data sharding to handle large databases and write-load increasesStore and replicate data on individual nodes with MySQL ClusterMonitor database activity and performance, and major operating system parametersKeep track of masters and slaves, and deal with failures and restarts, corruption, and other incidentsExamine tools including MySQL Enterprise Monitor, MySQL Utilities, and GTIDs Spis treści: MySQL High Availability Foreword for the Second Edition Foreword for the First Edition Preface Who This Book Is For How This Book Is Organized Part I, High Availability and Scalability Part II, Monitoring and Managing Appendixes Conventions Used in This Book Using Code Examples Safari Books Online How to Contact Us Acknowledgments I. High Availability and Scalability 1. Introduction Whats This Replication Stuff, Anyway? So, Backups Are Not Needed Then? Whats With All the Monitoring? Is There Anything Else I Can Read? Conclusion 2. MySQL Replicant Library Basic Classes and Functions Supporting Different Operating Systems Servers Server Roles Conclusion 3. MySQL Replication Fundamentals Basic Steps in Replication Configuring the Master Configuring the Slave Connecting the Master and Slave A Brief Introduction to the Binary Log Whats Recorded in the Binary Log Watching Replication in Action The Binary Logs Structure and Content Adding Slaves Cloning the Master Cloning a Slave Scripting the Clone Operation Performing Common Tasks with Replication Reporting Handling reporting in Python Scheduling tasks on Unix Scheduling tasks on Windows Conclusion 4. The Binary Log Structure of the Binary Log Binlog Event Structure Event Checksums Logging Statements Logging Data Manipulation Language Statements Logging Data Definition Language Statements Logging Queries Current database Current time Context events Thread ID LOAD DATA INFILE Statements Binary Log Filters Triggers, Events, and Stored Routines Statements that define or destroy stored programs Statements that invoke triggers and stored routines Stored Procedures Stored Functions Events Special Constructions The LOAD_FILE function Nontransactional Changes and Error Handling Logging Transactions Transaction Cache How nontransactional statements are logged How to avoid replication problems with nontransactional statements Distributed Transaction Processing Using XA Binary Log Group Commit Row-Based Replication Enabling Row-based Replication Using Mixed Mode Binary Log Management The Binary Log and Crash Safety Binlog File Rotation Incidents Purging the Binlog File The mysqlbinlog Utility Basic Usage Reading remote files Reading raw binary logfiles Interpreting Events Query event post header and body Format description event post header and body Binary Log Options and Variables Options for Row-Based Replication Conclusion 5. Replication for High Availability Redundancy Planning Slave Failures Master Failures Relay Failures Disaster Recovery Procedures Hot Standby Handling a switchover Handling a switchover in Python Dual Masters Shared disks Replicated disks using DRBD Bidirectional replication Slave Promotion The traditional method for promoting a slave A revised method for promoting a slave Circular Replication Conclusion 6. MySQL Replication for Scale-Out Scaling Out Reads, Not Writes The Value of Asynchronous Replication Managing the Replication Topology Application-Level Load Balancing Example of an application-level load balancer MySQL native driver replication and load balancing plug-in Hierarchical Replication Setting Up a Relay Server Adding a Relay in Python Specialized Slaves Filtering Replication Events Master filters Slave filters Using Filtering to Partition Events to Slaves Managing Consistency of Data Consistency in a Nonhierarchical Deployment Consistency in a Hierarchical Deployment Conclusion 7. Data Sharding What Is Sharding? Why Should You Shard? Limitations of Sharding Cross-shard joins Using AUTO_INCREMENT Elements of a Sharding Solution High-Level Sharding Architecture Partitioning the Data Shard Allocation Single shard per server Multiple shards per server (virtual shards) Mapping the Sharding Key Sharding Scheme Static sharding schemes Dynamic sharding schemes Shard Mapping Functions Range mapping Creating the index table Adding new shards Fetching the shard Hash mapping and consistent hashing Creating the index table Adding new shards Fetching the shard Processing Queries and Dispatching Transactions Handling Transactions Dispatching Queries Shard Management Moving a Shard to a Different Node Splitting Shards Conclusion 8. Replication Deep Dive Replication Architecture Basics The Structure of the Relay Log The Replication Threads Starting and Stopping the Slave Threads Running Replication over the Internet Setting Up Secure Replication Using Built-in Support Setting Up Secure Replication Using Stunnel Finer-Grained Control Over Replication Information About Replication Status The state of the I/O and SQL threads The binary log and relay log positions Options for Handling Broken Connections How the Slave Processes Events Housekeeping in the I/O Thread SQL Thread Processing Context events Thread-specific events Filtering and skipping events Semisynchronous Replication Configuring Semisynchronous Replication Monitoring Semisynchronous Replication Global Transaction Identifiers Setting Up Replication Using GTIDs Failover Using GTIDs Slave Promotion Using GTIDs Replication of GTIDs Slave Safety and Recovery Syncing, Transactions, and Problems with Database Crashes I/O thread syncing SQL thread syncing Transactional Replication Setting up transactional replication Details of transactional replication Rules for Protecting Nontransactional Statements Multisource Replication Details of Row-Based Replication Table_map Events The Structure of Row Events Execution of Row Event Events and Triggers Filtering in Row-Based Replication Partial Row Replication Conclusion 9. MySQL Cluster What Is MySQL Cluster? Terminology and Components How Does MySQL Cluster Differ from MySQL? Typical Configuration Features of MySQL Cluster Local and Global Redundancy Log Handling Redundancy and Distributed Data Architecture of MySQL Cluster How Data Is Stored Partitioning Transaction Management Online Operations Example Configuration Getting Started Starting a MySQL Cluster Starting the management node Starting the management console Starting data nodes Starting the SQL nodes Testing the Cluster Shutting Down the Cluster Achieving High Availability System Recovery Node Recovery Replication Replication inside the cluster versus MySQL replication Replicating inside the cluster MySQL replication between clusters Architecture of MySQL Cluster (external) replication Single-channel and multichannel replication Achieving High Performance Considerations for High Performance High Performance Best Practices Conclusion II. Monitoring and Managing 10. Getting Started with Monitoring Ways of Monitoring Benefits of Monitoring System Components to Monitor Processor Memory Disk Network Subsystem Monitoring Solutions Linux and Unix Monitoring Process Activity The top command The iostat command The mpstat command The ps command Memory Usage The free command The pmap command Disk Usage The iostat command The sar command Disk usage analyzer Network Activity The netstat command The ifconfig command General System Statistics The uptime command The vmstat command Automated Monitoring with cron Mac OS X Monitoring System Profiler Console Activity Monitor Microsoft Windows Monitoring The Windows Experience The System Health Report The Event Viewer The Reliability Monitor The Task Manager The Performance Monitor Monitoring as Preventive Maintenance Conclusion 11. Monitoring MySQL What Is Performance? MySQL Server Monitoring How MySQL Communicates Performance Performance Monitoring SQL Commands The mysqladmin Utility MySQL Workbench MySQL server administration Management Configuration Security Data export/restore SQL development Third-Party Tools MySAR mytop InnoTop MONyog The MySQL Benchmark Suite Server Logs Performance Schema Concepts Getting Started Using Performance Schema to Diagnose Performance Problems MySQL Monitoring Taxonomy Database Performance Measuring Database Performance Using EXPLAIN Using ANALYZE TABLE Using OPTIMIZE TABLE Best Practices for Database Optimization Use indexes sparingly but effectively Use normalization, but dont overdo it Use the right storage engine for the task Use views for faster results via the query cache Use constraints Use EXPLAIN, ANALYZE, and OPTIMIZE Best Practices for Improving Performance Everything Is Slow Slow Queries Slow Applications Slow Replication Conclusion 12. Storage Engine Monitoring InnoDB Using the SHOW ENGINE Command Using InnoDB Monitors Monitoring Logfiles Monitoring the Buffer Pool Monitoring Tablespaces Using INFORMATION_SCHEMA Tables Using PERFORMANCE_SCHEMA Tables Other Parameters to Consider Troubleshooting Tips for InnoDB Errors Deadlocks Data dictionary problems Observe console messages I/O problems Corrupted databases MyISAM Optimizing Disk Storage Repairing Your Tables Using the MyISAM Utilities Storing a Table in Index Order Compressing Tables Defragmenting Tables Monitoring the Key Cache Preloading Key Caches Using Multiple Key Caches Other Parameters to Consider Conclusion 13. Replication Monitoring Getting Started Server Setup Inclusive and Exclusive Replication Replication Threads Monitoring the Master Monitoring Commands for the Master Master Status Variables Monitoring Slaves Monitoring Commands for the Slave Slave Status Variables Replication Monitoring with MySQL Workbench Other Items to Consider Networking Monitor and Manage Slave Lag Causes and Cures for Slave Lag Working with GTIDs Conclusion 14. Replication Troubleshooting What Can Go Wrong Problems on the Master Master Crashed and Memory Tables Are in Use Master Crashed and Binary Log Events Are Missing Query Runs Fine on the Master but Not on the Slave Table Corruption After a Crash Binary Log Is Corrupt on the Master Killing Long-Running Queries for Nontransactional Tables Unsafe Statements Problems on the Slave Slave Server Crashed and Replication Wont Start Slave Connection Times Out and Reconnects Frequently Query Results Are Different on the Slave than on the Master Slave Issues Errors when Attempting to Restart with SSL Memory Table Data Goes Missing Temporary Tables Are Missing After a Slave Crash Slave Is Slow and Is Not Synced with the Master Data Loss After a Slave Crash Table Corruption After a Crash Relay Log Is Corrupt on the Slave Multiple Errors During Slave Restart Consequences of a Failed Transaction on the Slave I/O Thread Problems SQL Thread Problems: Inconsistencies Different Errors on the Slave Advanced Replication Problems A Change Is Not Replicated Among the Topology Circular Replication Issues Multimaster Issues The HA_ERR_KEY_NOT_FOUND Error GTID Problems Tools for Troubleshooting Replication Best Practices Know Your Topology Check the Status of All of Your Servers Check Your Logs Check Your Configuration Conduct Orderly Shutdowns Conduct Orderly Restarts After a Failure Manually Execute Failed Queries Dont Mix Transactional and Nontransactional Tables Common Procedures Troubleshooting replication failures Pausing replication Reporting Replication Bugs Conclusion 15. Protecting Your Investment What Is Information Assurance? The Three Practices of Information Assurance Why Is Information Assurance Important? Information Integrity, Disaster Recovery, and the Role of Backups High Availability Versus Disaster Recovery Disaster Recovery Disaster recovery planning Disaster recovery workflow Tools for disaster recovery The Importance of Data Recovery Terminology Backup and Restore Why back up? Expectations for backups Expectations for the restore process Logical versus physical backup Forming an archival plan Backup Tools and OS-Level Solutions MySQL Enterprise Backup How MEB works Performing a full backup Performing an incremental backup Restoring data Backing up a downed server MySQL Utilities Database Export and Import The mysqldump Utility Physical File Copy Logical Volume Manager Snapshots Getting started with LVM LVM in a backup and restore XtraBackup Comparison of Backup Methods Backup and MySQL Replication Backup and Recovery with Replication PITR Restoring after an error is replicated Recovery example Recovery images Backup procedure PITR in Python Automating Backups Conclusion 16. MySQL Enterprise Monitor Getting Started with MySQL Enterprise Monitor Commercial Offerings Anatomy of MySQL Enterprise Monitor Installation Overview Installing the MEM Service Manager Installing the MEM Agent Fixing monitoring agent problems Configuring the dashboard MySQL Enterprise Monitor Components Dashboard Monitoring Agent Advisors Query Analyzer MySQL Production Support Using MySQL Enterprise Monitor Monitoring Critical issues details Consolidated server graphs Server details Replication details Advisors Query Analyzer Further Information Conclusion 17. Managing MySQL Replication with MySQL Utilities Common MySQL Replication Tasks Checking Status Stopping Replication Adding Slaves MySQL Utilities Getting Started Using the Utilities Without Workbench Using the Utilities via Workbench General Utilities Comparing Databases for Consistency: mysqldbcompare Use with replication Examples of mysqldbcompare Copying Databases: mysqldbcopy Use with replication Example of mysqldbcopy Exporting Databases: mysqldbexport Example of mysqldbexport Importing Databases: mysqldbimport Use with replication Examples of mysqldbimport Discovering Differences: mysqldiff Use with replication Example of mysqldiff Showing Disk Usage: mysqldiskusage Use with replication Example of mysqldiskusage Checking Tables Indexes: mysqlindexcheck Use with replication Example of mysqlindexcheck Searching Metadata: mysqlmetagrep Use with replication Example of mysqlmetagrep Searching for Processes: mysqlprocgrep Use with replication Example of mysqlprocgrep Cloning Servers: mysqlserverclone Use with replication Example of mysqlserverclone Showing Server Information: mysqlserverinfo Use with replication Example of mysqlserverinfo Cloning Users: mysqluserclone Use with replication Example of mysqluserclone Utilities Client: mysqluc Use with replication Example of mysqluc Replication Utilities Setting Up Replication: mysqlreplicate Example of mysqlreplicate Checking Replication Setup: mysqlrplcheck Example of mysqlrplcheck Showing Topologies: mysqlrplshow Example of mysqlrplshow High Availability Utilities Concepts Global Transaction Identifiers Candidate slave Slave election Failover Switchover mysqlrpladmin Examples of mysqlrpladmin mysqlfailover Failover modes The failover event Extension points Creating Your Own Utilities Architecture of MySQL Utilities Custom Utility Example Operations Sample code Putting it all together Suggestions Conclusion A. Replication Tips and Tricks Examining the Binary Log with Verbose Using Replication to Repopulate a Table Statement-Based Logging Row-Based Logging Using MySQL Proxy to Perform Multimaster Replication Using a Default Storage Engine MySQL Cluster Multisource Replication Multichannel Replication with Failover Using the Current Database to Filter More Columns on Slave Than Master Fewer Columns on Slave Than Master Replicate Selected Rows to Slave Replication Heartbeat (5.4.4 or Later) Ignoring Servers in Circular Replication (5.5 or Later) Time-Delayed Replication (5.6 or Later) Shell Commands for Common Tasks Multisource Replication in a Shell Script Stored Procedure to Change Master Implementing Multisource Replication in Pure SQL (5.6 or Later) B. A GTID Implementation Adding GTIDs to a Server Transaction Handling Using GTIDs Finding Positions of GTIDs Index Colophon Copyright