搭建mysql集群实现高可用
搭建mysql集群实现高可用
一:实验目标
MySQL 集群 概述
实戓:MySQL 集群 搭建
二:实验环境
rhel-server-6.5-x86_64-dvd.iso
mysql cluster 集群各机器角色:
Mysql 管理结点:xuegod63.cn IP:192.168.1.63
mysql 数据结点:xuegod63.cn IP:192.168.1.63
mysql 数据结点:xuegod64.cn IP:192.168.1.64
msyql SQL结点:xuegod63.cn IP:192.168.1.63
msyql SQL结点:xuegod64.cn IP:192.168.1.64
MySQL Cluster(MySQL 集群) 概述:
MySQL Cluster 是MySQL适合于分布式计算环境的高实用、高冗余版本。它采用了NDB Cluster 存储引擎,允许在1个 Cluster 中运行多个MySQL服务器。
MySQL Cluster 是一种技术,该技术允许在无共享的系统中部署“内存中”数据库的 Cluster 。通过无共享体系结构,系统能够使用廉价的硬件,而且对软硬件无特殊要求。此外,由于每个组件有自己的内存和磁盘,不存在单点故障。
mysql 集群架构原理:
注释:
SQL节点: 给上层应用层提供sql访问。
管理节点(MGM): 管理整个集群。 启动,关闭集群。 通过ndb_mgmd命令启动集群
存储/数据节点: 保存cluster中的数据。 数据节点,可以提供副本。实现数据冗余。
NDB引擎: 是一种 “内存中”的存储引擎 , 它具有可用性高和数据一致性好的特点
三:实验代码
前提:将MySQL-Cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar 上传到xuegod63和xuegod64上。
xuegod63上执行:
1. mysql旧版本清除: 首先使用如下命令来清理之前操作系统自带的mysql安装:
[[email protected] ~]# yum -y remove mysql #然后使用如下命令:
[[email protected] ~]# rpm -qa | grep mysql* #找到2个剩余mysql包1请按照如下的命令格式予以删除
mysql-libs-5.1.71-1.el6.i686
[[email protected] ~]# rpm -e --nodeps mysql-libs
2. mysql cluster版本安装准备:将MySQL-Cluster-gpl-7.3.4-1.el6.x86_64.rpm-bundle.tar放到某个目录下(譬如/package) 下面,并且执行如下命令解压:
[[email protected] ~]#tar -xvf MySQL-Cluster-gpl-7.3.4-1.el6.x86_64.rpm-bundle.tar #得到如下文件清单:
MySQL-Cluster-client-gpl-7.3.4-1.el6.x86_64.rpm MySQL-Cluster-devel-gpl-7.3.4-1.el6.x86_64.rpm
MySQL-Cluster-embedded-gpl-7.3.4-1.el6.x86_64.rpm MySQL-Cluster-server-gpl-7.3.4-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.3.4-1.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.3.4-1.el6.x86_64.rpm MySQL-Cluster-test-gpl-7.3.4-1.el6.x86_64.rpm
3.创建文件夹(分如下3个类来创建对应的文件夹)
[[email protected] ~]# mkdir /var/lib/mysql #数据(存储)结点
[[email protected] ~]# mkdir /var/lib/mysql-cluster #管理节点
[[email protected] ~]# mkdir /var/run/mysqld #进程DIR
SQL节点:可不用 所以不需要创建
[[email protected] ~]# chmod -R 777 /var/lib/mysql
[[email protected] ~]# chmod -R 777 /var/lib/mysql-cluster
[[email protected] ~]# chmod -R 777 /var/run/mysqld
4.安装mysql集群软件包:
[[email protected] ~]# rpm -ivh MySQL-Cluster-server-gpl-7.3.4-1.el6.x86_64.rpm
[[email protected] ~]# rpm -ivh MySQL-Cluster-client-gpl-7.3.4-1.el6.x86_64.rpm
#特别注意,当安装完毕MySQL-Cluster-server-gpl包后,将出现如下提示信息,提醒我们整个cluster安装后的初次超级账户密码存在/root/.mysql_secret这个文件当中。 ----------------------------------------------------------------------------------------------------------A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. ----------------------------------------------------------------------------------------------------------
5.使用如下的命令来变更权限保证可写入:
[[email protected] ~]# id mysql
uid=495(mysql) gid=489(mysql) groups=489(mysql)
[[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 /var/run/mysqld/
xuegod64 执行
1. mysql旧版本清除: 在xuegod63和xuegod64同时执行: 首先使用如下命令来清理之前操作系统自带的mysql安装:
[[email protected] ~]#yum -y remove mysql #然后使用如下命令:
[[email protected] ~]# rpm -qa | grep mysql*
mysql-libs-5.1.52-1.el6_0.1.x86_64 #对于找到的1个剩余mysql包,请按照如下的命令格式予以删除:
[[email protected] ~]# rpm -e --nodeps mysql-libs
[[email protected] ~]# rpm -qa | grep mysql*
2.复制软件包并安装server包:
[[email protected] ~]# scp MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64.rpm 192.168.1.64:/root/
[email protected]'s password:
[[email protected] ~]# scp MySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm 192.168.1.64:/root
xuegod63安装mysql集群相关的软件包:
[[email protected] ~]# rpm -ivh MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64.rpm
注意,当安装完毕MySQL-Cluster-server-gpl包后,将出现如下提示信息,提醒我们整个cluster安装后的初次超级账户密码存在/root/.mysql_secret这个文件当中。 --------------------------------------------------------------------------------------- A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers.
1)查看mysql root用户密码:
[[email protected] ~]# cat /root/.mysql_secret
# The random password set for the root user at Wed Apr 1 21:10:53 2015 (local time): NSblG9hMkThTgFHY
2)会生成两个主要的命令文件:ndb_mgmd 和ndb_mgm
[[email protected] ~]# which ndb_mgm
/usr/bin/ndb_mgm
[[email protected] ~]# rpm -qf /usr/bin/ndb_mgm
MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64
[[email protected] ~]# which ndb_mgmd /usr/sbin/ndb_mgmd
[[email protected] ~]# rpm -qf /usr/sbin/ndb_mgmd
MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64
3)还会生成用户:mysql
[[email protected] ~]# id mysql
uid=495(mysql) gid=489(mysql) groups=489(mysql)
3.创建文件夹: 存放进程PID文件: mkdir /var/run/mysqld
[[email protected] ~]# id mysql
uid=495(mysql) gid=489(mysql) groups=489(mysql)
[[email protected] ~]# mkdir /var/lib/mysql #不需要创建自动生成
[[email protected] ~]# ll /var/lib/mysql
[[email protected] ~]# mkdir /var/run/mysqld
[[email protected] ~]# chown mysql:mysql -R /var/run/mysqld
注: 数据结点存放数据: /var/lib/mysql 会自动创建。
4.安装mysql集群客户端命令:
[[email protected] ~]# rpm -ivh MySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm
注:MySQL-Cluster-server 和MySQL-Cluster-client 的关系 和msyql-server同mysql 包的关系一样。
xuegod63: 创建管理结点上配置文件:
[[email protected] ~]# cd /var/lib/mysql-cluster
[[email protected] ~]# vi config.ini #写入以下内容
[ndbd default]
NoOfReplicas=2
DataMemory=200M
IndexMemory=100M
##### data node manger:
[ndb_mgmd]
id=1
datadir=/var/lib/mysql-cluster
HostName=192.168.1.63
###### data node options:
[ndbd]
HostName=192.168.1.63
DataDir=/var/lib/mysql
id=2
[ndbd]
HostName=192.168.1.64
DataDir=/var/lib/mysql
id=3
######SQL node options:
[mysqld]
HostName=192.168.1.63
id=4
[mysqld]
HostName=192.168.1.64
id=5
参数说明:
在这个文件里,我们分别给五个节点分配了ID,这有利于更好的管理和区分各个节点。当然,要是不指定,MySQL也会动态分配一个
cat /var/lib/mysql-cluster/config.ini
[ndbd default]
NoOfReplicas=2 #数据写入数量。2表示两份
DataMemory=200M
IndexMemory=100M
##### data node manger:#管理结点
[ndb_mgmd]
id=1
datadir=/var/lib/mysql-cluster # 管理结点的日志
HostName=192.168.1.63 #管理结点的IP地址。本机IP
###### data node options: 存储结点
[ndbd]
HostName=192.168.1.63
DataDir=/var/lib/mysql #mysql数据存储路径
id=2
[ndbd]
HostName=192.168.1.64
DataDir=/var/lib/mysql
id=3
# SQL node options: #关于SQL结点
[mysqld]
HostName=192.168.1.63
id=4
[mysqld]
HostName=192.168.1.64
id=5
xuegod63数据节点+SQL节点配置文档:
[[email protected] /]# vim /etc/my.cnf #写入以下内容
[mysqld]
datadir=/var/lib/mysql #mysql数据存储路径
ndbcluster #启动ndb引擎
ndb-connectstring=192.168.1.63 # 管理节点IP地址
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.1.63 #管理节点IP地址
----------------注:如果xuegod63 只做 SQL节点,则配置文档为:------------
[[email protected] /]# cat !$
cat /etc/my.cnf
[mysqld]
ndbcluster #启动ndb引擎
ndb-connectstring=192.168.1.63 # 管理节点IP地址
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.1.63 #管理节点IP地址
说明: 数据节点和SQL结点配置文件区别 ,就多一行,数据结点有:datadir=/var/lib/mysql ,SQL节点上没有。
-------------------------------------------------------------------------------------------------
xuegod64配置数据结点和SQL结点:生成配置文件:
[[email protected] mysql-cluster]# scp /etc/my.cnf 192.168.1.64:/etc/
[email protected]'s password: 123456
[[email protected] /]# cat /etc/my.cnf #写入以下内容。默认没有my.cnf文件
[mysqld] datadir=/var/lib/mysql #mysql数据存储路径
ndbcluster #启动ndb引擎
ndb-connectstring=192.168.1.63 # 管理节点IP地址
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.1.63 #管理节点IP地址
mySQL Cluster初次启动命令以及用户密码更改调整:(请严格按照次序启动)
启动 mysql cluster
先启动: 管理结点服务-->数据结点服务-->sql结点服务
关闭: 关闭管理结点服务,关闭管理结点服务后,nbdb数据结点服务会自动关闭-->手动把sql结点服务关了。 执行初次启动前请先确认 将两台机器的防火墙关闭(service iptables stop 或者 设定 防火墙端口可通,两个端口即通讯端口1186、数据端口3306 )
1:xuegod63上启动管理结点命令:
[[email protected] ~]#ndb_mgmd -f /var/lib/mysql-cluster/config.ini # mysql cluster 后台运行进程
这里出现警告是正常的。
报错的原因:管理结点好。 数据结点,存储结点。
解答:这里讲管理结点和数据结点用一台服务器搭建,实际生产环境中是不可行的,因为一旦数据结点挂了,那么就意味着数据节点也会挂掉的。
查看端口号:
[[email protected] ~]# netstat -antup | grep 1186
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 7057/ndb_mgmd
tcp 0 0 127.0.0.1:1186 127.0.0.1:60324 ESTABLISHED 7057/ndb_mgmd
tcp 0 0 127.0.0.1:60324 127.0.0.1:1186 ESTABLISHED 7057/ndb_mgmd
2:xuegod63和xuegod64都要启动数据结点服务:
[[email protected] ~]# ndbd --initial #启动初始化
2015-04-01 21:57:58 [ndbd] INFO -- Angel connected to '192.168.1.63:1186'
2015-04-01 21:57:58 [ndbd] INFO -- Angel allocated nodeid: 2
[[email protected] ~]# ndbd --initial #启动初始化
2014-12-02 22:24:31 [ndbd] INFO -- Angel connected to '192.168.1.63:1186'
2014-12-02 22:24:31 [ndbd] INFO -- Angel allocated nodeid: 3
[[email protected] ~]# vim /var/lib/mysql-cluster/config.ini #查看数据结点对应的ID
[ndbd]
HostName=192.168.1.63
DataDir=/var/lib/mysql
id=2
[ndbd]
HostName=192.168.1.64
DataDir=/var/lib/mysql
id=3
3:xuegod63和xuegod64都要启动SQL结点服务:
[[email protected] ~]#mysqld_safe --defaults-file=/etc/my.cnf &
[[email protected] ~]#mysqld_safe --defaults-file=/etc/my.cnf &
4:查看mysql 集群状态:
[[email protected] ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
[ndbd(NDB)]2 node(s)
id=2@127.0.0.1 (mysql-5.6.21 ndb-7.3.7, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.1.64)
[ndb_mgmd(MGM)]1 node(s)
id=1@192.168.1.63 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)]2 node(s)
id=4 (not connected, accepting connect from 192.168.1.63)
id=5 (not connected, accepting connect from 192.168.1.64)
测试集群,数据同步:
1:xuegod63 修改root密码:
[[email protected] /]# cat /root/.mysql_secret
# The random password set for the root user at Tue Dec 2 21:25:59 2014 (local time): rWG1av6XjzT4ghfd
[[email protected] /]# mysql -uroot -prWG1av6XjzT4ghfd
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('123456');
mysql> show databases;
[[email protected] /]# mysql -uroot -p123456
2:xuegod64 修改root密码:
[[email protected] ~]# cat /root/.mysql_secret
# The random password set for the root user at Tue Dec 2 21:31:11 2014 (local time): 2nOsamIw5wkBNhDm
[[email protected] ~]# mysql -uroot -p2nOsamIw5wkBNhDm
mysql> set password=password('123456');
3:插入数据并测试数据同步
1)xuegod64插入数据
[[email protected] ~]# mysql -uroot -p123456
mysql> create database mk;
mysql> show databases;
+--------------------+ | Database |
+--------------------+
| information_schema |
| mk |
| mysql |
| ndb_3_fs |
| ndbinfo |
| performance_schema |
| test |
2)xuegod63同步:
[[email protected]3 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+ | Database |
+--------------------+
| information_schema |
| mk |
| mysql |
| ndb_3_fs |
| ndbinfo |
| performance_schema |
| test |
关闭服务器
注:关闭: 关闭管理节点服务时数据结点服务会自动关闭 –-> 手动关闭SQL结点服务
1:关闭管理节点服务 ,时数据结点服务自动关闭
[[email protected] ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
Node 2: Cluster shutdown initiated
Node 3: Cluster shutdown initiated
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
Node 2: Node shutdown completed.
ndb_mgm> exit
ps -axu | grep ndbd #查看不到,说明数据节点已经被关 :
2:手动关闭SQL结点服务
[[email protected] ~]# ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
root 7620 0.0 0.0 106280 640 pts/0 S 22:25 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 7746 4.6 37.9 1692684 442112 pts/0 Sl 22:25 0:39 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
root 7917 0.0 0.2 125856 2372 pts/1 S+ 22:34 0:00 mysql -uroot -px xxxx
root 8014 0.0 0.0 103300 852 pts/0 S+ 22:39 0:00 grep mysql
[[email protected] /]# kill -9 7620 #关闭主就OK了
[[email protected] /]# kill -9 7746
[[email protected]4 ~]# ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
root 5572 0.0 0.0 106280 640 pts/0 S 22:25 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 5770 4.6 37.9 1692684 442112 pts/0 Sl 22:25 0:39 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
root 7917 0.0 0.2 125856 2372 pts/1 S+ 22:34 0:00 mysql -uroot -px xxxx
root 8014 0.0 0.0 103300 852 pts/0 S+ 22:39 0:00 grep mysql
[[email protected]4 ~]# kill -9 7620
实验总结:
msyql集群启动:
[[email protected] /]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.21 ndb-7.3.7
[[email protected] /]# ndbd
2014-12-02 22:41:59 [ndbd] INFO -- Angel connected to '192.168.1.63:1186'
2014-12-02 22:41:59 [ndbd] INFO -- Angel allocated nodeid: 2
[[email protected] /]# ndbd
2014-12-02 22:41:59 [ndbd] INFO -- Angel connected to '192.168.1.63:1186'
2014-12-02 22:41:59 [ndbd] INFO -- Angel allocated nodeid: 2
[[email protected] /]# mysqld_safe --defaults-file=/etc/my.cnf &
[[email protected] /]# mysqld_safe --defaults-file=/etc/my.cnf &