8/9/2019 MySQL Cluster Configuration
1/15
MySQL Cluster part 1: Installing Cluster Management
Node in Ubuntu Server v.12.0
There are lots of tutorial in the internet on how you can setup MySQL Cluster in UbuntuServer v.12.04 but none of the! are suitable for a roo"ie li"e !e both in Linu# an$ MySQL.
Important
1. %S which &'! usin( for !ana(e!ent no$e is) Ubuntu !es"top v.12.0
2. MySQL binary which & use is) mys#l$%luster$gpl$&.2.'$linu(2.'$()'*'.tar.g+
*hy $o you thin" & nee$ to !ention the binary of MySQL which & use+ &ts because & foun$
that)
1. Most of the tutorial $oesn't !ention on which binary release they use
2. %r !ostly the lin" to $ownloa$ it are (one
,. So!e settin(s they use in the tutorial -even in the boo" are no lon(er supporte$ by
the current MySQL cluster release
4. /n$ if you rea$in( this tutorial a year fro! the $ate & create$ this so!e of the
settin(s !i(ht obsolete alrea$y so beware
So after stru((lin( in a strai(ht $ays at last & coul$ !a"e it wor" yesss
3efore we (et our han$s $irty it is (oo$ to un$erstan$) the logical architectureof a cluster
environ!ent we are about to buil$. ro! the below $ia(ra! our cluster will consists of ,
5Ms. eel free to e#a!ine it before we (o further.
8/9/2019 MySQL Cluster Configuration
2/15
,. -reparations:
1. &nstall 5M*are player for *in$ows -&'! usin( *in$ows 6 by the way
2. Creates a 5M of an Ubuntu 7es"top instance
,. 7isablin( &8 filterin( in this instance -rea$ !y post here
. Steps:
1. Lo(in as root
2. Creates a new $irectory un$er /root/!onloads
1
2
,
cd~/Downloadsmkdirmysqlcd/mysql
,. 7ownloa$ MySQL Cluster binary fro! the web
1wget http://cdn.mysql.com/Downloads/MySQL-Clster-!."/mysql-clster-gpl-
!.".#-lin$".#-$%#'.tar.g(
http://bromokun.wordpress.com/2012/07/07/disabling-ip-filtering-in-ubuntu-server/http://bromokun.wordpress.com/2012/07/07/disabling-ip-filtering-in-ubuntu-server/8/9/2019 MySQL Cluster Configuration
3/15
4. 9#tract the $ownloa$e$ file to /usr/lo%al/$irectory
1tar-C /sr/local-$()* mysql-clster-gpl-!.".#-lin$".#-$%#'.tar.g(
. Creates a lin" fol$er
1ln-s /sr/local/mysql-clster-gpl-!.".#-lin$".#-i#%# /sr/local/mysql
:. %pen mys#llin" $irectory an$ creates another $irectory calle$ ;mys#l$%luster< in it
1
2
cd/sr/local/mysql/mkdirmysql-clster
6. Creates a new confi( file calle$ ;%onig.ini
8/9/2019 MySQL Cluster Configuration
4/15
2
1
2
2
2
,2
4
2
2
:
2
6
2
=
>. Create a new $irectory to stores $ata as it is set insi$e the %onig.ini
1 mkdir/)ar/li/mysql-clster
10. 9#ecutes ndb*mgmdinsi$e /usr/lo%al/mys#l/bin/ to run this !ana(e!ent no$e
1
2
cd/sr/local/mysql./in/nd&mgmd--con*ig-*ile1con*ig.ini
11. To chec" e#ecutes ndb*mgminsi$e /usr/lo%al/mys#l/bin
1 ./in/nd&mgm
12. /fter ndb*mgmapplication runnin( type S34
1 nd&mgm>S38?
1,. &f we succee$ on installin( the !ana(er no$e our ndb*mgmconsole screen will shows
so!ethin( li"e below.
8/9/2019 MySQL Cluster Configuration
5/15
That's it (uys
MySQL Cluster part 2: Installing N! Node in Ubuntu
Server v.12.0
So we are (oin( to install ?73 no$e now
,. -reparations:
1. Turn on the Cluster Managerservice so when N!no$e is runnin( it will
re(istere$ it self to it auto!atically
2. Creates a 5M of an Ubuntu Server instance -&'! usin( v.12.04
,. 7isablin( &8 filterin( in this Ubuntu instance -rea$ !y post here.
. Steps:
http://bromokun.wordpress.com/2012/07/07/disabling-ip-filtering-in-ubuntu-server/http://bromokun.wordpress.com/2012/07/07/disabling-ip-filtering-in-ubuntu-server/8/9/2019 MySQL Cluster Configuration
6/15
1. Lo(in as root
2. %pen /var/tmp/$irectory an$ $ownloa$ MySQL cluster fro! the web
1
2
cd/)ar/tmp/wget http://cdn.mysql.com/Downloads/MySQL-Clster-!."/mysql-clster-gpl-!.".#-lin$".#-$%#'.tar.g(
,. 9#tract the $ownloa$e$ file into @usr@local@ $irectory
1tar-C /sr/local/-$()* mysql-clster-gpl-!.".#-lin$".#-$%#'.tar.g(
4. Creates a lin" fol$er
1ln-s /sr/local/mysql-clster-gpl-!.".#-lin$".#-$%#' /sr/local/mysql
. Creates a new $irectory calle$ ;mys#l< un$er /et%/$irectory
1 mkdir/etc/mysql
:. Creates a new file calle$ ;!y.cnf< in /et%/mys#l/
1
2
cd/etc/mysql/)imy.cn*
6. Types below co!!an$s into the my.%nfile
1
2,
4
:
6
=
>
10
11
12
1,
+ my.cn* content,clientport155#socket1/tmp/mysql-clster
,mysqldport155#socket1/tmp/mysql-clsterndclsternd-connectstring124".2#%.'. Creates a new $irectory for $ata as it is set in the %onig.ini-refer to !yposthere
http://bromokun.wordpress.com/2012/07/07/installing-mysql-cluster-management-node-in-ubuntu-server-v-12-04/http://bromokun.wordpress.com/2012/07/07/installing-mysql-cluster-management-node-in-ubuntu-server-v-12-04/http://bromokun.wordpress.com/2012/07/07/installing-mysql-cluster-management-node-in-ubuntu-server-v-12-04/8/9/2019 MySQL Cluster Configuration
7/15
1 mkdir/)ar/li/mysql-clster
10. 9#ecute n$b$
1
2
cd/sr/local/mysql
./in/ndd
11. &f everythin( %A our screen will shows so!ethin( li"e below
12. To further chec" whether this ?73 no$e has alrea$y re(istere$ in our Mana(e!ent no$e
1nd&mgm>S38?
1,. &f our ?73 no$e re(istere$ our ndb*mgmconsole screen woul$ shows so!ethin( li"e
below
That's it (uys
MySQL Cluster part 6: Installing SQL Node in Ubuntu
Server v.12.0
*e are (oin( to install the last piece of the first half of this cluster environ!ent now which
is) an SQLno$e.
&n this learnin( & $eploy both) SQLan$ N! in a sin(le instance of 5M.
So instea$ of creatin( a new 5M &'! usin( the sa!e 5M where & $eploye$ an N!no$e we
create$ before.
&nsi$e our my.%nfile which we create$ on the 6th step in our previoustutorialcontains a
co$e bloc" li"e below.
http://bromokun.wordpress.com/2012/07/07/mysql-cluster-part-3-installing-ndbd-node-in-ubuntu-server-v-12-04/http://bromokun.wordpress.com/2012/07/07/mysql-cluster-part-3-installing-ndbd-node-in-ubuntu-server-v-12-04/http://bromokun.wordpress.com/2012/07/07/mysql-cluster-part-3-installing-ndbd-node-in-ubuntu-server-v-12-04/http://bromokun.wordpress.com/2012/07/07/mysql-cluster-part-3-installing-ndbd-node-in-ubuntu-server-v-12-04/8/9/2019 MySQL Cluster Configuration
8/15
1
2
,
4
:6
......,mysqldport155#socket1/tmp/mysql-clsterndclsterndconnectstring124".2#%.'
8/9/2019 MySQL Cluster Configuration
9/15
. Set new per!issions access to both MySQL server an$ its $ata
1
2
,
chown-9 root .chown-9 mysql datachgrp-9 mysql .
:. 9#ecute mys#l.serverto start the SQL instance
1
2
cd/sr/local/mysql/./spport-*iles/mysql.ser)er start
6. 9#ecute S34co!!an$ in ndb*mgmconsole in the Management Clusterno$eB /n$
if everythin( runs well our screen will shows so!ethin( li"e below.
8/9/2019 MySQL Cluster Configuration
10/15
ro! the above pic we can see that we alrea$y have the 1st half of the cluster we want to
!a"e -1 ndbdno$e an$ 1 mys#ld7.
HOWTO: MySQL Cluster on Ubuntu 11.04May 11, 2011MySQL, Networkingbalance, balancing, cluster, guide, howto, load,
load-balance, loadbalancing, mysql, ndb, ndbcluster, ro!y
There are a few (ui$es out for settin( up a MySQL Cluster alrea$yB unfortunately the lar(e
!aority of the! aren't (eare$ towar$s the be(inner an$ those that are (enerally involve a
sin(leDserver setup. This (ui$e will ai! to e#plain the purpose of each choice an$ will (etyou up an$ runnin( with a basic , server setup with a sin(le loa$Dbalancin( server.
Preliminary ReuirementsThere are a few thin(s you will nee$ firstB , servers -or 5M's for the cluster an$ 1 server for
loa$Dbalancin(. They shoul$ all be runnin( Ubuntu 11.04 Server 9$ition.
9ach no$e shoul$ have a !ini!u! of 1E3 of F/M an$ 1:E3 of har$ $rive space. The
!ana(e!ent no$e can wor" with 12M3 of ra! an$ the $efault =E3 of har$ $rive space that
5M*are allocates.
"ackage #nstallation
These pac"a(es will be installe$ on all , servers -!ana(e!ent no$e $ata no$e 1 an$ $ata
no$e 2. There is a bu( in the installation for the MySQL Cluster pac"a(es on Ubuntu you
will nee$ to install MySQL Server first then install the Cluster pac"a(es li"e so)
su$o aptD(et install !ysGlDserver
The !ysGlDserver pac"a(e installs so!e crucial confi(uration files an$ scripts that ares"ippe$ an$ cause $p"( to (et hun( up $urin( confi(uration. 89e root passord you sele%t
http://blakemesdag.com/blog/2011/05/11/howto-mysql-cluster-on-ubuntu-11-04/http://blakemesdag.com/blog/category/mysql-2/http://blakemesdag.com/blog/category/networking/http://blakemesdag.com/blog/tag/balance/http://blakemesdag.com/blog/tag/balancing/http://blakemesdag.com/blog/tag/cluster/http://blakemesdag.com/blog/tag/guide/http://blakemesdag.com/blog/tag/howto/http://blakemesdag.com/blog/tag/load/http://blakemesdag.com/blog/tag/load-balance/http://blakemesdag.com/blog/tag/loadbalancing/http://blakemesdag.com/blog/tag/mysql/http://blakemesdag.com/blog/tag/ndb/http://blakemesdag.com/blog/tag/ndbcluster/http://blakemesdag.com/blog/tag/proxy/http://blakemesdag.com/blog/2011/05/11/howto-mysql-cluster-on-ubuntu-11-04/http://blakemesdag.com/blog/category/mysql-2/http://blakemesdag.com/blog/category/networking/http://blakemesdag.com/blog/tag/balance/http://blakemesdag.com/blog/tag/balancing/http://blakemesdag.com/blog/tag/cluster/http://blakemesdag.com/blog/tag/guide/http://blakemesdag.com/blog/tag/howto/http://blakemesdag.com/blog/tag/load/http://blakemesdag.com/blog/tag/load-balance/http://blakemesdag.com/blog/tag/loadbalancing/http://blakemesdag.com/blog/tag/mysql/http://blakemesdag.com/blog/tag/ndb/http://blakemesdag.com/blog/tag/ndbcluster/http://blakemesdag.com/blog/tag/proxy/8/9/2019 MySQL Cluster Configuration
11/15
9ere 4ILL N38 be overritten in t9e mys#l$%luster$server installation remember t9is
passord.
su$o aptD(et install !ysGlDclusterDserver
/ccept any e#tra pac"a(es these installations nee$ it will ta"e about 200M3 total for both.
$on%guration
This is the brea$ an$ butter of a MySQL Cluster installation. &n a pro$uction environ!ent
you woul$ run with !ore than 2 $ata no$es across !ore than one physical !achine on the
sa!e networ". There shoul$ always be as little latency between no$es as possible. &f you $o
choose to run 5Ms on a physical host you shoul$ never overallocate F/M to the no$es the
$atabase is !ainly store$ in F/M an$ overallocation !eans so!e $ata will be place$ into the
hosts swap space which increases latency in or$ers of 10Hs 100Hs or even 1000Hs in the worst
cases.
&he Management Node
This no$e is the brain of the cluster. *ithout the $ata no$es can lose sync an$ cause all sorts
of inconsistencies. &n a pro$uction environ!ent you have at least 2 !ana(e!ent no$es -the
confi(uration chan(es sli(htly an$ will be note$ in the files here. Iere is the confi(uration
file -@etc@!ysGl@n$bJ!(!$.cnf)
K?737 79/ULT
?o%fFeplicas2
7ataMe!ory2:M N Iow !uch !e!ory to allocate for $ata stora(e
&n$e#Me!ory1=M N Iow !uch !e!ory to allocate for in$e# stora(e
N or 7ataMe!ory an$ &n$e#Me!ory we have use$ the
N $efault values. Since the ;worl$< $atabase ta"es up
N only about 00A3 this shoul$ be !ore than enou(h for
N this e#a!ple Cluster setup.
KMOSQL7 79/ULT
K?73JMEM7 79/ULT
KTC8 79/ULTN Section for the cluster !ana(e!ent no$e
K?73JMEM7
N &8 a$$ress of the !ana(e!ent no$e -this syste!
Iost?a!e162.1:.>.1,4
Nor !ultiple !ana(e!ent no$es we ust create !ore K?73JMEM7 sections for each no$e
N Section for the stora(e no$es
K?737
N &8 a$$ress of the first stora(e no$e
8/9/2019 MySQL Cluster Configuration
12/15
Iost?a!e162.1:.>.1,2
7ata7ir@var@lib@!ysGlDcluster
3ac"up7ata7ir@var@lib@!ysGlDcluster@bac"up
7ataMe!ory12M
K?737N &8 a$$ress of the secon$ stora(e no$e
Iost?a!e162.1:.>.1,,
7ata7ir@var@lib@!ysGlDcluster
3ac"up7ata7ir@var@lib@!ysGlDcluster@bac"up
7ataMe!ory12M
N one KMOSQL7 per stora(e no$e
NThese $o not reGuire any confi(uration they are the ;frontDen$< access to our $ata
NTheir a$$resses can be assi(ne$ when they connect
KMOSQL7KMOSQL7
This confi(uration assu!es 2 thin(sB first that your no$es are isolate$ on their own networ"
an$ all the !achines on it are truste$ -5L/? the! onto their own networ" $a!nit. Secon$
it assu!es you are (oin( to run two !ysGl$ instances -& run the! on the $ata no$es
the!selves an$ balance the loa$ with a 4th server usin( !ysGlDpro#y.
&he 'ata Nodes
The $ata no$es are !uch easier to confi(ure we can use the confi(uration that was installe$an$ a$$ 4 lines. Iere are the chan(es that nee$ to be !a$e -@etc@!ysGl@!y.cnf)
4e add t9is to t9e e(isting se%tion
K!ysGl$
n$bcluster
n$bDconnectstrin(162.1:.>.1,4 NThis is the !ana(e!ent no$e
Nn$bDconnectstrin(.1,4 host162.1:.>.1,P This is if we ha$e T*%
!ana(e!ent no$es one on 162.1:.>.1,4 an$ one on 162.1:.>.1,
4e add t9is se%tion at t9e bottom o t9e ile
K!ysGlJcluster
n$bDconnectstrin(162.1:.>.1,4
Nn$bDconnectstrin(.1,4 host162.1:.>.1,P &f we ha$ two !ana(e!ent
no$es
%ne thin( !issin( on the $ata no$es is the bac"up $irectory & have reference$ in the
n$bJ!(!$.cnf file. The followin( co!!an$s will create the! an$ set their per!isisons -$o
this on each $ata no$e)
8/9/2019 MySQL Cluster Configuration
13/15
su$o !"$ir @var@lib@!ysGlDcluster@bac"up
su$o chown !ysGl)!ysGl DF @var@lib@!ysGlDcluster
(ringing it )nline
3rin(in( the whole arran(e!ent online involves a very specific or$erin()
%n the !ana(e!ent no$e)
su$o @etc@init.$@!ysGlDn$bD!(! restart
%n the $ata no$es -$o this on all of the $ata no$es first)
su$o @etc@init.$@!ysGlDn$b restart
%nce all the $ata no$es have their n$b $ae!ons restarte$)
su$o @etc@init.$@!ysGl restart
This last one will start the !ysGl $ae!ons an$ assu!es you are runnin( the! on the $ata
no$es.
&esting the $luster
&he Nodes are $onnected
irst off we want to verify that the cluster is runnin( properlyB run the followin( on the
!ana(e!ent no$e)
su$o n$bJ!(!
!(! show
Oou shoul$ see at least separate no$es the first two are the $ata no$es !i$$le ones are the
!ana(e!ent no$es an$ lastly you will see the !ysGl $ae!ons. &f the $ata no$es are stuc" in
the startin( state a Guic" restart shoul$ fi# the! 7% ?%T RUST TO89 F93%%T.
ro! n$bJ!(!
!(! shut$own
&ssuin( the shut$own co!!an$ fro! within n$bJ!(! will brin( the cluster $own. Oou can
then safely reboot the $ata no$es however !a"e sure to restart the !ana(e!ent no$e first as
the $ata no$es will co!e up without it otherwise -shoul$ probably ust reboot the
!ana(e!ent no$e-s then the $ata no$es for (oo$ !easure. &f everythin( (oes well you
shoul$ be set.
&est 'atabases
8/9/2019 MySQL Cluster Configuration
14/15
Connect to the first $ata no$e an$ run the followin( co!!an$s)
!ysGl Du root Dp
!ysGl show $atabasesB
Oou shoul$ see a few $atabases lets create a test $atabase an$ a$$ a table to it)
!ysGl create $atabases testJ$bB
!ysGl use testJ$bB
!ysGl create table testJtable -ival int-1 en(inen$bclusterB
!ysGl insert into testJtable values-1B
!ysGl select fro! testJtableB
Oou shoul$ see one value 1 in the table. ?ow connect to one of the other $ata no$es an$ you
shoul$ be able to $o the followin()
su$o !ysGl Du root Dp
!ysGl show $atabasesB
This shoul$ show the $atabase we create$ on the first no$e testJ$bB
!ysGl use testJ$bB
!ysGl select fro! testJtableB
&f all is well this shoul$ show the sa!e value as we ha$ before con(ratulations your cluster is
wor"in(.
*d+anced Setu * Load (alancer
This is actually the easier part of the (ui$e. %n a 4th server install !ysGlDpro#y)
su$o aptD(et install !ysGlDpro#y
?e#t lets start the pro#y an$ have it connect to our two $ata no$es)
screen DS pro#y
!ysGlDpro#y pro#yDbac"en$Da$$resses162.1:.>.1,2),,0: pro#yDbac"en$D
a$$resses162.1:.>.1,,),,0:
8/9/2019 MySQL Cluster Configuration
15/15
CTFL/ 7
This starts the pro#y an$ allows it to balance across the two no$es & specifie$ in !y
confi(uration. &f you want to specify a no$e as rea$Donly substitute pro#yDbac"en$D
a$$resses with pro#yDrea$DonlyDbac"en$Da$$resses
Let's connect to the pro#y an$ see if it wor"s)
!ysGl Du root Dp Dh 126.0.0.1 D8 4040
!ysGl use testJ$bB
!ysGl select fro! testJtableB
&f all is wor"in( well you will see the sa!e thin(s as if you were connecte$ to your actual
!ysGl instances
Top Related