MySQL cluster + master-Slave synchronization

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. Achieve data redundancy.

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

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

2.2 Advantages

• Multiple nodes can be distributed in 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.
• Redundancy is good, there is complete 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:xuegod63.cn IP:192.168.31.130

MySQL data node:xuegod63.cn IP:192.168.31.130

MySQL data node:xuegod64.cn IP:192.168.31.131

MSYQL SQL node:xuegod63.cn IP:192.168.31.130

MSYQL SQL node:xuegod64.cn IP:192.168.31.131

Uploading Mysql-cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar to 130,131 hosts

Actual combat:mysql Cluster setup

1.mysql old version Clear

Yum-y Remove MySQL

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.71-1.el6.x86_64

2.mysql Cluster version Installation preparation

[Email protected] ~]# TAR-XVF Mysql-cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar

[Email protected] yy]# TAR-XVF Mysql-cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar

3. Create a folder

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

Data node storage data,: [[email protected] ~]# Mkdir/var/lib/mysql

Management node: [[email protected] ~]# mkdir/var/lib/mysql-cluster SQL node: Can not use folder authorization

Process dir: [[email protected] ~]# Mkdir/var/run/mysqld

[Email protected] yy]# mkdir/var/run/mysqld

4. Installing the MySQL cluster package

[Email protected] ~]# RPM-IVH mysql-cluster-server-gpl-7.3.7-1.el6.x86_64.rpm

[Email protected] ~]# RPM-IVH mysql-cluster-client-gpl-7.3.7-1.el6.x86_64.rpm

[Email protected] yy]# RPM-IVH mysql-cluster-server-gpl-7.3.7-1.el6.x86_64.rpm

[Email protected] yy]# RPM-IVH mysql-cluster-client-gpl-7.3.7-1.el6.x86_64.rpm

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. Assigning Permissions

[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/usr/mysql-cluster/

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

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

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

Build MySQL cluster

[Email protected] ~]# Vim/var/lib/mysql-cluster/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.130 #管理结点的IP地址. Native IP
###### Data node Options:
[NDBD]
hostname=192.168.31.130
Datadir=/var/lib/mysql #mysql数据存储路径

id=2
[NDBD]
hostname=192.168.31.131
Datadir=/var/lib/mysql
Id=3
# SQL node Options: #关于SQL结点
[Mysqld]
hostname=192.168.31.130
Id=4
[Mysqld]
hostname=192.168.31.131
Id=5

~
Data node +sql node configuration document + Master-Slave synchronization

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]
Datadir=/var/lib/mysql #mysql数据存储路径 Data node (configuration to add)

Ndbcluster #启动NDB engine

ndb-connectstring=192.168.31.130 # 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.130 #管理节点IP地址
[Mysqld]
Log-bin=mysql-bin-master #配置主从同步
Server-id = 10

[Email protected] yy]# VIM/ETC/MY.CNF

[Mysqld]
Datadir=/var/lib/mysql
Ndbcluster
ndb-connectstring=192.168.31.130
[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
[Mysql_cluster]
ndb-connectstring=192.168.31.130
[Mysqld]
Log-bin=mysql-bin-slave
Server-id = 11
~

MySQL Cluster boot

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

[[email protected] ~]# NDBD--initial first launch plus--initial

[Email protected] yy]# NDBD--initial

Start the SQL Node service

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

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

Set password and related MySQL settings

[Email protected] ~]# Cat/home/yy/.mysql_secret

[Email protected] ~]# mysql_secure_installation

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

[Email protected] yy]# mysql_secure_installation

To view mysql cluster status:

[Email protected] ~]# NDB_MGM

130

mysql> Grant replication Slave on * * to [e-mail protected] identified by "123456";

mysql> flush Privileges;

131

Mysql>change Master to master_host= ' 192.168.31.130 ', master_user= ' slave ', master_password= ' 123456 ';

mysql> start slave;

Mysql> Show slave status\g View status

130

mysql> CREATE Database yy;

Mysql> Use yy

Mysql> CREATE TABLE TT (ID int);

Mysql> INSERT INTO TT values (11);

131

Mysql> Use yy

Mysql> select * from TT;

Close Service

[Email protected] ~]# NDB_MGM

--NDB Cluster--Management Client--

ndb_mgm> shutdown

Ndb_mgm> exit

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

[[email protected] ~]# kill-9 PID number

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

[[email protected] yy]# kill-9 PID number

Start the service

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

[Email protected] ~]# NDBD

[Email protected] yy]# NDBD

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

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

Note: The cluster did the database synchronization, did not do the database table synchronization, or did not understand the meaning of doing so, if the cluster can not master-slave synchronization, then the previous picture is not accurate.

So here's a custom addition to the synchronization of the database tables: the master-Slave synchronization method.

Later made M-s s-m, so no matter which side of the data to modify, can be synchronized.

MySQL cluster + master-Slave synchronization