Using Mysql-cluster to build a MySQL db cluster

1, the role of MySQL cluster:

- resolves a single point of failure for access nodes

- single point of failure for data storage nodes

- Troubleshoot data storage node data backup issues

2. Cluster:

Use a group of servers to provide the same service

3, about Mysql-cluster:

MySQL 's officially available cluster version

Standard version is integrated MySQL program, can be installed and used independently

Adopt NDB(Network DataBase) engine

Assume that each node has a separate memory, hard disk

Reduce single point of failure across the database with inexpensive hardware

4, the role of the server in the cluster

- Data node:ndbd ( single threaded ) NDB_MTD ( multithreaded ) store data (table of Records)

-SQL node:mysqld is the interface that the client accesses the data and is responsible for executing the SQL command

can be understood to support NDB Standard MySQL server, only table structure, independent user authorization

client-facing user authentication, access services such as SQL queries

- Management node:NDB_MGMD Manage all servers in the cluster

clients: Client access to data

5. Case topology:

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/82/D7/wKioL1diZQbROH8sAAJm9-ucjMk347.png-wh_500x0-wm_3 -wmp_4-s_4123782686.png "title=" 1.png "alt=" Wkiol1dizqbroh8saajm9-ucjmk347.png-wh_50 "/>

6 ,650) this.width=650; "width=" 1 "height=" 1 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/themes/default/images/word.gif") no-repeat center;border:1px solid #ddd; "alt=" spacer.gif "/ >mysql cluster Construction (configuration)

1 ) Public Configuration :

# Rpm-qa | Grep-i MySQL

# service MySQL stop; Chkconfig MySQL off

# rpm-e--nodeps mysql-devel mysql-embeddedmysql-test mysql-server mysql-client mysql-shared MySQL-shared-compat

Keep the mysql-libs package that comes with RHEL

# RM-RF/ETC/MY.CNF

# rm-rf/var/lib/mysql/*

# Tar-xvfmysql-cluster-gpl-7.3.3-1.el6.x86_64.rpm-bundle.tar

# RPM-UVH mysql-cluster-*.rpm

2 ) configuration Management node :

run the management process NDB_MGMD

profile /etc/config.ini (the working directory of the IP ID number that corresponds to the role and role of the server )

[NDBD Default] : Public configuration of data nodes

[NDB_MGMD] : Specify the Management node

[NDBD] : Specify the Data node

[Mysqld] : Specify the SQL node

# mkdir-p/var/log/mysql-cluster// Create working directory

# Vim/etc/config.ini// Write master config file

[NDBD Default]

noofreplicas=2// reserved 2 copies of data

datamemory=80m// data cache size

indexmemory=18m// index Cache size

[NDB_MGMD]

ID identification of the nodeid=7// Management node

IP Address of the hostname=192.168.4.100// Management node

Datadir=/var/log/mysql-cluster// working directory

[NDBD]// Set data node NDBA

ID identification of nodeid=8// data node NDBA

hostname=192.168.4.30//IP address of this node

Datadir=/var/log/mysql-cluster/data// working directory

[NDBD]// Set data node NDBB

Nodeid=9

hostname=192.168.4.40

Datadir=/var/log/mysql-cluster/data

[mysqld]// set SQL node sqla

ID identification of the nodeid=10//sql node sqla

hostname=192.168.4.10//IP address of this node

[mysqld]// set SQL node sqlb

nodeid=11

hostname=192.168.4.20

: Wq

3 ) Configure the data node :

running the data process NDBD

configuration file: /etc/my.cnf

#mkdir-P/var/log/mysql-cluster/data// Create working directory

#vim/etc/my.cnf

[Mysqld]

Datadir=/var/log/mysql-cluster/data// working directory

NDB-CONNECTSTRING=192.168.4.100// Management node IP address

Ndbcluster// specify using ndbcluster cluster storage engine

[Mysql_cluster]

NDB-CONNECTSTRING=192.168.4.100// How to connect the management node

: Wq

4 ) Configure SQL node

running MySQL service MySQL(this service is provided by the Mysql-cluster package)

#vim/etc/my.cnf

[Mysqld]

Ndbcluster// specify using ndbcluster cluster storage engine

Default-storage-engine=ndbcluster// The default storage engine for the specified table is ndbcluster

ndb-connectstring=192.168.4.100

[Mysql_cluster]

NDB-CONNECTSTRING=192.168.4.100// How to connect the management node

: Wq

7. Start processes on different role servers

1) start the management process on the management node

#Ndb_mgmd-f/etc/config.ini

start the process, with the-f option read . ini configuration, default background mode, debugging can add --nodaemon; After modifying the configuration, you can add --initial Re-initialize

#netstat-untlap | grep:1186// View process

#pkill-9 NDB_MGMD// kill process

2) Start the data process on the data node in turn

#NDBD

The first execution, can be added --initial initialization; Multiple data nodes operate the same

3) start The database service on the SQL node in turn

#Service MySQL start // multiple SQL node operations are the same

8. View cluster status

1) Login management interface on Management node to View status

#ndb_mgm

Ndb_mgm> Show

Cluster Configuration

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

[NDBD (NDB)] 2node (s)

Id=8 @192.168.4.30 (mysql-5.6.14 ndb-7.3.3, nodegroup:0, *)

Id=9 @192.168.4.40 (mysql-5.6.14 ndb-7.3.3, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)

Id=7 @192.168.4.100 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)

id=10 @192.168.4.10 (mysql-5.6.14 ndb-7.3.3)

id=11 @192.168.4.20 (mysql-5.6.14 ndb-7.3.3)

Ndb_mgm>

2)MySQL initialization of SQL node

resetting the root password; Adding user authorization for Client access

#service Mysqlstart

#mysql-uroot-p Initial password // initial password see /root/.mysql_secret file

3) on the SQL node, check the default storage engine

Mysql>show engines;

9. Test the single point of failure of the access node

authorize on the SQL node to connect itself from the remote client

Grant all on webdb.* to [e-mail protected] "%" identified by "webuser88"; two SQL nodes are authorized

Client Access:#mysql-h192.168.10/20-uwebuser-pwebuser88

Mysql>create database webdb;

Mysql>create table WEBDB.A (id int);

Mysql>insert into WEBDB.A values (1000);

Mysql> select * from WEBDB.A;

accesses the SQLA node to manipulate the data and synchronizes the results on the sqlb .

10, test Data node single point of failure

data nodes can be backed up with each other, automatically elected Master, automatically synchronizes data. When testing, you can turn off the NDBD service of a data node , insert and query the synchronization of data validation data.

After the SQL node and data node processes are running correctly, the management node can be switched off without restarting

This article is from the IT Technology Learning blog, so be sure to keep this source http://learningit.blog.51cto.com/9666723/1789961

Using Mysql-cluster to build a MySQL db cluster