Mysql-Cluster
MySQL Cluster 7.3 - NDB 引擎 - 無法連接到儲存引擎
設置
我已經安裝了三個Oracle Linux 7.3。2 個用於數據節點,1 個用於管理和 SQL API 節點。機器可以互相 ping 通,SSH 工作,“etc/hosts”文件被正確填充:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.16.1.101 mysqld_mgmt_1 172.16.1.103 mysql_data_1 172.16.1.104 mysql_data_2
管理節點在“/var/lib/mysql-cluster/config.ini”文件中有如下配置:
# Options affecting ndbd processes on all data nodes: [NDBD DEFAULT] NoOfReplicas=2 # Number of replicas [ndb_mgmd] # Management process options: hostname=mysqld_mgmt_1 # Hostname of the manager datadir=/var/lib/mysql-cluster # Directory for the log files [ndbd] hostname=mysql_data_1 # Hostname of the first data node datadir=/var/lib/mysql-cluster # Remote directory for the data files ServerPort=50501 [ndbd] hostname=mysql_data_2 # Hostname of the second data node datadir=/var/lib/mysql-cluster # Remote directory for the data files ServerPort=50502 [mysqld] # SQL node options: hostname=mysqld_mgmt_1 # In our case the MySQL server/client is on the same Droplet as the cluster manager
SQL API 節點(與管理相同)在“/etc/my.cnf”文件中具有以下配置:
[mysqld] ndbcluster # Run NDB storage engine ndb-connectstring=172.16.1.101 [mysql_cluster] ndb-connectstring=mysqld_mgmt_1 # IP address for server management node
兩個數據節點在每個節點上都有相同的配置文件(“/etc/my.cnf”):
[mysqld] ndbcluster ndb-connectstring=172.16.1.101 [mysql_cluster] ndb-connectstring=172.16.1.101 # IP address of Management Node 1
啟動集群:
1. management: ndb_mgmd -f /var/lib/mysql-cluster/config.ini or with a new config to erase the cache: ndb_mgmd --initial --config-file=/var/lib/mysql-cluster/config.ini 2. data node 1 and 2: ndbd 3. mysql server: service mysql start
集群狀態:
[root@mysqld-mgmt-1 ~]# ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: mysqld_mgmt_1:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @172.16.1.103 (mysql-5.6.37 ndb-7.3.18, Nodegroup: 0, *) id=3 @172.16.1.104 (mysql-5.6.37 ndb-7.3.18, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @172.16.1.101 (mysql-5.6.37 ndb-7.3.18) [mysqld(API)] 1 node(s) id=4 (not connected, accepting connect from 172.16.1.101)
我可以從伺服器節點上的命令行登錄到 MySQL(以及從 MySQL Workbench 以及從另一個 VM):
[root@mysqld-mgmt-1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.37-ndb-7.3.18-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 11 rows in set (0.01 sec)
測試
我創建了一個沒有“引擎”參數的範例表,只是為了嘗試。我想更改表以使用 ndb 引擎,但我有以下問題:
mysql> use clusterdb; 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 simples; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) mysql> alter table simples engine=ndb; ERROR 157 (HY000): Could not connect to storage engine mysql> show warnings; +---------+------+---------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------+ | Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock | | Error | 157 | Could not connect to storage engine | | Error | 1499 | Too many partitions (including subpartitions) were defined | +---------+------+---------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> show global status like 'ndb_number_of%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Ndb_number_of_data_nodes | 2 | | Ndb_number_of_ready_data_nodes | 0 | +--------------------------------+-------+ 2 rows in set (0.00 sec)
此外,使用“engine=ndb”參數創建表也會失敗並給出相同的錯誤。我看到數據庫可以與 InnoDB 引擎一起正常工作,但我需要一個容錯(類似)數據庫。
好的,所以我設法解決了這個問題。
由於它是 Oracle Linux 7.3,因此有一個所謂的 Security-Enhanced Linux (SELinux) 選項。我將其設置為“禁用”並重新啟動節點:
[root@mysqld-mgmt-1 ~]# vi /etc/selinux/config
SELINUX=禁用
[root@mysqld-mgmt-1 ~]# shutdown -r now
節點重啟後,我再次啟動服務並使用ndb_mgm列出節點:
ndb_mgm> show Connected to Management Server at: mysqld_mgmt_1:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @172.16.1.103 (mysql-5.6.37 ndb-7.3.18, Nodegroup: 0, *) id=3 @172.16.1.104 (mysql-5.6.37 ndb-7.3.18, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @172.16.1.101 (mysql-5.6.37 ndb-7.3.18) [mysqld(API)] 1 node(s) id=4 @172.16.1.101 (mysql-5.6.37 ndb-7.3.18)