MySQL 5.1 NDB Cluster Replication on EC2

As I mentioned previously once the MySQL Cluster was setup, the next thing to test was setting up a MySQL NDB Cluster Replication i.e. Master Cluster and Slave Cluster.

You would use this type of setup in situations where you want availability and redundancy.
So even if your multiple (n+1) management nodes and data nodes and SQL/API nodes die on one site, you can redirect traffic or load to a replicated site.

Essentially the (row based) replication is done via a pair of NDB SQL nodes, with the master and slave configuration similar to the plain vanilla variety of MySQL replication. I would suggest getting that working first before attempting this, just so you gain some familiarity with how MySQL replication works.

The main differences from normal replication was the creation of the slave NDB Cluster from the clone of the Master and the way to check where the NDB apply is up to, so you can set the slave correctly.
The steps to do this setup start in the MySQL documentation.

I used two pairs of multi-purpose nodes (Management, Data and MySQL combined) to setup the MySQL 5.1 NDB Clusters replication. So it was a 2 data node Master cluster and a 2 data node Slave cluster.
Here are my high level steps:

  1. Configure /etc/hosts. Added mysql-ndb-master and mysql-ndb-slave IP addresses
  2. Configure the configure.ini to reflect the number of data nodes and MySQL nodes required.
  3. On either management node issue the START BACKUP command to create a backup.
  4. Copy all files from all NDB data nodes to one of the Slave management nodes.
  5. Restore the NDB database in order by data node_id.
  6. Configure the Master /etc/my.cnf and restart the Master MySQL node.
  7. Configure the Slave /etc/my.cnf and restart the Slave MySQL node.
  8. Verify that SHOW MASTER STATUS and SHOW SLAVE STATUS are correct.

Once that was done the Cluster replication was up and running. I then proceeded to run mysqlslap on the master SQL node to quickly test the replication and it was fine under light load. (more on that later).

As per normal I have dumped my work out so that you can see the same stuff I did whilst I was setting up.

Have Fun

Paul

Previous articles in the series:
MySQL 5.1 NDB Cluster – Part 1.
MySQL 5.1 NDB Cluster – Part 2.
MySQL 5.1 NDB Cluster – Testing redundancy.
MySQL 5.1 NDB Cluster – sample configuration files.



ndb_mgm> show
Connected to Management Server at: mysql-ndb_mgmd1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @10.255.39.130 (Version: 5.1.20, Nodegroup: 0, Master)
id=2 @10.255.7.34 (Version: 5.1.20, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=3 @10.255.39.130 (Version: 5.1.20)
id=4 @10.255.7.34 (Version: 5.1.20)

[mysqld(API)] 2 node(s)
id=5 @10.255.7.34 (Version: 5.1.20)
id=6 @10.255.39.130 (Version: 5.1.20)

Original 2 data node configure.ini file

[root@domU-12-31-35-00-20-74 ~]# cat /usr/local/mysql-cluster/EC2-configure-2node.ini
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 64
MaxNoOfConcurrentOperations= 128
DataMemory= 256M
IndexMemory= 256M
Diskless= false
TimeBetweenWatchDogCheck= 30000
DataDir= /mnt/mysql-cluster/data
MaxNoOfOrderedIndexes= 500
MaxNoOfAttributes= 1000
TimeBetweenGlobalCheckpoints= 500
NoOfFragmentLogFiles= 4
FragmentLogFileSize=12M
DiskPageBufferMemory= 64M
ODirect= 1
# the following parametes just function as a small regression
# test that the parameter exists
InitialNoOfOpenFiles= 27

#
# Increase timeouts to cater for slow test-machines
# (possibly running several tests in parallell)
#
HeartbeatIntervalDbDb= 30000
HeartbeatIntervalDbApi= 30000
#TransactionDeadlockDetectionTimeout= 7500

[ndbd]
HostName= mysql-ndbd1 # hostname is a valid network adress

[ndbd]
HostName= mysql-ndbd2 # hostname is a valid network adress

[ndb_mgmd]
HostName= mysql-ndb_mgmd1 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
[ndb_mgmd]
HostName= mysql-ndb_mgmd2 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[mysqld]

[mysqld]

Create REPLICATION user on master MySQL node

create user myslave IDENTIFIED BY '53cr37';

GRANT REPLICATION SLAVE
ON *.* TO 'myslave'@'mysql-ndb-slave'
IDENTIFIED BY '53cr37';

mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-ndb-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Hosts file on each Master and slave node

cat /etc/hosts

127.0.0.1 localhost
# Mysql Cluster data node
10.255.46.240 mysql-ndbd1
10.255.19.225 mysql-ndbd2
# Mysql Cluster mgm node
10.255.46.240 mysql-ndb_mgmd1
10.255.19.225 mysql-ndb_mgmd2
# Mysql replication
10.255.46.240 mysql-ndb-master
10.255.11.159 mysql-ndb-slave

Master MYSQL node my.cnf file

[root@domU-12-31-35-00-29-02 BACKUP]# cat /etc/my.cnf
# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
log-bin=row
server-id=1

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Slave /etc/my.cnf file

cat /etc/my.cnf

# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
master-host='mysql-ndb-master'
master-port=3306
master-user='myslave'
master-password='53cr37'
server_id=2

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Checking slave status

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status\G
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-ndb-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37';
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: domU-12-31-35-00-0E-51-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Run backup on master cluster using START BACKUP from ndb_mgm command line

ndb_mgm> start backup
Connected to Management Server at: mysql-ndb_mgmd1:1186
Waiting for completed, this may take several minutes
Node 1: Backup 1 started from node 3
Node 1: Backup 1 started from node 3 completed
StartGCP: 6681 StopGCP: 6684
#Records: 2053 #LogRecords: 0
Data: 33888 bytes Log: 0 bytes

Copy backup to slave from all data nodes

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.1.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.1.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.1.ctl 100% 6832 6.7KB/s 00:00

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.2.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.2.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.2.ctl 100% 6832 6.7KB/s 00:00

Checking databases are all on SLAVE, otherwise use CREATE SCHEMA

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| world |
+--------------------+
4 rows in set (0.00 sec)

Slave

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| world |
+--------------------+
4 rows in set (0.02 sec)

Restore the Backup onto the slave

ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/


ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 1
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 2
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 1
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

Check apply status on slave

SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;


SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE epoch > 1110
ORDER BY epoch ASC LIMIT 1;



mysql> SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;
+---------------------+
| @latest:=MAX(epoch) |
+---------------------+
| 1110 |
+---------------------+
1 row in set (0.09 sec)

mysql> SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
-> @pos:=Position
-> FROM mysql.ndb_binlog_index
-> WHERE epoch > 1110
-> ORDER BY epoch ASC LIMIT 1;
Empty set (0.00 sec)

Restart MASTER and SLAVE MySQL nodes to make sure /etc/my.cnf is ok

service mysql.server restart


On master:

mysql> show master status\G
*************************** 1. row ***************************
File: row.000001
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On Slave:

[root@domU-12-31-35-00-04-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: row.000001
Read_Master_Log_Pos: 106
Relay_Log_File: domU-12-31-35-00-04-51-relay-bin.000003
Relay_Log_Pos: 245
Relay_Master_Log_File: row.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 417
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Running mysqlslap to test

mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password


mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 --number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password
Enter password:
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '88' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '36' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '38' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '73' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '55' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
Killed

cat /tmp/mysqlslap.csv
ndb,mixed,0.129,0.102,0.165,1,100
ndb,mixed,0.035,0.011,0.067,1,10
ndb,mixed,0.004,0.001,0.031,1,1

Concurrency settings were too low, setting higher

[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 2048
MaxNoOfConcurrentOperations= 4096

Restart the cluster.

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10 --user=root --password


[root@domU-12-31-35-00-29-02 BACKUP]# cat /tmp/mysqlslap.csv
ndb,mixed,0.053,0.012,0.075,1,10
ndb,mixed,1.202,1.040,1.321,25,0
ndb,mixed,2.482,2.386,2.596,50,0
ndb,mixed,10.239,9.398,11.006,100,0

Advertisements

MySQL 5.1 NDB Cluster on EC2 configuration files

As I mentioned in the MySQL 5.1 NDB Cluster testing article I was going to provide the configuration files I used for setting up the cluster.

There are three main files for setting up a MySQL cluster

  1. configure.ini : The MySQL cluster management configuration file.
  2. /etc/my.cnf: Standard MySQL configuration file, required for data and SQL/API nodes.
  3. /etc/hosts: Local names resolution file required on all cluster nodes.

Specifically for EC2. I use names rather ip addresses and use /etc/hosts to locally resolve those names to specific ip addresses. This means you will have to change the ip addresses to be correct before you launch the cluster.
To get the local ip address you can use a command: nslookup `hostname`

These configuration files will enable you to have 2 management nodes on the hosts:

  1. mysql-ndb_mgmd1
  2. mysql-ndb_mgmd2

Two data nodes on the hosts:

  1. mysql-ndbd1
  2. mysql-ndbd2

And eight MySQL nodes on any host, this is dangerous, so launch the cluster behind a firewall or on EC2 in a specific security group.

Have Fun

Paul

Here are my configuration files.

1. The configure.ini file, required on all management nodes.


[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 64
MaxNoOfConcurrentOperations= 128
DataMemory= 256M
IndexMemory= 256M
Diskless= false
TimeBetweenWatchDogCheck= 30000
DataDir= /mnt/mysql-cluster/data
MaxNoOfOrderedIndexes= 500
MaxNoOfAttributes= 1000
TimeBetweenGlobalCheckpoints= 500
NoOfFragmentLogFiles= 4
FragmentLogFileSize=12M
DiskPageBufferMemory= 64M
ODirect= 1
# the following parametes just function as a small regression
# test that the parameter exists
InitialNoOfOpenFiles= 27

#
# Increase timeouts to cater for slow test-machines
# (possibly running several tests in parallell)
#
HeartbeatIntervalDbDb= 30000
HeartbeatIntervalDbApi= 30000
#TransactionDeadlockDetectionTimeout= 7500

[ndbd]
HostName= mysql-ndbd1 # hostname is a valid network address

[ndbd]
HostName= mysql-ndbd2 # hostname is a valid network address

[ndb_mgmd]
HostName= mysql-ndb_mgmd1 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[ndb_mgmd]
HostName= mysql-ndb_mgmd2 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

2. The /etc/my.cnf required on all data and SQL/API nodes.


# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server

3. The /etc/hosts file required on all hosts


# Mysql Cluster data node
10.255.14.177 mysql-ndbd1
10.255.31.160 mysql-ndbd2
# Mysql Cluster mgm node
10.255.14.177 mysql-ndb_mgmd1
10.255.31.160 mysql-ndb_mgmd2
# extra
127.0.0.1 localhost

MySQL 5.1 NDB Cluster on EC2 testing redundancy

Over the last couple of weeks I have building and installing MySQL 5.1 NDB Cluster EC2 Amazon machine images (AMI).

http://blog.dbadojo.com/2007/07/mysql-51-ndb-cluster-on-ec2.html
http://blog.dbadojo.com/2007/07/mysql-51-ndb-cluster-on-ec2-part-2.html

There has plenty of forum questions with regards to using MySQL 5.1 NDB Cluster as a way to provide redundancy and specifically for EC2, a way to provide persistent storage.

The real benefit with MySQL 5.1 is the new feature ability to store non indexed columns on disk, this essentially increases the size of the database which can run under NDB. With the previous versions the whole databases had to be stored in memory, this constrains the size of the database you could run, both from physical and also the cost.

Last couple of days I have smoothed out the running of multiple NDB nodes.

I have built both dedicated data, management and sql/API nodes and also combined nodes.

The main thing I have been testing is the redundancy and the ability for MySQL NDB to provide the required redundancy and data persistent.

So I used a two combined nodes (a node with has management, data and sql/API software) as the base and two dedicated data nodes.

The thing to remember here is the network bandwidth provided (100Mbps) is the bare minimum required for the cluster.

So the results:

  1. Having 2 management nodes works as documented.
  2. Losing any node causes all connections from sql/API nodes to be disconnected, however once the cluster is ok, connections are restored. This is similar to any other HA solution without a front end cache.
  3. I had two occasions where the lost of management and data node caused the cluster to not rebalance and had to be completely shutdown. This may have been related to a slow network connection.
  4. Make sure that the number of replicas (a NDB cluster configuration variable) is what you require. With the 4 data nodes with number of replicas of 4 means that the NDB will keep 4 replicas of the data. This is maximum redundancy available provided by MySQL 5.1 NDB.
  5. Like any scaling solution you should serious look at caching so any loss of connections is a minor issue.

The next testing involves (finally) testing MySQL Cluster replication.

I will post another article with the main configure files and settings I choose to use.

Have Fun

Paul

MySQL 5.1 NDB Cluster on EC2 – Part 2

Given I had already been through the procedure of setting up an MySQL 5.0 NDB Cluster on EC2, getting the MySQL 5.1 NDB Cluster installed was a reasonably straight forward task.

I will create a new HOWTO wiki from my screen dumps in the next day or so though the documentation provided by MySQL is very thorough.

Given I want to give a large MySQL 5.1 cluster a whirl, maybe with Cluster replication, as described in this documentation, I built and bundled the following AMIs (Amazon Machine Images)

  1. MySQL 5.1 NDB data node – built from the rpms
  2. MySQL 5.1 NDB Management node – built from rpms
  3. MySQL 5.1 NDB SQL/API node – built from rpms
  4. Complete MySQL 5.1 install.

I built number 4 as you can run the whole cluster to test everything off one box or in my case image. You could also run the management node and SQL node on the same box as well.

The biggest issue I found which is specific to running on EC2 is the hostname, which is allocated via DHCP. I am working on scripts to automate the updating of the /etc/hosts file on each box so that the configure.ini required on the management node and the /etc/my.cnf settings required on the data nodes can point at a name rather than a specific IP address or DNS name.

The work I do here can be replicated to handle the hostname stuff required for Openfiler, Oracle standbys and anything else requiring network connectivity.

For particular interest for users of EC2 is the lack of persistent storage on any specific instance. Plenty of people are looking at various solutions, I guess the interest in using MySQL cluster is that any one data node or two (if you are paranoid) could die so running enough data nodes specifically and also maybe sql nodes with management nodes on the same would provide a solution to this. Backing up to S3 or some form of persistent storage is the fail back if everything goes pear-shaped.

Replicating these virtual images to boxes with persistent storage alleviates some of the pain, however EC2 remains a great area to built your knowledge of various technologies available. Even if some of quirks of running under a virtual machine make things slightly different.

If there is interest I will release a public image of the various types of nodes.

Have Fun

Paul

Part 1: Why use MySQL 5.1 NDB Cluster?

MySQL 5.1 NDB Cluster on EC2 – Part 1

Can you use MySQL Cluster and have persistent storage?

Yes, if you run enough cluster (data) nodes that it can survive the loss of one or more nodes.
So if reliability is required, the minimum number of nodes would be 3 data nodes.

The real solution would be to use MySQL Cluster replication. It requires more nodes but almost guarantees no loss data even if you lose one whole cluster.

Use MySQL 5.1 cluster
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html

As this version will give you the ability to have non-indexed data on disk, otherwise the whole lot has to be in memory. So on EC2 that is 1-1.2Gig or so.

As to persistent storage, you will need either to

  • Run enough nodes (n+1) so that any crash of your AMI is handled ok. You can then use /mnt as storage.
  • Use a S3 filesystem, there are couple of people providing solutions. You could still use S3 as a backup storage, as you still need backups even if you are running a fault tolerant cluster.

I would use option 1, if you are really paranoid use a Master MySQL 5.1 cluster 3 nodes replicating to a Slave MySQL 5.1 cluster 3 nodes.

The MySQL Cluster replication is documented here
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html

I have made a MySQL 5.0 NDB cluster of 4 nodes (1 master, 2 data and 1 mysql) so the next thing is built a Mysql 5.1 NDB master and mysql node and a MySQL 5.1 NDB data node for EC2 AMI (Amazon Machine Images).

The process continues in Part 2

Have Fun

Paul