A Million Monkeys

I’m posting this mainly because I forget the exact syntax, but it might be useful for others.

This applies to all services on all ports, but for this example I’ll use the standard IMAP port. For example, if I have a mail server (listening for IMAP connections on port 143) which isn’t accessible from outside it’s LAN, but I have another server on the LAN which is accessible from outside – I could forward the port on the open server to give myself access to the mail server.

Here’s the example setup:
Mail server: 10.1.0.2, listening on port 143 which isn’t accessible from outside.
Other server 10.1.0.3, accessible from outside.

By running these 2 commands, I can forward all requests to 10.1.0.3:143 to 10.1.0.2:143 and connect to the IMAP server from anywhere:

iptables -t nat -I PREROUTING 1 -i eth0 -p tcp –dport 143 -j DNAT –to-destination 10.1.0.2:143
iptables -t nat -I POSTROUTING 1 -o eth0 -p tcp -d 10.1.0.2 –dport 143 -j SNAT –to-source 10.1.0.3

For this to work, you will need to have IP forwarding enabled, which can be done temporarily (which will reset on reboot), or permanently:

echo 1 > /proc/sys/net/ipv4/ip_forward
(temporary)

vim /etc/sysctl.conf
// uncomment the line ‘net.ipv4.ip_forward=1′
(permanent)

Now if you telnet to 10.1.0.3:143, the connection should be forwarded to 10.1.0.2:143

This guide explains how to turn standard debian or ubuntu mysql-server installs into a full ndb cluster. As we’re using the standard mysql-server package, you won’t need to download any non .deb binaries or do any compiling. If everything goes well, this should take under 10 minutes to get working. I don’t plan to cover much theory here, as it is just a guide to getting the server up and running.

Please note that clustering works differently in MySQL 5.1, so this guide may not be relevant for that.

I also recommend the book ‘MySQL Clustering’ by Alex Davies and Harrison Fisk (ISBN 0-672-32855-0) as it explains how the cluster works in a lot more detail than I plan to here.

I will explain how to set up 3 servers, with the following roles:

server-a = management node (IP 10.1.0.10)
server-b = storage and sql node (IP 10.1.0.11)
server-c = storage and sql node (IP 10.1.0.12)

It is easy to have storage and sql nodes on separate servers, but to make this guide easier to follow (and so we don’t need 5 machines), I’ll use 3 servers.

All servers will need to have mysql-server installed. If you need help doing this, have a look at this guide.

Setting up the management node (10.1.0.10)

Default debian/ubuntu installs look for /etc/mysql/ndb_mgmd.cnf for the management node, so we need to create this file, with the following contents:

[NDBD DEFAULT]
NoOfReplicas=2
DataDir= /var/lib/mysql-cluster

# Management Node
[NDB_MGMD]
HostName=10.1.0.10
DataDir=/var/lib/mysql-cluster

# Storage Nodes (one for each node)
[NDBD]
HostName=10.1.0.11
DataDir=/var/lib/mysql-cluster
[NDBD]
HostName=10.1.0.12
DataDir=/var/lib/mysql-cluster

# SQL Nodes (one for each node)
[MYSQLD]
HostName=10.1.0.11
[MYSQLD]
HostName=10.1.0.12

The line beginning NoOfReplicas tells the cluster how many copies of data should be kept.

Now we start the management node, and it will sit waiting for connections from the storage and SQL nodes:

/etc/init.d/mysql-ndb-mgm start

Setting up SQL and data nodes

All we need to do here is make a few changes to /etc/mysql/my.cnf (the mysql config file)

First, add the following 2 lines (using the IP of your management node) inside the [mysqld] section of the config file:

ndbcluster
ndb-connectstring=10.1.0.10

And near the bottom of the file there will be a section for [MYSQL_CLUSTER] which you will need to uncomment, and change the ndb-connectstring line to your management nodes IP.

If you are using separate data and sql nodes, the [mysqld] part is relevant to the sql nodes only, and the [MYSQL_CLUSTER] part is relevant to the data nodes only.

Before we start the services, we have to create the /var/lib/mysql-cluster directory and set it to be owned by the mysql user:

mkdir /var/lib/mysql-cluster
chown mysql:mysql /var/lib/mysql-cluster

Now we need to start the node services:

/etc/init.d/mysql restart
(which starts the sql node)
/etc/init.d/mysql-ndb restart
(which starts the data node)

By now, everything should be running, so we connect to the management node (by running ndb_mgm from the command line) and check that the other nodes have connected properly (using the show command):

ndb_mgm> show;
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=2    @10.1.0.11  (Version: 5.0.51, Nodegroup: 0)
id=3    @10.1.0.12  (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.1.0.10  (Version: 5.0.51)

[mysqld(API)]   2 node(s)
id=4    @10.1.0.11  (Version: 5.0.51)
id=5    @10.1.0.12  (Version: 5.0.51)

This shows that everything has connected properly. If connections are missing, then it’s worth checking /var/log/syslog on the affected server to see if there are any error messages.

Using the cluster

It’s worth noting that any databases already on the servers will continue to work as before. Tables only become part of the cluster when their engine type is changed to ‘ndbcluster’ by issuing this command (from a mysql prompt):

alter table tablename engine=ndbcluster;

But for now we will create a new database and clustered table, and test that the data is clustered. The cluster setup applies to tables only, not databases, so we first need to create the database on both sql nodes:

create database cluster;

Now, when we create a table inside the cluster database, as long as the engine is ndbcluster, the data will be synced across data nodes, which we can test by doing the following (from a mysql prompt on either sql node):

create table clustertest (i int) engine=ndbcluster;
insert into clustertest () values (1);
select i from clustertest;

Which should return the single row with the value of 1. Now connect to the other SQL node and try…

insert into clustertest () values (2);
select i from clustertest;

Which should return both rows, which will happen whichever SQL node you connect to as the table is now stored in the cluster.

If you receive an error like:

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

Then it is likely that some of your cluster nodes haven’t started correctly, so its worth checking the management interface again.

Shutting down and restarting the cluster

To shutdown the data nodes and management node, all you need to do is enter the command ‘shutdown’ in the management interface.

To restart, simply run

/etc/init.d/mysql-ndb-mgm start

On the management node, and

/etc/init.d/mysql-ndb start

On the data nodes. The SQL nodes continue running, and can be stopped/started using the standard mysql init script.

That’s it, you’ve now should have a working MySQL cluster. As you have NoOfReplicas=2 in the management config, you should be able to unplug either data node at any time and still have access to all of the clustered tables.

If you install and uninstall a lot of packages using apt, aptitude, or dpkg, you may find that you end up with a lot of redundant packages, or ones which have been uninstalled but the config files are left behind.

You can find packages which have been removed but still have config files by running (basically the status field will be ‘rc’):

dpkg -l | grep “^rc”

Or, suppose you installed mysql 5.1 from my previous post using the dotdeb repositories and wanted to see a list of what was installed:

dpkg -l | grep dotdeb

Now, removing those packages is easy, all you need to do is pass the list to dpkg –purge using awk in a similar way to grep above, so the 2 examples above would become:

dpkg --purge `dpkg -l | awk '/^rc/{print $2}'`
dpkg --purge `dpkg -l | awk '/dotdeb/{print $2}'`

(note that you may need to delete and retype the backticks depending on how cut and paste is handled)

Using the methods above, it should be easy to clean up your system.