MySQL cluster configuration

Compared with the various replication structures described above, the most significant advantage of MySQL cluster is high availability, high real-time performance, high redundancy, and high scalability. However, the performance of the MySQL cluster has been low. Fortunately, the performance of the current 7.x version has been greatly improved and improved.

MySQL cluster uses the NDB storage engine. When creating a table, you must specify the engine as ndbcluster. This is a memory-based storage engine and therefore has high requirements on memory. Large enough memory, faster CPU processing, Faster network environment (Gigabit or above), and the use of Cache Technology on SQL nodes can significantly improve cluster efficiency, however, the specific Optimization Configuration depends on your actual situation and needs. Therefore, this article only verifies the cluster configuration in the test environment and does not involve performance tuning and other details.

First, let's take a look at the simple explanation of the three major components of MySQL cluster on the MySQL Official Website:
Manage (MGM) nodes: This type of node is used to manage other nodes in the MySQL cluster, such as providing configuration data, starting and stopping nodes, and running backups. Because these nodes are responsible for managing the configurations of other nodes, they should be started before other nodes are started. The MGM node is started with the command ndb_mgmd.
Data Node): These nodes are used to save cluster data. The data node is started with the command ndbd.
SQL node (SQL nodes): This is the node used to access cluster data. For MySQL clusters, client nodes are traditional MySQL servers that use the NDB Cluster Storage engine.

The simple structure of the configuration in this article is as follows (click the picture to see the big picture ):

Server Configuration conventions:

?

12345678

host name IP node type clustermgm 192.168.0.100 management node sqlnode1 192.168.0.101 SQL node sqlnode2 192.168.0.102 SQL node sqlnode3 192.168.0.103 SQL node datanode1 192.168.0.104 data node datanode2 192.168.0.105 data node datanode3 192.168.0.106 data node

As shown in the figure, apart from the three major components of MySQL clusterProgramA server Load balancer device is added between the SQL node and the SQL node. This is because the SQL node of the cluster does not implement Server Load balancer, and a single node failure occurs when it is used separately, to achieve this access load balancing, you can use related software methods (such as LVS) or dedicated hardware devices. To achieve better results, I recommend you use the latter.

Software to be installed on various nodes:
SQL nodes: mysql-cluster-GPL-server, MySQL-cluster-GPL-Client
Data nodes: mysql-cluster-GPL-storage
NDB Management Server: mysql-cluster-GPL-management, MySQL-cluster-GPL-Tools

Step 1: download and install software on each node:
1. Download and install mysql-cluster-GPL-server and MySQL-cluster-GPL-client on the SQL node (sqlnode1, sqlnode2, sqlnode3 ).

?

1234

[Root @ sqlnode1 /2/3 ~] # Wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-server-7.1.15-1.rhel5.i386.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/ [Root @ sqlnode1 /2/3 ~] # Rpm-IVH MySQL-Cluster-gpl-server-7.1.15-1.rhel5.i386.rpm [Root @ sqlnode1 /2/3 ~] # Wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-client-7.1.15-1.rhel5.i386.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/ [Root @ sqlnode1 /2/3 ~] # Rpm-IVH MySQL-Cluster-gpl-client-7.1.15-1.rhel5.i386.rpm

Note: After installation, you are prompted to set the root password. Do not set it or start the MySQL server!

2. Download and install mysql-cluster-GPL-storage on the NDB node (datanode1, datanode2, datanode3)

?

12	[Root @ datanode1/2/3 ~]# Wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-storage-7.1.15-1.rhel5.i386.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/[Root @ datanode1/2/3 ~]# Rpm-IVH MySQL-Cluster-gpl-storage-7.1.15-1.rhel5.i386.rpm

3. Download and install mysql-cluster-GPL-management and MySQL-cluster-GPL-tools on the Management node (clustermgm ).

?

123

[Root @ clustermgm ~]# Wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-management-7.1.15-1.rhel5.i386.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/[Root @ clustermgm ~]# Wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-tools-7.1.15-1.rhel5.i386.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/[Root @ clustermgm ~]# Rpm-IVH MySQL-Cluster-gpl-management-7.1.15-1.rhel5.i386.rpm MySQL-Cluster-gpl-tools-7.1.15-1.rhel5.i386.rpm

Note: I downloaded it from the MySQL official website. If you copyCodeIf the software version cannot be downloaded, it indicates that the software version may have been upgraded. You can download it from the official website.

Step 2: configure the cluster:
1. Configure SQL nodes (sqlnode1, sqlnode2, sqlnode3) in three nodes:
A. Create the setting file/etc/My. CNF:

?

12345678910

[Root @ sqlnode1 /2/3 ~] # Vi/etc/My. CNF # ...... Other parameters ...... [Client] Port = 3306 Socket = /Var/lib/MySQL . Sock [Mysqld] Port = 3306 Socket = /Var/lib/MySQL . Sock Ndbcluster NDB-connectstring = 192.168.0.100 <---- manage node IP

2. Configure the data nodes (datanode1, datanode2, datanode3) in three nodes:
A. Create a cluster-related data storage directory and create a dedicated user:

?

12	[Root @ datanode1/2/3 ~]# Useradd-m-D/dev/null-S/sbin/nologin-c "MySQL cluster" MySQL[Root @ datanode1/2/3 ~]# Chown MYSQL: MySQL/var/lib/MySQL-Cluster

B. Create the setting file/etc/My. CNF:

?

123	[Root @ datanode1/2/3 ~]# Vi/etc/My. CNF[Mysql_cluster]NDB-connectstring = 192.168.0.100 <---- manage node IP

3. clustermgm ):
A. Create a config file directory and configure the config file:

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344

[Root @ clustermgm ~] # Mkdir/var/lib/MySQL-Cluster [Root @ clustermgm ~] # Vi/var/lib/MySQL-cluster/cluster. conf # Global configuration of all data nodes is affected # If you do not have much memory and the datamemory and indexmemory settings are too large, cluster startup will fail (the data node runs out of memory) [Ndbd default] Noofreplicas = 3 <---- number of data nodes Datamemory = 64 m <---- memory allocated to Data Storage Indexmemory = 18 m <---- memory allocated to index Storage # TCP/IP options: [TCP default] Portnumber = 2202 # Manage node configuration: [Ndb_mgmd] Hostname = 192.168.0.100 Datadir = /Var/lib/MySQL-Cluster # SQL Node 1 configuration (sqlnode1 ): [Mysqld] Hostname = 192.168.0.101 # SQL Node 2 configuration (sqlnode21 ): [Mysqld] Hostname = 192.168.0.102 # SQL Node 3 configuration (sqlnode3 ): [Mysqld] Hostname = 192.168.0.103 # Data node 1 configuration (datanode1 ): [Ndbd] Hostname = 192.168.0.104 Datadir = /Var/lib/MySQL-Cluster # Data node 2 configuration (datanode2 ): [Ndbd] Hostname = 192.168.0.105 Datadir = /Var/lib/MySQL-Cluster # Data node 3 configuration (datanode3 ): [Ndbd] Hostname = 192.168.0.106 Datadir = /Var/lib/MySQL-Cluster

Note: This configuration file is basically the simplest configuration file. For more configuration parameters, refer to the official documentation and set them according to your server's hardware conditions and actual requirements.

Step 3: Start the cluster:
The starting sequence is management node> data node> SQL node.
1. Start the management node:

?

1	[Root @ clustermgm ~]# Ndb_mgmd-F/var/lib/MySQL-cluster/cluster. conf

2. After a moment, start the data nodes (datanode1, datanode2, datanode3) and run them in three nodes:

?

1	[Root @ datanode1/2/3 ~]# Ndbd -- Initial

★Note: The-initial parameter is added only when ndbd is started for the first time or after backup/recovery or configuration change!

3. After a moment, start the SQL node (sqlnode1, sqlnode2, sqlnode3) and set the root password, respectively, and run the following three nodes:

?

12	[Root @ sqlnode1/2/3 ~]#/Etc/init. d/MySQL start[Root @ sqlnode1/2/3 ~]#/Usr/bin/mysqladmin-u Root Password 'mysqlpassword'

4. Confirm the cluster status and run the following command on the Management node (clustermgm:

?

1234567891011121314151617181920

[Root @ clustermgm ~] # Ndb_mgm -- NDB Cluster -- management client -- Ndb_mgm> show Connected to management server at: localhost: 1186 Cluster configuration --------------------- [Ndbd (NDB)] 3 node (s) ID = 5 @ 192.168.0.104 (mysql-5.1.56, nodegroup: 0, Master) ID = 6 @ 192.168.0.105 (mysql-5.1.56 ndb-7.1.15, node group: 0) ID = 7 @ 192.168.0.106 (mysql-5.1.56 ndb-7.1.15, node group: 0) [Ndb_mgmd (MGM)] 1 node (s) ID = 1 @ 192.168.0.100 (mysql-5.1.56 ndb-7.1.15) [Mysqld (API)] 3 node (s) ID = 2 @ 192.168.0.101 (mysql-5.1.56 ndb-7.1.15) ID = 3 @ 192.168.0.102 (mysql-5.1.56 ndb-7.1.15) ID = 4 @ 192.168.0.103 (mysql-5.1.56 ndb-7.1.15) Ndb_mgm>

From the above results, we can see that our cluster has been configured successfully. If any node is not connected in your display results, check all your configurations, check the log information of each node.
Now you can add some mysql users on the SQL node and test various databases.

★Important:
1. When creating a table, you must use engine = NDB or engine = ndbcluster to specify the NDB Cluster Storage engine or use the alter table option to change the table storage engine.
2. The NDB table must have a primary key. Therefore, the primary key must be defined during table creation. Otherwise, the NDB storage engine will automatically generate an implicit primary key.
3. the user permission table of the SQL node is still saved using the MyISAM storage engine. Therefore, mysql users created on an SQL node can only access this node. If the same user is used to access other SQL nodes, the user needs to be appended to the corresponding SQL node. Although "user permission sharing" is provided in MySQL cluster7.2, MySQL 7.2 is still a development version. This article uses the stable version 7.15, so we will not introduce this function.

Step 3: Disable the cluster:
1. To disable Management Nodes and data nodes, you only need to execute the following in the management node (clustermgm:

?

1234	[Root @ clustermgm ~]# Ndb_mgm-e shutdownConnected to management server at: localhost: 11863 NDB cluster node (s) haveShutdown.Disconnecting to allow management serverShutdown.

2. Then close the SQL node (sqlnode1, sqlnode2, sqlnode3) and run the following three nodes:

?

12	[Root @ sqlnode1/2/3 ~]#/Etc/init. d/MySQL stopShutting down MySQL... success!

Note: To start the cluster again, follow the step 3 to start the cluster, but do not add the "-initial" parameter when starting the data node this time.