Figure Windows Server 2008r2 Configure MySQL cluster tutorial

Configuring the Environment

VMware: (Version 10.0.01)

Operating system: Windows Server 2008 R2 Enterprise

VM1:192.168.220.103 Management node (MGM), Data node (NDBD1), SQL node (SQL1)

VM2:192.168.220.104 Data Node (NDBD2), SQL node (SQL2)

MySQL cluster version: 7.3.7 (MSI Installer) Download address: http://dev.mysql.com/downloads/cluster/

Test tool: Navicate for MySQL

Related Knowledge

Brief introduction:

MySQL Cluster is a technology that allows the deployment of Cluster in an in-memory database on a system that is not shared. With no shared architecture, the system can use inexpensive hardware and no special requirements for hardware and software. In addition, because each component has its own memory and disk, there is no single point of failure.

The MySQL Cluster consists of a group of computers running multiple processes on each computer, including MySQL servers, NDB Cluster data nodes, Management servers, and (possibly) specialized data access programs

Features: http://www.mysql.com/products/cluster/features.html

Three kinds of nodes:

Management node: Used to manage clusters of nodes; Daemon Process NDB_MGMD

Data node: Used to save the data of the cluster; Daemon Process NDBD

SQL node: Used to connect data nodes; Daemon Process mysqld

For more information, please refer to the MySQL cluster chapter in the reference manual

Configuration process

Install MySQL Cluster (mysql-cluster-gpl-7.3.7-winx64) on VM1 and VM2

Select Typical installation, installation directory are: C:Program filesmysqlmysql Cluster 7.3

In Windows Explorer on VM1 and VM2, open C:Program filesmysql, create a new folder under this directory MySQL server 5.6, and then in the new MySQL server 5.6 Folder under new Mysql-cluster folder

The path to the new folder is: C:Program filesmysqlmysql Server 5.6

C:Program Filesmysqlmysql Server 5.6mysql-cluster

Open Directory C:Program filesmysqlmysql Cluster 7.3 in VM1 Windows Explorer, and create a new Config.ini file in this directory

The code is as follows

Copy Code

[NDBD Default] noofreplicas=2 [NDB_MGMD] Nodeid=1 hostname=192.168.220.103 Datadir=c:program Filesmysqlmysql Cluster 7.3data [NDBD] Nodeid=21 hostname=192.168.220.103 Datadir=c:program Filesmysqlmysql Server 5.6mysql-cluster [NDBD] Nodeid=22 hostname=192.168.220.104 Datadir=c:program Filesmysqlmysql Server 5.6mysql-cluster [Mysqld] nodeid=11 hostname=192.168.220.103 [Mysqld] Nodeid=12 hostname=192.168.220.104

Open Directory C:Program filesmysqlmysql Cluster 7.3 in the VM1 and VM2 Explorer, and create a new My.ini file in this directory

The code is as follows	Copy Code
[Mysqld] Character_set_server=utf8 Basedir= "C:Program filesmysqlmysql Cluster 7.3" Datadir= "C:Program filesmysqlmysql Cluster 7.3data" Sql_mode=no_engine_substitution,strict_trans_tables Ndbcluster ndb-connectstring=192.168.220.103 Explicit_defaults_for_timestamp=true [Mysql_cluster] ndb-connectstring=192.168.220.103

Setting environment variables in VM1 and VM2 (not required, not used in the text, the CD will go to the directory to execute the command)

Computer Right-key properties (or Control Panel system and security system)---> Advanced system Configuration---> Advanced tab---> Environment variables---> System variables find Path---> Edit---> Plus c:program Filesmysqlmysql Cluster 7.3bin

Note: Multiple paths are separated by semicolons

Profile Main parameter Introduction:

[NDBD]: A data node that defines a cluster

[MYSQLD]: A MySQL server node that defines a cluster

[MGM] or [NDB_MGMD]: A Management Server node that defines a cluster

[NDB_MGMD] ID: Unique identification of node

[NDBD] Noofreplicas: Global parameters can only be set in [NDBD DEFAULT], which defines the number of replicas saved per table in the cluster. The parameter also specifies the size of the node group. A node group refers to a collection of nodes that hold the same information

[NDBD] DataDir: This parameter specifies the directory where trace files, log files, PID files, and error logs are stored

[Mysqld]basedir: Use the specified directory as the root directory

[Mysqld]datadir: Reading database files using the specified directory

Open the CMD window on the Management node (MGM) machine (VM1) Execute the following command to start the management node

The code is as follows	Copy Code
CD "C:Program Filesmysqlmysql Cluster 7.3bin" Db_mgmd.exe--config-file= "C:Program filesmysqlmysql Cluster 7.3config.ini"--configdir= C:Program Cluster 7.3 "

Note: The warning information does not affect the two machine configuration management nodes and data nodes on a single machine, and does not cause cluster failure

Open the CMD window on the Data node (NDBD1) machine (VM1) and execute the following command to start the data node

CD "C:Program Filesmysqlmysql Cluster 7.3bin"

Ndbd.exe--initial

Note:--initial is only required for initial execution, Ndbd.exe can be executed later (the reference manual should be used when the cluster file needs to be cleaned, so this parameter should be used cautiously or backed up in advance)

On the SQL node (SQL1) machine (VM1), open the new CMD window to execute the following command to start the SQL node

The code is as follows	Copy Code
CD "C:Program Filesmysqlmysql Cluster 7.3bin" Mysqld.exe--default-file= "C:Program filesmysqlmysql Cluster 7.3my.ini" Mysqld--install net start MySQL

Note: Perform mysqld--install you can see the service named "MySQL" on the computer server, in the execution net start MySQL, you can see the MySQL service as "startup" status in the service

Open the CMD window on the Data node (NDBD2) machine (VM2) and execute the following command to start the data node

The code is as follows	Copy Code
CD "C:Program Filesmysqlmysql Cluster 7.3bin" Ndbd.exe

On the SQL node (SQL2) machine (VM2), open the new CMD window to execute the following command to start the SQL node

The code is as follows	Copy Code
CD "C:Program Filesmysqlmysql Cluster 7.3bin" Mysqld.exe--default-file= "C:Program filesmysqlmysql Cluster 7.3my.ini" Mysqld--install net start MySQL

Check Configuration

Open the CMD window on the Management node (MGM) machine (VM1) to execute the following command

The code is as follows	Copy Code
Ndb_mgm.exe Show

If the above figure state, the installation is successful!

Sync test

Install Navicat for MySQL on VM1 and VM2 (installation steps omitted)

Open navicat for MySQL on VM1 and VM2 and create a new connection (example: 192.168.220.103 and 192.168.220.104) after installation The default user name is root and no password

Create a new database in 192.168.220.103 wilson_mysqlcluster_test, view in 192.168.220.104

Found that 192.168.220.104 also appeared in Wilson_mysqlcluster_test

Build a tb_test table in the Wilson_mysqlcluster in 192.168.220.104, note that the engine chooses "ndbcluster" and that there will be 192.168.220.103 tables appearing in Tb_test

Test in 192.168.220.103 or 192.168.220.104 to add, delete, change operation, two database will be synchronized

Fault Testing

Stop the SQL node in VM2, insert a piece of data on 192.168.220.103 (originally 2 data), and then start the SQL node on the VM2 to see if it can sync properly

1. In VM2 the CMD window executes the following command to stop the MySQL service

net stop MSYQL

2. After inserting the data of ID 3 in 192.168.220.103, execute the following command, start the MySQL service in VM2 again to see if the data in 192.168.220.104 is complete

net start MySQL

Test results as shown: get Complete data

Other tests are not listed.

Summarize

Carefully read the MySQL reference file MySQL cluster chapter before configuration
MySQL cluster configuration process is still more error-prone, the configuration error encountered a lot of errors (directory, SQL node not connected, MySQL service can not start, data can not sync, etc.), according to CMD error and log file to find the corresponding solution
Note that the boot sequence is less likely to encounter many errors: Management node---> Data node--->sql node
Production environment recommended to use at least 3 servers to configure, need to modify the Config.ini file, configuration process and two no difference