Windows Server 2008r2 configuration MySQL Cluster

Directory

• Configuring the Environment
• Related knowledge
• Configuration process
  • Configuration
  • INI parameter interpretation
  • Start the cluster
• Check Configuration
• Synchronization test
• Fault Testing
• After the writing

Configuring the Environment

VMware: (Version 10.0.01)

Operating system: Windows Server 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): http://dev.mysql.com/downloads/cluster/

Test tool: Navicate for MySQL

Related knowledge

Brief introduction:

MySQL Cluster is a technology that allows the Cluster of an "in-memory" database to be deployed in a system that is not shared. With no shared architecture, the system can use inexpensive hardware and has 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.

MySQL Cluster is made up of a group of computers, each running a variety of processes, including MySQL server, NDB Cluster data node, Management Server, and (possibly) specialized data access program

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

Three types of nodes:

Management node: Used to manage the cluster common node; daemon NDB_MGMD

Data node: The data used to hold the cluster; Daemon NDBD

SQL node: Used to connect data nodes; Daemon mysqld

For details, refer to the MySQL cluster chapter in the reference manual.

Configuration process

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

Select Typical installation, the installation directory is: C:\Program files\mysql\mysql Cluster 7.3

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

The path to the new folder is: C:\Program files\mysql\mysql Server 5.6

C:\Program Files\mysql\mysql Server 5.6\mysql-cluster

• In Windows Explorer for VM1, open Directory C:\Program files\mysql\mysql Cluster 7.3 and create a new Config.ini file in this directory

[ndbd Default]Noofreplicas=2[NDB_MGMD]NodeId=1HostName=192.168.220.103DataDir=c:\program Files\mysql\mysql Cluster 7.3\data[NDBD]NodeId=21HostName=192.168.220.103DataDir=c:\program Files\mysql\mysql Server 5.6\mysql-cluster[NDBD]NodeId=22HostName=192.168.220.104DataDir=c:\program Files\mysql\mysql Server 5.6\mysql-cluster[mysqld]NodeId=11HostName=192.168.220.103[mysqld]NodeId=12HostName=192.168.220.104

• Open Directory C:\Program files\mysql\mysql Cluster 7.3 in VM1 and VM2 Explorer and create a new My.ini file in this directory

 [ mysqld  ]  character_set_server  =utf8basedir  = "C:\Program files\mysql\mysql Cluster 7.3" datadir  = "C:\Program files\ Mysql\mysql Cluster 7.3\data "sql_mode  =no_engine_substitution,strict_trans_tablesndbclusterndb-connectstring  =192.168.220.103explicit_ Defaults_for_timestamp  =true  [ mysql_cluster  Span style= "color: #800000; Font-weight:bold; " >]  ndb-connectstring  =192.168.220.103 

• Set environment variables in VM1 and VM2 (not required, not in the text, CD to the appropriate directory to execute commands)

Computer Right-click Properties (or Control Panel \ System and Security \ System)---> Advanced system Configuration---> Advanced selection Card---> Environment variables---> System variables find path---> Edit---> Plus C:\Program Files\mysql\mysql Cluster 7.3\bin

Note: Multiple paths are separated by semicolons

• Configuration file Main Parameters Introduction:

[NDBD]: Defines the data node of the cluster

[MYSQLD]: Defines the MySQL server node for the cluster

[MGM] or [NDB_MGMD]: Defines the Management Server node for the cluster

[NDB_MGMD] ID: node-unique identity

[NDBD] Noofreplicas: The global parameter can only be set in [NDBD DEFAULT], which defines the number of copies saved per table in the cluster. This 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: Reads a database file using the specified directory

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

" C:\Program files\mysql\mysql Cluster 7.3\bin "  --config-file="C:\Program files\mysql\mysql Cluster 7.3\config.ini" - -configdir="C:\Program files\mysql\mysql Cluster 7.3"  

Note: The warning information does not affect the reason that both machines are configured to manage nodes and data nodes on a single machine and do not cause cluster failures

• The new CMD window on the Data node (NDBD1) machine (VM1) executes the following command to start the data node

" C:\Program files\mysql\mysql Cluster 7.3\bin "  --initial             

Note:--initial only needs to be executed for the first time, Ndbd.exe can be executed later (refer to the manual should be used when the cluster file needs to be cleaned, so this parameter should be used with caution or early backup)

• The new CMD window on the SQL node (SQL1) machine (VM1) executes the following command to start the SQL node

" C:\Program files\mysql\mysql Cluster 7.3\bin "  --default-file="C:\Program files\mysql\mysql Cluster 7.3\my.ini"  --installnet start MySQL

Note: Perform mysqld--install you can see a service called "MySQL" on the computer's server, in the execution of net start MySQL, you can see the MySQL service as "boot" status in the service

• The new CMD window on the Data node (NDBD2) machine (VM2) executes the following command to start the data node

" C:\Program files\mysql\mysql Cluster 7.3\bin " Ndbd.exe

• The new CMD window on the SQL node (SQL2) machine (VM2) executes the following command to start the SQL node

" C:\Program files\mysql\mysql Cluster 7.3\bin "  --default-file="C:\Program files\mysql\mysql Cluster 7.3\my.ini" --installnet start mysql 

Check Configuration

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

Ndb_mgm.exeshow

　　such as status, indicates the installation is successful!

Synchronization test

• Installing Navicat for MySQL on VM1 and VM2 (installation steps omitted)
• Open navicat for MySQL on VM1 and VM2 and create a new connection (example to establish a connection of: 192.168.220.103 and 192.168.220.104) after installation The default user name is root, no password
• Create a new database wilson_mysqlcluster_test in 192.168.220.103 and view it in 192.168.220.104

　　Found in 192.168.220.104 also appeared wilson_mysqlcluster_test

• Build a tb_test table in the Wilson_mysqlcluster in 192.168.220.104, notice that the engine chooses "ndbcluster", and you can see the 192.168.220.103 table appear in Tb_test.

• Test in 192.168.220.103 or 192.168.220.104 to increase, delete, change operation, two databases will be synchronized

Fault Testing

• Stop the SQL node in VM2, insert a piece of data on the 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 the VM2 cmd window, execute the following command to stop the MySQL service

net stop MSYQL

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

net start MySQL

Complete data from test results

　　

No other tests are listed.

After the writing

Carefully read MySQL reference file before configuring MySQL cluster chapter

MySQL cluster configuration process is still more error-prone, the configuration process encountered a lot of size error (directory, SQL node not connected, MySQL service does not start, data can not be synchronized, etc.), according to CMD on the error and log files to find the corresponding solution

Note that there are many errors in the boot Order: Management node---> Data node--->sql node

Production environment recommendations with at least 3 servers to configure, need to modify the Config.ini file, the configuration process and two sets no difference

Main references:

mysql_5.5 Chinese Reference Manual

Configuring MySQL Cluster in the Windows environment

Windows Server 2008r2 configuration MySQL Cluster