Archive for the ‘Cluster’ Category

Articles

MySQL Cluster Installation

In Cluster,Distribution,MySQL on 08/01/2013 by pier0w

Installing MySQL Cluster is surprisingly easy, almost as easy as installing HDFS. MySQL Cluster consists of three main parts; a MySQL Server, a Management Node, and a Data Node. All of these parts can have multiple instances running within a MySQL Cluster setup.

MySQL Server
To use MySQL Cluster a MySQL server must be present, this is just like any other MySQL server that you’re used to, the only difference being the it uses the NDBCLUSTER sorage engine instead of InnoDB or MyISAM. Data is stored and retrieved from MySQL cluster through the connected MySQL instances.
The NDBCLUSTER storage engine is of course the back bone of MySQL Cluster. The next two parts of the installation manage this storage engine.

Management Node
The Management Node is similar to an HDFS Name Node, it is this that keeps track of where the data resides on the cluster of Data Nodes.

Data Node
The Data Node, again just like in HDFS, stores the actual data that is persisted in MySQL Cluster. It is the instances of this that will typically be increase the most to increase read and write performance.

The following installation instructions are for installing on Ubuntu Server so some of the steps may be platform specific.
 

Preliminary Setup

There are two steps that have to be carried out on the installation server before starting the following MySQL Cluster Installation instructions.

Step 1: Create a new mysqlcl user.

#> sudo useradd -m -G adm,cdrom,sudo,dip,plugdev,lpadmin,sambashare -s /bin/bash mysqlcl
#> sudo passwd mysqlcl
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

 
Step 2: Install the libaio libraries so that MySQL Server will run.

#> sudo apt-get install libaio1

 
It’s also worth noting that the MySQL Cluster components user a lot of RAM, a single Data Node will not start up on a server with less than 1Gig of RAM. To run this installation example you will need at least 4Gigs of RAM on the installation server.
 

Installation

Installing the MySQL Cluster files is simply a case of downloading then extracting a tar ball.

#> cd /opt/
#> tar xzf mysql-cluster-gpl-<version>-linux2.6-i686.tar.gz
#> sudo ln -s mysql-cluster-gpl-<version>-linux2.6-i686/ mysql-cluster

 
It would also be worth adding the MySQL Cluster bin directory to your global PATH environment variable.

#> sudo vim sudo vim /etc/profile.d/mysql-cluster.sh
export PATH=$PATH:/opt/mysql-cluster/bin/
~
~
~
:wq
#> . /etc/profile

 
MySQL Cluster is now installed.
 

MySQL Cluster Manager Configuration

The first thing that needs to be configured is the MySQL Cluster Manager, it needs to be told the hostname of the server it is being installed on, the directory it is going to store it’s data in, the data replication factor, the directory the Data Nodes will store their data, and lastly the ID’s that should be allocated to the data nodes and any clients that will connect to it. The clients being MySQL and any monitoring applications.
The following configuration does all the above, allocating two Data Node ID’s and two client ID’s.

#> cd
#> mkdir -p manager/conf/
#> mkdir manager/data/
#> vim manager/conf/config.ini
[ndb_mgmd]
hostname=localhost
datadir=/home/mysqlcl/manager/data
NodeId=1

[ndbd default]
noofreplicas=1
datadir=/home/mysqlcl/node/data

[ndbd]
hostname=localhost
NodeId=3

[ndbd]
hostname=localhost
NodeId=4

[mysqld]
hostname=localhost
NodeId=50

[mysqld]
hostname=localhost
NodeId=51
~
~
~
:qw

 
This configuration file has been setup to have all the MySQL Cluster components running on the one server, it’s not a very practucal solution, but is a simple starting point.

To start and inspect the MySQL Cluster Manager run the following commands.

#> ndb_mgmd -f /home/mysqlcl/manager/conf/config.ini --initial --configdir=/home/mysqlcl/manager/conf/
#> ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=3 (not connected, accepting connect from mysqlmaster)
id=4 (not connected, accepting connect from mysqlmaster)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.1.1 (mysql-5.5.29 ndb-7.2.10)

[mysqld(API)] 2 node(s)
id=50 (not connected, accepting connect from mysqlmaster)
id=51 (not connected, accepting connect from mysqlmaster)

 
So the MySQL Cluster Manager has been started correctly and is waiting for Data Nodes ([ndbd(NDB)]) and client connections ([mysqld(API)]).

Now stop the MySQL Cluster Manager with the following command in prepuration for setting up the MySQL Server and Data Nodes.

#> ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
1 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

 

MySQL Server Configuration

The MySQL Server configuration is extremely simple, you just need to create a my.conf that enables the NDBCLUSTER storage engine, sets the data directory, sets the MySQL Server base directory, and sets the default client connection port.

#> cd
#> mkdir -p mysql/conf/
#> mkdir mysql/data/
#> vim mysql/conf/mysql/conf/my.cnf
[mysqld]
ndbcluster
datadir=/home/mysqlcl/mysql/data/
basedir=/opt/mysql-cluster
port=5000
~
~
~
:wq

 
The MySQL Base directory has been set to the MySQL Cluster install directory, this is because it seems that a special MySQL Cluster specific MySQL Client and Server must be used.
Now that MySQL Server is configured it just needs to be initialised.

#> /opt/mysql-cluster/scripts/mysql_install_db --no-defaults --basedir=/opt/mysql-cluster/ --datadir=/home/mysqlcl/mysql/data/
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
...

 
That’s MySQL Server setup, but unfortunately we can’t start it until the Data Nodes have been started.
 

Data Node Configuration

To configure the Data Nodes all you have to do is create their data directory that was previously set in the MySQL Cluster Manager configuration file (~/node/data).

#> cd
#> mkdir node/data

 
That’s it, the Data Nodes are ready.
 

Start Up

So it’s now time to start up MySQL Cluster, first start the MySQL Cluster Manager and the Data Nodes.

#> ndb_mgmd -f /home/mysqlcl/manager/conf/config.ini --initial --configdir=/home/mysqlcl/manager/conf/
#> ndbd -c localhost:1186 1>/tmp/node1.log 2>&1&
#> ndbd -c localhost:1186 1>/tmp/node2.log 2>&1&

 
The startup of the nodes can take a very long time, you can monitor their progress through there log files (node/data/ndb_*_out.log). When a node has finished starting up you should see the following in the log.

...
2013-01-08 00:22:46 [ndbd] INFO     -- Start phase 100 completed
2013-01-08 00:22:46 [ndbd] INFO     -- Start phase 101 completed
2013-01-08 00:22:46 [ndbd] INFO     -- Node started
2013-01-08 00:22:46 [ndbd] INFO     -- Started arbitrator node 1 [ticket=056d0001000632fd]

 
Also you should not see the word “starting” in any of the Data Node definitions in the ndb_mgm -e show command output. The output should look like this.

#> 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.5.29 ndb-7.2.10, Nodegroup: 0, Master)
id=4	@127.0.0.1  (mysql-5.5.29 ndb-7.2.10, Nodegroup: 1)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@127.0.1.1  (mysql-5.5.29 ndb-7.2.10)

[mysqld(API)]	2 node(s)
id=50 (not connected, accepting connect from mysqlmaster)
id=51 (not connected, accepting connect from mysqlmaster)

 
If any of the Data Node definitions look like the following then the startup has not completed.

id=4	@127.0.0.1  (mysql-5.5.29 ndb-7.2.10, starting, Nodegroup: 0)

 
So now that the manager and nodes are started it is possible to start up MySQL Server.

#> mysqld --defaults-file=/home/mysqlcl/mysql/conf/my.cnf 1>/tmp/mysql.log 2>&1 &

 
If it has started correctly you should see that one of the MySQL Cluster client ID’s has been allocated.

#> 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.5.29 ndb-7.2.10, Nodegroup: 0, Master)
id=4	@127.0.0.1  (mysql-5.5.29 ndb-7.2.10, Nodegroup: 1)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@127.0.1.1  (mysql-5.5.29 ndb-7.2.10)

[mysqld(API)]	2 node(s)
id=50	@127.0.1.1  (mysql-5.5.29 ndb-7.2.10)
id=51 (not connected, accepting connect from mysqlmaster)

 
You can also double check that the NDBCLUSTER engine is enabled.

#> mysql -uroot -e "SHOW ENGINES;"
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

 
Right it’s all up and running so the very last thing to do is create a data base and then a table that uses the NDBCLUSTER engine.

#> mysql -uroot
mysql#> CREATE DATABASE clusterdb;
mysql#> CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT, 
                            email VARCHAR(32) NOT NULL, 
                            first_name VARCHAR(32) NOT NULL, 
                            last_name VARCHAR(32) NOT NULL, 
                            PRIMARY KEY (id), UNIQUE(email)) 
        engine=ndbcluster;

 
And that’s it.
 

Usage

MySQL Cluster is used through the connected MySQL Server instances so the CRUD operations should be very familiar. It is also possible to monitor the distribution of the data accross the different data nodes.

#> mysql -uroot -e "INSERT INTO users (email, first_name, last_name) VALUES ('some.one@there.com', 'Some', 'One');"
#> ndb_desc -c localhost:1186 users -d clusterdb -p
...
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
1        	1        	1           	32768            	32768               	0            	0 

NDBT_ProgramExit: 0 - OK
                	
#> mysql -uroot -e "INSERT INTO users (email, first_name, last_name) VALUES ('some.two@there.com', 'Some', 'Two');"
#> ndb_desc -c localhost:1186 users -d clusterdb -p
...
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
1        	2        	2           	32768            	32768               	0            	0

NDBT_ProgramExit: 0 - OK

#> mysql -uroot -e "INSERT INTO users (email, first_name, last_name) VALUES ('some.three@there.com', 'Some', 'Three');"
#> ndb_desc -c localhost:1186 users -d clusterdb -p
...
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
0         	1        	1           	32768            	32768               	0            	0                 	
1        	2        	2           	32768            	32768               	0            	0

NDBT_ProgramExit: 0 - OK
#> mysql -uroot -e "SELECT * FROM clusterdb.users ORDER BY id;"
+----+----------------------+------------+-----------+
| id | email                | first_name | last_name |
+----+----------------------+------------+-----------+
|  1 | some.one@there.com   | Some       | One       |
|  2 | some.two@there.com   | Some       | Two       |
|  3 | some.three@there.com | Some       | Three     |
+----+----------------------+------------+-----------+

 
The ndb_desc command connects to the second [mysqld] ID that we allocated at the beginning in the MySQL Cluster Manager configuration.

One nice thing about using MySQL Cluster to destribute your data is that you don’t have to give up secondary uniqueness.

#> mysql -uroot -e "INSERT INTO clusterdb.users (email, first_name, last_name) VALUES ('some.three@there.com', 'Some', 'Three');"
ERROR 1062 (23000) at line 1: Duplicate entry 'some.three@there.com' for key 'email'

 
Now that MySQL Cluster is running and usable we might as well shut it down.

#> mysqladmin -u root -h localhost -P 5000 shutdown
#> ndb_mgm -e shutdown
Advertisements