MMM CenterOS 7.5下Mysql cluster 高可用集群搭建

MMM CenterOS 7.5下Mysql  cluster 高可用集群搭建

节点规划

  管理节点（1个）： 10.106.224.181

  Sql节点（2个）： 10.106.224.182:3306   10.106.224.183:3306

  数据节点（2个）：10.106.224.182   10.106.224.183

安装mysql cluster 软件预先准备事项

Mysql cluster安装之前需要将mysql server 卸载掉，如果mysql已经卸载，则直接安装mysqlcluster

1.删除原先的mysql 或者 mariadb（三台都卸载）

 [root@hadoop01 ~]# rpm -qa|grep mariadb

 mariadb-libs-5.5.56-2.el7.x86_64

[root@hadoop01 ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

[root@hadoop01 ~]# rpm -qa|grep mariadb

  2.下载mysql cluster ，这里我们采用7.6 版本

3.假设每个节点都采用mysql用户来运行程序，先在各个节点上创建该用户

Groupadd mysql

Useradd mysql –g mysql

安装 数据和mysql节点上的mysql数据库

4. Sql和数据节点10.106.224.182  10.106.224.183 下对mysql-cluster-gpl-7.6.12-el7-x86_64.tar.gz 进行解压 并安装mysql

解压

tar -zxvf mysql-cluster-gpl-7.6.12-el7-x86_64.tar.gz

将解压的文件拷贝到/usr/local/下 并重命名为mysql-cluster7.6

mv mysql-cluster-gpl-7.6.12-el7-x86_64  /usr/local/mysql-cluster7.6

在mysql-cluster7.6目录下创建data文件夹

    mkdir data

   创建my.cnf 文件并放到/etc 下 内容如下（注意server_id 要进行区分）

[client]
socket=/usr/local/mysql-cluster7.6/mysql.sock
port=3306[mysqld]
basedir=/usr/local/mysql-cluster7.6
datadir=/usr/local/mysql-cluster7.6/data
socket=/usr/local/mysql-cluster7.6/mysql.sock
port=3306
symbolic-links=0
log-error=/usr/local/mysql-cluster7.6/mysqld.log
pid-file=/usr/local/mysql-cluster7.6/mysqld.pid
log_bin=mysql-bin
server_id=1
default-storage-engine=innodb
character_set_server=utf8
skip-grant-tables

将mysql-cluster7.6授权给mysql

chown -R mysql:mysql mysql-cluster7.6

     初始化数据库

cd /usr/local/mysql-cluster7.6/bin

./mysqld --initialize --user=mysql --basedir=/usr/local/mysql-cluster7.6 --datadir=/usr/local/mysql-cluster7.6/data

     启动mysql

          ./mysqld_safe --user=mysql&

    检查是否启动

ps -ef|grep mysql

 将mysql路径添加到/etc/profile便于指令操作

source /etc/profile

设置登录密码

   添加 skip-grant-tables 到/etc/my.cnf 中并重启mysql

   Kill 19417     （上图的mysql 进程是19417）

./mysqld_safe --user=mysql&

mysql –u root

update mysql.user set authentication_string=password('123456') where user='root';

flush privileges;

exit 

mysql -u root –p

输入刚设置的密码123456

alter user user() identified by '123456';

创建一个授权用户

grant all privileges on *.* to 'goodhope'@'%'  identified by '123456' with grant option;

flush privileges;

   exit

   添加到服务中去

         cp /usr/local/mysql-cluster7.6/support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod a+x /etc/rc.d/init.d/mysqld

chkconfig --add mysqld

systemctl enable  mysqld

systemctl status mysqld

管理节点上安装cluster 并初始化

5.管理节点10.106.224.181 下对mysql-cluster-gpl-7.6.12-el7-x86_64.tar.gz 进行解压

  tar -zxvf mysql-cluster-gpl-7.6.12-el7-x86_64.tar.gz

6. 10.106.224.181开始安装mysql cluster

 mkdir /usr/local/mysql-cluster7.6

mv mysql-cluster-gpl-7.6.12-el7-x86_64/*  /usr/local/mysql-cluster7.6/

chown -R  mysql:mysql  /usr/local/mysql-cluster7.6

7.创建config.ini 和 管理节点配置目录 文件目录（后面初始化管理节点会用到）

创建目录为管理节点初始化使用（放置集群二进制配置文件）

mkdir /usr/local/mysql-cluster7.6/cluster-configdir

chown mysql:mysql /usr/local/mysql-cluster7.6/cluster-configdir

创建文件目录

mkdir /usr/local/mysql-cluster7.6/data

chown mysql:mysql /usr/local/mysql-cluster7.6/data

   创建配置文件

cd /usr/local/mysql-cluster7.6/

vi config.ini

   内容如下：

[ndbd default]

NoOfReplicas=2  #数据写入数量。2表示两份

DataMemory=512M  #配置数据存储可使用的内存

IndexMemory=180M  #索引给180M

[ndb_mgmd]

nodeid=1

HostName=10.106.224.181 #管理节点的IP地址

DataDir=/usr/local/mysql-cluster7.6/data  #管理节点的日志

[ndbd]

nodeid=2

HostName=10.106.224.182

DataDir=/usr/local/mysql-cluster7.6/data  #mysql数据存储路径

[ndbd]

nodeid=3

HostName=10.106.224.183

DataDir=/usr/local/mysql-cluster7.6/data

[mysqld]

nodeid=4

HostName=10.106.224.182

[mysqld]

nodeid=5

HostName=10.106.224.183

8.初始化管理节点

 /usr/local/mysql-cluster7.6/bin/ndb_mgmd --initial --ndb-nodeid=1 -f /usr/local/mysql-cluster7.6/config.ini  --configdir=/usr/local/mysql-cluster7.6/cluster-configdir

（#--initial：第一次启动时加上，其它时候不要加，不然会数据清空，除非是在备份、恢复或配置变化后重启时）

返回结果如下：

MySQL Cluster Management Server mysql-5.7.28 ndb-7.6.12

2019-12-11 16:42:36 [MgmtSrvr] WARNING  -- at line 4: [DB] IndexMemory is deprecated, use Number bytes on each ndbd(DB) node allocated for storing indexes instead

  该集群版本使用mysql是5.7.28 ，另外我们从返回警告信息看到IndexMemory 后续会针对各个db进行配置，而不是统一配置了，这个比较灵活了

查看二进制配置路径

9.检查是否正常启动 ndb_mgmd

ps -ef |grep ndb_mgmd

10.编写启动sh脚本如下

vi mysql-cluster-start.sh

ndb_mgmd --ndb-nodeid=1 -f /usr/local/mysql-cluster7.6/config.ini  --configdir=/usr/local/mysql-cluster7.6/cluster-configdir

授权执行权限

chmod 700 mysql-cluster-start.sh

11.进入bin目录查看集群状态

./ndb_mgm

Show

配置 数据和mysql节点

12.修改10.106.224.182 和10.106.224.183 上my.conf 文件

[mysqld]

ndbcluster #使用ndb集群引擎

ndb-connectstring=10.106.224.181 #指定管理集群的ip地址,多个以，分隔

default-storage-engine=ndbcluster #设置默认引擎

[mysql_cluster]

ndb-connectstring=10.106.224.181

13.启动ndbd

/usr/local/mysql-cluster7.6/bin/ndbd  --initial

注意第一初始化才需要 –initial, 后面启动去掉 –initial 就可以

ps -ef |grep ndbd

14.登录10.106.224.181 查看集群状态

/usr/local/mysql-cluster7.6/bin/ndb_mgm

show

关闭和开启集群

15.关闭集群

先关闭管理节点（10.106.224.181）

ndb_mgm -e shutdown

执行完成之后管理节点会关闭，数据节点也会关闭，但SQL节点不会，也就是数据库服务需要手动到每一台服务器上停止以保证数据同步）

到182 和183 上关闭mysql

16.开启集群

顺序为 管理节点→数据节点→SQL节点。

10.106.224.181启动管理节点（使用我们创建的sh脚本）

182 和183 上启动数据节点

/usr/local/mysql-clester7.6/bin/ndbd

systemctl start mysqld

systemctl status mysqld

17.登录管理器 查看集群状态

测试机器同步

18.测试数据同步

182 上操作

create database test_cluster;

use test_cluster;

CREATE TABLE `test_cluster`.`new_table` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `namne` VARCHAR(50) NULL,

  PRIMARY KEY (`id`));

insert into new_table(namne) values('this is a test for cluster');

183 上进行查询

select *from test_cluster.new_table

到此mysql 集群安装完成

参考资料

MYSQL系列书籍

高可用mysql: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (访问密码: 2651)

MySQL王者晋级之路.pdf: https://url41.ctfile.com/f/49289241-959127432-204284?p=2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (访问密码: 2651)

MySQL技术内幕 第4版.pdf: https://url41.ctfile.com/f/49289241-959125506-a5bcec?p=2651 (访问密码: 2651)

MySQL管理之道，性能调优，高可用与监控（第二版）.pdf: https://url41.ctfile.com/f/49289241-959124249-d59f54?p=2651 (访问密码: 2651)

深入浅出MySQL数据库开发、优化与管理维护第2版.pdf: https://url41.ctfile.com/f/49289241-961464090-68bf10?p=2651 (访问密码: 2651)

高性能MySQL.第3版.Baron Schwartz.pdf: https://url41.ctfile.com/f/49289241-961462308-52cc5d?p=2651 (访问密码: 2651)

MYSQL内核：INNODB存储引擎 卷1.pdf: https://url41.ctfile.com/f/49289241-961461357-ee63e3?p=2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (访问密码: 2651)

MySQLDBA修炼之道.pdf: https://url41.ctfile.com/f/49289241-961459500-9b201d?p=2651 (访问密码: 2651)

MySQL5.7从入门到精通.pdf: https://url41.ctfile.com/f/49289241-961459329-48cbcf?p=2651 (访问密码: 2651)

高可用mysql.pdf: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (访问密码: 2651)

HIVE电子书

Practical Hive.pdf: https://url41.ctfile.com/f/49289241-959129883-d35ee9?p=2651 (访问密码: 2651)

Hive-Succinctly.pdf: https://url41.ctfile.com/f/49289241-959129709-30f30b?p=2651 (访问密码: 2651)

Apache Hive Essentials.pdf: https://url41.ctfile.com/f/49289241-959129691-b1a4aa?p=2651 (访问密码: 2651)

Apache Hive Cookbook.pdf: https://url41.ctfile.com/f/49289241-959129619-3a8ea6?p=2651 (访问密码: 2651)

hadoop电子书

Practical Hadoop Migration.pdf: https://url41.ctfile.com/f/49289241-959131470-dd3e24?p=2651 (访问密码: 2651)

Hadoop实战-陆嘉恒(高清完整版).pdf: https://url41.ctfile.com/f/49289241-959131365-433ec9?p=2651 (访问密码: 2651)

Hadoop & Spark大数据开发实战.pdf: https://url41.ctfile.com/f/49289241-959131032-ba40ea?p=2651 (访问密码: 2651)

Expert Hadoop Administration.pdf: https://url41.ctfile.com/f/49289241-959130468-ba70cd?p=2651 (访问密码: 2651)

Big Data Forensics - Learning Hadoop Investigations.pdf: https://url41.ctfile.com/f/49289241-959130435-9ab981?p=2651 (访问密码: 2651)

python电子书

python学习手册.pdf: https://url41.ctfile.com/f/49289241-959129403-5b45b1?p=2651 (访问密码: 2651)

Python基础教程-第3版.pdf: https://url41.ctfile.com/f/49289241-959128707-de6ef2?p=2651 (访问密码: 2651)

Python编程：从入门到实践.pdf: https://url41.ctfile.com/f/49289241-959128548-ce965d?p=2651 (访问密码: 2651)

Python Projects for Beginners.pdf: https://url41.ctfile.com/f/49289241-959128461-b53321?p=2651 (访问密码: 2651)

kafka电子书

Learning Apache Kafka, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959134953-a14305?p=2651 (访问密码: 2651)

Kafka权威指南.pdf: https://url41.ctfile.com/f/49289241-959134932-295734?p=2651 (访问密码: 2651)

Kafka in Action.pdf: https://url41.ctfile.com/f/49289241-959134116-12111a?p=2651 (访问密码: 2651)

Apache Kafka实战.pdf: https://url41.ctfile.com/f/49289241-959133999-76ef77?p=2651 (访问密码: 2651)

Apache Kafka Cookbook.pdf: https://url41.ctfile.com/f/49289241-959132547-055c36?p=2651 (访问密码: 2651)

spark电子书

Spark最佳实践.pdf: https://url41.ctfile.com/f/49289241-959415393-5829fe?p=2651 (访问密码: 2651)

数据算法--Hadoop-Spark大数据处理技巧.pdf: https://url41.ctfile.com/f/49289241-959415927-5bdddc?p=2651 (访问密码: 2651)

Spark大数据分析实战.pdf: https://url41.ctfile.com/f/49289241-959416377-924161?p=2651 (访问密码: 2651)

Spark 2.0 for Beginners.pdf: https://url41.ctfile.com/f/49289241-959416710-7ea156?p=2651 (访问密码: 2651)

Pro Spark Streaming.pdf: https://url41.ctfile.com/f/49289241-959416866-6116d7?p=2651 (访问密码: 2651)

Spark in Action.pdf: https://url41.ctfile.com/f/49289241-959416986-e759e9?p=2651 (访问密码: 2651)

Learn PySpark.pdf: https://url41.ctfile.com/f/49289241-959417049-ac04a0?p=2651 (访问密码: 2651)

Fast Data Processing with Spark.pdf: https://url41.ctfile.com/f/49289241-959417157-8ec3b0?p=2651 (访问密码: 2651)

Fast Data Processing with Spark, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959417211-856d08?p=2651 (访问密码: 2651)

OReilly.Learning.Spark.2015.1.pdf: https://url41.ctfile.com/f/49289241-959417292-90c1bc?p=2651 (访问密码: 2651)

High Performance Spark.pdf: https://url41.ctfile.com/f/49289241-959417439-7e7893?p=2651 (访问密码: 2651)

Machine Learning with PySpark.pdf: https://url41.ctfile.com/f/49289241-959417580-5941b3?p=2651 (访问密码: 2651)

Spark for Python Developers.pdf: https://url41.ctfile.com/f/49289241-959417721-d59fbe?p=2651 (访问密码: 2651)

Spark Cookbook.pdf: https://url41.ctfile.com/f/49289241-959417811-19c75d?p=2651 (访问密码: 2651)

Big Data Analytics with Spark.pdf: https://url41.ctfile.com/f/49289241-959417907-41dbce?p=2651 (访问密码: 2651)

PySpark SQL Recipes.pdf: https://url41.ctfile.com/f/49289241-959417970-c23242?p=2651 (访问密码: 2651)

Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: https://url41.ctfile.com/f/49289241-959417997-a5e3f5?p=2651 (访问密码: 2651)

OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: https://url41.ctfile.com/f/49289241-959418024-2ff34c?p=2651 (访问密码: 2651)

Big Data Analytics Beyond Hadoop_ Real-Time Applications with Storm, Spark, and More Hadoop Alternatives.pdf: https://url41.ctfile.com/f/49289241-959418042-581fb9?p=2651 (访问密码: 2651)