MySQL Cluster

Algemeen

MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The shared-nothing architecture allows the system to work with very inexpensive hardware, and with a minimum of specific requirements for hardware or software

MySQL Cluster is designed not to have any single point of failure. For this reason, each component is expected to have its own memory and disk, and the use of shared storage mechanisms such as network shares, network filesystems, and SANs is not recommended or supported.

A MySQL Cluster consists of a set of computers, each running a one or more processes which may include a MySQL server, a data node, a management server, and (possibly) a specialized data access programs. The relationship of these components in a cluster is shown here:

Op alle servers

• Installeer mySQL via de ports tree
• Gebruik een extra argument my het Make commando

# cd /usr/ports/databases/mysql56-server/
# make WITH_NDB=yes
# make WITH_NDB=yes install
# /usr/local/bin/mysql_install_db --user=mysql
# /usr/local/bin/mysqladmin -u root password 'xxx'

Edit het bestand /etc/rc.conf

mysql_enable="YES"

Management Node

Edit het bestand /var/lib/mysql-cluster/config.ini

# Options affecting ndbd processes on all data nodes:
[NDBD DEFAULT]    
NoOfReplicas=2    # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example Cluster setup.

# TCP/IP options:
[TCP DEFAULT]     
portnumber=2202   # This the default; however, you can use any
                               # port that is free for all the hosts in the cluster
                               # Note: It is recommended that you do not specify the 
                               # portnumber at all and allow the default value to be 
                               # used instead

# Management process options:
[NDB_MGMD]                      
hostname=192.168.1.122           # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

# Options for data node "A":
[NDBD]                          
                                # (one [NDBD] section per data node)
hostname=192.168.1.120           # Hostname or IP address
datadir=/var/db/mysql   # Directory for this data node's data files

# Options for data node "B":
[NDBD]                          
hostname=192.168.1.121           # Hostname or IP address
datadir=/var/db/mysql   # Directory for this data node's data files

# SQL node options:
[MYSQLD]                        
hostname=192.168.1.123          # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)
[MYSQLD] 
hostname=192.168.1.140

[MYSQLD] 
hostname=192.168.1.141

Starten van de Management Node

/usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini &

Starten van de console

/usr/local/bin/ndb_mgm

Laat de status zien binnen de console

NDB> SHOW

Data Node

Edit het bestand /etc/my.cnf

[MYSQLD]                        
ndbcluster
ndb-connectstring=192.168.1.122    # IP management server

[MYSQL_CLUSTER]                 
ndb-connectstring=192.168.1.122  # location of management server

Toevoegen in /etc/rc.local

/usr/local/libexec/ndbd &

API Node

Een API node kan op bijvoorbeeld een webserver worden gedraaid. Zo kan de scripting naar de localhost een connectie maken.

Edit het bestand /etc/my.cnf

[MYSQLD]                        
ndbcluster
ndb-connectstring=192.168.1.122    # IP management server

[MYSQL_CLUSTER]                 
ndb-connectstring=192.168.1.122  # location of management server

Aanmaken Table

Als je een tabel wilt aanmaken moet dit gebeuren met ‘ENGINE=NDBCLUSTER’

DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);

Links

How to Set Up Cluster