MySQL Optimization-detailed explanation of cluster building steps and detailed explanation of mysql cluster building

MySQL Optimization-detailed explanation of cluster building steps and detailed explanation of mysql cluster building

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46833179

1 Overview

MySQL Cluster is a highly practical, scalable, high-performance, and highly redundant MySQL version suitable for distributed computing environments. Its original design is to meet the most stringent application requirements in many industries, these applications often require that the database operation reliability reach 99.999%. MySQL Cluster allows you to deploy "in memory" database clusters in a non-shared system. Without a shared architecture, the system can use inexpensive hardware without special requirements on hardware and software. In addition, since each component has its own memory and disk, there is no single point of failure.

In fact, a MySQL cluster integrates a memory Cluster Storage engine called NDB with a standard MySQL server. It contains a group of computers, each running one or more processes, which may include a MySQL server, a data node, a Management Server, and a proprietary data access program.

MySQL Cluster can use a variety of failover and load balancing options to configure the NDB storage engine, but it is the easiest to do this on the storage engine at the Cluster level. The following figure shows the structure diagram of the MySQL cluster,

 

From the perspective of structure, MySQL consists of three types of nodes (computer or process:

Management node: used to provide configuration, management, arbitration, and other functions for other nodes in the cluster. Theoretically, you can provide services through a server.

Data Node: the Core of MySQL Cluster, which stores data and logs and provides various data management services. When there are more than two nodes, the high availability of the cluster can be ensured. When the DB nodes increase, the processing speed of the cluster will be slow.

SQL node (API): used to access MySQL Cluster data and provide external application services. Adding an API node increases the concurrent access speed and overall throughput of the entire cluster. This node can be deployed on a Web application server or a dedicated server, it is also deployed on the same server as the database.

2 NDB Engine

MySQL Cluster uses a dedicated memory-based storage engine-NDB engine. The advantage of this is that the speed is fast and there is no bottleneck on disk I/O, but because it is memory-based, therefore, the size of the database is limited by the total memory of the system. If the MySQL server running NDB must have enough memory, such as 4G, 8G, or even 16G. The NDB engine is distributed and can be configured on multiple servers for data reliability and scalability, theoretically, by configuring two NDB storage nodes, we can achieve the redundancy of the entire database cluster and solve the single point of failure (spof) problem.

2.1 Defects

• Based on memory, the size of the database is limited by the total memory size of the cluster.

  Based on memory, data may be lost after power failure, which also needs to be verified by testing.

  Multiple nodes implement communication, data synchronization, query, and other operations through the network. Therefore, the overall performance is affected by the network speed,

  Therefore, the speed is also slow.

   

  2.2 advantages

  • Multiple nodes can be distributed across different geographic locations. Therefore, it is also a solution for implementing distributed databases.

    The scalability is good. You can expand the database cluster by adding nodes.

    Redundancy is good, and multiple nodes have complete database data. Therefore, any node downtime will not cause service interruption.

    The cost of achieving high availability is relatively low. Unlike traditional high availability solutions, shared storage devices and dedicated software are required for implementation, and NDB can be implemented with enough memory.

Ii. Cluster Construction

The most simplified MySQL Cluster system will be built, and all the commands in the configuration method will be run with the root account. This MySQL Cluster contains a management node, two data nodes, and two SQL nodes. These five nodes are installed on five virtual machines respectively. The virtual machine name and IP address are as follows:

Management Node	Mysql-mgm	192.168.124.141
Data Node 1	Mysql-ndbd-1	192.168.124.142
Data Node 2	Mysql-ndbd-2	192.168.124.143
SQL Node 1	Mysql-sql-1	192.168.124.144
SQL Node 2	Mysql-sql-2	192.168.124.145

I. Public Configuration

Configure the configuration items here on the three virtual machines.

1. Install virtual machines

Install CentOS 6.4 x86_64 in the virtual machine operating system, use the NAT network, and install vmware-tools. The installation method is not described here.

2. Copy mysql cluster

Download MySQL-Cluster of the following versions:

Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz

Copy the downloaded package to the/root/Downloads directory of the VM and run the following command in the shell:

Cd/root/Downloads Tar-xvzf mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz Music mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64/usr/local/mysql

3. disable security policies

Disable iptables firewall (or open ports 1186 and 3306 of the firewall) and run the following command in Shell:

Chkconfig -- level 35 iptables off

Close SELinux and run the following command in Shell:

Gedit/etc/selinux/config

Change the SELINUX item in the config file to disabled. The contents of the modified config file are as follows:

# This file controls the state of SELinux on the system. # SELINUX = can take one of these three values: # Enforcing-SELinux security policy is enforced. # Permissive-SELinux prints warnings instead of enforcing. # Disabled-No SELinux policy is loaded. SELINUX = disabled # SELINUXTYPE = can take one of these two values: # Targeted-Targeted processes are protected, # Mls-Multi Level Security protection. SELINUXTYPE = targeted

Finally, restart the system.

Ii. Configuration Management node (192.168.124.141)

1. Configure the config. ini configuration file

Run the following command in shell:

Mkdir/var/lib/mysql-cluster Cd/var/lib/mysql-cluster Gedit config. ini

The configuration file config. ini contains the following content:

[Ndbd default] NoOfReplicas = 2 DataMemory = 80 M IndexMemory = 18 M [Ndb_mgmd] NodeId = 1 Hostname = 192.168.124.141 Datadir =/var/lib/mysql-cluster [Ndbd] NodeId = 2 Hostname = 192.168.124.142 Datadir =/usr/local/mysql/data [Ndbd] NodeId = 3 Hostname = 192.168.124.143 Datadir =/usr/local/mysql/data [Mysqld] NodeId = 4 Hostname = 192.168.124.144 [Mysqld] NodeId = 5 Hostname = 192.168.124.145

2. Install the management Node

Install the management node without the mysqld binary file. You only need the MySQL Cluster server program (ndb_mgmd) and the listener Client Program (ndb_mgm ). Run the following command in shell:

Cp/usr/local/mysql/bin/ndb_mgm */usr/local/bin Cd/usr/local/bin Chmod + x ndb_mgm *

3. configure data nodes (192.168.124.142 and 192.168.124.143)

1. Add mysql groups and users

Run the following command in shell:

Groupadd mysql Useradd-g mysql

2. Configure the my. cnf configuration file

Run the following command in shell:

Gedit/etc/my. cnf

The content of my. cnf configuration file is as follows:

[Mysqld] Basedir =/usr/local/mysql Datadir =/usr/local/mysql/data Socket =/usr/local/mysql/sock/mysql. sock User = mysql # Disabling symbolic-links is recommended to prevent assorted security risks Symbolic-links = 0 [Mysqld_safe] Log-error =/var/log/mysqld. log Pid-file =/var/run/mysqld. pid [Mysql_cluster] Ndb-connectstring = 192.168.124.141

3. Create a system database

Run the following command in shell:

Cd/usr/local/mysql Mkdir sock Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/data

4. Set the data directory

Run the following command in shell:

Chown-R root. Chown-R mysql. mysql/usr/local/mysql/data Chown-R mysql. mysql/usr/local/mysql/sock Chgrp-R mysql.

5. Configure the MySQL Service

Run the following command in shell:

Cp support-files/mysql. server/etc/rc. d/init. d/ Chmod + x/etc/rc. d/init. d/mysql. server Chkconfig -- add mysql. server

4. Configure the SQL node (192.168.124.144 and 192.168.124.145)

1. Add mysql groups and users

Run the following command in shell:

Groupadd mysql Useradd-g mysql

2. Configure the my. cnf configuration file

Run the following command in shell:

Gedit/etc/my. cnf

The content of my. cnf configuration file is as follows:

[Client] Socket =/usr/local/mysql/sock/mysql. sock [Mysqld] Ndbcluster Datadir =/usr/local/mysql/data Socket =/usr/local/mysql/sock/mysql. sock Ndb-connectstring = 192.168.124.141 [Mysql_cluster] Ndb-connectstring = 192.168.124.141

3. Create a system database

Run the following command in shell:

Cd/usr/local/mysql Mkdir sock Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/data

4. Set the data directory

Run the following command in shell:

Chown-R root. Chown-R mysql. mysql/usr/local/mysql/data Chown-R mysql. mysql/usr/local/mysql/sock Chgrp-R mysql.

5. Configure the MySQL Service

Run the following command in shell:

Cp support-files/mysql. server/etc/rc. d/init. d/ Chmod + x/etc/rc. d/init. d/mysql. server Chkconfig -- add mysql. server

V. Cluster Environment startup

Note the startup sequence: first, the management node, then the data node, and finally the SQL node.

1. Start the management Node

Run the following command in shell:

Ndb_mgmd-f/var/lib/mysql-cluster/config. ini

You can also use ndb_mgm to listen to the client, as shown below:

Ndb_mgm

2. Start the data node

For the first time, you need to add the -- initial Parameter for NDB node initialization. This parameter cannot be added during subsequent startup. Otherwise, the ndbd program will clear all data files and log files created previously for restoration.

/Usr/local/mysql/bin/ndbd -- initial

If it is not the first time, run the following command.

/Usr/local/mysql/bin/ndbd

3. Start the SQL Node

If the MySQL service is not running, run the following command in shell:

/Usr/local/mysql/bin/mysqld_safe -- user = mysql &

4. Start the test

View the management node. started successfully:

Vi. Cluster Test

1. Test 1

Now we create the relevant database on one of the SQL nodes, and then go to another SQL node to check whether the data is synchronized.

Run the following command on SQL Node 1 (192.168.124.144:

Shell>/usr/local/mysql/bin/mysql-u root-p Mysql> show databases; Mysql> create database aa; Mysql> use aa; Mysql> create table ctest2 (I INT) ENGINE = NDB; // The ENGINE of the database TABLE must be specified here as NDB; otherwise, synchronization fails. Mysql> insert into ctest2 () VALUES (1 ); Mysql> SELECT * FROM ctest2;

Check whether the data is synchronized at SQL Node 2.

After testing, you can create data on a non-master and synchronize the data to the master.

Check whether the table engine is NDB,> show create table name;

2. Test 2

Close a data node, write the input to another node, and enable the closed node to check whether the data is synchronized.

First, restart data node 1, and then add data on node 2.

Perform the following operations on SQL Node 2 (192.168.124.145:

Mysql> create database bb; Mysql> use bb; Mysql> create table ctest3 (I INT) ENGINE = NDB; Mysql> use aa; Mysql> insert into ctest2 () VALUES (3333 ); Mysql> SELECT * FROM ctest2;

After data node 1 is started, start the service at data node 1.

#/Usr/local/mysql/bin/ndbd -- initial # service mysqld start

Then log in to view the data

#/Usr/local/mysql/bin/mysql-u root-p

We can see that the data has been synchronized, indicating that the data can be synchronized in both directions.

7. Disable the Cluster

1. To disable Management Nodes and data nodes, you only need to execute the following in the management node (ClusterMgm -- 134:

Shell>/usr/local/mysql/bin/ndb_mgm-e shutdown

Display

Connected to Management Server at: localhost: 1186 2 NDB Cluster node (s) have shutdown. Disconnecting to allow management server to shutdown.

2. Then close the SQL node (135,136) and run the following two nodes:

Shell>/etc/init. d/mysql. server stop Shutting down MySQL... SUCCESS!

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.