MySQL Cluster 7.0.8a primer instalacije

Evo jedan primer kako da instalirate klaster na vaš linux server. Kažem linux ali radiće na isti način na svakom unixu (*bsd, osx, *solaris…). Za windoze u verziji 7.0 postoji neka beta verzija management noda i beta verzija data nodova ali to jadno radi. USA mornarica nam je tražila da im napravimo management nod za windoze i taman kada smo izbacili ovu alfu oni su odustali pošto su izbacili sve windoze sa kritičnih mesta na brodovima tako da teraju solaris sada svuda. Da li će se razvoj tog porta za udovice nastaviti ili ne – pojma nemam.

Krecemo od source-a tako sto raspakujemo mysql cluster source u neki radni direktorijum. Ja radim sa „commercial-pro“ sorsom ali isti je đavo i sa GPL sorsom.
Isto tako ja ceo mysql stavljam u /M/verzija/ tako da pošto sada radimo sa 7.0.8a klasterom, configure skripti kažemo –prefix=/M/7.0.8a, isto tako ne koristim mysql user za ove testove neko svoj.

tar zxvf mysql-cluster-com-pro-7.0.8a.tar.gz
cd mysql-cluster-com-pro-7.0.8a
./configure –prefix=/M/7.0.8a –with-mysqld-user=arhimed –with-ndb-test –with-plugins=ndbcluster,myisammrg,myisam,innobase,heap,federated,partition
make -j3
make install

Šta smo dobili?

cd /M/7.0.8a/
[arhimed@gedora10 7.0.8a]$ ls -la
total 44
drwxrwxr-x 11 arhimed arhimed 4096 2009-10-15 07:47 .
drwxr-xr-x 26 arhimed arhimed 4096 2009-10-15 07:46 ..
drwxrwxr-x 2 arhimed arhimed 4096 2009-10-15 07:47 bin
drwxrwxr-x 3 arhimed arhimed 4096 2009-10-15 07:46 include
drwxr-xr-x 2 arhimed arhimed 4096 2009-10-15 07:46 info
drwxrwxr-x 3 arhimed arhimed 4096 2009-10-15 07:46 lib
drwxrwxr-x 2 arhimed arhimed 4096 2009-10-15 07:47 libexec
drwxrwxr-x 4 arhimed arhimed 4096 2009-10-15 07:47 man
drwxrwxr-x 11 arhimed arhimed 4096 2009-10-15 07:47 mysql-test
drwxrwxr-x 4 arhimed arhimed 4096 2009-10-15 07:47 share
drwxrwxr-x 5 arhimed arhimed 4096 2009-10-15 07:47 sql-bench

Sada možete dodati /M/7.0.8a/lib/mysql u ld.so.config ako želite mada nije neophodno (ja ne dodajem). Ono što jeste zgodno u ovom trenutku je (ako koristite bash):

export PATH=/M/7.0.8a/libexec:/M/7.0.8a/bin:$PATH

Obratite pažnju da u GPL verziji možda ne postoji libexec direktorijum i da se sve što se u CGE verziji nalazi u libexec direktorijumu na GPL verziji nalazi u bin direktorijumu. Postoje neke patchnute verzije sa sbin dirom umesto libexec ali to je generalno to. Isto tako postoji varijanta da se neke skripte ne nalaze u bin nego u scripts ali snaćićete se.

Sada treba da napravimo config fajl za MySQL Cluster (koristite vaš omiljeni editor):

[arhimed@gedora10 7.0.8a]$ cat /M/7.0.8a/config.ini

[TCP DEFAULT]
SendBufferMemory=2MB
ReceiveBufferMemory=2MB

#default vrednosti za management nodove
[NDB_MGMD DEFAULT]

#gde ce da cuva log fajl
DataDir=/M/cluster_log

#da li da bude arbitrator ili ne
ArbitrationRank=1

#i management nod je na localhostu
hostname=localhost

#default vrednosti za data nodove
[NDBD DEFAULT]

#gde ce da napravi data node file system
DataDir=/M/cluster_data

#koliko kopija svakog podatka
NoOfReplicas=2

#koliko memorije za data
DataMemory=32M

#koliko memorije za indexe
IndexMemory=16M

#koliko maximalno ima ukupno atributa (svih kolona u svim tabelama) u svim cluster tabelama
MaxNoOfAttributes=3000

#koliko maximalno ordered indexa u svim cluster tabelama
MaxNoOfOrderedIndexes=512

#koliko maximalno tabela ima u klasteru
MaxNoOfTables=100

#buffer za redo log
RedoBuffer=8M

#maximalno transakcija u isto vreme
MaxNoOfConcurrentTransactions=1000

#max broj operacija u isto vreme (jedan statement je jedna operacija)
MaxNoOfConcurrentOperations=4000

#local check point – kompresovan
CompressedLCP=1

#backup kompresovan
CompressedBackup=1

#REDO LOG je velik FragmentLogFileSize*4*NoOfFragmentLogFiles
NoOfFragmentLogFiles= 10
FragmentLogFileSize=16M
InitFragmentLogFiles=full

#heart beat izmedju data nodova i izmedju data nodova i api nodova
HeartbeatIntervalDbDb=200
HeartbeatIntervalDbApi=200

#svi nodovi su na localhostu
hostname=localhost

#management nodovi
[NDB_MGMD]
Id=10

#data nodovi
[NDBD]
Id=1

[NDBD]
Id=2

#slotovi za SQL nodove
[MYSQLD]
Id=20

[MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD]

#slotovi za API nodove
[API] [API] [API] [API] [API] [API] [API]

[arhimed@gedora10 7.0.8a]$

Ako pogledamo pažljivo, management nod će da čuva log u /M/cluster_log a data nodovi će da kreiraju svoj FS u /M/cluster_data, treba sada da kreiramo ta dva direktorijuma.

mkdir /M/cluster_data
mkdir /M/cluster_log

Takođe od verzije 6.4/7.0 management nodovi čuvaju binarno zadnji učitani config fajl te je zgodno napravidi direktorijum za to:

mkdir /M/cluster_config

Sada da napravimo konfiguraciju za SQL nod:

[arhimed@gedora10 7.0.8a]$ cat /M/7.0.8a/my.cnf
[mysqld] datadir=/M/mysql_data
socket=/tmp/mysql.sock
user=arhimed

ndbcluster
ndb-connectstring=localhost
engine_condition_pushdown=1
ndb_index_stat_enable=0
ndb_use_exact_count=0
ndb_force_send=1
ndb_autoincrement_prefetch_sz=128
ndb-cluster-connection-pool=8

sysdate-is-now
thread_cache_size=64
event_scheduler=ON
skip-name-resolve
query_cache_type = 0
query_cache_size = 0
max_allowed_packet=8388608
max_connections=1100
group_concat_max_len=1M
ndb_report_thresh_binlog_epoch_slip=16
log-bin=/M/mysql_data/binlog

[mysql] socket=/tmp/mysql.sock

[mysql_client] socket=/tmp/mysql.sock

[mysqld_safe] socket=/tmp/mysql.sock
log-error=/M/mysql_data/error.log
pid-file=/M/mysql_data/mysqld.pid
open-files-limit=30000

[mysql_admin] socket=/tmp/mysql.sock

[mysqldump] socket=/tmp/mysql.sock

Najbitniji deo my.cnf-a je:

ndbcluster
ndb-connectstring=localhost

To kaže mysql-u da se okači na klaster i „gde se klaster nalazi“.
Videli smo da u configu piše da će mysql da drzi podatke u /M/mysql_data pa i to treba da kreiramo:

mkdir /M/mysql_data

Šta nam sada treba? da, treba da inicijalizujemo mysql bazu:

cd /M/7.0.8a/
bin/mysql_install_db –defaults-file=/M/7.0.8a/my.cnf

i završili smo sa setovanjem :)

Sada da to sve startujemo:

Prvo startujemo management node:

[arhimed@gedora10 7.0.8a]$ libexec/ndb_mgmd –config-file=/M/7.0.8a/config.ini –configdir=/M/cluster_config
2009-10-15 08:17:39 [MgmtSrvr] INFO — NDB Cluster Management Server. mysql-5.1.37 ndb-7.0.8a
2009-10-15 08:17:39 [MgmtSrvr] INFO — Reading cluster configuration from ‘/M/7.0.8a/config.ini’
[arhimed@gedora10 7.0.8a]$

Onda se okačimo na management nod da vidimo dal se podigao i šta kaže:

[arhimed@gedora10 7.0.8a]$ bin/ndb_mgm -c localhost
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=1 (not connected, accepting connect from localhost)
id=2 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=10 @localhost (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)] 23 node(s)
id=20 (not connected, accepting connect from any host)
id=21 (not connected, accepting connect from any host)
id=22 (not connected, accepting connect from any host)
id=23 (not connected, accepting connect from any host)
id=24 (not connected, accepting connect from any host)
id=25 (not connected, accepting connect from any host)
id=26 (not connected, accepting connect from any host)
id=27 (not connected, accepting connect from any host)
id=28 (not connected, accepting connect from any host)
id=29 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=31 (not connected, accepting connect from any host)
id=32 (not connected, accepting connect from any host)
id=33 (not connected, accepting connect from any host)
id=34 (not connected, accepting connect from any host)
id=35 (not connected, accepting connect from any host)
id=36 (not connected, accepting connect from any host)
id=37 (not connected, accepting connect from any host)
id=38 (not connected, accepting connect from any host)
id=39 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)
id=41 (not connected, accepting connect from any host)
id=42 (not connected, accepting connect from any host)

ndb_mgm> quit
[arhimed@gedora10 7.0.8a]$

Dakle kao što vidimo, management node čeka na konekcije sa data nodova i sql nodova i api nodova.

Sada startujemo data nodove (2 komada, dakle startujemo proces 2 puta):

[arhimed@gedora10 7.0.8a]$ libexec/ndbd
2009-10-15 08:19:46 [ndbd] INFO — Configuration fetched from ‘localhost:1186’, generation: 1
[arhimed@gedora10 7.0.8a]$ libexec/ndbd
2009-10-15 08:19:47 [ndbd] INFO — Configuration fetched from ‘localhost:1186’, generation: 1
[arhimed@gedora10 7.0.8a]$

I pogledamo sada šta kaze management node:

[arhimed@gedora10 7.0.8a]$ bin/ndb_mgm -c localhost
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=1 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, starting, Nodegroup: 0, Master)
id=2 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=10 @localhost (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)] 23 node(s)
id=20 (not connected, accepting connect from any host)
id=21 (not connected, accepting connect from any host)
id=22 (not connected, accepting connect from any host)
id=23 (not connected, accepting connect from any host)
id=24 (not connected, accepting connect from any host)
id=25 (not connected, accepting connect from any host)
id=26 (not connected, accepting connect from any host)
id=27 (not connected, accepting connect from any host)
id=28 (not connected, accepting connect from any host)
id=29 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=31 (not connected, accepting connect from any host)
id=32 (not connected, accepting connect from any host)
id=33 (not connected, accepting connect from any host)
id=34 (not connected, accepting connect from any host)
id=35 (not connected, accepting connect from any host)
id=36 (not connected, accepting connect from any host)
id=37 (not connected, accepting connect from any host)
id=38 (not connected, accepting connect from any host)
id=39 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)
id=41 (not connected, accepting connect from any host)
id=42 (not connected, accepting connect from any host)

ndb_mgm>

Kao sto vidimo, data nodovi se „startuju“ … sačekamo malo pa opet kažemo „show“:

ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=1 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0, Master)
id=2 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=10 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)] 23 node(s)
id=20 (not connected, accepting connect from any host)
id=21 (not connected, accepting connect from any host)
id=22 (not connected, accepting connect from any host)
id=23 (not connected, accepting connect from any host)
id=24 (not connected, accepting connect from any host)
id=25 (not connected, accepting connect from any host)
id=26 (not connected, accepting connect from any host)
id=27 (not connected, accepting connect from any host)
id=28 (not connected, accepting connect from any host)
id=29 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=31 (not connected, accepting connect from any host)
id=32 (not connected, accepting connect from any host)
id=33 (not connected, accepting connect from any host)
id=34 (not connected, accepting connect from any host)
id=35 (not connected, accepting connect from any host)
id=36 (not connected, accepting connect from any host)
id=37 (not connected, accepting connect from any host)
id=38 (not connected, accepting connect from any host)
id=39 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)
id=41 (not connected, accepting connect from any host)
id=42 (not connected, accepting connect from any host)

ndb_mgm> quit

I kao što vidimo, imamo startovana 2 data noda, 1 mgm nod i nijedan sql/api, dakle, sada treba da startujemo mysql

[arhimed@gedora10 7.0.8a]$ bin/mysqld_safe –defaults-file=/M/7.0.8a/my.cnf &
[1] 16055
[arhimed@gedora10 7.0.8a]$ 091015 08:22:19 mysqld_safe Logging to ‘/M/mysql_data/error.log’.
091015 08:22:19 mysqld_safe Starting mysqld daemon with databases from /M/mysql_data

Sada da zvirnemo opet šta kaže management deamon:

[arhimed@gedora10 7.0.8a]$ bin/ndb_mgm -c localhost
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=1 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0, Master)
id=2 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=10 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)] 23 node(s)
id=20 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=21 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=22 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=23 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=24 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=25 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=26 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=27 @127.0.0.1 (mysql-5.1.37 ndb-7.0.8)
id=28 (not connected, accepting connect from any host)
id=29 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=31 (not connected, accepting connect from any host)
id=32 (not connected, accepting connect from any host)
id=33 (not connected, accepting connect from any host)
id=34 (not connected, accepting connect from any host)
id=35 (not connected, accepting connect from any host)
id=36 (not connected, accepting connect from any host)
id=37 (not connected, accepting connect from any host)
id=38 (not connected, accepting connect from any host)
id=39 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)
id=41 (not connected, accepting connect from any host)
id=42 (not connected, accepting connect from any host)

Voila, evo ga sada i gomila SQL nodova. Ako prebrojimo videćemo da se mysql zakačio sa 8 konekcija. To je zato što smo mu u my.cnf-u rekli ndb-cluster-connection-pool=8.

Sada da probamo da kreiramo tabelu:

[arhimed@gedora10 7.0.8a]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.37-ndb-7.0.8a-log Source distribution

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

mysql> use test;
Database changed
mysql> create table t1 (id int auto_increment primary key, a int, b int) engine=ndbcluster;
Query OK, 0 rows affected (0.85 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
1 row in set (0.00 sec)

mysql>

Možemo i da pozovemo API program (ima ih nekoliko u bin/ndb_*)

[arhimed@gedora10 7.0.8a]$ bin/ndb_show_tables -c localhost
id type state logging database schema name
1 0 Online – DEFAULT-HASHMAP-240-2
3 SystemTable Online Yes sys def NDB$EVENTS_0
6 UserTable Online Yes mysql def ndb_apply_status
0 IndexTrigger Online – NDB$INDEX_8_CUSTOM
7 UserTable Online Yes test def t1
5 UserTable Online Yes mysql def NDB$BLOB_4_3
8 OrderedIndex Online No sys def PRIMARY
2 SystemTable Online Yes sys def SYSTAB_0
4 UserTable Online Yes mysql def ndb_schema
1 TableEvent Online – REPL$mysql/ndb_schema
2 TableEvent Online – NDB$BLOBEVENT_REPL$mysql/ndb_schema_3
3 TableEvent Online – REPL$mysql/ndb_apply_status
4 TableEvent Online – REPL$test/t1

NDBT_ProgramExit: 0 – OK

Sada vama ostavljam da iskonfigurisete drugi mysql server i okacite ga na ovaj klaster. Probate da ugasite jedan nod (možete da ga ubijete sa kill -9 na primer)…