Triển khai MySQL Cluster trên Windows Server - Part 3

thanhdc

Super Moderator
Triển khai MySQL Cluster trên Windows Server - Part 3

1. Kết nối tới MySQL Server:



2. ENGINES:
So sánh ENGINES của MySQL Cluster và MySQL Community Server
MySQL Cluster có thêm ndbcluster và ndbinfo.
với Engine=ndbcluseter thì tables sẽ được đồng bộ và lưu trữ trên tất cả data node...

Code:
[TABLE="class: grid, width: 840"]
[TR]
[TD][COLOR=#000000][FONT=Verdana]Server version:5.6.11-ndb-7.3.2-cluster-commercial
-advanced MySQL Cluster Server - Advanced[/FONT][/COLOR][/TD]
[TD][COLOR=#000000][FONT=Verdana]Server version:5.5.15 MySQL Community Server (GPL)[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][COLOR=#000000][FONT=Verdana]mysql> SHOW ENGINES\G[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]1. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#ff0000][B][FONT=Verdana]ndbcluster[/FONT][/B][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Clustered, fault-tolerant tables[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Transactions: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]          XA: NO[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]  Savepoints: NO[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]2. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MRG_MYISAM[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Collection of identical MyISAM tables[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]3. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MyISAM[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: MyISAM storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]4. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#ff0000][B][FONT=Verdana]ndbinfo[/FONT][/B][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: MySQL Cluster system information 
storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]5. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]CSV[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: CSV storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]6. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]BLACKHOLE[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: /dev/null storage engine 
(anything you write to it disappears)[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]7. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]ARCHIVE[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Archive storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]8. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]FEDERATED[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: NO[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Federated MySQL storage engine[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Transactions: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]          XA: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]  Savepoints: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]9. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]PERFORMANCE_SCHEMA[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Performance Schema[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]10. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]InnoDB[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: DEFAULT[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Supports transactions, 
row-level locking, and foreign keys[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Transactions: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]          XA: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]  Savepoints: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]11. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MEMORY[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Hash based, stored in memory, 
useful for temporary tables[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]11 rows in set (0.00 sec)[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]mysql>[/FONT][/COLOR][/TD]
[TD][COLOR=#000000][FONT=Verdana]mysql> SHOW ENGINES\G[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]1. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]FEDERATED[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: NO[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Federated MySQL storage engine[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Transactions: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]          XA: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]  Savepoints: NULL[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]2. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MRG_MYISAM[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Collection of identical MyISAM tables[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]3. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MyISAM[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: MyISAM storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]4. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]BLACKHOLE[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: /dev/null storage engine 
(anything you write to it disappears)[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]5. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]CSV[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: CSV storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]6. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]MEMORY[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Hash based, stored in memory, 
useful for temporary tables[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]7. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]ARCHIVE[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Archive storage engine[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]8. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]InnoDB[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: DEFAULT[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Supports transactions, row-level locking, 
and foreign keys[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Transactions: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]          XA: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]  Savepoints: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]9. row ***************************[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]      Engine: [/FONT][/COLOR][COLOR=#000000][FONT=Verdana]PERFORMANCE_SCHEMA[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Support: YES[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]     Comment: Performance Schema[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]9 rows in set (0.00 sec)[/FONT][/COLOR]

[COLOR=#000000][FONT=Verdana]mysql>[/FONT][/COLOR]

[/TD]
[/TR]
[/TABLE]


3. import database:
http://downloads.mysql.com/docs/world_innodb.sql.gz


mysql -u root -p world < c:/mcm/world_innodb.sql
(Lưu ý để chạy được lệnh này bạn phải tạo trước database world... [mysql>create database world;])








4. Import lại database world:
mở file world_innodb.sql và sửa lại ENGINE=NDBCLUSTER
với ENGINE=NDBCLUSTER thì các table sẽ sẽ được đồng bộ và lưu trữ trên tất cả các data node:









5. CREATE TABLE:
Tạo một số table mẫu trong database test:
Code:
[COLOR=#000000][FONT=Verdana]CREATE TABLE svuit (id INT,data VARCHAR(100));[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]CREATE TABLE example (id INT,data VARCHAR(100));[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]CREATE TABLE example_innodb (id INT, data VARCHAR(100)) ENGINE=innodb;[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]CREATE TABLE example_autoincrement (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100));[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]INSERT INTO example_autoincrement (data) VALUES ('Hello world');[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]SELECT * FROM  example_autoincrement;[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]CREATE TABLE example_timestamp (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100), cur_timestamp TIMESTAMP(6)); [/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]INSERT INTO example_timestamp (data) VALUES ('The time of creation is:');[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]SELECT * FROM example_timestamp;[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]UPDATE example_timestamp SET data='The current timestamp is: ' WHERE id=1;[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]SELECT * FROM example_timestamp;[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]CREATE TABLE example_default_now ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100), created TIMESTAMP DEFAULT NOW());[/FONT][/COLOR]

6. ALTER TABLE:
Sử dụng lệnh ALTER TABLE table_name ENGINE=NDBCLUSTER để chuyển thay đổi ENGINE của table thành NDBCLUSTER
Table svuit và example được thay đổi ENGINE=NDBLUSTER nên sẽ được đồng bộ và lưu trữ trên cả 2 data node:

 
Last edited:
Top