how to set up a MySQL Cluster

MySQL database administration questions.

how to set up a MySQL Cluster

Postby marian » Mon Dec 13, 2010 1:13 pm

Laurentiu Coica (claur member), wrote a nice document, in which he explains how to set up a MySQL Cluster,
on 2 hosts.

Hosts:

172.16.196.129 mysql-01.devel.local mysql-01 - MySQL Node 1
172.16.196.131 mysql-02.devel.local mysql-02 - MySQL Node 2
172.16.196.132 mysql-00.devel.local mysql-00 - MySQL Mgmt node

* On nodes A (mysql-01) and B (mysql-02), we install the following rpm packages:

[root@mysql-01 MySQL-Cluster]# ll
total 492791
-rwxrwxrwx 1 root root 241779137 Sep 28 12:36 mysql-cluster-gpl-7.1.5-linux-x86_64-glibc23.tar.gz
-rwxrwxrwx 1 root root 7642333 Sep 28 12:27 MySQL-Cluster-gpl-client-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 1321045 Sep 28 12:27 MySQL-Cluster-gpl-clusterj-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 119672546 Sep 28 12:27 MySQL-Cluster-gpl-debuginfo-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 15983181 Sep 28 12:27 MySQL-Cluster-gpl-devel-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 51149464 Sep 28 12:27 MySQL-Cluster-gpl-embedded-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 4439380 Sep 28 12:28 MySQL-Cluster-gpl-extra-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 1352645 Sep 28 12:28 MySQL-Cluster-gpl-management-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 23118835 Sep 28 12:28 MySQL-Cluster-gpl-server-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 3333268 Sep 28 12:28 MySQL-Cluster-gpl-shared-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 4126062 Sep 28 12:28 MySQL-Cluster-gpl-storage-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 17031427 Sep 28 12:29 MySQL-Cluster-gpl-test-7.1.5-1.rhel5.x86_64.rpm
-rwxrwxrwx 1 root root 11673487 Sep 28 12:29 MySQL-Cluster-gpl-tools-7.1.5-1.rhel5.x86_64.rpm

[root@mysql-01 MySQL-Cluster]# rpm -ivh MySQL-Cluster-gpl-client-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-extra-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-server-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-shared-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-storage-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-test-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-tools-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-clusterj-7.1.5-1.rhel5.x86_64.rpm

Preparing... ########################################### [100%]
1:MySQL-Cluster-gpl-clien########################################### [ 13%]
2:MySQL-Cluster-gpl-extra########################################### [ 25%]
3:MySQL-Cluster-gpl-serve########################################### [ 50%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h mysql-01.devel.local password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

4:MySQL-Cluster-gpl-share########################################### [ 63%]
5:MySQL-Cluster-gpl-stora########################################### [ 75%]
6:MySQL-Cluster-gpl-test ########################################### [ 88%]
7:MySQL-Cluster-gpl-tools########################################### [100%]
8:MySQL-Cluster-gpl-clust########################################### [100%]


* On node mysql-00 we install :

[root@mysql-00 MySQL-Cluster]# rpm -ivh MySQL-Cluster-gpl-management-7.1.5-1.rhel5.x86_64.rpm \
MySQL-Cluster-gpl-tools-7.1.5-1.rhel5.x86_64.rpm

1:MySQL-Cluster-gpl-manag########################################### [ 50%]
2:MySQL-Cluster-gpl-tools########################################### [100%]

[root@mysql-00 MySQL-Cluster]#

* On nodes mysql-01 and mysql-02 we create file /etc/my.cnf, having:

[root@mysql-01 lib]# cat /etc/my.cnf
[mysqld]
ndbcluster
datadir=/var/lib/mysql
ndb-connectstring=172.16.196.132

[mysql_cluster]
ndb-connectstring=172.16.196.132

* We initialize the mysq instances from the nodes mysql-01 and mysql-02, using:

[root@mysql-01 lib]# mysql_install_db --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h mysql-01.devel.local password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!


* On the management node, we create file /var/lib/mysql-cluster/config.ini, like:

[root@mysql-00 ~]# mkdir -p /var/lib/mysql-cluster
[root@mysql-00 ~]# cd /var/lib/mysql-cluster

[root@mysql-00 mysql-cluster]# cat /var/lib/mysql-cluster/config.ini
[ndb_mgmd]
hostname=172.16.196.132
datadir=/var/lib/mysql-cluster/ndb_data
id=1

[ndbd default]
noofreplicas=2
datadir=/var/lib/mysql-cluster/ndb_data

[ndbd]
hostname=172.16.196.129
DataDir=/var/lib/mysql
id=3

[ndbd]
hostname=172.16.196.131
DataDir=/var/lib/mysql
id=4

[mysqld]
hostname=172.16.196.129
id=13

[mysqld]
hostname=172.16.196.131
id=14

[mysqld]
id=50


* We initialize the management node, using:

[root@mysql-00 mysql-cluster]# /usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial
2010-09-28 16:10:23 [MgmtSrvr] INFO -- NDB Cluster Management Server. mysql-5.1.47 ndb-7.1.5
2010-09-28 16:10:23 [MgmtSrvr] INFO -- Reading cluster configuration from '/var/lib/mysql-cluster/config.ini'
[root@mysql-00 mysql-cluster]#


* Start up the deamons ndbd on mysql-01 and mysql-02, using:

[root@mysql-01 lib]# ndbd
2010-09-28 16:11:54 [ndbd] INFO -- Angel connected to '172.16.196.132:1186'
2010-09-28 16:11:54 [ndbd] INFO -- Angel allocated nodeid: 3
[root@mysql-01 lib]#

* Start up MySQL instances on the both nodes, and create the administrator account for them:

[root@mysql-01 MySQL-Cluster]# /etc/rc.d/init.d/mysql start
Starting MySQL.. SUCCESS!

[root@mysql-01 MySQL-Cluster]# /usr/bin/mysqladmin -u root password 'passw0rd'

[root@mysql-01 MySQL-Cluster]# /usr/bin/mysqladmin -u root -h mysql-01.devel.local password 'passw0rd'

[root@mysql-02 MySQL-Cluster]# /etc/rc.d/init.d/mysql restart
Starting MySQL.. SUCCESS!

[root@mysql-02 MySQL-Cluster]# /usr/bin/mysqladmin -u root password 'passw0rd'

[root@mysql-02 MySQL-Cluster]# /usr/bin/mysqladmin -u root -h mysql-02.devel.local password 'passw0rd'


[root@mysql-01 MySQL-Cluster]# /etc/rc.d/init.d/mysql status
SUCCESS! MySQL running (3464)

[root@mysql-02 MySQL-Cluster]# /etc/rc.d/init.d/mysql status
SUCCESS! MySQL running (3482)


Checks:

* From the management node, we execute:

[root@mysql-00 mysql-cluster]# /usr/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @172.16.196.129 (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0, Master)
id=4 @172.16.196.131 (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.196.132 (mysql-5.1.47 ndb-7.1.5)

[mysqld(API)] 2 node(s)
id=13 @172.16.196.129 (mysql-5.1.47 ndb-7.1.5)
id=14 @172.16.196.131 (mysql-5.1.47 ndb-7.1.5)

[root@mysql-00 mysql-cluster]#


* From node mysql-01 we run:

[root@mysql-01 ]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.47-ndb-7.1.5-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> create database clusterdb;
Query OK, 1 row affected (0.27 sec)

mysql> use clusterdb;
Database changed

mysql> create table simples (id int not null primary key) engine=ndb;
Query OK, 0 rows affected (0.87 sec)

mysql> insert into simples values (1),(2),(3),(4);
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from simples;
+----+
| id |
+----+
| 3 |
| 1 |
| 2 |
| 4 |
+----+
4 rows in set (0.00 sec)

mysql> quit
Bye


* From node mysql-02 we check the clusterdb instance, using:

[root@mysql-02 lib]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.47-ndb-7.1.5-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use clusterdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from simples;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
| 3 |
+----+
4 rows in set (0.00 sec)

mysql> quit
Bye


* On nodes, mysql-01 and mysql-02 we create a script to stat the ndbd daemon :

[root@mysql-01 init.d]# cat ./ndbd
#!/bin/sh
#
# ndbd This shell script takes care of starting and stopping
# the ndbd daemon.
#
# chkconfig: - 11 89
# description: ndbd is a daemon for managing MySQL cluster
# processname: ndbd
# pidfile: /var/run/ndbd.pid


# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

if [ -f /etc/sysconfig/ndbd ]; then
. /etc/sysconfig/ndbd
fi

# See how we were called.
case "$1" in
start)
# Start daemon.
[ ${NETWORKING} = "no" ] && exit 1
[ -x /usr/sbin/ndbd ] || exit 1
echo -n $"Starting ndbd daemon: "
daemon /usr/sbin/ndbd ${NDBDDARGS}
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/ndbd
;;
stop)
# Stop daemon.
echo -n $"Shutting down ndbd daemon: "
killproc ndbd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/ndbd
;;
status)
status ndbd
RETVAL=$?
;;
restart|reload)
$0 stop
$0 start
;;
condrestart)
[ -f /var/lock/subsys/ndbd ] && restart || :
;;
*)
echo $"Usage: $0 {start|stop|status|restart}"
RETVAL=3
;;
esac

exit $RETVAL


* We set up starting automatically for ndbd:

[root@mysql-01 init.d]# chkconfig --add ndbd
[root@mysql-01 init.d]# chkconfig --level 3 ndbd on

For any questions, feel free to reply.
marian
Site Admin
 
Posts: 16
Joined: Tue Jul 14, 2009 9:13 am

Invitations sent: 15
Successful invitations: 2

Re: how to set up a MySQL Cluster

Postby marian » Tue Sep 11, 2012 1:16 am

Very nice paper. Maybe Laurentiu will share some more with us in the future.
marian
Site Admin
 
Posts: 16
Joined: Tue Jul 14, 2009 9:13 am

Invitations sent: 15
Successful invitations: 2


Return to MySQL Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron