High Availability MySQL Cookbook

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.

High Availability MySQL Cookbook

Cookbook
Alex Davies

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.
$26.99
$44.99
RRP $26.99
RRP $44.99
eBook
Print + eBook
$12.99 p/month

Want this title & more? Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.
+ Collection
Free Sample

Book Details

ISBN 139781847199942
Paperback264 pages

About This Book

  • 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

Who This Book Is For

This book is targeted at system administrators or database administrators who have basic familiarity with Linux, the shell, and MySQL. The typical user will be able to get MySQL installed and working, but needs practical guidance to make it highly available.

Table of Contents

Chapter 1: High Availability with MySQL Cluster
Introduction
Designing a MySQL Cluster
Creating an initial cluster configuration file—config.ini
Installing a management node
Starting a management node
Installing and starting storage nodes
Installing and starting SQL nodes
Creating a MySQL Cluster table
Restarting a MySQL Cluster without downtime
Recovering from a cluster shutdown
Chapter 2: MySQL Cluster Backup and Recovery
Introduction
Importing SQL files to a MySQL server and converting them to MySQL Cluster
Taking an online backup of a MySQL Cluster
Restoring from a MySQL Cluster online backup
Restricting write access to a MySQL Cluster with single-user mode
Taking an offline backup with MySQL Cluster
Chapter 3: MySQL Cluster Management
Introduction
Configuring multiple management nodes
Obtaining usage information
Adding storage nodes online
Replicating between MySQL Clusters
Replication between clusters with a backup channel
User-defined partitioning
Disk-based tables
Calculating DataMemory and IndexMemory
Chapter 4: MySQL Cluster Troubleshooting
Introduction
Single storage node failure
Multiple storage node failures
Storage node partitioning and arbitration
Debugging MySQL Clusters
Seeking help
NIC teaming with MySQL Cluster
Chapter 5: High Availability with MySQL Replication
Introduction
Designing a replication setup
Configuring a replication master
Configuring a replication slave without syncing data
Configuring a replication slave, migrating data with a simple SQL dump
Using LVM to reduce downtime on a master when bringing a slave online
Replication safety tricks
Multi Master Replication Manager (MMM): initial installation
Multi Master Replication Manager (MMM): installing the MySQL nodes
Multi Master Replication Manager (MMM): installing monitoring node
Managing and using Multi Master Replication Manager (MMM)
Chapter 6: High Availability with MySQL and Shared Storage
Introduction
Preparing a Linux server for shared storage
Configuring two servers for shared storage MySQL
Configuring MySQL on shared storage with Conga
Fencing for high availability
Configuring MySQL with GFS
Chapter 7: High Availability with Block Level Replication
Introduction
Installing DRBD on two Linux servers
Manually moving services within a DRBD cluster
Using heartbeat for automatic failover
Chapter 8: Performance Tuning
Introduction
Tuning the Linux kernel IO
Tuning MySQL Cluster storage nodes
Tuning MySQL Cluster SQL nodes
Tuning queries within a MySQL Cluster
Tuning GFS on shared storage
MySQL Replication tuning

What You Will Learn

  • Configure a MySQL Cluster for scaling out MySQL
  • Learn the technique of taking online backups and recovery
  • Achieve high availability with MySQL Replication and various tools such as the open source Multi-Master Replication Manager (MMM) and Flipper projects
  • Configure a MySQL replication design to replicate data from one MySQL database server to one or more other MySQL database servers
  • Master the safety tricks of replication to prevent replication failure
  • Configure a MySQL service to work using a shared storage device to achieve high availability, with and without a clustered filesystem
  • Learn to use the open source Global File System (GFS) to have the same file system on more than one node
  • Achieve high availability using block level replication with Distributed Replicated Block Device (DRBD)
  • Improve your system's performance with the technique of performance tuning

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.

Authors

Table of Contents

Chapter 1: High Availability with MySQL Cluster
Introduction
Designing a MySQL Cluster
Creating an initial cluster configuration file—config.ini
Installing a management node
Starting a management node
Installing and starting storage nodes
Installing and starting SQL nodes
Creating a MySQL Cluster table
Restarting a MySQL Cluster without downtime
Recovering from a cluster shutdown
Chapter 2: MySQL Cluster Backup and Recovery
Introduction
Importing SQL files to a MySQL server and converting them to MySQL Cluster
Taking an online backup of a MySQL Cluster
Restoring from a MySQL Cluster online backup
Restricting write access to a MySQL Cluster with single-user mode
Taking an offline backup with MySQL Cluster
Chapter 3: MySQL Cluster Management
Introduction
Configuring multiple management nodes
Obtaining usage information
Adding storage nodes online
Replicating between MySQL Clusters
Replication between clusters with a backup channel
User-defined partitioning
Disk-based tables
Calculating DataMemory and IndexMemory
Chapter 4: MySQL Cluster Troubleshooting
Introduction
Single storage node failure
Multiple storage node failures
Storage node partitioning and arbitration
Debugging MySQL Clusters
Seeking help
NIC teaming with MySQL Cluster
Chapter 5: High Availability with MySQL Replication
Introduction
Designing a replication setup
Configuring a replication master
Configuring a replication slave without syncing data
Configuring a replication slave, migrating data with a simple SQL dump
Using LVM to reduce downtime on a master when bringing a slave online
Replication safety tricks
Multi Master Replication Manager (MMM): initial installation
Multi Master Replication Manager (MMM): installing the MySQL nodes
Multi Master Replication Manager (MMM): installing monitoring node
Managing and using Multi Master Replication Manager (MMM)
Chapter 6: High Availability with MySQL and Shared Storage
Introduction
Preparing a Linux server for shared storage
Configuring two servers for shared storage MySQL
Configuring MySQL on shared storage with Conga
Fencing for high availability
Configuring MySQL with GFS
Chapter 7: High Availability with Block Level Replication
Introduction
Installing DRBD on two Linux servers
Manually moving services within a DRBD cluster
Using heartbeat for automatic failover
Chapter 8: Performance Tuning
Introduction
Tuning the Linux kernel IO
Tuning MySQL Cluster storage nodes
Tuning MySQL Cluster SQL nodes
Tuning queries within a MySQL Cluster
Tuning GFS on shared storage
MySQL Replication tuning

Book Details

ISBN 139781847199942
Paperback264 pages
Read More