Friday, December 9, 2011

MySQL Cluster to MySQL Server Part 1

Recently I was working on a MySQL Cluster that is replicated to a MySQL server. I will create a few blog posts to show how all of this can be done. This is of course not the only way to do this.. Any feedback or other options are welcomed in comments for the community to use.

Part one: the install

First the MySQL Documentation about MySQL Cluster is very good I will make references to it for your convenience.
You can go about this a few different ways but this is a simple RPM install:

Download all the related rpms from http://dev.mysql.com/downloads/cluster/ and install.

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-linux-rpm.html) is here to help you with the installs as well.

    rpm -ihv MySQL-Cluster-gpl-*.rpm


Ok that was easy enough ... Make sure we have what we expected...

rpm -qa | grep MySQL

MySQL-Cluster-gpl-debuginfo-7.1.17-1.el6.i686
MySQL-Cluster-gpl-client-7.1.17-1.el6.i686
MySQL-Cluster-gpl-clusterj-7.1.17-1.el6.i686
MySQL-Cluster-gpl-test-7.1.17-1.el6.i686
MySQL-Cluster-gpl-server-7.1.17-1.el6.i686
MySQL-Cluster-gpl-devel-7.1.17-1.el6.i686
MySQL-Cluster-gpl-management-7.1.17-1.el6.i686
MySQL-Cluster-gpl-extra-7.1.17-1.el6.i686
MySQL-Cluster-gpl-storage-7.1.17-1.el6.i686


Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.


[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm


OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-configuration.html) on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.

#/var/lib/mysql-cluster/config.ini
[ndb_mgmd]
# Management process options:
hostname=10.132.241.18         # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
NodeId=1

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=4                         # Number of replicas
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
DataMemory=80M                # How much memory to allocate for data storage
IndexMemory=18M               # How much memory to allocate for index storage

[ndbd]
hostname=mysql_demo.localdomain
NodeId=3

[ndbd]
hostname=10.159.37.130
NodeId=4

[mysqld]
Nodeid=10

[mysqld]
Nodeid=30

[mysqld]
Nodeid=40



Nodes... Start your engines..... ok so really, lets get it started...


MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-first-start.html) on how to start it up for the 1st time..



On Localhost

/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17


On Localhost / Data Node

/usr/sbin/ndbd
2011-12-08 12:21:16 [ndbd] INFO -- Angel connected to 'localhost:1186'
2011-12-08 12:21:16 [ndbd] INFO -- Angel allocated nodeid: 3



On remote node :

./ndbd
2011-12-08 12:25:39 [ndbd] INFO -- Angel connected to '10.132.241.18:1186'
2011-12-08 12:25:40 [ndbd] INFO -- Angel allocated nodeid: 6


On Localhost Start the sql server and check the nodes.

# /etc/init.d/mysql start

# /usr/local/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration

---------------------
[ndbd(NDB)] 4 node(s)
id=3 @127.0.0.1 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from mysql_demo.localdomain)
id=5 @10.159.37.130 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=6 @10.159.37.130 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.132.241.18 (mysql-5.1.56 ndb-7.1.17)

[mysqld(API)] 3 node(s)
id=10 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)


OK lets log in and test it ?

# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select VERSION();
+-------------------------------+
| VERSION() |
+-------------------------------+
| 5.1.56-ndb-7.1.17-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)



Of course if you need to stop and restart (MySQL Documentation:  http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-install-shutdown-restart.html) a cluster, it is a simple process.


/usr/local/bin/ndb_mgm -e shutdown
/etc/init.d/mysql stop



Others posts on cluster installs:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html

http://alexyu.se/comment/37

http://dbperf.wordpress.com/2011/03/04/mysql-cluster-setup-and-replication-between-ndb-and-non-ndb-engines-2/

Mine:
Part 1 -- The Install


Part 2 -- Testing Cluster more and Replication Setup