centos6.5安装mysql cluster问题求助

mysql ndbcluster 安装

1.数据库规划：

*** 作系统 centos 6.3

ndbcluster 版本：MySQL-Cluster-gpl-7.4.8-1.el6.x86_64

2个管理节点、2个sql节点、2个数据节点（管理节点和sql节点放在同一台服务器上）

如下：

管理节点1 192.168.1.17

管理节点2 192.168.1.18

sql节点1 192.168.1.17

sql节点2 192.168.1.18

ndbd 节点1 192.168.1.19

ndbd 节点2 192.168.1.20

2. *** 作系统设置：

设置服务主机名称

关闭selinux和防火墙

设置内核参数和最大进程数

下载mysql集群

MySQL-Cluster-gpl-7.4.8-1.el6.x86_64.rpm-bundle.tar

上传到服务器的相应目录下如 /home/mysqlinstall

3.安装管理节点和sql节点：

To check if your system has any RPM version of any MySQL package currently installed, run:

shell>rpm -qa | grep -i mysql

检查

[root@redis1 ~]# rpm -qa | grep -i mysql

mysql-libs-5.1.61-4.el6.x86_64

如果有删除

shell>rpm -e mysql-libs-5.1.61-4.el6.x86_64

删除报错：

[root@redis1 ~]# rpm -e mysql-libs-5.1.61-4.el6.x86_64

error: Failed dependencies:

libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

[root@redis1 ~]#

解决：

[root@redis1 ~]# rpm -e --nodeps mysql-libs-5.1.61-4.el6.x86_64

[root@redis1 ~]#

[root@ndbcluster1 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm

[root@ndbcluster1 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm

4.安装数据节点：

执行上面步骤

[root@ndb mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm

[root@ndb mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm

5.配置集群

管理节点建立目录

mkdir /var/lib/mysql-cluster

mkdir -p /data/mysql/ndbdata

5.启动关闭集群：

启动管理节点

ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial (第一次启动加--initial修改了配置文件后的启动)

启动数据节点

ndbmtd --initial

启动sql节点

service mysql start

查看集群状态

shell>ndb_mgm -e "SHOW"

关闭管理节点和数据节点

ndb_mgm -e shutdown

关闭sql节点

service mysql stop

安装遇到的问题：

1.权限问题启动mysql报错

[root@sqlmgm2 mysql]# service mysql start

Starting MySQL..The server quit without updating PID file ([FAILED]sql/sqlmgm2.pid).

[root@sqlmgm2 mysql]#

[root@sqlmgm2 mysql]# ll

total 110644

-rw-rw---- 1 mysql mysql 56 Dec 14 17:13 auto.cnf

-rw-rw---- 1 mysql mysql 12582912 Dec 14 17:39 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:40 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:13 ib_logfile1

drwx------ 2 mysql mysql 4096 Dec 14 17:39 mysql

drwx------ 2 root root 4096 Dec 14 17:39 ndbinfo

drwx------ 2 root root 4096 Dec 14 17:39 performance_schema

-rw-r----- 1 mysql mysql30068 Dec 14 17:40 sqlmgm2.err

drwx------ 2 root root 4096 Dec 14 17:33 test

[root@sqlmgm2 mysql]# chown mysql:mysql -R mysql

[root@sqlmgm2 mysql]# service mysql start

Starting MySQL.. [ OK ]

[root@sqlmgm2 mysql]#

2.安装rpm包时，报包依赖关系如下：

[root@ndbcluster2 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm

warning: MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

libnuma.so.1()(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64

libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64

libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64

解决方法是把安装的依赖的包

[root@ndbcluster2 mysqlinstall]# yum install numactl

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

* base: mirrors.163.com

* extras: mirrors.opencas.cn

* updates: mirrors.opencas.cn

Setting up Install Process

Resolving Dependencies

-->Running transaction check

--->Package numactl.x86_64 0:2.0.9-2.el6 will be installed

-->Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================

Package ArchVersion Repository Size

==============================================================================================================================

Installing:

numactl x86_64 2.0.9-2.el6 base 74 k

Transaction Summary

==============================================================================================================================

Install 1 Package(s)

Total download size: 74 k

Installed size: 171 k

Is this ok [y/N]: y

Downloading Packages:

numactl-2.0.9-2.el6.x86_64.rpm | 74 kB 00:00

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

Importing GPG key 0xC105B9DE:

Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>

Package: centos-release-6-3.el6.centos.9.x86_64 (@anaconda-CentOS-201207061011.x86_64/6.3)

From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : numactl-2.0.9-2.el6.x86_64 1/1

Verifying : numactl-2.0.9-2.el6.x86_64 1/1

Installed:

numactl.x86_64 0:2.0.9-2.el6

Complete!

3.启动数据库节点无法和管理节点相连

原因由于防火墙和selinux没有关闭

解决关闭防火墙和selinux

一、关闭防火墙

1、重启后永久性生效：

开启：chkconfig iptables on

关闭：chkconfig iptables off

2、即时生效，重启后失效：

开启：service iptables start

关闭：service iptables stop

二、关闭SELinux

vim /etc/selinux/config # 改为 SELINUX=disabled

# 保存退出，重启服务器

init 6

禁用SeLinux

#永久禁用，需要重启生效。

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

# 临时禁用，不需要重启

setenforce 0

4.root用户不能登录

[root@ndbcluster2 ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@ndbcluster2 ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

设置--skip-grant-tables跳过授权表认证

service mysqld stop

在配置文件中添加--skip-grant-tables（/etc/my.cnf）

service mysqld start

另外开个SSH连接

[root@localhost ~]# mysql

mysql>use mysql

mysql>update user set password=password("123456") where user="root"

mysql>flush privileges

mysql>exit

去掉--skip-grant-tables重启mysql,root用户可以用设置的密码登录

设置root用户远程登录

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION

执行报如下错误，ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

[root@ndbcluster1 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with or \g.

Your MySQL connection id is 2

Server version: 5.6.27-ndb-7.4.8-cluster-gpl

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql>

mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root001' WITH GRANT OPTION

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

执行SET PASSWORD修改成功

mysql>SET PASSWORD = PASSWORD('root001')

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION

Query OK, 0 rows affected (0.02 sec)

mysql>

FLUSH PRIVILEGES

执行完成

远程连接测试，mysql集群节点，发现一个节点可以连接，一个节点不能连接

这个问题纠结很久

原因上面的 *** 作，没有在另外一个节点上执行导致的，mysql集群的用户认证是分开管理的，要两个节点都要执行

索引以后创建用户要两个节点都执行

5.配置两个管理节点时show报错Could not get configuration

[root@ndbcluster2 mysql-cluster]# ndb_mgm

-- NDB Cluster -- Management Client --

ndb_mgm>show

Connected to Management Server at: 192.168.1.17:1186

ERROR Message: The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.

Could not get configuration

* 4012: Failed to get configuration

*The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.

ndb_mgm>show

Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=2 (not connected, accepting connect from 192.168.1.18)

id=3 (not connected, accepting connect from 192.168.1.17)

[ndb_mgmd(MGM)] 2 node(s)

id=1@192.168.1.17 (mysql-5.6.27 ndb-7.4.8)

id=6@192.168.1.18 (mysql-5.6.27 ndb-7.4.8)

[mysqld(API)] 2 node(s)

id=4 (not connected, accepting connect from 192.168.1.17)

id=5 (not connected, accepting connect from 192.168.1.18)

两个管理节点同时都启动起来，才能看到状态

ndb_mgm>show

Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=2@192.168.1.18 (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0, *)

id=3@192.168.1.17 (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)

id=1@192.168.1.17 (mysql-5.6.27 ndb-7.4.8)

id=6@192.168.1.18 (mysql-5.6.27 ndb-7.4.8)

[mysqld(API)] 2 node(s)

id=4@192.168.1.17 (mysql-5.6.27 ndb-7.4.8)

id=5@192.168.1.18 (mysql-5.6.27 ndb-7.4.8)

MySQL集群是什么鬼。。

应用用分库分表中间件shardingsphere，部署多个独立的mysql数据库实例就是集群了。

centos部署mysql网上翻一翻教程就好了

单机上可以在不同的端口起个mysql实例，组成单机伪集群

自己玩就弄多个虚拟机，或者用docker开多个docker-mysql进程也挺好的，推荐docker

在配置Mysql数据库主从复制集群的时候要保证：

1.主从服务器 *** 作系统版本和位数一致。

2.Mysql版本一致。

为了保证稳定性，最好服务器 *** 作系统和Mysql数据库环境一致。

CentOS服务器上Mysql的安装方法可以参见博客：

http://blog.csdn.net/jhq0113/article/details/43812895

服务器配置：

Master:192.168.1.18

Slave:192.168.1.16

Master(192.168.1.18)服务器:

1.编辑/etc/my.cnf

[root@jhq0229 ~]# vim /etc/my.cnf

2.配置

[plain] view plain copy print?

[mysqld]

datadir=/data/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

#主从复制配置

innodb_flush_log_at_trx_commit=1

sync_binlog=1

#需要备份的数据库

binlog-do-db=orders

#不需要备份的数据库

binlog-ignore-db=mysql

#启动二进制文件

log-bin=mysql-bin

#服务器ID

server-id=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

若没有配置binlog-do-db和binlog_ignore_db，表示备份全部数据库。

3.重启Mysqld服务

[root@jhq0229 ~]# service mysqld restart

4.为从Mysql创建用户

登录

[root@jhq0229 ~]# mysql -uroot -p

Enter password:

创建用户

mysql>create user 'mastj'@'192.168.1.16' identified by '123456'

配置主从复制权限

mysql>grant replication slave on *.* to 'mastj'@'192.168.1.16' identified by '123456'

若orders数据库中已经有数据，还需要：

锁定数据库

mysql>flush tables with read lock

将数据导入到从数据库，方法有多种，我是用Navicat复制的，复制完成后：

查看master状态并解锁：

[html] view plain copy print?

mysql>show master status

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 | 2005 | orders | mysql| |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>unlock tables

Slave(192.168.1.16)服务器：

1.配置服务ID

[root@jhq0113 ~]# vim /etc/my.cnf

在[mysqld]下面加入

server-id=2

重启Mysql服务

[root@jhq0113 ~]# service mysqld restart

2.配置复制

登录mysql

[root@jhq0113 ~]# mysql -uroot -p

执行

mysql>change master to master_host='192.168.1.18',

master_user='mastj',

master_password='123456',

master_port=3306,

master_log_file='mysql-bin.000003',

master_log_pos=2005,

master_connect_retry=10

参数详解：

master_host:主服务器的IP。

master_user：配置主服务器时建立的用户名

master_password：用户密码

master_port：主服务器mysql端口，如果未曾修改，默认即可。

master_log_file：日志文件名称，填写查看master状态时显示的File

master_log_pos:日志位置，填写查看master状态时显示的Position

master_connect_retry：重连次数

启动进程

mysql>start slave

检查主从复制状态

[plain] view plain copy print?

mysql>show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.18

Master_User: mastj

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 2369

Relay_Log_File: jhq0113-relay-bin.000002

Relay_Log_Pos: 647

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

若Slave_IO_Running和Slave_SQL_Running均为Yes，则表示连接正常。

此时就可以测试主从复制了。

---