- Create a configuration file as below:
conf/config.ini
[ndb_mgmd]
hostname=node1
datadir=/home/mysql/my_cluster/ndb_data
nodeid=1
[ndbd default]
noofreplicas=2
datadir=/home/mysql/my_cluster/ndb_data
[ndbd]
hostname=node1
nodeid=3
[ndbd]
hostname=node2
nodeid=6
[mysqld]
nodeid=50
- Start the MySQL Cluster Management Server with the setting
[mysql@node1 my_cluster]$ $HOME/mysqlc/bin/ndb_mgmd -f conf/config.ini --initial --configdir=$HOME/my_cluster/conf/
MySQL Cluster Management Server mysql-5.1.51 ndb-7.1.10
2011-05-18 13:44:33 [MgmtSrvr] WARNING -- at line 18: Cluster configuration warning:
arbitrator with id 1 and db node with id 3 on same host node1
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
[mysql@node1 my_cluster]$ $HOME/mysqlc/bin/ndbd -c node1:1186
2011-05-18 14:58:09 [ndbd] INFO -- Angel connected to 'node1:1186'
2011-05-18 14:58:09 [ndbd] INFO -- Angel allocated nodeid: 3
[mysql@node2 my_cluster]$ $HOME/mysqlc/bin/ndbd -c node1:1186
2011-05-18 14:58:23 [ndbd] INFO -- Angel connected to 'node1:1186'
2011-05-18 14:58:23 [ndbd] INFO -- Angel allocated nodeid: 6
[mysql@node1 my_cluster]$ $HOME/mysqlc/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=3 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)
id=6 @10.10.249.9 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10)
[mysqld(API)] 1 node(s)
id=50 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10)
alternatively
[mysql@node2 my_cluster]$ $HOME/mysqlc/bin/ndb_mgm -c node1
NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: node1:1186
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=3 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)
id=6 @10.10.249.9 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10)
[mysqld(API)] 1 node(s)
id=50 @10.10.249.8 (mysql-5.1.51 ndb-7.1.10)
Note: To start the storage node for the first time, use the command below. You should only pass the --initial option the first time the daemon is started, or any time you want to reload the configuration. Using this option removes all node recovery files.
my.cnf
[MYSQL_CLUSTER]
ndb-connectstring=<ip of management node>
/user/local/mysql/bin/ndbd --initial
- start the SQL node
[MYSQLD]
ndbcluster
ndb-connectstring=<ip of management node>[:<port>]
[mysql@node1 my_cluster]$ $HOME/mysqlc/bin/mysqld --defaults-file=$HOME/my_cluster/conf/my.cnf &
[mysql@node1 my_cluster]$ $HOME/mysqlc/bin/mysqld --defaults-file=$HOME/my_cluster/conf/my.cnf --federated &
[1] 25648
[mysql@node1 my_cluster]$ 110518 15:04:44 [Note] Plugin 'FEDERATED' is disabled.
110518 15:04:44 InnoDB: Started; log sequence number 0 44233
110518 15:04:44 [Note] NDB: NodeID is 50, management server 'localhost:1186'
110518 15:04:45 [Note] NDB[0]: NodeID: 50, all storage nodes connected
110518 15:04:45 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
110518 15:04:45 [Note] Starting Cluster Binlog Thread
110518 15:04:45 [Note] Event Scheduler: Loaded 0 events
110518 15:04:46 [Note] NDB: Creating mysql.ndb_schema
110518 15:04:46 [Note] NDB: Flushing mysql.ndb_schema
110518 15:04:46 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema110518 15:04:46 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
110518 15:04:46 [Note] NDB: Creating mysql.ndb_apply_status
110518 15:04:46 [Note] NDB: Flushing mysql.ndb_apply_status
110518 15:04:46 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_apply_status
110518 15:04:46 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2011-05-18 15:04:46 [NdbApi] INFO -- Flushing incomplete GCI:s < 187/4
2011-05-18 15:04:46 [NdbApi] INFO -- Flushing incomplete GCI:s < 187/4
110518 15:04:46 [Note] NDB Binlog: starting log at epoch 187/4
110518 15:04:46 [Note] NDB Binlog: ndb tables writable
110518 15:04:46 [Note] /home/mysql/mysqlc/bin/mysqld: ready for connections.
Version: '5.1.51-ndb-7.1.10-cluster-gpl' socket: '/tmp/mysql.sock' port: 5000 MySQL Cluster Server (GPL)
- test
$HOME/mysqlc/bin/mysql -h 127.0.0.1 -P 5000 -u root
grant all on * to root@'node2';
CREATE TABLE customer (
customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10)
) ENGINE=NDBCLUSTER;
INSERT INTO customer VALUES (1,'Mike'),
(2,'Jay'),
(3,'Johanna'),
(4,'Michael'),
(5,'Heidi'),
(6,'Ezra');
Note: When creating databases to be used in MySQL Cluster, you must issue the CREATE DATABASE statement on each SQL node in the cluster, as the statement isn't propagated to each node in the cluster.
kill one data node, and see the data is still available!
- shutdown safely
$HOME/mysqlc/bin/mysqladmin -u root -h 127.0.0.1 -P 5000 shutdown
$HOME/mysqlc/bin/ndb_mgm -e shutdown
- Cluster Backup and Restore
ndb_mgm> start backup;
Waiting for completed, this may take several minutes
Node 6: Backup 1 started from node 1
ndb_mgm> Node 6: Backup 1 started from node 1 completed
StartGCP: 43412 StopGCP: 43415
#Records: 2062 #LogRecords: 0
Data: 51048 bytes Log: 0 bytes
When the backup has completed, each node will have a set of backup files: one for the data dictionary information, a second with the actual data, and a third with the transaction log with information about when and how data was stored in the nodes.
[mysql@node1 BACKUP-1]$ pwd
/home/mysql/my_cluster/ndb_data/BACKUP/BACKUP-1
[mysql@node1 BACKUP-1]$ ls -l
total 44
-rw-rw-r-- 1 mysql mysql 25928 May 19 16:03 BACKUP-1-0.3.Data
-rw-rw-r-- 1 mysql mysql 9704 May 19 16:03 BACKUP-1.3.ctl
-rw-rw-r-- 1 mysql mysql 52 May 19 16:03 BACKUP-1.3.log
To restore from this backup, the cluster database must first be empty. You can do this by shutting down the storage nodes and starting them back up with the --initial option, which cleans out the log and data files. You must issue the restore command on each storage node, but you also need to restore the metadata (table structures) on the first node to be restored. below shows the command that should be issued on the first node that's restored. You need to be in the directory where the backup files were created on the node.
mysqld must be down before running following ndb_restore commands
[mysql@node1 BACKUP-1]$ $HOME/mysqlc/bin/ndb_restore --restore_meta --restore_data --nodeid=3 --backupid=1
Nodeid = 3
Backup Id = 1
backup path = ./
Opening file './BACKUP-1.3.ctl'
Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.51 ndb-7.1.10
Stop GCP of Backup: 43414
Connected to ndb!!
Successfully restored table `test/def/customer`
Successfully restored table event REPL$test/customer
Successfully created index `PRIMARY` on `customer`
NDBT_ProgramExit: 0 - OK
Once the first node has been restored, you can issue the same statement on the remaining nodes, but without the --restore_meta option to finish the restore.
[mysql@node2 BACKUP-1]$ $HOME/mysqlc/bin/ndb_restore --ndb-mgmd-host=node1 --restore_data --nodeid=6 --backupid=1
Nodeid = 6
Backup Id = 1
backup path = ./
Opening file './BACKUP-1.6.ctl'
Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.51 ndb-7.1.10
Stop GCP of Backup: 43414
Connected to ndb!!
Opening file './BACKUP-1-0.6.Data'
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(6) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1
_____________________________________________________
Processing data in table: test/def/customer(7) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(4) fragment 1
Opening file './BACKUP-1.6.log'
Restored 4 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK
then start mysql node, and run mysql client to test the customer table is still with the record.
- troubleshooting: check ndb log and trace
The cluster log file is named ndb_<id>_cluster.log
Node log file is named ndb_<id>_out.log
[mysql@node1 ndb_data]$ ls -l
total 15024
-rw-rw-r-- 1 mysql mysql 76105 May 18 14:58 ndb_1_cluster.log
-rw-r--r-- 1 mysql mysql 586 May 18 14:57 ndb_1_out.log
-rw-r--r-- 1 mysql mysql 5 May 18 14:57 ndb_1.pid
-rw-rw-r-- 1 mysql mysql 6556 May 18 14:44 ndb_3_error.log
drwxr-x--- 9 mysql mysql 4096 Apr 15 16:35 ndb_3_fs
-rw-r--r-- 1 mysql mysql 50435 May 18 14:44 ndb_3_out.log
-rw-r--r-- 1 mysql mysql 5 May 18 14:44 ndb_3.pid
-rw-rw-r-- 1 mysql mysql 976907 May 18 13:46 ndb_3_trace.log.1
-rw-rw-r-- 1 mysql mysql 1063377 May 18 14:34 ndb_3_trace.log.10
-rw-rw-r-- 1 mysql mysql 1058304 May 18 14:42 ndb_3_trace.log.11
-rw-rw-r-- 1 mysql mysql 1090931 May 18 14:43 ndb_3_trace.log.12
-rw-rw-r-- 1 mysql mysql 1064753 May 18 14:44 ndb_3_trace.log.13
-rw-rw-r-- 1 mysql mysql 976881 May 18 13:53 ndb_3_trace.log.2
-rw-rw-r-- 1 mysql mysql 216721 May 18 13:55 ndb_3_trace.log.3
-rw-rw-r-- 1 mysql mysql 968714 May 18 13:56 ndb_3_trace.log.4
-rw-rw-r-- 1 mysql mysql 976638 May 18 13:57 ndb_3_trace.log.5
-rw-rw-r-- 1 mysql mysql 976738 May 18 14:03 ndb_3_trace.log.6
-rw-rw-r-- 1 mysql mysql 974720 May 18 14:06 ndb_3_trace.log.7
-rw-rw-r-- 1 mysql mysql 969724 May 18 14:10 ndb_3_trace.log.8
-rw-rw-r-- 1 mysql mysql 569893 May 18 14:20 ndb_3_trace.log.9
-rw-rw-r-- 1 mysql mysql 2 May 18 14:44 ndb_3_trace.log.next
-rw-rw-r-- 1 mysql mysql 1566 May 18 14:44 ndb_4_error.log
drwxr-x--- 9 mysql mysql 4096 Apr 15 16:35 ndb_4_fs
-rw-r--r-- 1 mysql mysql 16149 May 18 14:44 ndb_4_out.log
-rw-r--r-- 1 mysql mysql 5 May 18 14:44 ndb_4.pid
-rw-rw-r-- 1 mysql mysql 1060567 May 18 14:42 ndb_4_trace.log.1
-rw-rw-r-- 1 mysql mysql 1072896 May 18 14:43 ndb_4_trace.log.2
-rw-rw-r-- 1 mysql mysql 1060959 May 18 14:44 ndb_4_trace.log.3
-rw-rw-r-- 1 mysql mysql 1 May 18 14:44 ndb_4_trace.log.next
drwxr-x--- 9 mysql mysql 4096 May 18 14:58 ndb_5_fs
-rw-r--r-- 1 mysql mysql 3420 May 18 14:58 ndb_5_out.log
-rw-r--r-- 1 mysql mysql 5 May 18 14:58 ndb_5.pid
