配置MySQLcluster

餘二五發表於2017-11-16

 

一,基本概念:

NDB是一種“記憶體中”儲存引擎,它具有可用性高和資料一致性好的特點。能夠使用多種故障切換和負載平衡選項配置NDB儲存引擎,但以簇層面上的儲存引擎開始最簡單。MySQL簇的NDB儲存引擎包含完整的資料集,僅取決於簇本身內的其他資料。

下面,我們介紹了設定由NDB儲存引擎和一些MySQL伺服器構成的MySQL簇的設定方法。

目前,MySQL簇的簇部分可獨立於MySQL伺服器進行配置。在MySQL簇中,簇的每個部分被視為1個節點。

 

  

有三類簇節點,在最低的MySQL簇配置中,至少有三個節點,這三類節點分別是:

管理(MGM)節點:這類節點的作用是管理MySQL簇內的其他節點,如提供配置資料、啟動並停止節點、執行備份等。由於這類節點負責管理其他節點的配置,應在啟動其他節點之前首先啟動這類節點。MGM節點是用命令ndb_mgmd啟動的。

 

資料節點:這類節點用於儲存簇的資料。資料節點的數目與副本的數目相關,是片段的倍數。例如,對於兩個副本,每個副本有兩個片段,那麼就有4個資料節點。沒有必要有一個以上的副本。資料節點是用命令ndbd啟動的。

 

SQL節點:這是用來訪問簇資料的節點。對於MySQL簇,客戶端節點是使用NDB簇儲存引擎的傳統MySQL伺服器。典型情況下,SQL節點是使用命令mysqld –ndbcluster啟動的,或將ndbcluster新增到my.cnf後使用mysqld啟動。

 

簇配置包括對簇中單獨節點的配置,以及設定節點之間的單獨通訊鏈路。對於目前設計的MySQL簇,其意圖在於,從處理器的能力、記憶體空間和頻寬來講,儲存節點是同質的,此外,為了提供單一的配置點,作為整體,簇的所有配置資料均位於1個配置檔案中。

 

管理伺服器(MGM節點)負責管理簇配置檔案和簇日誌。簇中的每個節點從管理伺服器檢索配置資料,並請求確定管理伺服器所在位置的方式。當資料節點內出現有趣的事件時,節點將關於這類事件的資訊傳輸到管理伺服器,然後,將這類資訊寫入簇日誌。

 

 

二,配置例項:

伺服器使用情況:

Node

IP Address

Management (MGMD) node

192.168.0.10

MySQL server (SQL) node

192.168.0.20

Data (NDBD) node “A”

192.168.0.30

Data (NDBD) node “B”

192.168.0.40

 

 1,安裝ManagementSql node Data node 重複以下步驟:

[root@www local]# tar -zxvf mysql-cluster-gpl-7.1.3-linux-i686-glibc23.tar.gz

[root@www local]# mv mysql-cluster-gpl-7.1.3-linux-i686-glibc23 mysql

[root@www local]# groupadd mysql

[root@www local]# useradd -g mysql mysql

[root@www local]# chown -R mysql.mysql mysql

[root@www local]# /usr/local/mysql/scripts/mysql_install_db  –user=mysql

[root@www mysql]# cp support-files/mysql.server /etc/rc.d/init.d/

[root@www mysql]# chmod +x /etc/rc.d/init.d/mysql.server

[root@www mysql]# chkconfig –add mysql.server

  

2,配置Management節點:

[root@www mysql]# mkdir /var/lib/mysql-cluster

[root@www mysql]# vi /var/lib/mysql-cluster/config.ini

[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 the cluster

                  # Note: It is recommended that you do not specify the port

                  # number at all and allow the default value to be used instead

 

# Management process options:

[ndb_mgmd]

hostname=192.168.0.10           # Hostname or IP address of management node

datadir=/var/lib/mysql-cluster  # Directory for management node log files

 

# Options for data node “A”:

[ndbd]

                                # (one [ndbd] section per data node)

hostname=192.168.0.30           # Hostname or IP address

datadir=/usr/local/mysql/data   # Directory for this data node`s data files

 

# Options for data node “B”:

[ndbd]

hostname=192.168.0.40           # Hostname or IP address

datadir=/usr/local/mysql/data   # Directory for this data node`s data files

 

# SQL node options:

[mysqld]

hostname=192.168.0.20           # Hostname or IP address

                                # (additional mysqld connections can be

                                # specified for this node for various

                                # purposes such as running ndb_restore)

 

[root@www mysql-cluster]# cp bin/ndb_mgm* /usr/local/bin

 

3,配置MySQL server (SQL) node

建立  /etc/my.cnf並新增下面的內容

#ptions for mysqld process:

[mysqld]

ndbcluster                      # run NDB storage engine

ndb-connectstring=192.168.0.10  # location of management server

 

# Options for ndbd process:

[mysql_cluster]

ndb-connectstring=192.168.0.10  # location of management server

 

4,配置 Data (NDBD) node “A”

建立/etc/my.cnf檔案,並新增下面的內容

#ptions for mysqld process:

[mysqld]

ndbcluster                      # run NDB storage engine

ndb-connectstring=192.168.0.10  # location of management server

 

# Options for ndbd process:

[mysql_cluster]

ndb-connectstring=192.168.0.10  # location of management server

 

5,配置 Data (NDBD) node “B”

建立/etc/my.cnf檔案,並新增下面的內容

#ptions for mysqld process:

[mysqld]

ndbcluster                      # run NDB storage engine

ndb-connectstring=192.168.0.10  # location of management server

 

# Options for ndbd process:

[mysql_cluster]

ndb-connectstring=192.168.0.10  # location of management server

 

6,啟動Management節點:

[root@www local]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

2010-05-05 13:20:19 [MgmtSrvr] INFO     — NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.3

2010-05-05 13:20:19 [MgmtSrvr] INFO     — Loaded config from `/usr/local/mysql/mysql-cluster/ndb_1_config.bin.1`

 

7,起動  Data (NDBD) node “A” Data (NDBD) node “B

[root@www local]# /usr/local/mysql/bin/ndbd

2010-05-05 13:20:30 [ndbd] INFO     — Configuration fetched from `192.168.0.10:1186`, generation: 1

 

8,起動  MySQL server (SQL) node

[root@www local]# /etc/init.d/mysql.server start

 

 

9,如果一切順利,執行ndb_mgm命令之後,其輸出如下:

[root@www local]# 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.30  (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0, Master)

id=3    @192.168.0.40  (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)

id=1    @192.168.0.10  (mysql-5.1.44 ndb-7.1.3)

[mysqld(API)]   1 node(s)

id=4    @192.168.0.20  (mysql-5.1.44 ndb-7.1.3)

 

 

測試單點故障:

1,模擬NDB節點崩潰:

Data (NDBD) node “A”

[root@www ~]# netstat -tunlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   

tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      4456/hpiod          

tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      4240/portmap        

tcp        0      0 0.0.0.0:627                 0.0.0.0:*                   LISTEN      4261/rpc.statd      

tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      4487/cupsd          

tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      4507/sendmail: acce 

tcp        0      0 192.168.0.30:2202           0.0.0.0:*                   LISTEN      6205/ndbd           

tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      4461/python         

tcp        0      0 :::3306                     :::*                        LISTEN      11766/mysqld        

tcp        0      0 :::22                       :::*                        LISTEN      4476/sshd           

udp        0      0 0.0.0.0:32768               0.0.0.0:*                               4617/avahi-daemon:  

udp        0      0 0.0.0.0:5353                0.0.0.0:*                               4617/avahi-daemon:  

udp        0      0 0.0.0.0:621                 0.0.0.0:*                               4261/rpc.statd      

udp        0      0 0.0.0.0:111                 0.0.0.0:*                               4240/portmap        

udp        0      0 0.0.0.0:624                 0.0.0.0:*                               4261/rpc.statd      

udp        0      0 0.0.0.0:631                 0.0.0.0:*                               4487/cupsd          

udp        0      0 :::32769                    :::*                                    4617/avahi-daemon:  

udp        0      0 :::5353                     :::*                                    4617/avahi-daemon:  

[root@www ~]# kill -9 6205

[root@www ~]# netstat -tunlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   

tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      4456/hpiod          

tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      4240/portmap        

tcp        0      0 0.0.0.0:627                 0.0.0.0:*                   LISTEN      4261/rpc.statd      

tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      4487/cupsd          

tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      4507/sendmail: acce 

tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      4461/python         

tcp        0      0 :::3306                     :::*                        LISTEN      11766/mysqld        

tcp        0      0 :::22                       :::*                        LISTEN      4476/sshd           

udp        0      0 0.0.0.0:32768               0.0.0.0:*                               4617/avahi-daemon:  

udp        0      0 0.0.0.0:5353                0.0.0.0:*                               4617/avahi-daemon:  

udp        0      0 0.0.0.0:621                 0.0.0.0:*                               4261/rpc.statd      

udp        0      0 0.0.0.0:111                 0.0.0.0:*                               4240/portmap        

udp        0      0 0.0.0.0:624                 0.0.0.0:*                               4261/rpc.statd      

udp        0      0 0.0.0.0:631                 0.0.0.0:*                               4487/cupsd          

udp        0      0 :::32769                    :::*                                    4617/avahi-daemon:  

udp        0      0 :::5353                     :::*                                    4617/avahi-daemon:  

[root@www ~]# kill -9

 

mysql> use test;

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> select * from youshengtao;

+——+

| a    |

+——+

|  400 |

|  300 |

|  200 |

|  500 |

+——+

4 rows in set (0.60 sec)



 

2, 在 MySQL server (SQL) node執行查詢,可見down掉其中的一臺data node,對cluster沒有任何影響

mysql> use test;

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> select * from youshengtao;

+——+

| a    |

+——+

|  400 |

|  300 |

|  200 |

|  500 |

+——+

4 rows in set (0.60 sec)



3,模擬NDB節點崩潰:

Data (NDBD) node “B”

[root@www ~]# netstat -tunlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   

tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      4453/hpiod          

tcp        0      0 0.0.0.0:622                 0.0.0.0:*                   LISTEN      4256/rpc.statd      

tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      4235/portmap        

tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      4484/cupsd          

tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      4504/sendmail: acce 

tcp        0      0 192.168.0.40:2202           0.0.0.0:*                   LISTEN      6105/ndbd           

tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      4458/python         

tcp        0      0 :::3306                     :::*                        LISTEN      11940/mysqld        

tcp        0      0 :::22                       :::*                        LISTEN      4473/sshd           

udp        0      0 0.0.0.0:32768               0.0.0.0:*                               4614/avahi-daemon:  

udp        0      0 0.0.0.0:616                 0.0.0.0:*                               4256/rpc.statd      

udp        0      0 0.0.0.0:5353                0.0.0.0:*                               4614/avahi-daemon:  

udp        0      0 0.0.0.0:619                 0.0.0.0:*                               4256/rpc.statd      

udp        0      0 0.0.0.0:111                 0.0.0.0:*                               4235/portmap        

udp        0      0 0.0.0.0:631                 0.0.0.0:*                               4484/cupsd          

udp        0      0 :::32769                    :::*                                    4614/avahi-daemon:  

udp        0      0 :::5353                     :::*                                    4614/avahi-daemon:  

[root@www ~]# kill -9 6105

[root@www ~]# netstat -tunlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   

tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      4453/hpiod          

tcp        0      0 0.0.0.0:622                 0.0.0.0:*                   LISTEN      4256/rpc.statd      

tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      4235/portmap        

tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      4484/cupsd          

tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      4504/sendmail: acce 

tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      4458/python         

tcp        0      0 :::3306                     :::*                        LISTEN      11940/mysqld        

tcp        0      0 :::22                       :::*                        LISTEN      4473/sshd           

udp        0      0 0.0.0.0:32768               0.0.0.0:*                               4614/avahi-daemon:  

udp        0      0 0.0.0.0:616                 0.0.0.0:*                               4256/rpc.statd      

udp        0      0 0.0.0.0:5353                0.0.0.0:*                               4614/avahi-daemon:  

udp        0      0 0.0.0.0:619                 0.0.0.0:*                               4256/rpc.statd      

udp        0      0 0.0.0.0:111                 0.0.0.0:*                               4235/portmap        

udp        0      0 0.0.0.0:631                 0.0.0.0:*                               4484/cupsd          

udp        0      0 :::32769                    :::*                                    4614/avahi-daemon:  

udp        0      0 :::5353                     :::*                                    4614/avahi-daemon:  

[root@www ~]#




4,在 MySQL server (SQL) node執行查詢,data node節點全down掉之後無法進行查詢了:

mysql> select * from youshengtao;

ERROR 1296 (HY000): Got error 157 `Unknown error code` from NDBCLUSTER

mysql> select * from youshengtao;

ERROR 1296 (HY000): Got error 157 `Unknown error code` from NDBCLUSTER

mysql> show tables;

+—————-+

| Tables_in_test |

+—————-+

| t1             |

| youshengtao    |

+—————-+

2 rows in set (0.00 sec)

mysql> desc youshengtao;

ERROR 1296 (HY000): Got error 157 `Unknown error code` from NDBCLUSTER

mysql> desc t1;

ERROR 1296 (HY000): Got error 157 `Unknown error code` from NDBCLUSTER

本文轉自 trt2008 51CTO部落格,原文連結:http://blog.51cto.com/chlotte/371368,如需轉載請自行聯絡原作者