High Availability MySQL Cookbook电子书

High Availability MySQL Cookbook

Table of Contents

High Availability MySQL Cookbook

Credits

About the Author

About the Reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. High Availability with MySQL Cluster

Introduction

Designing a MySQL Cluster

How to do it...

How it works…

There's more…

Creating an initial cluster configuration file—config.ini

How to do it…

There's more…

Installing a management node

How to do it…

Starting a management node

How to do it…

There's more…

Installing and starting storage nodes

Getting ready

How to do it…

There's more…

Installing and starting SQL nodes

How to do it…

Creating a MySQL Cluster table

How to do it…

Restarting a MySQL Cluster without downtime

Getting started

How to do it…

Recovering from a cluster shutdown

How to do it…

How it works…

There's more…

2. MySQL Cluster Backup and Recovery

Introduction

Importing SQL files to a MySQL server and converting them to MySQL Cluster

How to do it…

There's more…

Taking an online backup of a MySQL Cluster

Getting ready

How to do it…

There's more…

Preventing commands hanging

Aborting backups in progress

Defining an exact time for a consistent backup

Restoring from a MySQL Cluster online backup

How to do it…

Restricting write access to a MySQL Cluster with single-user mode

How to do it…

There's more…

Taking an offline backup with MySQL Cluster

How to do it…

There's more…

Importing a cluster SQL file to an unclustered MySQL Server

Running mysqldump for a consistent backup

3. MySQL Cluster Management

Introduction

Configuring multiple management nodes

Getting ready

How to do it...

There's more...

See also

Obtaining usage information

Getting ready

How to do it...

How it works...

There's more...

See also

Adding storage nodes online

Getting ready

How to do it...

How it works...

There's more...

Replicating between MySQL Clusters

Getting ready

How to do it...

How it works...

There's more...

Replication between clusters with a backup channel

How to do it…

There's more…

See also

User-defined partitioning

Getting ready

How to do it...

There's more...

Disk-based tables

Getting ready

How to do it...

How it works...

There's more...

Calculating DataMemory and IndexMemory

Getting ready

How to do it...

There's more...

See also

4. MySQL Cluster Troubleshooting

Introduction

Single storage node failure

Getting ready

How to do it…

How it works…

There's more…

Multiple storage node failures

Getting ready

How to do it…

Storage node partitioning and arbitration

Getting ready

How to do it…

How it works…

Debugging MySQL Clusters

Getting ready

How to do it...

There's more…

Firewalls

Host resolution

Memory

Seeking help

Getting ready

How to do it...

There's more...

NIC teaming with MySQL Cluster

Getting ready

How to do it…

There's more…

5. High Availability with MySQL Replication

Introduction

Designing a replication setup

Getting ready

How to do it...

Master and slave

Multi-master (active / active)

Active / passive master

How it works...

Mixed-mode replication

Statement-based replication

Row-based replication

Configuring a replication master

Getting ready

How to do it...

How it works...

There's more...

Disk space

Only logging some databases

Limiting individual binary log size

Rotating binary logs

Performance

Binary log caching

Configuring a replication slave without syncing data

How to do it...

Configuring a replication slave, migrating data with a simple SQL dump

How to do it...

How it works...

Using LVM to reduce downtime on a master when bringing a slave online

Getting ready

How to do it...

Replication safety tricks

Getting ready

How to do it...

There's more...

Multi Master Replication Manager (MMM): initial installation

How to do it...

Multi Master Replication Manager (MMM): installing the MySQL nodes

How to do it...

Multi Master Replication Manager (MMM): installing monitoring node

How to do it...

Managing and using Multi Master Replication Manager (MMM)

How to do it…

How it works...

There's more...

Failure detection

6. High Availability with MySQL and Shared Storage

Introduction

Preparing a Linux server for shared storage

How to do it…

See also

Configuring two servers for shared storage MySQL

How to do it…

There's more…

Configuring MySQL on shared storage with Conga

How to do it…

How it works…

There's more…

Obtaining the cluster status

Migration of MySQL from node to node

Fencing for high availability

How to do it…

There's more…

See also

Configuring MySQL with GFS

How to do it…

There's more…

Cron job woes

Preventing unnecessary small writes

Mounting filesystem on both nodes

7. High Availability with Block Level Replication

Introduction

Installing DRBD on two Linux servers

Getting ready

How to do it...

How it works...

There's more...

Manually moving services within a DRBD cluster

Getting ready

How to do it...

How it works...

Using heartbeat for automatic failover

Getting ready

How to do it...

How it works...

8. Performance Tuning

Introduction

Make one modification at a time

Aim your efforts towards the biggest "bang for buck"

Be scientific in your approach

Tuning the Linux kernel IO

Getting ready

How to do it...

How it works...

There's more...

Tuning MySQL Cluster storage nodes

How to do it...

How it works...

Tuning MySQL Cluster SQL nodes

How to do it...

How it works...

There's more...

Tuning queries within a MySQL Cluster

Getting ready

How to do it...

Tuning GFS on shared storage

Getting ready

How to do it...

How it works...

MySQL Replication tuning

How to do it...

There's more...

A. Base Installation

B. LVM and MySQL

How to do it...

How it works…

C. Highly Available Architectures

Single-site architectures

MySQL master / slave replication

MySQL master / master replication

Shared storage

Block level replication

MySQL Cluster

Multi-site architectures

Summary of options

Index