Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 知乎專欄 | 視頻教程 | About

19.7. MySQL Custer

The cluster need a lot of server for experiments, if you haven't any server for one, I have a good idea that using Vmware for you.

at first, let's create lots of virtual machine(You MUST have a third server). and then follow me step by step learning how to set up a mysql cluster on your virtual machine.

mgm 		192.168.0.1		# Management
data 		192.168.0.2		# Ndbd Node
data 		192.168.0.3		# Ndbd Node
sql			192.168.0.4		# SQL Node
sql			192.168.0.5		# SQL Node
	

19.7.1. Management node (MGM node)

neo@mgm:~$ sudo vim /var/lib/mysql-cluster/config.ini

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]
portnumber=2202

[NDB_MGMD]
hostname=192.168.0.1
datadir=/var/lib/mysql-cluster

[NDBD]
hostname=192.168.0.2
datadir=/var/lib/mysql-cluster

[NDBD]
hostname=192.168.0.3
datadir=/var/lib/mysql-cluster

[MYSQLD]
hostname=192.168.0.4

[MYSQLD]
hostname=192.168.0.5
		

19.7.2. Data node

my.cnf

neo@data:~$ sudo vim /etc/mysql/my.cnf

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.1	# the IP of the MANAGMENT SERVER
[mysql_cluster]
ndb-connectstring=192.168.0.1	# the IP of the MANAGMENT SERVER

		

19.7.3. SQL node

my.cnf

neo@sql:~$ sudo vim /etc/mysql/my.cnf

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.1	# the IP of the MANAGMENT SERVER
[mysql_cluster]
ndb-connectstring=192.168.0.1	# the IP of the MANAGMENT SERVER
		

19.7.4. Starting

  1. starting mgm

    neo@mgm:~$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini
    				
  2. initial ndbd

    neo@data:~$ sudo ndbd --initial
    				

    首次運行需要 --initial 參數,以後不需要。

19.7.5. Shutdown

MGM

$ sudo ndb_mgm -e shutdown
		

19.7.6. Testing

		
neo@mgm:~$ ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.0.2  (Version: 5.0.51, Nodegroup: 0)
id=3    @192.168.0.3  (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.1  (Version: 5.0.51)

[mysqld(API)]   2 node(s)
id=4    @192.168.0.4  (Version: 5.0.51)
id=5    @192.168.0.5  (Version: 5.0.51)

ndb_mgm>

		
		
[提示]與沒有使用簇的MySQL相比,在MySQL簇內操作數據的方式沒有太大的區別。

執行這類操作時應記住三點

  1. 表必須用ENGINE=NDB或ENGINE=NDBCLUSTER選項創建,或用ALTER TABLE選項更改,以使用NDB Cluster存儲引擎在 Cluster內複製它們。如果使用mysqldump的輸出從已有資料庫導入表,可在文本編輯器中打開SQL腳本,並將該選項添加到任何表創建語句,或用這類選項之一替換任何已有的ENGINE(或TYPE)選項。

  2. 另外還請記住,每個NDB表必須有一個主鍵。如果在創建表時用戶未定義主鍵,NDB Cluster存儲引擎將自動生成隱含的主鍵。(註釋:該隱含 鍵也將占用空間,就像任何其他的表索引一樣。由於沒有足夠的內存來容納這些自動創建的鍵,出現問題並不罕見)。

  3. 當你在一個節點上運行create database mydb;你去其他sql node上執行show databases;將不能看到mydb,你需要創建它,然後use mydb; show tables;你將看到同步的表。

SQL Node 1

		
neo@sql:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)

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

mysql> create database cluster;
Query OK, 1 row affected (0.00 sec)

mysql> use cluster
Database changed
mysql> create table city( id mediumint unsigned not null auto_increment primary key, name varchar(20) not null default '' ) engine = ndbcluster default charset utf8;
Query OK, 0 rows affected (1.07 sec)

mysql> insert into city values(1, 'Shenzhen');
Query OK, 1 row affected (0.12 sec)

mysql> insert into city values(2, 'Guangdong');
Query OK, 1 row affected (0.00 sec)

		
		

SQL Node 2

		
neo@sql:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mydb               |
| mysql              |
| neo                |
+--------------------+
6 rows in set (0.13 sec)

mysql> create database cluster;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster            |
| example            |
| mydb               |
| mysql              |
| neo                |
+--------------------+
6 rows in set (0.13 sec)

mysql> use cluster;
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> show tables;
+-------------------+
| Tables_in_cluster |
+-------------------+
| city              |
+-------------------+
1 row in set (0.01 sec)

mysql> select * from city;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shenzhen  |
|  2 | Guangdong |
+----+-----------+
2 rows in set (0.03 sec)

mysql>