Mysql-cluster

An Introduction to MySQL Cluster
PHP Québec March 31, 2006
Mike Kruckenberg mike@kruckenberg.com http://mike.kruckenberg.com

Session Overview

About Mike

Two Books

When to ThinkAbout Cluster
 Standalone server(s) overloaded  Replication lag time unacceptable  Failover process is inadequate  Uptime is critical (really)  Others…

Executive Overview
 MySQL Cluster is afault-tolerant, clustered, distributed database  Acquired from the telecom industry in 2003  Integrated, and released in 2004  Provides synchronous data replication

Executive Overview (cont)
No single point of failure  Share nothing architecture  Automatic failover & recovery  Runs on commodity hardware

Technical Overview
 Is just another MySQL storage engine
CREATE TABLE t1 (idINTEGER) ENGINE=NDB;

 Requires multiple machines for true redundancy  Data written in two-phase commit

Technical Overview (cont)
 Transactional  In-memory data storage (pre-5.1)  Datawritten to disk at checkpoints and shutdown  Communicates via TCP/IP or other  Rolling upgrades

Anatomy of a MySQL Cluster
 Three Types of Processes
Management Node (ndb_mgmd) Storage Node(ndbd) SQL Node (mysqld)

 Spread across multiple machines  Storage node count based on amount of data and number of replicas
(data MB * no. of replicas * 1.1)/nodes = MB/node ie. (5000MB * 2replicas * 1.1)/4 nodes = 2750MB

Cluster Diagram
(Courtesy MySQL AB)

Live Demo
Demonstration of setting up three-machine cluster

SQL node

SQL node

storage node

storage nodeMachine 1 (cluster1)
management node

Machine 2 (cluster2)

Machine 3 (cluster3)

Notable Configuration Options
 All Nodes
ndb-connectstring

 Management Node
noofreplicas - NDBDdatamemory - NDBD indexmemory - NDBD datadir – NDBD hostname – MYSQLD arbitrationrank – MYSQLD or NDB_MGMD

Management Commands
SHOW – shows status SHUTDOWN – stops all nodes STATUS – show status of...