Shulou(Shulou.com)05/31 Report--

This article introduces you how to build a MySQL cluster, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Overview

MySQL Cluster is a highly practical, scalable, high-performance and highly redundant version of MySQL suitable for distributed computing environments. The original purpose of its research and design is to meet the most stringent application requirements in many industries. These applications often require the reliability of database operation to reach 99.999%. MySQL Cluster allows the deployment of "in-memory" database clusters in non-shared systems. Through the no-shared architecture, the system can use cheap hardware with 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.

In fact, a MySQL cluster integrates a memory cluster storage engine called NDB with a standard MySQL server. It consists of 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 configure the NDB storage engine with a variety of failover and load balancing options, but this is easiest at the Cluster level. The following is the MySQL cluster structure diagram

In terms of structure, MySQL consists of three types of nodes (computers or processes), which are:

Management node: used to provide configuration, management, arbitration and other functions to other nodes in the entire cluster. In theory, services can be provided through a server.

Data node: the core of MySQL Cluster, which stores data and logs, and provides various management services for data. The high availability guarantee of the cluster can be achieved when there are more than 2, and the processing speed of the cluster slows down when the number of DB nodes increases.

SQL node (API): used to access MySQL Cluster data and provide external application services. Increasing the API node will improve the concurrent access speed and overall throughput of the whole cluster. The node can be deployed on the Web application server, on a dedicated server, or on the same server as DB.

NDB engine

MySQL Cluster uses a dedicated memory-based storage engine-NDB engine, which has the advantage of high speed and no bottleneck of disk Igamo, but because it is memory-based, 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 it can be configured on multiple servers to achieve data reliability and expansibility. Theoretically, the redundancy of the whole database cluster and the problem of single point of failure can be solved by configuring two NDB storage nodes.

Defect

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

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

Multiple nodes realize communication, data synchronization, query and other operations through the network, so the integrity is affected by the network speed, so the speed is relatively slow.

2.2 benefits

Multiple nodes can be distributed in different geographical locations, so it is also a scheme to realize distributed database.

The expansibility is very good, and the expansion of the database cluster can be realized by adding nodes.

The redundancy is very good, and there is complete database data on multiple nodes, so the outage of any node will not cause service interruption.

The cost of achieving high availability is relatively low, unlike the traditional high availability scheme, which requires shared storage devices and dedicated software, NDB can be implemented as long as it has enough memory.

This article will build a simplified MySQL Cluster system where all commands in the configuration method are run under the root account. This MySQL Cluster consists of one management node, two data nodes, and two SQL nodes, which will be installed on five virtual machines. The name and IP of the virtual machine are as follows:

I. Public configuration

Configure the configuration items here on each of the three virtual machines.

1. Install a virtual machine

The virtual machine operating system installs the x86x64 version of CentOS 6.4, uses the NAT network, and also installs vmware-tools. The installation method is not detailed here.

two。 Copy mysql cluster

Download the following versions of MySQL-Cluster:

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 virtual machine, and then run the following command in shell:

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

3. Turn off security policy

Turn off the iptables firewall (or open ports 1186 or 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 entry 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

* restart the system

Configuration management node (192.168.124.141)

1. Configure config.ini Profil

Run the following command in shell:

Mkdir / var/lib/mysql-cluster cd / var/lib/mysql-cluster gedit config.ini

The configuration file config.ini is as follows:

[ndbd default] NoOfReplicas=2 DataMemory=80M IndexMemory=18M [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

two。 Install the management node

To install the management node, you do not need mysqld binaries, only the MySQL Cluster server program (ndb_mgmd) and the listening 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, 192.168.124.143)

1. Add mysql groups and users

Run the following command in shell:

Groupadd mysql useradd-g mysql mysql

two。 Configure my.cnf Profil

Run the following command in shell:

Gedit / etc/my.cnf

The configuration file my.cnf 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/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 up 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 SQL nodes (192.168.124.144,192.168.124.145)

1. Add mysql groups and users

Run the following command in shell:

Groupadd mysql useradd-g mysql mysql

two。 Configure my.cnf Profil

Run the following command in shell:

Gedit / etc/my.cnf

The configuration file my.cnf 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 up 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

5. Cluster environment starts

Note the startup sequence: first the management node, then the data node, and then 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 on the client, as follows:

Ndb_mgm

two。 Start the data node

* to start, you need to add a-- initial parameter to initialize the NDB node. This parameter cannot be added during later startup, otherwise the ndbd program will clear all previously established data and log files for recovery.

/ usr/local/mysql/bin/ndbd-- initial

If it is not started by *, execute 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 and start successfully

VI. Cluster testing

1. Test one

Now let's create the relevant database on one of the SQL nodes, and then go to the other SQL node to see if the data is synchronized.

Execute 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; / / here the database table engine must be specified as NDB, otherwise synchronization fails mysql > INSERT INTO ctest2 () VALUES (1); mysql > SELECT * FROM ctest2

Then check whether the data is synchronized on SQL Node 2.

After testing, data can be created on non-master and can be synchronized to master.

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

two。 Test two

Close one data node, write input in another, turn on the closed node and see if the data is synchronized.

First restart data node 1, and then add data to node 2

On SQL node 2 (192.168.124.145), do the following:

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

When data node 1 is started, start the service of 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

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

7. Shut down the cluster

1. To shut down the management node and data node, you only need to execute it 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.

two。 Then close the Sql node (135136) and run in two nodes:

Shell > / etc/init.d/mysql.server stop

Shutting down MySQL... SUCCESS!

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

On how to build a MySQL cluster to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.