"Turn" mysql-cluster-7.5 build a DB cluster

Read Catalogue

• Objective
• Several conceptual explanations in MySQL cluster
• Architecture Diagram and Description
• Download MySQL Cluster
• Before installing MySQL Cluster
• Install the Configuration Management node
• Installing configuration data and MySQL nodes
• Test
• Start and close
• Summarize

Objective

When your business arrives at a certain amount of the equivalent, you certainly need a certain number of databases to load balance your database requests, I have explained in the previous blog, how to achieve load balancing, but there is also a problem is data synchronization, because the premise of load balancing is that each server database is data synchronization. When the volume of business is small, we will use the master-slave replication method to achieve server data synchronization, a master and many from or dual-master, but although the read-write separation, but for the reading method is still relatively large, so the problem of solving data synchronization is the significance of the database cluster. I use the mysql-cluster provided by the MySQL website to implement the cluster. (to the article before the release of the latest version of the installation tutorial, the network trampled the pits have stepped over;)

Several conceptual explanations in MySQL cluster

For the sake of simplicity, I hereafter referred to as Mysql-cluster for MC.

1, MC already contains MySQL, I download the latest mc7.5, official note contains is the MySQL version is 5.7. Therefore, you do not need to install the database using another MSYQL installation package. Also note that the mysql5.7 version in the installation of the command and configuration above and before the version is very different, so there are a lot of mc7.5 before the version, contains a different version of MySQL, so the installation method is different.

2, Management node, MC Management node is responsible for the management, configuration, monitoring the entire cluster.

3, the Data node, the use of memory storage data, the data stored in the data node will be automatically copied and stored to other data nodes.

4, MySQL node, also known as the database node, and we usually use the same MySQL, as a database use. Accessed by the data node.

Architecture Diagram and Description

The configuration in my experiment is that because the virtual machine consumes a large amount of memory and uses only 3 servers, it is best to separate the data node from the MySQL node in the actual situation. In practice, the load balancing service also needs to be backed up, because in the event that the Load Balancer server goes down and all data nodes are inaccessible, you need to manage the node and load balancer separately for the Load Balancer server backup, conditionally. The experiment only realizes the whole database cluster, load Balancing please refer to the previous blog configuration.

Download MySQL Cluster

First download MySQL cluster, recommended to use the following image, download faster than the official website

http://mirrors.sohu.com/mysql/MySQL-Cluster-7.5/

The version I downloaded is mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz

Note that the 64-bit version, do not download the wrong

Before installing MySQL Cluster

Before installing, if you have previously installed MySQL, then you need to delete the corresponding various MySQL files, please stop the MySQL service before deleting. And do not forget to delete the my.cnf these configuration files. Make sure to delete cleanly. Otherwise it may be in conflict with the subsequent installation. If it is an experiment, turn off the firewall, actually, the firewall opens the corresponding port. Ensure that the server can access each other before, can ping through. Secure a fixed IP address. Ensure that no other programs occupy the ports that need to be used. such as 3306. These are confirmed and then installed. Requires Linux Basic command proficiency, need to be proficient in the installation of MySQL basic version of operations, because some of the post-order operations I will simply describe, do not do too much explanation.

Install the Configuration Management node

Upload the downloaded package to the server/usr/local

Extract

# tar XVF mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz

Take the required files out

# CD Mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64

# CP bin/ndb_mgm*/usr/local/bin

# Cd/usr/local/bin

# chmod +x ndb_mgm*

Create a new configuration file and initialize the management node

# Mkdir/var/lib/mysql-cluster

# Mkdir/usr/local/mysql

# Vi/var/lib/mysql-cluster/config.ini

The following is the configuration file, according to their own needs to modify, first give the official website of the default profile, and then give my configuration file, according to my modified changes can be, other can not move.

-Hide Code

[NDBD default]# Options affecting NDBD processes on all data nodes:noofreplicas=2 # Number of replicasdatamemory=80m                  # How much memory to allocate for data storageindexmemory=18m # How much memory to allocate for index storage # for Datamemory and indexmemory, we have used the # default values.                  Since the ' World ' database takes up # is about 500KB, this should is more than enough for # This example NDB Cluster setup. SERVERPORT=2202 # This default value; However, you can use any # Port-is-free for all the hosts in the cluster # NOTE1:I T is recommended so you don't specify the port # number at all and simply allow the default value to B                   E used # instead # Note2:the port was formerly specified using the PortNumber # TCP parameter; This parameter was no longer available in NDB # CluSter 7.5. [ndb_mgmd]# Management Process options:hostname=192.168.0.10 # HostName or IP address of MGM nodedatadir=/var/li B/mysql-cluster # Directory for MGM node log files[ndbd]# Options for data node ' A ': # (on e [NDBD] section per data node) hostname=192.168.0.30 # HostName or IP addressnodeid=2 # N Ode ID for this data nodedatadir=/usr/local/mysql/data # Directory of this data node ' s data files[ndbd]# Options for DA TA node "B": hostname=192.168.0.40 # HostName or IP addressnodeid=3 # node ID for this dat A Nodedatadir=/usr/local/mysql/data # Directory for this data node ' s data files[mysqld]# SQL node options:hostname=192.1                                68.0.20 # Hostname or IP Address # (additional mysqld connections can be # specified for this node for various # purposes such as running NDB _restore)

-Hide Code

[NDBD Default] noofreplicas=2datamemory=512mindexmemory=18m[ndb_mgmd]hostname=192.168.75.129datadir=/var/lib/mysql-cluster[ ndbd]hostname=192.168.75.128datadir=/var/lib/mysql-cluster[ndbd]hostname=192.168.75.130datadir=/var/lib/ MYSQL-CLUSTER[MYSQLD][MYSQLD]

Initializing the management node with a configuration file

#/usr/local/bin/ndb_mgmd-f/var/lib/mysql-cluster/config.ini--initial

The MySQL Cluster Management Server mysql-5.7.16 appears ndb-7.5.4

And then you can use NDBD to go in and manage.

# NDBD

Ndb_mgm>show (use the show command to view the management situation, when the data node is configured, we use this command to view and manage)

So far, the management node is configured, then the configuration data and SQL node

Installing configuration data and MySQL nodes

All of the following operations require the same action on all cluster nodes

New user group MySQL and user msyql

# Groupadd MySQL

# useradd-g mysql-s/bin/false MySQL

Create a new folder and give permissions

# Mkdir/var/lib/mysql-cluster

# chown Root:mysql/var/lib/mysql-cluster

Upload the downloaded package to the server/usr/local

Extract

# tar XVF mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64.tar.gz

Create links for easy access

# ln-s/usr/local/mysql-cluster-gpl-7.5.4-linux-glibc2.5-x86_64/usr/local/mysql

Initialize the database (note here that if you install the version unlike mine, the database initializes the commands differently, many of the previous versions will use: scripts/mysql_install_db--user=mysql to initialize, This has been deprecated in the new version of MySQL, so you need to use the following command to install, if you need to install another version please refer to the corresponding version of the MySQL official installation command. )

# CD MySQL

# mysqld--initialize

Modify Permissions

# Chown-r Root.

# chown-r MySQL Data

# chgrp-r MySQL.

# CP Support-files/mysql.server/etc/rc.d/init.d/

# chmod +x/etc/rc.d/init.d/mysql.server

# chkconfig--add Mysql.server

Configure Data nodes

# VI/ETC/MY.CNF

-Hide Code

[mysqld]ndbclusterndb-connectstring=192.168.75.129[mysql_cluster]ndb-connectstring=192.168.75.129

Where IP is the IP of the management node

Start the service on the cluster node above start MySQL (success will have success)#/etc/init.d/mysql.server Start

Start ndbd#/etc/init.d/ndbd--initial If the above does not use the absolute path of this : #/usr/local/mysql/bin/ndbd--initial If the following occurs, it succeeds.

2017-03-06 14:04:07 [NDBD] INFO--Angel connected to ' 192.168.75.129:1186 '
2017-03-06 14:04:07 [NDBD] INFO--Angel allocated Nodeid:2

Finally, when all the node configuration is complete, go back to the management node, use the above mentioned show to view, similar display below, prove that the connection is complete

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=2 (not connected, accepting connect from 192.168.75.128)
Id=3 @192.168.75.130 (mysql-5.1.63 ndb-7.1.23, starting, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @192.168.75.129 (mysql-5.7.16 ndb-7.5.4)

[Mysqld (API)] 2 node (s)
Id=4 (not connected, accepting connect from any host)
Id=5 (not connected, accepting connect from any host)

Test

Modify MySQL password unified, modify MySQL access, so that external IP can remotely access MySQL

Then create a database on one of the above, see if the other one is synchronized, then create the table, then add the delete and so on.

The only thing to note is that when you create a table, you must select the engine for the table ndbcluster, otherwise the table will not be synchronized

The following is the test

If you use SQL to create a table, the command is: CREATE TABLE student (age INT) Engine=ndbcluster

Start and close

Start the MySQL cluster. The boot order is: management node → data node →sql node.

Start the command above all, delete--initial can

Shutdown only need to close the management node, the subsequent data node will be closed at the same time, MySQL is the same as the original

Management node shutdown command: NDB_MGM-E shutdown

Summarize

1, in the actual need to separate data nodes and SQL nodes.

2, install the best reference official website of the document to configure the installation, the online blog installation may be the old version, the command may be different, even I do not know that you are looking at the time is not a new version of the official website, may have been changed in some places.

3, the configuration file is too simple, there are many configurations will be used in the actual, need to refer to the document to add.

4, the structure is simple, the experiment is enough, the reality is far from enough.

5, has not tested all kinds of node disconnection, the server stopped, the database service stopped, whether there will be data anomalies and so on special circumstances, also need to be considered. Because once the data is out of sync, it can be tricky to recover the data.

Finally give the MySQL official website reference Document: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-installation.html

Official website Installation steps may be different from mine, I as soon as possible to make another one, the official website has the configuration and start-up division, each at the same time, so need to see clearly.

The way to implement the cluster is more than this one, the Internet gives several ways, here only for reference:

Http://www.2cto.com/database/201504/387166.htm

Reprint Please specify source: http://www.cnblogs.com/linkstar/p/6510713.html

Linkinstar

"Turn" mysql-cluster-7.5 build a DB cluster