2-20 MySQL cluster build for high availability

MySQL Cluster overview and installation environment

MySQL cluster is a high-utility, high-redundancy version of MySQL suitable for distributed computing environments. Cluster Chinese is the meaning of "cluster". It uses the NDB Cluster storage engine to allow multiple MySQL servers to run in 1 Cluster.

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 architecture

is a common cluster of MySQL

SQL node: Provides SQL access to the upper application layer.

Management node (MGM): manages the entire cluster. Start, close the cluster. Starting a cluster with the NDB_MGMD command

Storage/Data node: Saves the data in the cluster.

A data node that can provide a copy. Data redundancy is achieved.

NDB Engine: Is an "in-memory" storage engine with high availability and good data consistency.

Expand : NDB Engine Introduction

NDB engine

MySQL Cluster uses a dedicated memory-based storage engine--NDB engine, which has the advantage of being fast and without disk I/O bottlenecks, but because it is memory-based, the size of the database is limited by the total system memory. If running NDB MySQL server must be large enough memory, such as 4G, 8G, even the 16G.NDB engine is distributed, it can be configured on multiple servers to achieve the reliability and scalability of data, theoretically By configuring 2 NDB storage nodes, we can realize the redundancy of the whole database cluster and solve the single point of failure problem.

Defects

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

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

• Multiple nodes through the network to achieve communication and data synchronization, query and other operations, so the whole is affected by the network speed, so the speed is also relatively slow

Advantages

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

• Extensibility is good, the expansion of the database cluster can be achieved by adding nodes.

• Good redundancy, full database data on multiple nodes, so any one node outage will not cause service interruption.

Mysql cluster: http://dev.mysql.com/downloads/cluster/

In our experiment, the MySQL cluster cluster machine roles are assigned as follows:

MySQL Management node: xiaogan63.cn ip:192.168.31.63

MySQL data node: xiaogan63.cn ip:192.168.31.63

MySQL data node: xiaogan64.cn ip:192.168.31.64

MSYQL SQL node: xiaogan63.cn ip:192.168.31.63

MSYQL SQL node: xiaogan64.cn ip:192.168.31.64

Will Mysql-cluster-gpl-7.4.13-1.el6.x86_64.rpm-bundle.tar

Uploaded to Xiaogan63 and Xiaogan64.

actual Combat : MySQL Cluster setup

Environment Cleanup and installation

Perform the following on Xiaogan63 and Xiaogan64:

First we have to clear the old version, then install the MySQL cluster, and finally the file Rights management.

1.mysql old version Clear

First use the following command to clean up the MySQL installation that came with your previous operating system:

Yum-y Remove MySQL

Then use the following command:

Rpm-qa | grep mysql*

For the 2 remaining MySQL packages found, please follow the following command format to delete:

RPM-E--nodeps mysql-libs-5.1.73-7.el6.x86_64

2. MySQL Cluster version installation preparation

Place the Mysql-cluster-gpl-7.4.13-1.el6.x86_64.rpm-bundle.tar under a directory (such as/package) and perform the following command to extract:

TAR-XF Mysql-cluster-gpl-7.4.13-1.el6.x86_64.rpm-bundle.tar

# The current time cannot be later than the time to download the file, or it will be an error

# to avoid error, you can synchronize the time with Ntpdate first

# ntpdate Time.windows.com

# rpm-ivh/mnt/packages/ntpdate-*

Get a list of the following documents:

mysql-cluster-client-gpl-7.4.13-1.el6.x86_64.rpm

mysql-cluster-devel-gpl-7.4.13-1.el6.x86_64.rpm

mysql-cluster-embedded-gpl-7.4.13-1.el6.x86_64.rpm

Mysql-cluster-gpl-7.4.13-1.el6.x86_64.rpm-bundle.tar

mysql-cluster-server-gpl-7.4.13-1.el6.x86_64.rpm

mysql-cluster-shared-compat-gpl-7.4.13-1.el6.x86_64.rpm

mysql-cluster-shared-gpl-7.4.13-1.el6.x86_64.rpm

mysql-cluster-test-gpl-7.4.13-1.el6.x86_64.rpm

3. Create a folder

(Divide the following 4 classes to create a corresponding folder)

Data node Storage data: Mkdir/var/lib/mysql

Management node: Mkdir/var/lib/mysql-cluster SQL Node: No folder authorization is available

Process Dir:mkdir/var/run/mysqld

Manage node Run directory:/usr/mysql-cluster

Use the following command to change permission guarantees to write:

[[email protected] ~]# useradd-m-s/sbin/nologin MySQL

[Email protected] ~]# chown mysql:mysql-r/var/lib/mysql

[Email protected] ~]# chown mysql:mysql-r/var/lib/mysql-cluster/

[Email protected] ~]# chown mysql:mysql-r/var/run/mysqld/

[Email protected] ~]# chown mysql:mysql-r/usr/mysqld-cluster/

# 64 does not need to create/usr/mysqld-cluster

4. Installing the MySQL cluster package (client)

RPM-IVH mysql-cluster-server-gpl-7.4.13-1.el6.x86_64. Rpm

RPM-IVH mysql-cluster-client-gpl-7.4.13-1.el6.x86_64. Rpm

Note: The relationship between Mysql-cluster-server and Mysql-cluster-client and Mysql-server is the same as the MySQL package. Data node holds data:/var/lib/mysql is created automatically.

Generates two main command files: NDB_MGMD and NDB_MGM

[email protected] ~]# which NDB_MGM

/usr/bin/ndb_mgm

[Email protected] ~]# RPM-QF/USR/BIN/NDB_MGM

Mysql-cluster-server-gpl-7.4.13-1.el6.x86_64

[email protected] ~]# which NDB_MGMD

/usr/sbin/ndb_mgmd

[Email protected] ~]# RPM-QF/USR/SBIN/NDB_MGMD

Mysql-cluster-server-gpl-7.4.13-1.el6.x86_64

Note: When the MYSQL-CLUSTER-SERVER-GPL package is installed, the following message will appear, reminding us that the first Super account password exists/root/.mysql_secret This file after the entire Cluster installation.

---------------------------------------------------------------------------------------------------------

5. View MySQL root user password

[Email protected] ~]# Cat/root/.mysql_secret

# The random password set for the root user at Wed Apr 1 21:10:53 (local time): Nsblg9hmkthtgfhy

Build MySQL cluster

After a complex initial configuration, we finally set up the cluster. We organize this part by configuring, starting, and testing the order.

Configuration on individual machines

We first configure the requirements on each machine

1. Xiaogan63 creating a configuration file on a management node

[Email protected] ~]# Cd/var/lib/mysql-cluster

[Email protected] mysql-cluster]# VI config.ini #写入以下内容

[NDBD Default]

noofreplicas=2 #数据写入数量. 2 means two copies

datamemory=200m

indexmemory=100m #索引给100M

[NDB_MGMD]

Id=1

Datadir=/var/lib/mysql-cluster # Managing Nodes ' logs

hostname=192.168.31.63 #管理结点的IP地址. Native IP

###### Data node options: #存储结点

[NDBD]

hostname=192.168.31.63

Datadir=/var/lib/mysql #mysql数据存储路径

id=2

[NDBD]

hostname=192.168.31.64

Datadir=/var/lib/mysql #mysql数据存储路径

Id=3

# SQL node Options: #关于SQL结点

[Mysqld]

hostname=192.168.31.63

Id=4

[Mysqld]

hostname=192.168.31.64

Id=5

In this file, we assign IDs to five nodes, which helps to better manage and differentiate nodes. Of course, if not specified, MySQL will also dynamically assign a

2. XIAOGAN63 Data node +sql node configuration document

[Email protected]/]# vim/etc/my.cnf #写入以下内容

[Mysqld]

Datadir=/var/lib/mysql #mysql数据存储路径

# Note: If xiaogan63 is only a SQL node, the configuration document does not datadir this item

# Description: Data node and SQL node configuration file differences, more than one line

# Data nodes are: Datadir=/var/lib/mysql#mysql data storage path.

# Not on the SQL node.

Ndbcluster #启动ndb引擎

NDB-CONNECTSTRING=192.168.31.63 # Management Node IP address

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

[Mysql_cluster]

ndb-connectstring=192.168.31.63 #管理节点IP地址

3. Xiaogan64 Configuring data nodes and SQL nodes.

[Email protected] ~]# scp/etc/my.cnf 192.168.31.64:/etc/

Note: The contents of the xiaogan63 and Xiaogan64/my.cnf can be copied directly from the xiaogan63

MySQL cluster boot

Initial start command and user Password change adjustment: (please start in strict order)

First Start: Management node Service-data node service->sql node service.

Close: Close the Management node service, after the Management node service is closed, the NBDB Data node service will automatically shut down and manually turn off the SQL node service.

Please confirm before performing initial start

Shut down the firewall of two machines (service iptables stop or set firewall port to pass, two ports are communication port 1186, data port 3306)

1. Start the Management node command on xiaogan63.

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

# mysql cluster background running process

#尽量不要把 management node, data node, storage node configuration on the same machine, or a hung, all hung up.

To view the port number:

2. Xiaogan63 and Xiaogan 64 start Data node service

[Email protected] ~]# NDBD--initial

2016-10-23 11:58:48 [NDBD] INFO--Angel connected to ' 192.168.31.63:1186 '

2016-10-23 11:58:48 [NDBD] INFO--Angel allocated nodeid:2 [[email protected] ~]# NDBD--initial

2016-10-23 11:58:59 [NDBD] INFO--Angel connected to ' 192.168.31.63:1186 '

2016-10-23 11:58:59 [NDBD] INFO--Angel allocated Nodeid:3

3. Xiaogan63 and Xiaogan64 Start the SQL node service

[[Email protected] ~] #mysqld_safe--defaults-file=/etc/my.cnf &

[[Email protected] ~] #mysqld_safe--defaults-file=/etc/my.cnf &

To view MySQL cluster status:

[[Email protected] ~] #ndb_mgm

Data synchronization

1. xiaogan63 Change the root password:

[Email protected]/]# Cat/root/.mysql_secret

[Email protected]/]# MYSQL-UROOT-PHV6ISR1WNOTQXADC

Mysql> set Password=password (' 123456 ');

[Email protected]/]# mysql-uroot-p123456

2. Xiaogan64 Change the root password:

[Email protected] ~]# Cat/root/.mysql_secret

[Email protected] ~]# mysql_secure_installation

Enter current password to root (enter for none):

Change the root password? [y/n] Y

New Password:

Re-enter new password:

Password Updated successfully!

Remove anonymous users? [y/n] Y

Disallow Root login remotely? [y/n] Y

Remove test database and access to it? [y/n] Y

Reload privilege tables now? [y/n] Y

[Email protected] ~]# mysql-uroot-p123456

3. Insert data:

XIAOGAN63:

mysql> CREATE DATABASE Xiaogan;

Xiaogan64:

mysql> show databases;

Close Service

Turn off MySQL cluster order: Close the Management node service-when you close the management node, the data node service automatically shuts down – The SQL node service needs to be shut down manually

[[email protected]/] # NDB_MGM

--NDB Cluster--Management Client--

ndb_mgm> shutdown

Node 2:cluster shutdown initiated

Node 3:cluster shutdown initiated

3 NDB Cluster node (s) has shutdown.

Disconnecting to allow Management Server to shutdown.

Node 3:node shutdown completed.

Ndb_mgm> exit

Ps-axu | grep ndbd #查看不到, indicating that the data node has been shut down

To manually close the SQL node service

Xiaogan63, manually turn off SQL node service

[Email protected] ~]# Ps-axu | grep MySQL

[Email protected] ~]# kill-9 7617

[Email protected] ~]# kill-9 7743

[Email protected] ~]# Ps-axu | grep MySQL

Xiaogan64, manually turn off SQL node service

[Email protected] ~]# Ps-axu | grep MySQL

[Email protected] ~]# kill-9 5576

[Email protected] ~]# kill-9 5701

[Email protected] ~]# Ps-axu | grep MySQL

Summary

Start again, MSYQL cluster boot:

[Email protected] ~]# ndb_mgmd-f/var/lib/mysql-cluster/config.ini

[Email protected] ~]# NDBD

[Email protected] ~]# NDBD

[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my.cnf &

[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my.cnf &

Test:

To view MySQL cluster status:

[Email protected]/]# NDB_MGM

--NDB Cluster--Management Client--

Ndb_mgm> Show

2-20 MySQL cluster build for high availability