Mysql Cluster Cluster configuration detailed

I. MySQL cluster introduction

MySQL clustering technology provides redundancy for MySQL data in distributed system, and enhances security, so that single MySQL server failure does not have a huge negative effect on the system, and the stability of the system is guaranteed.
A MySQL cluster requires a set of computers, and the roles of each computer may be different. There are three types of nodes in the MySQL cluster: management nodes, data nodes, and SQL nodes. A computer in a cluster may be a node or a collection of two or three nodes. These three nodes are logically divided, so they do not necessarily correspond to the physical computer one by one.

Management nodes (also known as Management Servers) are primarily responsible for managing data nodes and SQL nodes, as well as cluster configuration files and cluster log files. It monitors the working state of other nodes and is capable of starting, shutting down, or restarting a node. Other nodes retrieve configuration data from the management node, sending event information to the management node and writing to the cluster log when a new event occurs on the data node.
Data nodes are used to store data.
SQL node is the same as the normal MySQL server, we can use it for SQL operations.

The relationships between the three cluster nodes and applications are drawn:

Two. Deployment process

From the introduction above, there are three nodes in the MySQL cluster: management nodes, data nodes, and SQL nodes, so if you want to implement a highly available redundant cluster, you need at least 6 hosts.

Here we put the data node and the SQL node on the same host, using the stage host, the host information is as follows:

10.10.91.71 Node1 # MGMD Node
10.10.91.72 node2 # SQL and Data node
10.10.91.75 node3 # SQL and Data node
10.10.91.77 Node4 # MGMD Node

1.  Initialize the system

  each node operates

# systemctl disable firewalld # vi /etc/ selinux/config    selinux=disabled    #  hostnamectl  set-hostname xxx# cd/usr/local/# wget http://mirrors.sohu.com/mysql/mysql-cluster-7.5/ mysql-cluster-gpl-7.5.5-linux-glibc2.5-x86_64.tar.gz  #  can be downloaded according to time, from other image source or official download # tar - zxf mysql-cluster-gpl-7.5.5-linux-glibc2.5-x86_64.tar.gz && mv  mysql-cluster-gpl-7.5.5-linux-glibc2.5-x86_64.tar.gz /usr/local/mysql# cp /usr/local/mysql/bin/ ndb_mgm* /usr/local/bin/# chmod +x /usr/local/bin/ndb_mgm*# chmod +x /usr/ local/mysql/bin/# echo  "export path= $PATH:/usr/local/mysql/bin/"  >> /etc/ Profile# reboot 

2. Configuring the Management node on Node1 and Node4

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

Node1

[NDBD Default] noofreplicas=2datamemory=512mindexmemory=18m[ndb_mgmd]nodeid=1hostname=10.10.91.71datadir=/var/lib/ Mysql-cluster[ndb_mgmd]nodeid=6hostname=10.10.91.77datadir=/var/lib/mysql-cluster[ndbd]nodeid=2hostname= 10.10.91.72datadir=/usr/local/mysql/data[ndbd]nodeid=3hostname=10.10.91.75datadir=/usr/local/mysql/data[mysqld ]nodeid=4hostname=10.10.91.72[mysqld]nodeid=5hostname=10.10.91.75

Node4

[NDBD Default] noofreplicas=2datamemory=512mindexmemory=18m[ndb_mgmd]nodeid=6hostname=10.10.91.77datadir=/var/lib/ Mysql-cluster[ndb_mgmd]nodeid=1hostname=10.10.91.71datadir=/var/lib/mysql-cluster[ndbd]nodeid=2hostname= 10.10.91.72datadir=/usr/local/mysql/data[ndbd]nodeid=3hostname=10.10.91.75datadir=/usr/local/mysql/data[mysqld ]nodeid=4hostname=10.10.91.72[mysqld]nodeid=5hostname=10.10.91.75

3. Configuring data nodes and SQL nodes on Node2/node3

# groupadd MySQL && useradd-g mysql mysql# vi/etc/my.cnf

Node2

[client]socket=/usr/local/mysql/mysql.sock[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket= /usr/local/mysql/mysql.sockuser=mysqlsymbolic-links=0ndbclusterndb-connectstring=10.10.91.71,10.10.91.77[ mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysql_cluster]ndb-connectstring= 10.10.91.71,10.10.91.77

Node3

[client]socket=/usr/local/mysql/mysql.sock[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket= /usr/local/mysql/mysql.sockuser=mysqlsymbolic-links=0ndbclusterndb-connectstring=10.10.91.71,10.10.91.77[ mysqld_safe]log-error=/var/log/mysqld.logpid-file=/usr/local/mysql/mysqld.pid[mysql_cluster]ndb-connectstring= 10.10.91.71,10.10.91.77

# cd /usr/local/mysql# bin/mysql_install_db --user= mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data# chown -r  mysql.mysql /usr/local/mysql# cp mysql/support-files/mysql.server /etc/rc.d/init.d/#  Chmod +x /etc/rc.d/init.d/mysql.server# chkconfig --add mysql.server 

4 cluster cluster start

# Start Management node, first boot requires +--initial# ndb_mgmd-f/var/lib/mysql-cluster/config.ini--initial # Start Data node, first boot required +--initial#/usr/loc AL/MYSQL/BIN/NDBD--initial # Start SQL node #/usr/local/mysql/bin/mysqld_safe--user=mysql &

# View cluster status

# Operations on the Management node:

[Email protected] local]# ndb_mgm--NDB Cluster--Management Client--ndb_mgm> showconnected to Management Server at: Localhost:1186cluster Configuration---------------------[ndbd (NDB)]2 node (s) id=2@10.10.91.72 (mysql-5.7.17 ndb-7.5.5, nodegroup:0, *) # DBDB node id=3@10.10.91.75 (mysql-5.7.17 ndb-7.5.5, nodegroup:0) [NDB_MGMD (MGM)]2 node (s) id= 1@10.10.91.71 (mysql-5.7.17 ndb-7.5.5) # MGMD node id=6@10.10.91.77 (mysql-5.7.17 ndb-7.5.5) [Mysqld (API)]2 node (s) id=4@10 .10.91.72 (mysql-5.7.17 ndb-7.5.5) # mysqld node id=5@10.10.91.75 (mysql-5.7.17 ndb-7.5.5)

5. Testing

#  The database password for the SQL node is modified to the same

#/usr/local/mysql/bin/mysql_secure_installation # This command modifies the password mysql-u root-p for the MySQL initialization command operation or using the following method; Random password (see/root/.mysql_secret file for details), after entering, use the following instructions to change the password: SET PASSWORD = PASSWORD (' new password ');

# SQL node 1 on the new library, and insert the data

# mysql-uroot-pmysql> CREATE DATABASE aa;mysql> use aa;mysql> CREATE TABLE student (ID int (TEN) primary key auto_ Increment,name varchar (+), age tinyint (2)) engine=ndb; # The database table engine must be NDB, or the synchronization fails, you can use show create table name; To view engine mysql> insert into student (id,name,age) value (321281, "Mzh",);mysql> quit

# SQL Node 2 to see if synchronization

mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  aa                  ||  mysql              | |  ndbinfo            | |  performance_schema | |  sys                |+--- -----------------+6 rows in set  (0.00 sec) mysql> use aa;mysql>  show tables;+--------------+| tables_in_aa |+--------------+| student       |+--------------+1 row in set  (0.01 sec)  mysql> select  * from student;+--------+------+------+| id     | name | age  |+--------+------+- -----+| 321281 | mzh  |   18 |+--------+------+------+1 row  in set  (4.27 sec)    #  do here, have seen can be synchronized. Then we simulate the SQL Node 1 downtime, insert new data on the SQL2, and then SQL1 the boot to see if the data will be synchronized.

 

#  operation on sql 1: # /etc/rc.d/init.d/mysql.server stop# ps -ef| grep mysql  #  in  SQL 2  operation:mysql> insert into student  (id,name,age)   Value (12121, "HP", mysql> select * from student;+--------+------+------+| id      | name | age  |+--------+------+------+|   12121 | hp   |   19 | |  321281 | mzh  |   18 |+--------+------+------+2 rows  in set  (0.00 sec)     #  see if Sync # /etc/rc.d/on  SQL1  init.d/mysql.server startmysql> select * from student;+--------+------+------+|  id     | name | age  |+--------+------+------+|   12121 | hp   |   19 | |  321281 | mzh  |   18 |+--------+------+------+2 rows in  set  (0.00 SEC)

6. Mysql Cluster shutdown and startup

Manage node actions: # NDB_MGM-E shutdown # close # ndb_mgmd-f/var/lib/mysql-cluster/config.ini # boot #/usr/local/mysql-cluster/ndb_mg M-E exit # exit #/USR/LOCAL/MYSQL-CLUSTER/NDB_MGM-E Show # View and manage Data nodes: #/usr/local/mysql/bin/ndbd # Boot, data node and Management node no boot feet This, you can manually write or add to the system's starter script SQL node operations: #/etc/rc.d/init.d/mysql.server stop/start/restart# chkconfig mysql.server # Add to boot

Mysql Cluster Cluster configuration detailed