ããMySQL 群éæ¯ä¸ç§ææ¯ï¼è¯¥ææ¯å
许å¨æ å
±äº«çç³»ç»ä¸é¨ç½²âå
åä¸âåâç£çä¸âæ°æ®åºç Cluster ãéè¿æ å
±äº«ä½ç³»ç»æï¼ç³»ç»è½å¤ä½¿ç¨å»ä»·ç硬件ï¼èä¸å¯¹è½¯ç¡¬ä»¶æ ç¹æ®è¦æ±ãæ¤å¤ï¼ç±äºæ¯ä¸ªç»ä»¶æèªå·±çå
ååç£çï¼ä¸åå¨åç¹æ
éãMySQL Cluster ç±ä¸ç»è®¡ç®æºææï¼æ¯å°è®¡ç®æºä¸åè¿è¡çå¤ç§è¿ç¨ï¼å
æ¬ MySQL æå¡å¨ï¼NDB Cluster çæ°æ®èç¹ï¼ç®¡çæå¡å¨ï¼ä»¥åï¼å¯è½åå¨çï¼ä¸é¨çæ°æ®è®¿é®ç¨åºã
管çæå¡å¨(MGMèç¹)è´è´£ç®¡ç Cluster é
ç½®æ件å Cluster æ¥å¿ãCluster ä¸çæ¯ä¸ªèç¹ä»ç®¡çæå¡å¨æ£ç´¢é
ç½®æ°æ®ãå½æ°æ®èç¹å
åºç°æ°çäºä»¶æ¶ï¼èç¹å°å
³äºè¿ç±»äºä»¶çä¿¡æ¯ä¼ è¾å°ç®¡çæå¡å¨ï¼ç¶åï¼å°è¿ç±»ä¿¡æ¯åå
¥ Cluster æ¥å¿ã
ç®åè½å¤è¿è¡ MySQL Cluster çæä½ç³»ç»æ LinuxãMac OS X å Solarisï¼ææ°ççæ¬å·²ç»æ¯æ Windows æä½ç³»ç»ã
MySQL 群éçæ°æ®èç¹ä¹é´çéä¿¡æ¯ä¸å å¯çï¼å¹¶ä¸éè¦é«éç带宽ï¼æ以建议æ群é建ç«å¨ä¸ä¸ªé«éå±åç½å
ï¼ä¸å»ºè®®è·¨ç½æ®µãè·¨å
¬ç½çé¨ç½²è¿ç§ç³»ç»ä½ç³»ã
MySQL 群éå为ä¸ç§èç¹ï¼ç®¡çèç¹ï¼æ°æ®èç¹åSQLèç¹ã
管çèç¹ï¼ä¸»è¦ç¨äºç®¡çå个èç¹ï¼è½å¤éè¿å½ä»¤å¯¹æ个èç¹è¿è¡éå¯ãå
³éãå¯å¨çæä½ãä¹è½å¤çè§å
¨é¨èç¹çå·¥ä½ç¶æã
æ°æ®èç¹ï¼ä¸»è¦æ¯å¯¹æ°æ®çåå¨ï¼ä¸æä¾å
¶ä»çæå¡ã
SQLèç¹ï¼ä¸»è¦æ¯å¯¹å¤æä¾SQLåè½ï¼ç±»ä¼¼ä¸å°æ®éç MySQL Serverã
èSQLèç¹åæ°æ®èç¹å¯ä»¥æ¯åä¸å°æºå¨ï¼ä¹å°±æ¯è¯´è¿å°æºå¨å³æ¯SQLèç¹ä¹æ¯æ°æ®èç¹ãå®ä»¬åªæ¯é»è¾å
³ç³»ä¸çååï¼å®é
é¨ç½²æ¶ï¼çè³ææçé¶æ®µé½å¯ä»¥ä½äºåä¸å°ç©çæºå¨ä¸ï¼åªæ¯é
ç½®è¾å¤æäºã
ä¸ã软件ä¸è½½æºå¨æä½ç¯å¢
é
ç½® MySQL 群éå¿
é使ç¨å
¶ç¾¤éçæ¬ï¼æ³¨æå MySQL Server çæ¬çåºå«ãæ¬æ以 Windows å¹³å°ä¸ç MySQL 群éçæ¬ MySQL Cluster 7.1.3 为ä¾ï¼æªè³2010å¹´6æåçæé«çæ¬ï¼ï¼è¿æ¯ MySQL Server 5.1 ç³»åçæ¬ä¹ä¸ï¼æ·»å äºç¾¤éçåè½ãä¸è½½å°å为ï¼
http://dev.mysql.com/downloads/clusterï¼éæ© mysql-cluster-gpl-noinstall-7.1.3-win32.zip æ件ï¼è¿æ¯ä¸ä¸ª for Windows 32ä½çæ¬çãå
å®è£
çäºè¿å¶çæ¬ã
æ ¹æ®èªå·±çæä½ç³»ç»çä½æ°ï¼ä¹å¯ä»¥éæ© 64 ä½çæ¬çãè¿æä¸ä¸ª 27.2M ç Windows(x86, 32-bit) çæ¬ï¼ä¸è½½ä¸æ¥éè¦èªå·±ç¼è¯ï¼æç»éªçé«çº§ç¨æ·å¯ä»¥éç¨ã
æ¬å®éªå¨ 2 å°å®è£
Windows Server 2003(sp2) çæºå¨ä¸è¿è¡ãèç¹åé
å IP é
ç½®å¦ä¸ï¼
第ä¸å°æºå¨ï¼IP 为 10.0.0.201ï¼ä½ä¸ºç®¡çèç¹(MGM)ï¼SQLèç¹1(SQL1)ï¼æ°æ®èç¹1(NDBD1)ã
第äºå°æºå¨ï¼IP 为 10.0.0.202ï¼ä½ä¸ºSQLèç¹2(SQL2)ï¼æ°æ®èç¹2(NDBD2)ã
管çèç¹æ好ä¸è¦ä¸æ°æ®èç¹é¨ç½²å¨åä¸å°æå¡å¨ä¸ï¼å¦åå¯è½ä¼å 为该æ°æ®èç¹æå¡å¨çå½æºï¼è导è´ç®¡çèç¹æå¡å¨çé®é¢ï¼ä»è导è´æ´ä¸ªç¾¤éç³»ç»çå´©æºã
äºï¼é
置管çèç¹ï¼
å¨ç¬¬ä¸å°æºå¨ä¸ï¼å»ºæ件夹 D:\mysql-clusterï¼å¨å
¶ä¸å»ºç«æ件 config.iniï¼å
容å¦ä¸ï¼
[NDBD DEFAULT]
NoOfReplicas=1
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Managment Server
[NDB_MGMD]
hostname=10.0.0.201
# Storage Engines
[NDBD]
hostname=10.0.0.201
datadir= D:\data
[NDBD]
hostname=10.0.0.202
datadir= D:\data
# SQL Engines
[MYSQLD]
hostname=10.0.0.201
[MYSQLD]
hostname=10.0.0.202
Cluster 管çèç¹çé»è®¤ç«¯å£æ¯1186ï¼æ°æ®èç¹çé»è®¤ç«¯å£æ¯ 2202ãä» MySQL 5.0.3å¼å§ï¼è¯¥éå¶å·²è¢«æ¾å®½ï¼Cluster è½å¤æ ¹æ®ç©ºé²ç端å£èªå¨å°ä¸ºæ°æ®èç¹åé
端å£ãå¦æä½ ççæ¬ä½äº5.0.22ï¼è¯·æ³¨æè¿ä¸ªç»èã
Cluster 管çèç¹ä½ä¸ºä¸ä¸ªæå¡ç«¯(éè¿è¿è¡ db_mgmd.exe ç¨åºè¯»åæ¬é
ç½®æ件æ¥å¯å¨)ï¼éè¿æ¬æºä¸ç客æ·ç«¯ ndb_mgm.exe æ¥è¿æ¥åæä½ã
ä¸ãé
ç½® MySQL æ°æ®åºæå¡å¨ï¼
å¨2å°æºå¨ä¸ï¼åå«ä¾æ¬¡æä½ã
解å mysql-cluster-gpl-noinstall-7.1.3-win32.zip æä»¶å° D:\mysql-cluster-gpl-noinstall-7.1.3-win32 æ件夹ä¸ï¼æ D:\mysql-cluster-gpl-noinstall-7.1.3-win32\bin å å° Windows çç³»ç» path ä¸ã
æå¼DOSå½ä»¤è¡çªå£ï¼é
ç½®å®ç³»ç»pathåï¼å¨å次æå¼æ°çå½ä»¤è¡çªå£ï¼ï¼æ§è¡ä»¥ä¸è¯å¥ï¼è®© MySQL ä½ä¸º Windows æå¡è¿è¡ï¼
mysqld.exe -install mysql
åå¨ Windows æå¡ç®¡ççé¢éï¼é
ç½® mysql æå¡ï¼æå¨å¯å¨ï¼ä¸è¦èªå¨å¯å¨ï¼ã
æ D:\mysql-cluster-gpl-noinstall-7.1.3-win32 ä¸çæ个å¤ç¨çé
ç½®æ件ï¼ä¾å¦my-small.iniï¼å¤å¶ä¸º my.ini æ件ã
å»ºç« D:\tmp æ件夹ã
åãé
ç½®SQLèç¹åæ°æ®èç¹ï¼
å¨2å°æºå¨ä¸ï¼åå«ä¾æ¬¡æä½ã
建ç«æ件夹 D:\dataã
ç¼è¾ NySQL é
ç½®æ件 D:\mysql-cluster-gpl-noinstall-7.1.3-win32\my.iniï¼å¨ [mysqld] èæ«å°¾å è¯å¥ï¼
#SQL群éèç¹
ndbcluster
ndb-connectstring=10.0.0.201
æäº ndbcluster è¯å¥ï¼mysql æå¡å°ä½ä¸ºç¾¤éç SQL èç¹å¯å¨ãmysqld.exe å½ä»¤å¸¦åæ° ândbcluster è¿è¡æ¯ä¸æ ·çææã
mysql æå¡è¿æ¥å°æ°æ®èç¹ç 2202 é»è®¤ç«¯å£ï¼æèªå¨åé
çå«çå¯ç¨ç«¯å£ã
ï¼é®é¢ï¼SQLèç¹å¦ä½è·å¾æ°æ®èç¹çIPå°åï¼æ¯å¦éè¿è¿æ¥ç®¡çèç¹ï¼åå¾æææ°æ®èç¹çIPå°åçå表ï¼ï¼
è¿éçè¿æ¥å符串çå¼å¨ MySQL æå¡å¯å¨æ¶ä½¿ç¨ï¼ç¨äºè¿æ¥å°ç®¡çèç¹ã
å¨æ«å°¾å è¯å¥ï¼
#NDBé群èç¹
[mysql_cluster]
ndb-connectstring=10.0.0.201
注ï¼å¥½å以ä¸å½¢å¼ä¹å¯ï¼
[ndbd]
ndb-connectstring=10.0.0.201
è¿éçè¿æ¥å符串çå¼å¨æ°æ®èç¹å¯å¨æ¶ä½¿ç¨ï¼ç¨äºè¿æ¥å°ç®¡çèç¹ã
äºï¼å¯å¨ç¾¤éåæå¡å¨
å¯å¨é¡ºåºä¾æ¬¡æ¯ï¼ç®¡çèç¹ãæ°æ®èç¹ãSQLèç¹ã
1ï¼å¯å¨ç®¡çèç¹
å¨ç¬¬ä¸å°æå¡å¨çDOSçªå£ï¼è¿è¡å½ä»¤ï¼
C:\>ndb_mgmd.exe -f d:\mysql-cluster.conf\config.ini âconfigdir=d:\mysql-cluster
注ï¼å¦æä¸å¸¦ âconfigdir=d:\mysql-cluster åæ°ï¼å°é»è®¤ä¸º C:\mysql\mysql-cluster æ件夹ã
å±å¹æ¾ç¤ºï¼
C:\>ndb_mgmd.exe -f d:\mysql-cluster.conf\config.ini âconfigdir=d:\mysql-cluster
2010-06-10 01:16:57 [MgmtSrvr] INFO â NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.3
2010-06-10 01:16:57 [MgmtSrvr] INFO â Reading cluster configuration from âd:\mysql-cluster.conf\config.ini'
2010-06-10 01:16:57 [MgmtSrvr] INFO â Got initial configuration from âd:\mysql-cluster.conf\config.ini', will try to set it when all ndb_mgmd(s) started
2010-06-10 01:16:57 [MgmtSrvr] INFO â Mgmt server state: nodeid 1 reserved for ip 10.0.0.201, m_reserved_nodes 1.
2010-06-10 01:16:57 [MgmtSrvr] INFO â Id: 1, Command port: *:1186
==INITIAL==
2010-06-10 01:16:57 [MgmtSrvr] INFO â Starting initial configuration change
2010-06-10 01:16:57 [MgmtSrvr] INFO â Configuration 1 commited
2010-06-10 01:16:57 [MgmtSrvr] INFO â Config change completed! New generation: 1
==CONFIRMED==
2ï¼å¯å¨æ°æ®èç¹
åå«å¨2å°æå¡å¨çDOSçªå£è¿è¡å½ä»¤ã
第ä¸æ¬¡ï¼æåå§å群éèç¹æ¶ï¼è¿è¡å½ä»¤ï¼
ndbd.exe âinitial
åå§åä¹åï¼åªè¿è¡ ndbd.exe å³å¯ãè¥å¸¦åæ° âinitial è¿è¡ï¼å°ä½¿æ£å¸¸è¿è¡ç群éç³»ç»ä¸ï¼æ°æ®èç¹çæ°æ®å
¨é¨ä¸¢å¤±ã
æ°æ®èç¹ä¾èµç®¡çèç¹æå¡å¨ï¼è¿è¡æ°æ®çèªå¨å¤å¶ååæ¥ï¼ä½¿å个æ°æ®èç¹çæ°æ®ä¿æä¸è´ï¼å¹¶å¨æ个æ°æ®èç¹æå¤å
³éåæ¢å¤åï¼è¿è¡æ°æ®çæ¢å¤é建ã
3ï¼å¯å¨SQLèç¹
æäº ndbcluster è¯å¥ï¼å¯å¨ mysql æå¡ï¼å°±å¯å¨äºSQLèç¹ãåºå¨å2ç§èç¹å¯å¨åï¼åå«å¨2å°æå¡å¨ä¸è¿è¡ã
å
ã群é管ç
å¨ææçæ°æ®èç¹åSQLèç¹æªå¯å¨ä¹åï¼è¿è¡ç¾¤é管çèç¹æå¡ç客æ·ç«¯ ndb_mgm.exeï¼åªè½è·å¾ä»¥ä¸ä¿¡æ¯ï¼
C:\>ndb_mgm.exe
â NDB Cluster â Management Client â
ndb_mgm> show
Cluster Configuration
âââââââ
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 10.0.0.201)
id=3 (not connected, accepting connect from 10.0.0.202)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 10.0.0.201)
id=5 (not connected, accepting connect from 10.0.0.202)
ndb_mgm>
说ææ°æ®èç¹ãSQLèç¹åæªè¿æ¥å°ç®¡çèç¹æå¡ã
å¨ææçæ°æ®èç¹åSQLèç¹æ£ç¡®å¯å¨ä¹åï¼å°è·å¾ä»¥ä¸ä¿¡æ¯ï¼
ndb_mgm> show
Cluster Configuration
âââââââ
[ndbd(NDB)] 2 node(s)
id=2 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0, Master)
id=3 @10.0.0.202 (mysql-5.1.44 ndb-7.1.3, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)
[mysqld(API)] 2 node(s)
id=4 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)
id=5 @10.0.0.202 (mysql-5.1.44 ndb-7.1.3)
ndb_mgm>
å
³é群éçDOSå½ä»¤ï¼
ndb_mgm -e shutdown
æå¨ ndb_mgm ç¯å¢ä¸æ§è¡ shutdown å½ä»¤ã
以ä¸å½ä»¤æå
³é管çèç¹æå¡åææçæ°æ®èç¹ãéæã强è¡å
³é群éç³»ç»ï¼å
³æºæå
³éè¿ç¨ï¼ï¼ä¼å¯¼è´æ°æ®æ²¡æå
¨é¨ååç£çè导è´çæ°æ®ä¸¢å¤±ã
å
³éSQLèç¹ç mysqld æå¡ï¼
C:\>net stop mysqlï¼æï¼
C:\>mysqladmin -u root shutdown
ä¸ãæµè¯
æ£å¸¸è¿è¡ç MySQL 群éç³»ç»ï¼éè¿SQLèç¹å¯ä»¥å¯¹æ°æ®èç¹è¿è¡æ°æ®åºæä½ï¼åæ°æ®èç¹å¯ä»¥èªå¨è¿è¡æ°æ®åæ¥ãæä¸ä¸ªæ°æ®èç¹å
³éåï¼ä¸å½±åSQLèç¹ç使ç¨ãæäºæ°æ®èç¹åºéåï¼å¯ä»¥è¿è¡æ¢å¤ãéè¦æ³¨æçæ¯ï¼SQLèç¹å»ºç«æ°æ®åºæ¶ï¼å¿
é¡»éæ©ândbclusterâæ°æ®åºå¼æãå¦æä¸éæ©ândbclusterâå¼æï¼å»ºç«çæ°æ®åºå°ä¸ä¼è¿å
¥MySQL群éç³»ç»ä¸ï¼ä½æ¯å¯ä»¥ç¬ç«ä½¿ç¨ã
å¦å¤ï¼æ¯ä¸ª NDB 表å¿
é¡»æä¸ä¸ªä¸»é®ãå¦æå¨å建表æ¶æªå®ä¹ä¸»é®ï¼NDB Cluster åå¨å¼æå°èªå¨çæéå«ç主é®ã该éå«çé®ä¹å°å ç¨ç©ºé´ï¼å°±åä»»ä½å
¶ä»ç表索å¼ä¸æ ·ãç±äºæ²¡æ足å¤çå
åæ¥å®¹çº³è¿äºèªå¨å建çé®ï¼åºç°é®é¢å¹¶ä¸ç½è§ã