Shulou(Shulou.com)06/01 Report--

1. The role of MySQL cluster:

- Resolving single points of failure of access nodes

- Single point of failure of data storage nodes

- Solve data storage node data backup problems

2. Cluster:

Use a set of servers to provide the same service

About MySQL-Cluster:

MySQL official cluster version

Integrated standard MySQL program, can be installed independently

Using NDB (Network DataBase) engine

Assume that each node has its own memory, hard disk,

Reduce single points of failure of the entire database with inexpensive hardware

The role of the server in the cluster

- Data node: ndbd(single thread) ndb_mtd(multithread) stores data (records in table)

- SQL node: mysqld is the interface for clients to access data, responsible for executing SQL commands

It can be understood as a standard MySQL server supporting NDB, only storing table structure, independent user authorization

Provide access services such as user authentication and SQL query for clients

- Management node: ndb_mgmd manages all servers in the cluster

Client: client accessing data

5. Case topology:

6. MySQL cluster setup (configuration)

1) Common 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 RHEL's own mysql-libs package

# 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 admin process ndb_mgmd

The requested URL/etc/config.ini was not found on this server.

[ndbd default]: Common configuration of the data node

[ndb_mgmd]: Specify an administrative node

[ndbd]: Specify the data node

[mysqld]: Specify sql node

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

# vim/etc/config. ini//Write the main configuration file

[ndbd default]

NoOfReplicas=2 //Keep 2 copies of data

DataMemory=80M //Data cache size

IndexMemory=18M //Index cache size

[ndb_mgmd]

nodeid=7 //ID of the management node

hostname= 192.168.4.100//IP address of management node

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

[ndbd] //Set data node ndbA

nodeid=8 //ID of 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

nodeid=10 //sql ID ID of 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 data nodes:

Run 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//IP address of management node

ndbcluster //Specifies the use of the ndbcluster cluster storage engine

[mysql_cluster]

ndb-connectstring= 192.168.4.100//How to connect management nodes

:wq

4) Configure SQL node

Run mysql service mysql (this service is provided by mysql-cluster package)

#vim /etc/my.cnf

[mysqld]

ndbcluster //Specifies the use of the ndbcluster cluster storage engine

default-storage-engine=ndbcluster //specifies that the default storage engine for tables is ndbcluster

ndb-connectstring=192.168.4.100

[mysql_cluster]

ndb-connectstring= 192.168.4.100//How to connect management nodes

: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, read the.ini configuration with the-f option; default background mode, add--nodaemon when debugging; after modifying the configuration, add--initial to reinitialize

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

#pkill -9 ndb_mgmd //kill process

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

#ndbd

//When executed for the first time, add--initial initialization; multiple data nodes operate the same

3) Start database services on sql node in turn

#service mysql start //multiple sql nodes operate the same

8. View cluster status

1) Log in to the management interface on the management node to view the 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

Reset root password; add user authorization for client access

#service mysqlstart

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

3) On SQL node, check default storage engine

Mysql>show engines;

9. Test access nodes for single point failures

Authorization on sql nodes allows you to connect to yourself from remote clients

Grant all on webdb.* to webuser@"%"identified by "webuser88"; //both sql nodes do authorization

Client access: #mysql-h292.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 ;

Access sqlA node manipulates data and synchronizes results on sqlB.

10. Single point failure of test data node

Data nodes can backup each other, automatically elect masters, and automatically synchronize data. During testing, you can turn off the ndbd service of a data node, insert and query data to verify the synchronization of data.

After the process of SQL node and data node runs normally, if there is no need to restart, the management node can be shut down.

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.