HOW TO INSTALL ,CONFIGURE, MySQL ClUSTER

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 without any specific requirements on hardware or software. It also does not have any single point of failure because each component has its own memory and disk.

MySQL Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB. In our documentation, the term NDB refers to the part of the setup that is specific to the storage engine, whereas “MySQL Cluster” refers to the combination of MySQL and the NDB storage engine.

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:

1. simple Multi-computer How-To

This section is a “How-To” that describes the basics for how to plan, install, configure, and run a MySQL Cluster

This section covers hardware and software requirements; networking issues; installation of MySQL Cluster; configuration issues; starting, stopping, and restarting the cluster; loading of a sample database; and performing queries.

2.2 BasicAssumptions

This How-To makes the following assumption

2.2.1

The cluster setup has three nodes,each on a separate host,and each with a fixed network address,My equinments are as followings.......

NODE IP Address

Management (GMG) node 60.173.7.32

Data (NDBD) node 'A' 60.173.7.35

Data (NDBD) node 'B' 218.107.243.2

1. Mysql Cluster installation

noet:Each Mysql cluster host computer running data or sql nodes must have installed on it a mysql server binary ,For management nodes, it is not necessary to install the MySQL server binary, but you do have to install the MGM server daemon and client binaries (ndb_mgmd and ndb_mgm, respectively). This section covers the steps necessary to install the correct binaries for each type of Cluster node.

MySQL AB provides precompiled binaries that support Cluster, and there is generally no need to compile these yourself. Therefore, the first step in the installation process for each cluster host is to download the file,you can download it in the below link address,if you are use linux ,download it easily.

#wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.27-linux-i686-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

Note: After completing the installation, do not yet start any of the binaries. We will show you how to do so following the configuration of all nodes.

3.2 Storage Installation

On each of the two machines designated to host storage nodes, perform the following steps as the system root user:

Check your /etc/passwd and /etc/group files (or use whatever tools are provided by your operating system for manging users and groups) to see whether there is already a mysql group and mysql user on the system. Some OS distributions create these as part of the operating system installation process. If they are not already present, create a new mysql user group, and then add a mysql user to this group:

shell> groupadd mysql shell> useradd -g mysql mysql 3.3

Change location to the directory containing the downloaded file, unpack the archive, and create a symlink to the mysql directory. Note that the actual file and directory names will vary according to the MySQL version number.

shell> cd /server/soft shell> tar -C /server -xzvf mysql-max-5.0.27-linux-i686-glibc23.tar.gz shell> ln -s /server/mysql-max-5.0.27-linux-i686-glibc23 /server/mysql 3.4

Change location to the mysql directory and run the supplied script for creating the system databases:

shell> cd /server/mysql shell> scripts/mysql_install_db –user=mysql 3.5

Set the necessary permissions for the MySQL server and data directories:

shell> chown -R root . #pay attention to '.' shell> chown -R mysql data shell> chgrp -R mysql . #pay attention to '.' Note that the data directory on each machine hosting a data node is /usr/local/mysql/data. We will use this piece of information when we configure the management node. 3.6 Copy the MySQL startup script to the appropriate dircetory ,make it execuable,and set it start when the operating system is booted up: shell> cp support-files/mysql.server /etc/rc.d/init.d/ shell> chmod +x /etc/rc.d/init.d/mysql.server shell> chkconfig --add mysql.server (The startup scripts directory may vary depending on your operationing system and version -for emample in centos distributions ,it is /etc/init.d/) Here we use centos 'chkconfig' for creating links to the startup scripts;use whatever means is appropriate for this purpose on your operating system and distribution, such as update-rc.d on Debian.  Remember that the preceding steps must be performed separately for each machine on which a storage node is to reside. 4.1 Management Node Installation Installation for the management (MGM) node does not require installation of the mysqld binary. Only the binaries for the MGM server and client are required, which can be found in the downloaded archive. We assume that you have palced this archive file in /server/soft 4.2 Change location to the /var/tmp directory, and extract the ndb_mgm and ndb_mgmd from the archive into a suitable directory such as /usr/local/bin shell> cd /server/soft/ shell> tar -zxvf mysql-max-5.0.27-linux-i686-glibc23.tar.gz shell> cd mysql-max-5.0.27-linux-i686-glibc23 shell> cp /bin/ndb_mgm* /usr/local/bin (You can safely delete the directory created by unpacking the downloaded archive, and the files it contains, from /var/tmp once ndb_mgm and ndb_mgmd have been copied to the executables directory.) 4.3 Change location to the directory into which you copied the files, and then make both of them executable:  shell> cd /usr/local/bin shell> chmod +x ndb_mgm* 5.1 Multi-Computer Configuration For our three-node, we will need to write four configuration files, one per node/host.

Each data node requires a my.cnf file that provides two pieces of information: a connectstring telling the node where to find the MGM node, and a line telling the MySQL server on this host (the machine hosting the data node) to run in NDB mode.

The management node needs a config.ini file telling it how many replicas to maintain, how much memory to allocate for data and indexes on each data node, where to find the data nodes, where to save data to disk on each data node, and where to find any SQL nodes.

1. Configuring the Storage and SQL Nodes

The my.cnf file needed for the data nodes is fairly simple. The configuration file should be located in the /etc directory and can be edited using any text editor. (Create the file if it does not exist.) For example:

shell> vi /etc/my.cnf

We show vi being used here to create the file, but any text editor should work just as well.

For each data node in our example setup, my.cnf should look like this:

# Options for mysqld process: [MYSQLD]  ndbcluster # run NDB engine ndb-connectstring=60.173.7.2 # location of MGM node # Options for ndbd process: [MYSQL_CLUSTER]  ndb-connectstring=60.173.7.2 # location of MGM node After entering the preceding information, save this file and exit the text editor. Do this for the machines hosting data node “A”, data node “B”

Important: Once you have started a mysqld process with the ndbcluster and ndb-connectstring parameters in the [MYSQLD] in the my.cnf file as shown previously, you cannot execute any CREATE TABLE or ALTER TABLE statements without having actually started the cluster. Otherwise, these statements will fail with an error. This is by design.

Configuring the Management Node

The first step in configuring the MGM node is to create the directory in which the configuration file can be found and then to create the file itself. For example (running as root):

shell> mkdir /var/lib/mysql-cluster shell> cd /var/lib/mysql-cluster shell> vi config.ini For our representative setup, the config.ini file should read as follows: [NDBD DEFAULT] NoOfReplicas=2 # Number of replicas [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] [NDB_MGMD] hostname=60.173.7.2 # 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=60.173.7.35 # Hostname or IP address datadir=/server/mysql/data # Directory for this data node's data files # Options for data node "B": [NDBD] hostname=218.107.243.2 # Hostname or IP address datadir=/server/mysql/data # Directory for this data node's data files # SQL node options: [MYSQLD] [MYSQLD] 6.1  Initial Startup Starting the cluster is not very difficult after it has been configured. Each cluster node process must be started separately, and on the host where it resides. Although it is possible to start the nodes in any order, it is recommended that the management node be started first, followed by the storage nodes, and then finally by any SQL nodes: 6.2 On the management host, issue the following command from the system shell to start the MGM node process:  shell> /usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini Note that ndb_mgmd must be told where to find its configuration file, using the -f or --config-file option.

On each of the data node hosts, run this command to start the ndbd process for the first time:

shell>/server/mysql/bin/ndbd –initial shell>/server/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &

Note that it is very important to use the --initial parameter only when starting ndbd for the first time, or when restarting after a backup/restore operation or a configuration change. This is because the --initial option causes the node to delete any files created by earlier ndbd instances that are needed for recovery, including the recovery log files.

An exception to this is that --initial does not delete Disk Data files. If you do need to perform an initial restart of the cluster, you must delete any existing Disk Data log files and data files manually.

If all has gone well, and the cluster has been set up correctly, the cluster should now be operational. You can test this by invoking the ndb_mgm management node client. The output should look like that shown here, although you might see some slight differences in the output depending upon the exact version of MySQL that you are using: shell> /usr/local/bin/ndb_mgm Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @60.173.7.35 (Version: 5.0.27, Nodegroup: 0, Master) id=3 @218.107.243.2 (Version: 5.0.27, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @60.173.7.2 (Version: 5.0.27) [mysqld(API)] 2 node(s) id=4 @60.173.7.35 (Version: 5.0.27) id=5 @218.107.243.2 (Version: 5.0.27) 7.1 Testing this Mysql Cluster On the MGM node ,Use this command to view the state of cluster shell>/usr/local/bin/ndb_mgm --NDB Cluster –Management Client -- ndb_mgm>SHOW Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @60.173.7.35 (Version: 5.0.27, Nodegroup: 0, Master) id=3 @218.107.243.2 (Version: 5.0.27, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @60.173.7.2 (Version: 5.0.27) [mysqld(API)] 2 node(s) id=4 @60.173.7.35 (Version: 5.0.27) id=5 @218.107.243.2 (Version: 5.0.27) 7.3 On the data node 'A' run this command to execute the sql; shell>/server/mysql/bin/mysql -uroot test then run the sql on the command of mysql. 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); If all are right ,it is ok.........................