MySQL Cluster to Hadoop

February 25, 2014

How do you get data from a MySQL Cluster into Hadoop? Easy, replicate from the cluster to a stand alone MySQL instance and from there use the MySQL Hadoop Applier to HDFS. MySQL data to Hadoop via MySQL Hadoop Applier

This question came from a long time MySQL user who has jumped into the Big Data world.

Advertisements

MySQL Cluster 7.2 & Future Webcasts

March 22, 2012

Check out this MySQL Cluster 7.2 Demo

From the Cluster 7.2 Demo

if you have missed any of the new features.

And upcoming MySQL Webcasts on Cluster, MySQL on Windows, MySQL Enterprise Edition, NoSQL access to MySQL, and Building High Performance and High Traffic PHP applications are available for registration!


Who will win a MySQL Cluster 7.2 shirt today??

March 12, 2012

Someone will win a limited edition and very rare MySQL Cluster 7.2 shirt today

Someone will win this shirt at the North Texas MySQL User Group meeting on Monday March 12th

at the North Texas MySQL Users Group Meeting. Many other prizes including thumb drives, buttons, and lanyards plus we will have pizza. Please RSVP to make sure we get enough food!

The presentation will be on MySQL 5.6 + 20 MySQL Tips in 20 Minutes!

Date: Monday, March 12, 2012 Remind Me
Time: 4:45 PM – 7:00 PM
Oracle Corporation
6031 Connection Drive
Room 900
Irving, TX


MySQl Cluster 7.2 — a single node cluster

November 18, 2011

Today we will get MySQL Cluster running on a single node.

A few years ago I created as presentation on running MySQL Cluster 5.1 on a laptop using Virtualbox that has proven to be the most popular download from NorthTexasMySQL.org with over nine hundred downloads.  Now MySQL 7.2 is in development milestone release and I find that I have some catching up to do.

Those of you with access to the Carrier Grade Edition (CGE) of Cluster, check out MySQL Cluster Manager 1.1 and Andrew Morgan’s  blog.  You can set up a cluster with minimal editing of configuration files or perform online software upgrades.

But MySQL Cluster 7.2 from Labs.MySQL.com is not CGE.  I started reading other introductory writing on cluster and discovered the Cluster Quickstart Guides.  This guide details what need to be done got get cluster running on a single node. But the guide needs just a little tweaking to make it work with Cluster 7.2.2. Please note that my earlier document and this guide are to allow you to familiarize yourself with MySQL cluster but you will need multiple nodes and supporting network to get the high performance, highly available five nine’s performance cluster can provide.

The first step is downloading MySQL Cluster 7.2 from Labs.MySQl.Com.

Next un-tar the software under your home directory.  I created a directory named cluster and then set up a symbolic linked named mysql to point to the software.  I then created ~/cluster/data, ~/cluster/conf, and ~/cluster/ndb_data.

This step is the configuration files and placing them under ~/cluster/conf. First the my.cnf file:

[mysqld]
ndbcluster
datadir=/home/dstokes/cluster/data
basedir=/home/dstokes/cluster/mysql

Next the config.ini file:

[ndb_mgmd]
hostname=UBox
datadir=/home/dstokes/cluster/ndb_data
nodeid=1

[ndbd default]
noofreplicas=2
datadir=/home/dstokes/cluster/ndb_data

[ndbd]
hostname=Ubox
nodeid=3

[ndbd]
hostname=Ubox
nodeid=4

[mysqld]
nodeid=50

If you are following along with the Quickstart Guide, note that id has been depreciated and nodeid is used.

The forth step is installing the MySQl software from the ~/cluster/mysql directory. ./scripts/scripts/mysql_install_db –no-defaults –datadir=~/cluster/data

Now we can start the cluster manager, /bin/ndb_mgmd -f ~/cluster/conf/config.ini –initial –configdir=~/cluster/conf/.

Then two data node daemons are started. And you will need to give them a little time to get fully functional. So enter ./bin/ndbd followed by ./bin/ndbd.

You can check the status of the cluster by using ./bin/ndb_mgm -e show and you show see something similar to:

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1 (mysql-5.5.15 ndb-7.2.2, Nodegroup: 0, Master)
id=4 @127.0.0.1 (mysql-5.5.15 ndb-7.2.2, Nodegroup: 0)

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

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)

So we see our nodegroup of two data servers, our management server node and unoccupied slot for the SQL node.

Now we can test by creating a NDB table and adding the data.

dstokes@UBox:~/cluster/mysql$ ./bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-ndb-7.2.2-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
./bin/mysql Ver 14.14 Distrib 5.5.15-ndb-7.2.2, for linux2.6 (i686) using readline 5.1

Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.15-ndb-7.2.2-gpl MySQL Cluster Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 4 min 4 sec

Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 2 Open tables: 0 Queries per second avg: 0.016
--------------

mysql> create database testcluster; use testcluster;
Query OK, 1 row affected (0.31 sec)

Database changed
mysql> create table t1 (id int unsigned not null primary key, name char(20)) engine=ndb;

Query OK, 0 rows affected (1.21 sec)

mysql> show crate teate table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL,
`name` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> insert into t1 values (1,'Adam'),(2,'Bob'),(3,'Charles'),(4,'Dave'),(5,'Edward'),(6,'Fred');
Query OK, 6 rows affected (0.27 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 5 | Edward |
| 1 | Adam |
| 2 | Bob |
| 4 | Dave |
| 3 | Charles |
| 6 | Fred |
+----+---------+
6 rows in set (0.24 sec)

mysql>

Be sure to check out the files under ndb_data for extra credit.


MySQl Cluster 7.2 at OOW Demo Pod

September 21, 2011

MySQl Cluster 7.2 Be sure to drop by the MySQL Demo Pod while you are attending Oracle Open World. There you will see a demo of the changes in MySQL Cluster 7.2. My favorite part of this release is that you can access the same data via SQL, LDAP, REST, memcached or via APIs. You get the best of both SQLand NoSQL worlds in one. At the Demo Pod, you can see how to see how Adaptive Query Localization speed up joins. Plus the demo will preview the latest and
greatest 7.2 DMR capabilities, and also demonstrate how MySQL Cluster
remains operational during node failures, upgrades and re-configuration
and how simple it is to create a live cluster using MySQL Cluster Manager. In the past, many cluster users avoid joins and simply used primary key or key/value pairs. But Adaptive Query Localization gets the data nodes to do more of the work in parallel.

And Cluster Administration has been greatly streamlined. So less typing gets you more performance.

So drop by the MySQL Demo Pod as you will want to see MySQL Cluster 7.2 in action. And there will be several session on cluster covering in detail things I have not mentioned here.