RedHat as 5中测试mysql cluster

依然使用VMware安装了RedHat as 5，再克隆了三台。
pc1:10.0.5.231( cluster management node)
pc2:10.0.5.233(data node1 , sql node 1)
pc3:10.0.5.234(data node2 , sql node 2)

前期安装配置
先在pc2,pc3中安装mysql(mysql-max-5.1.5-alpha-linux-i686-glibc23.tar.gz)
#groupadd mysql
#useradd -g mysql mysql
#cd /var/tmp
#tar -zxvf mysql-max-5.1.5-alpha-linux-i686-glibc23.tar.gz
#mv mysql-max-5.1.5-alpha-linux-i686-glibc23 /usr/local/
#cd /usr/local
#ln -s /usr/local/mysql-max-5.1.5-alpha-linux-i686-glibc23 mysql
#cd mysql
#scripts/mysql_install_db --user=mysql
#chown -R root .
#chown -R mysql data
#chgrp -R mysql .


并在/etc/my.cnf中加入
==========================================
[mysqld]
ndbcluster
ndb-connectstring=10.0.5.231

[mysql_cluster]
ndb-connectstring=10.0.5.231
==========================================

将mysql包中的ndb_mgm,ndb_mgmd提取到pc1的/usr/local/bin中
#tar -zxvf mysql-max-5.1.5-alpha-linux-i686-glibc23.tar.gz '*/bin/ndb_mgm*'
#mv ndb_mgm /usr/local/bin
#mv ndb_mgmd /usr/local/bin
#cd /usr/local/bin
#chmod +x ndb_mgm*

并建立/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 cluster
                  # Note: It is recommended beginning with MySQL 5.0 that
                  # you do not specify the portnumber at all and simply allow
                  # the default value to be used instead

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

# Options for data node "A":
[NDBD]                          # (one [NDBD] section per data node)
hostname=10.0.5.233           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's datafiles

# Options for data node "B":
[NDBD]                         
hostname=10.0.5.234           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's datafiles

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

==========================================
最后两行空的[MYSQLD]是为了在ndb_restore恢复数据时以免出现类似：
Could not alloc node id at 10.0.5.231 port 1186: No free node id found for mysqld(API)
的错误。

开始启动mysql cluster

1.在pc1中执行
#ndb_mgmd -f /var/lib/mysql-cluster/config.ini
#ndb_mgm
ndb_mgm>

2.在pc2、pc3上运行
#cd /usr/local/mysql/bin
# ./ndbd --initial
注意，仅应在首次启动ndbd时，或在备份／恢复或配置变化后重启ndbd时使用“--initial”参数，这很重要。原因在于，该参数会使节点删除由早期ndbd实例创建的、用于恢复的任何文件，包括恢复用日志文件。
#./safe_mysqld &

3.在pc1中
ndb_mgm>show
Cluster Configuration
----------------------------------
[ndbd(NDB)]     2 node(s)
id=2       @10.0.5.233     (Version: 5.1.5, Nodegroup: 0, Master)
id=3       @10.0.5.234     (Version: 5.1.5, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1        @10.0.5.231    (Version: 5.1.5)

[mysqld(API)]    4 node(s)
id=4        @10.0.5.233    (Version:   5.1.5)
id=5        @10.0.5.234    (Version: 5.1.5)
id=6   (not connected, accepting connect from any host)
id=7   (not connected, accepting connect from any host)

ndb_mgm>

测试数据
在pc2上
#cd /usr/local/mysql/bin
#./mysql
Welcome to the MySQL monitor. Commands end with; or \g
Your MySQL connection id is 5 to server version: 5.1.5-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database ndbtest;  

mysql> use ndbtest;
mysql> create table itbook(
id int primary key,
name char(20)
) engine = ndbcluster;
mysql> show create table itbook\G
*************************** 1. row ***************************
       Table: itbook
Create Table: CREATE TABLE `itbook` (
`id` int(11) NOT NULL,
`name` char(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into itbook values
(1, ‘Java’),
(2, ‘C++’),
(3, ‘Perl’),
(4, ‘MySQL’),
(5, ‘Oracle’);
mysql> select * from itbook;
+----+--------+
| id | name   |
+----+--------+
| 5 | Oracle |
| 1 | Java   |
| 2 | C++    |
| 4 | MySQL |
| 3 | Perl   |
+----+--------+
5 rows in set (0.03 sec)

在pc3中进入mysql
mysql> create database ndbtest;
mysql> use ndbtest;
mysql> show tables;
+-------------------+
| Tables_in_ndbtest |
+-------------------+
| itbook            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from itbook; (The same record of the table.)
+----+--------+
| id | name   |
+----+--------+
| 5 | Oracle |
| 1 | Java   |
| 2 | C++    |
| 4 | MySQL |
| 3 | Perl   |
+----+--------+
5 rows in set (0.03 sec)

测试备份数据功能
在pc1中ndb_mgm
ndb_mgm>start backup
Waiting for completed, this may take serval minutes
Node 2: Backup 2 started from node 1
Node 2: Backup 2 started from node 1 completed
StartGCP: 1278   StopGCP: 1281
   #Records: 4100 #LogRecords: 0
Data: 65760 bytes   Log: 0 bytes

ndb_mgm>

测试恢复数据功能
1.在pc2上将itbook表删除
mysql>drop table itbook;

2.重启所有节点
pc1:   ndb_mgmd -f /var/lib/mysql-cluster/config.ini
pc2:   ndbd --initial
pc3:   ndbd --initial
pc2:   safe_mysqld &
pc3:   safe_mysqld &

3.从ndb节点恢复数据（所有节点都要执行ndb_restore)
#cd /usr/local/mysql/bin
#ndb_restore -c 10.0.5.231 -n 2 -m -b 1 -r /mysql/BACKUP/BACKUP-1/

mgmd 为管理节点的ip
node_id为数据节点ID,在mgm的客户端通过show查看
-m 在第一个数据节点上执行,它的作用恢复数据元,数据元的作用:所有数据库表的名称和定义.在其他节点,上就不需要此参数了.
backup_id 就是备份的次数.也就是你在此start backup上,提示的那个id,如果不知道,可以到保存此备份的目录下看.
执行的结果:

# /usr/local/mysql/bin/ndb_restore -c 10.0.5.231 -n 2 -m -b 1 -r /usr/local/mysql/data/BACKUP/BACKUP-1/
Ndb version in backup files: Version 5.0.19
Connected to ndb!!
Successfully restored table test/def/ctest
_____________________________________________________
Processing data in table: test/def/itbook(2) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 0
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

执行过后,在其sql节点上,只有表结构，没有数据,这就是-m的作用!
(7)在其他数据节点上执行
# ./ndb_restore -c 10.0.5.231 -n 3 -b 1 -r /usr/local/mysql/data/BACKUP/BACKUP-1/
Ndb version in backup files: Version 5.0.19
Connected to ndb!!
_____________________________________________________
Processing data in table: test/def/itbook(2) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
Restored 1 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

自动备份数据
MySQL daily backup job in Linux system setting.
(Login management node: 192.168.4.203.)
a. Backup script:
# more /usr/local/mysql/ndb_backup/ndb_backup.sh
/usr/local/mysql/bin/ndb_mgm -e 'start backup'

b. make cron job:
# vi /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly
01 4 * * * root run-parts /usr/local/mysql/ndb_backup # Every date 4:00AM execute backup.