You are previewing High Availability MySQL Cookbook.
O'Reilly logo
High Availability MySQL Cookbook

Book Description

There’s more than one way to achieve high availability for MySQL and this Cookbook covers a range of techniques and tools in over 60 practical recipes. The only book of its kind, you’ll be learning the natural, engaging way.

  • Analyze and learn different high availability options, including clustering and replication solutions within MySQL

  • Improve uptime of your MySQL databases with simple recipes showing powerful high availability techniques for MySQL

  • Tune your MySQL database for optimal performance.

  • The only complete, practical, book of MySQL high availability techniques and tools on the market

  • Part of Packt's Cookbook series: Each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

  • In Detail

    High Availability is something that all web sites hope to achieve, especially those that are linked to big companies.

    MySQL, an open source relational database management system (RDBMS), can be made highly available to protect from corruption, hardware failure, software crashes, and user error. Running a MySQL setup is quite simple. Things start getting complex when you start thinking about the best way to provide redundancy. There are a large number of techniques available to add 'redundancy' and 'high availability' to MySQL, but most are both poorly understood and documented.

    This book will provide you with recipes showing how to design, implement, and manage a MySQL Cluster and achieve high availability using MySQL replication, block level replication, shared storage, and the open source Global File System (GFS).

    This book covers all the major techniques available for increasing availability of your MySQL databases. It demonstrates how to design, implement, troubleshoot and manage a highly available MySQL setup using any one of several techniques, which are shown in different recipes. It is based on MySQL Cluster 7.0, MySQL (for non clustered recipes) 5.0.77, and CentOS / RedHat Enterprise Linux 5.3.

    The book starts by introducing MySQL Cluster as a technology and explaining how to set up a simple cluster. It will help you to master the options available for backing up and restoring a file in the MySQL Cluster. By following the practical examples in this book, you will learn how to manage the MySQL Cluster. Further, we will discuss some troubleshooting aspects of the MySQL Cluster.

    We also have a look at achieving high availability for MySQL databases with the techniques of MySQL Replication, block level replication, shared storage (a SAN or NAS), and DRBD.

    Finally, you will learn the principles of Performance tuning and tune MySQL database for optimal performance.

    A cookbook containing recipes for all the common high availability techniques for database administrators to tackle real-world challenges to achieve high availability using MySQL

    Table of Contents

    1. High Availability MySQL Cookbook
      1. High Availability MySQL Cookbook
      2. Credits
      3. About the Author
      4. About the Reviewers
      5. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Errata
          2. Piracy
          3. Questions
      6. 1. High Availability with MySQL Cluster
        1. Introduction
        2. Designing a MySQL Cluster
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Creating an initial cluster configuration file—config.ini
          1. How to do it...
          2. There's more...
        4. Installing a management node
          1. How to do it...
        5. Starting a management node
          1. How to do it...
          2. There's more...
        6. Installing and starting storage nodes
          1. Getting ready
          2. How to do it...
          3. There's more...
        7. Installing and starting SQL nodes
          1. How to do it...
        8. Creating a MySQL Cluster table
          1. How to do it...
        9. Restarting a MySQL Cluster without downtime
          1. Getting started
          2. How to do it...
        10. Recovering from a cluster shutdown
          1. How to do it...
          2. How it works...
          3. There's more...
      7. 2. MySQL Cluster Backup and Recovery
        1. Introduction
        2. Importing SQL files to a MySQL server and converting them to MySQL Cluster
          1. How to do it...
          2. There's more...
        3. Taking an online backup of a MySQL Cluster
          1. Getting ready
          2. How to do it...
          3. There's more...
            1. Preventing commands hanging
            2. Aborting backups in progress
            3. Defining an exact time for a consistent backup
        4. Restoring from a MySQL Cluster online backup
          1. How to do it...
        5. Restricting write access to a MySQL Cluster with single-user mode
          1. How to do it...
          2. There's more...
        6. Taking an offline backup with MySQL Cluster
          1. How to do it...
          2. There's more...
            1. Importing a cluster SQL file to an unclustered MySQL Server
            2. Running mysqldump for a consistent backup
      8. 3. MySQL Cluster Management
        1. Introduction
        2. Configuring multiple management nodes
          1. Getting ready
          2. How to do it...
          3. There's more...
          4. See also
        3. Obtaining usage information
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Adding storage nodes online
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        5. Replicating between MySQL Clusters
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Replication between clusters with a backup channel
          1. How to do it...
          2. There's more...
          3. See also
        7. User-defined partitioning
          1. Getting ready
          2. How to do it...
          3. There's more...
        8. Disk-based tables
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        9. Calculating DataMemory and IndexMemory
          1. Getting ready
          2. How to do it...
          3. There's more...
          4. See also
      9. 4. MySQL Cluster Troubleshooting
        1. Introduction
        2. Single storage node failure
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Multiple storage node failures
          1. Getting ready
          2. How to do it...
        4. Storage node partitioning and arbitration
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Debugging MySQL Clusters
          1. Getting ready
          2. How to do it...
          3. There's more...
            1. Firewalls
            2. Host resolution
            3. Memory
        6. Seeking help
          1. Getting ready
          2. How to do it...
          3. There's more...
        7. NIC teaming with MySQL Cluster
          1. Getting ready
          2. How to do it...
          3. There's more...
      10. 5. High Availability with MySQL Replication
        1. Introduction
        2. Designing a replication setup
          1. Getting ready
          2. How to do it...
            1. Master and slave
            2. Multi-master (active / active)
            3. Active / passive master
          3. How it works...
            1. Mixed-mode replication
            2. Statement-based replication
            3. Row-based replication
        3. Configuring a replication master
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Disk space
              1. Only logging some databases
              2. Limiting individual binary log size
              3. Rotating binary logs
            2. Performance
              1. Binary log caching
        4. Configuring a replication slave without syncing data
          1. How to do it...
        5. Configuring a replication slave, migrating data with a simple SQL dump
          1. How to do it...
          2. How it works...
        6. Using LVM to reduce downtime on a master when bringing a slave online
          1. Getting ready
          2. How to do it...
        7. Replication safety tricks
          1. Getting ready
          2. How to do it...
          3. There's more...
        8. Multi Master Replication Manager (MMM): initial installation
          1. How to do it...
        9. Multi Master Replication Manager (MMM): installing the MySQL nodes
          1. How to do it...
        10. Multi Master Replication Manager (MMM): installing monitoring node
          1. How to do it...
        11. Managing and using Multi Master Replication Manager (MMM)
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Failure detection
      11. 6. High Availability with MySQL and Shared Storage
        1. Introduction
        2. Preparing a Linux server for shared storage
          1. How to do it...
          2. See also
        3. Configuring two servers for shared storage MySQL
          1. How to do it...
          2. There's more...
        4. Configuring MySQL on shared storage with Conga
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Obtaining the cluster status
            2. Migration of MySQL from node to node
        5. Fencing for high availability
          1. How to do it...
          2. There's more...
          3. See also
        6. Configuring MySQL with GFS
          1. How to do it...
          2. There's more...
            1. Cron job woes
            2. Preventing unnecessary small writes
            3. Mounting filesystem on both nodes
      12. 7. High Availability with Block Level Replication
        1. Introduction
        2. Installing DRBD on two Linux servers
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Manually moving services within a DRBD cluster
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Using heartbeat for automatic failover
          1. Getting ready
          2. How to do it...
          3. How it works...
      13. 8. Performance Tuning
        1. Introduction
          1. Make one modification at a time
          2. Aim your efforts towards the biggest "bang for buck"
          3. Be scientific in your approach
        2. Tuning the Linux kernel IO
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Tuning MySQL Cluster storage nodes
          1. How to do it...
          2. How it works...
        4. Tuning MySQL Cluster SQL nodes
          1. How to do it...
            1. How it works...
            2. There's more...
        5. Tuning queries within a MySQL Cluster
          1. Getting ready
          2. How to do it...
        6. Tuning GFS on shared storage
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. MySQL Replication tuning
          1. How to do it...
          2. There's more...
      14. A. Base Installation
      15. B. LVM and MySQL
      16. C. Highly Available Architectures
        1. Single-site architectures
            1. MySQL master / slave replication
            2. MySQL master / master replication
            3. Shared storage
            4. Block level replication
            5. MySQL Cluster
        2. Multi-site architectures
        3. Summary of options