Table of Contents

Introduction

Introduction to Database Clustering

Why Cluster a Database?

Types of Clustering

Replication Versus Clustering

Hardware Considerations with MySQL Cluster

Networking Considerations with MySQL Cluster

Why Use a MySQL Cluster?

High Availability

Scalability

Performance

Other Advanced Features

Cluster Terminology

How a MySQL Cluster Works

Different Parts of a Cluster in MySQL Cluster

The MySQL Cluster Storage Engine

MyISAM Versus NDB

InnoDB Versus NDB

Limitations of NDB

Data Partitioning

Benefits of Partitioning

Synchronous Replication

Node Groups

A Practical Example of Partitioning and Node Groups

Network Partitioning and Arbitration

A Brief History of MySQL Cluster

What’s New in MySQL Cluster 5.0

What’s New in MySQL Cluster 5.1

Designing a Cluster

Networking

Determining the Number of Replicas

Determining the Amount of RAM

Determining the Amount of Disk Space

Choosing an Operating System

What This Book Covers

Software Versions

More Sources of Information

1  Installation

Before You Begin with MySQL Cluster

Obtaining and Installing MySQL Cluster

Designing a Cluster

Installing MySQL-Max

Installing MySQL Cluster from a Binary Tarball

Installing from a Binary RPM

Compiling from Source

Retrieving the Latest Snapshot from BitKeeper

Upgrading a BitKeeper Installation

Ensuring That MySQL Cluster Works

Configuring and Starting a Cluster

An Example of a config.ini File

Starting a Management Node

Starting Storage Nodes

Connecting SQL Nodes

An Example of a Working Cluster

Creating a Table

Auto-discovery of Databases

Startup Phases

Restarting a Cluster

Restarting on Failure

Testing Your Cluster

Upgrading MySQL Cluster

Upgrading Through Major Versions

Other Methods of Starting a Cluster

Obtaining, Installing, and Configuring MySQL Cluster on Other Platforms

RAM Usage

Memory Usage

Using MySQL Cluster on 64-Bit Operating Systems

Calculating the DataMemory and IndexMemory Parameters

Reducing Memory Usage

Adding Tables

Using ALTER TABLE

Using CREATE TABLE

Common Errors While Importing Tables

ERROR 1114 (HY000) at line 227: The table ‘table_log’ is full

ERROR 1005 (HY000) at line x: Can’t create table ‘./ dbname/tablename.frm’ (errno: 4242)

ERROR 1005 (HY000) at line x: Can’t create table ‘./ dbname/tablename.frm’ (errno: 4335)

ERROR 1015 (HY000): Can’t lock file (errno: 4006)

ERROR 1050 (42S01): Table ‘ctest’ already exists

“Out of operation records in transaction coordinator”

2  Configuration

The Structure of config.ini

All Nodes

Management Nodes

Parameters to Define for All Management Nodes (NDB_MGMD_DEFAULT)

Parameters to Define for Each Management Node (NDB_MGMD)

An Example of a Management Section of a config.ini File

Storage Nodes

Parameters to Define for All Storage Nodes (NDBD_DEFAULT)

Transaction Parameters

Transaction Temporary Storage

Scans and Buffering

Logging and Checkpointing

Metadata Objects

Boolean Parameters

Controlling Timeouts, Intervals, and Disk Paging

Buffering

Backup Parameters

Logging

Parameters to Define for Each Storage Node (NDBD)

An Example of a Storage Section of a config.ini File

SQL Nodes

Parameters to Define for All SQL Nodes (MYSQLD_DEFAULT)

Parameters to Define for Each SQL Node

A Final Note on SQL Nodes

Using Multiple Management Nodes

3  Backup and Recovery

MySQL Cluster’s Native Backup Tool

Using mysqldump for Backups

Single-User Mode

Restoring a Backup

Restoring a Backup by Using Native Hot Backup

Restoring a Backup Made Using mysqldump

Speeding Up Restoration

Troubleshooting Backups

Disk Is Full

Disk Is Too Slow

Not Enough Memory

4  Security and Management

Security for MySQL Cluster

Software Firewalls

Standard Security Procedures

Managing MySQL Cluster

The Management Console and Scripting

Doing a Rolling Cluster Restart

Issuing a SQL Command to Each SQL Node

Running MySQL in a Chrooted Environment

5  Performance

Performance Metrics

MySQL Cluster Performance

Benchmarking

Using the Wrong Data Size

Using a Data Set That Is Not Representative

Using Inaccurate Data Access Patterns

Failing to Consider Cache Effects

Using Too Little Load or Too Few Users

Benchmarking Solutions

Indexes

PRIMARY KEY Indexes

UNIQUE Hash Indexes

Ordered Indexes

Index Statistics

Query Execution

The Query Cache in MySQL Cluster

Data Retrieval in MySQL Cluster

Primary Key Access

Unique Key Access

Ordered Index Access

Full Table Scan

Using EXPLAIN

Physical Factors

High-Speed Interconnects

TCP/IP

Shared Memory

SCI

Adding More Transports

6  Troubleshooting

What to Look for When Something Goes Wrong

MySQL Cluster Errors

NDBD Won’t Connect

NDBD Won’t Start

Arbitrator Decides to Shut Down a Node

Table Is Full

Error 708

DBTUP Could Not Allocate Memory

Multiple Storage Nodes per Server

Cluster Nodes Apparently Randomly Shut Down or Crash

7  Common Setups

Load Balancing and Failover

JDBC Driver Support

Round-Robin DNS

Hardware Load Balancing Solutions

Software Load Balancing Solutions

Cluster Topographies

The Simplest Setup

Web Farm/MySQL Farm

Client/Server Applications

Network Redundancy

A  MySQL Cluster Binaries

An Overview of Binary Options

Configuration Files

Individual Programs

The mysqld Program

The mysql Program

The ndbd Program

The ndb_mgmd Program

The ndb_mgm Program

The ndb_desc Program

The ndb_restore Program

The ndb_show_tables Program

The ndb_waiter Program

The perror Program

B  Management Commands

Displaying the Status of the Cluster/Controlling Nodes

The SHOW Command

The <id> START, STOP, and RESTART Commands

The <id> STATUS Command

The <id> DUMP 1000 Command

Logging Commands

The CLUSTERLOG ON and CLUSTERLOG OFF Commands

The CLUSTERLOG TOGGLE Command

The CLUSTERLOG INFO Command

The CLUSTERLOG FILTER <severity> Command

The <id> CLUSTERLOG <category>=<threshold level> Command

Miscellaneous Commands

The ENTER SINGLE USER MODE <sql-node-id> Command

The EXIT SINGLE USER MODE Command

The SHUTDOWN Command

The PURGE STALE SESSIONS Command

The START BACKUP Command

The ABORT BACKUP <backup id> Command

C  Glossary of Cluster Terminology

Index