mysql-cluster5.7搭建集群(实际测试有效)
一，节点分布
mysql-cluster图解
管理节点：172.19.132.161
sql节点1：172.19.132.164
data节点1：172.19.132.164
sql节点2：172.19.132.167
data节点2：172.19.132.167
我的 是32位centos6.6
下载32位的mysql-cluster-gpl-7.5.5-linux-glibc2.5-i686.tar.gz
二、172.19.132.161安装配置管理节点
所有节点都要创建mysql用户
[root@bogon ~]#groupadd mysql
[root@bogon ~]#useradd mysql -g mysql
[root@bogon ~]# vim /var/lib/mysql-cluster/config.ini
/var/lib/mysql-cluster/config.ini文件具体内容为
[ndbd default]
NoOfReplicas=2
DataMemory=512M
IndexMemory=18M
[ndb_mgmd]
HostName=172.19.132.161
DataDir=/var/lib/mysql-cluster
[ndbd]
HostName=172.19.132.164
DataDir=/var/lib/mysql-cluster
[ndbd]
HostName=172.19.132.167
DataDir=/var/lib/mysql-cluster
[mysqld]
HostName=172.19.132.164
[mysqld]
HostName=172.19.132.167
解压
[root@bogon ~]# tar xvf mysql-cluster-gpl-7.5.5-linux-glibc2.5-i686.tar.gz
改名
[root@bogon ~]# mv mysql-cluster-gpl-7.5.5-linux-glibc2.5-i686 mysql
[root@bogon ~]# cd mysql
复制命令脚本ndb_mgm
[root@bogon ~]# cp bin/ndb_mgm* /usr/local/bin
[root@bogon ~]# cd /usr/local/bin
[root@bogon ~]# chmod +x ndb_mgm*
[root@bogon ~]# mkdir /var/lib/mysql-cluster
使用配置文件初始化管理节点
[root@bogon ~]# /usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial
注意（首次启动才需要加 --initial后面启动去掉--initial即可）
管理节点关闭命令：ndb_mgm -e shutdown
查看状态：ndb_mgm-e status
查看状态：ndb_mgm-e show
安装启动成功如下
二、172.19.132.164和172.19.132.167安装数据和sql节点
按步骤一中安装mysql-cluster
添加/etc/my.cnf
my.cnf内容是
[root@bogon ~]# cat /etc/my.cnf
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
ndbcluster #使用ndb集群引擎
ndb-connectstring=172.19.132.161
default-storage-engine=ndbcluster #设置默认引擎
skip-name-resolve
#skip-grant-tables
lower_case_table_names=1
autocommit=1
# MySQL支持4种事务隔离级别，他们分别是：
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED
#transaction_isolation = READ-COMMITTED
#transaction_isolation = REPEATABLE-READ
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_Create_USER
# 一般配置选项
log-error=/usr/local/mysql/data/centos68.err
pid-file=/usr/local/mysql/data/centos68.pid
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
#socket = /var/run/mysqld/mysqld.sock
character-set-server=utf8
# 通用查询日志
#general_log = 1
#general_log_file = /usr/local/mysql/data//mysql.log
# 解决日志时间格式
log_timestamps=SYSTEM
#下面是可选项，要不要都行，如果出现启动错误，则全部注释掉，保留最基本的配置选项，然后尝试添加某些配置项后启动，检测配置项是否有误
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
#binlog_cache_size = 4M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
tmp_table_size = 128M
#log-bin=mysql-bin
long_query_time = 6
server_id=1
innodb_buffer_pool_size = 1G
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
default-character-set=utf8
safe-updates
prompt=mysql(\\u@\\h:\\d)>
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[client]
[mysql_cluster]
ndb-connectstring=172.19.132.161
主要比普通my.cnf多如下配置项
ndbcluster #使用ndb集群引擎
ndb-connectstring=172.19.132.161
default-storage-engine=ndbcluster #设置默认引擎
[mysql_cluster]
ndb-connectstring=172.19.132.161
启动ndbd
[root@bogon ~]# /usr/local/mysql/bin/ndbd --initial
注意（首次启动才需要加 --initial后面启动去掉--initial即可）
启动成功截图
安装mysqld进程：供用户访问和执行sql语句
初始化数据库
进入到/opt/mysql/mysql/bin，执行命令
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
生成的密码保存下来
2018-03-21T13:01:46.179890Z 1 [Note] A temporary password is generated for root@localhost: o1)=u*LHw#mj
将mysql做成开机服务cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
mysql启动关闭停止
service mysql start
service mysql status
service mysql stop
登录mysql
mysql -uroot -p
输入密码 o1)=u*LHw#mj
登录进去后修改root密码
set password for root@localhost = password('root');
修改下mysql的连接为任意ip
新安装的mysql密码是空的。
use mysql
SELECT `Host`,`User` FROM user;
UPDATE user SET `Host` = '%' WHERE `User` = 'root' LIMIT 1;
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;
SELECT `Host`,`User` FROM user;
172.19.132.167同样步骤
启动ndbd
启动mysql
关闭集群
先关闭管理节点，然后关闭SQL节点和数据节点。
集群启动操作顺序
要再次启动集群，按照以下顺序执行：
管理节点 ->数据节点 –> SQL节点
三、测试集群
172.19.132.164上创建数据库和表新增数据 172.19.132.167都可以同步
注意创建表需要指定存储引擎为NDBCLUSTER
CREATE TABLE yeyusehg(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL DEFAULT '')
ENGINE = NDBCLUSTER DEFAULT CHARSET utf8mb4;
