Advanced projects it cluster environment construction (two) MySQL cluster

last blog We introduce the related concept cluster, today we are going to introduce the blog related content cluster.

1, MySQL cluster simple introductionMySQL clustering technology provides redundancy for MySQL data in distributed system, enhances security, so that single mysqlserver failure does not have a huge negative effect on the system, and the stability of the system is guaranteed.

A MySQL cluster requires a set of computers. The role of each computer may be different. There are three types of nodes in the MySQL cluster: management nodes, data nodes, and SQL nodes. A computer in a cluster may be a node. It can also be a collection of two or three types of nodes. These three nodes are only logically divided, so they are not necessarily related to the physical computer one by one.

Management nodes (also known as Management Servers) are primarily responsible for managing data nodes and SQL nodes, as well as cluster configuration files and cluster log files. It monitors the working state of other nodes and can start, shut down, or restart a node. Other nodes retrieve configuration data from the management node. When a data node has a new event, it sends the event information to the management node and writes to the cluster log.
Data nodes are used to store data.
The SQL node is the same as the general MySQLServer. We are able to do SQL operations through it.

The relationships between the three cluster nodes and applications are drawn:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvenmymzq=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/southeast ">

2. mysql cluster installation configuration

Download MySQL cluster: http://dev.mysql.com/downloads/cluster/

Mysql-cluster-gpl-7.3.5-winx64.zip:
Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.5-winx64.zip
Mysql-cluster-gpl-7.3.5-win32.zip:
Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.5-win32.zip

1), MySQL simple introduction

First find three computers. or open three virtual machines. The management node is deployed on one machine, and each of the other two units deploys a data node and a SQL node.

Here, for example, one of the two machines (IP 192.168.24.16) deploys the management node, data node, and SQL node, and one (IP-192.168.24.43) deployment Data node and SQL node.

In practice, do not deploy the management node with the data node on a single machine, because assuming that the data node outage causes the management node to be unavailable, the entire MySQL cluster is not available at the same time. So a MySQL cluster ideally has at least three servers, and the management node is placed on a single server. Here are two examples, just to illustrate the configuration of three nodes to start the method.

2), Configuration Management node1, in the IP 192.168.24.16 host's C drive new folder MySQL, and then under this folder to create new subfolders bin and Mysql-cluster, and then install the package in the Bin folder under Ndb_mgm.exe and Ndb_ Mgmd.exe copied to C:\mysql\bin.

Create a new Cluster-logs folder, Config.ini file, and My.ini file under folder C:\mysql\bin.

2. config.ini file contents such as the following:

[NDBD Default] # Options affecting NDBD processes on all data nodes: # Number of replicasnoofreplicas=2 DATADIR=C:/MYSQLCL  Uster/datanode/mysql/bin/cluster-data # Directory for each data node ' s data files # Memory  Allocated to data storage datamemory=80m# Memory allocated to index storage indexmemory=18m # for Datamemory and Indexmemory, we have used the # default values. [NDB_MGMD] # Management process options:# Hostname or IP address of Management node hostname=192.168.24.16 #                                Directory for Management node log files DATADIR=C:/MYSQL/BIN/CLUSTER-LOGS[NDBD] # Options for data node "A": # (one [NDBD] section per data node) # Hostname or IP addresshostname=192.168.24.16 [NDBD] # Opti ONS for data node "B": # Hostname or IP addresshostname=192.168.24.43 [mysqld] # SQL node options: # Hostname or IP addresshostname=192.168.24.16 [mysqld] # SQL node options: # Hostname or IP addresshostname=192.168.24.43   

3, the contents of My.ini such as the following:

[mysql_cluster]# Options for Management node Processconfig-file=c:/mysql/bin/config.ini

3), configure Data node

1. Create a new folder C:\mysqlcluster\datanode\mysql in the IP 192.168.24.16 host. Then continue with the new subfolder in this folder bin and cluster-data,bin the next sub-folder is also called Cluster-data.
2. Copy all files under the Data folder of the installation package to C:\mysqlcluster\datanode\mysql\cluster-data
3. Copy the Ndbd.exe under the Bin folder in the installation package to C:\mysqlcluster\datanode\mysql\bin under the C:\mysqlcluster\datanode\ Create a new My.ini file in MySQL with the following file contents:

[mysql_cluster]# optionsfor Data node process: #location of management serverndb-connectstring=192.168.24.16   

Note: Since the configuration of the data node is the same, we can directly copy the directory C:\mysqlcluster from the 192.168.24.16 host to the C drive of the 192.168.24.43 host.

4), Configuring the SQL node1. Create a new sub-folder Sqlnode under the C:\mysqlcluster of the 192.168.24.16 host. Create a new subfolder under C:\mysqlcluster\sqlnode MySQL, extract the installation package files to the MySQL folder, and then create a new My.ini file under C:\mysqlcluster\sqlnode\mysql with the following file contents:

[Mysqld] # Options for MYSQLD process: # run NDB storage engine Ndbcluster                       # Location of Management Server Ndb-connect string=192.168.24.16

2. Delete or rename the My-default.ini file to My-default.ini.bak
NOTE: Because the SQL node configuration is the same, we are able to directly copy the directory C:\mysqlcluster\sqlnode directory from the 192.168.24.16 host to the 192.168.24.43 host's C drive.


3. Start MySQL Cluster1) Brief introduction

Before starting MySQL cluster, the first thing to do is to ensure that the MySQL service on each SQL node can start successfully and assign permissions to each SQL node to allow remote access. Then start three nodes in turn. Three kinds of node services when starting, be sure to follow the start of the management node, after the start of the data node, and then start the SQL node sequence.

        2), start the MySQL service

1. Enter the C:\mysqlcluster\sqlnode\mysql\bin folder and use Mysqld–install to install the MySQL service.

2. start MySQL service with net start MySQL command

3) Configure MySQL remote connection1, Enter the C:\mysqlcluster\sqlnode\mysql\bin folder, use Mysql–uroot–p, and then enter password, set the root user password.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvenmymzq=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/southeast "> Note: The first input of the password, that is, the default setting is Rootpassword.
2. switch to the MySQL database and enter the following command to assign the root user permissions to the remote connection

GRANT all privileges on * * to ' root ' @ '% ' identified by ' root ' with GRANT OPTION;

4), start the management node

1, open the command line form in the 192.168.24.16 host, cut to the C:\mysql\bin folder, enter:

Ndb_mgmd-f Config.ini--configdir=c:\mysql\mysql-cluster

Enter, the Management node service is started. There may not be any prompt information on the command line. Ability to open C:\mysql\bin\cluster-logs\ndb_1_cluster.log log file to view boot information.
Note that this command-line form cannot be closed unless you want to stop the service.

2, can also be made into services, in the command line input:

NDB_MGMD--install=ndb_mgmd-f Config.ini--configdir=c:\mysql\mysql-cluster

5), Start data node

1. Open a new command line form in the 192.168.24.16 host, cut to C:\mysqlcluster\datanode\mysql\bin, enter:

ndbd

2, can also be made into services, in the command line input:

NDBD--INSTALL=NDBD

        3, in the same way in other data nodes. The Data node service is started in 192.168.24.43.

4. Open a new command line form in the 192.168.24.16 host. Cut to Folder C:\mysql\bin, enter:

Ndb_mgm

Enter, and then type:

All STATUS

Enter to see the connection information for the data node.

6), start the SQL node

1. Open a new command line form in the 192.168.24.16 host, cut to C:\mysqlcluster\sqlnode\mysql\bin, enter:

Mysqld--console

Enter. The SQL node starts.
2, can also be made into services, input:

Mysqld-install MySQL

3. In the same way, start the SQL node in the other SQL node, that is, 192.168.24.43.
4. To view the startup situation of the SQL node, you can open the new command line in the same 192.168.1.10 host. Input:

Ndb_mgm

Enter, and then type:

SHOW

Enter, you can see the connection of the SQL node.

4. Test MySQL Cluster1), a new database mydb is created on any SQL node, that is, the 192.168.24.16 host, to see other SQL nodes, that is, 192.168.24.43 creates a mydb database
2), in any SQL node, that is, the mydb on the 192.168.24.16 host is used

CREATE TABLE student (ID int (2)) Engine=ndbcluster;

Create a new student table. can see other SQL nodes. That is, 192.168.24.43 created a student table in the MyDB database.
3), at any SQL node. The student table in MyDB on the 192.168.24.16 host is inserted after a few data. can see other SQL nodes, that is, the 192.168.24.43 have made related changes
  Note: There is not much difference in how data is manipulated within MySQL cluster compared to MySQL that does not use cluster. You should remember two points when you run this type of operation:
        1, the table must be created with the ENGINE=NDB or Engine=ndbcluster option. or change with the ALTER TABLE option to replicate them within cluster using the NDB cluster storage engine.

If you import a table from an existing database using the output of mysqldump, you can open the SQL script in a text editor and add the option to either the table creation statement, or one of these options to replace whatever the existing engine (or type) option is.

        2, also keep in mind that each NDB table must have a primary key. Assuming that the user does not define a primary key when the table is created, the NDB cluster storage engine will proactively generate the implied primary key. (gaze: The implied key will also occupy space.) It's like no other table index.

Failure is not uncommon because there is not enough memory to accommodate the keys that you have actively created.

          the explanation for the MySQL cluster is complete. But it is a simple way to build another flaw. What flaws? Next time we review, please pay attention!

copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

Advanced Projects it cluster environment construction (two) MySQL cluster