MySQL集群_MySQL
Mysql集群
bitsCN.com一 MySQL集群简介
上一篇文章我们提到MySQL 读写分离，这篇文章我们讲解MySQL集群。我们提到的集群，是指多台机器完成一个工作，最主要的场景是数据库服务器和Web服务器，但是集群环境不适合大规模计算。前面我们有提到MySQL AB复制，因为MySQL AB复制不适合大规模运用，要解决这个问题，我们可以使用MySQL集群。
MySQL集群分为三类节点：管理节点、SQL节点、存储节点。管理节点的功能是管理其他节点，负责调度不同的SQL节点和存储节点。SQL节点作用是用户和该节点进行交互，用户发送SQL语句到该节点，进行读写请求。存储节点负责到磁盘中读数据和写数据。MySQL集群中采用一种特殊存储引擎，名叫NDB。NDB负责对数据进行读写，并保证节点之间的数据一致性，存储节点没有必要使用共享存储，因为第一存储节点本身的数据互为镜像，本身已经对数据做了备份。其中，管理节点只需要一个，SQL节点根据业务需要可以有多个，存储节点同理。
二 MySQL集群示意图
三 使用MySQL集群的优劣
3.1 优势
1.处理业务能力大幅提高；
2.用户关注的点更集中于业务；
3.数据不易丢失，因为存储节点对数据做备份。当然不要完全依靠MySQL集群，制定合理的备份和恢复策略还是很有必要的；
4.在SQL节点有多台的情况下，一台SQL节点宕机不影响，只需要开发人员手动判断该节点是否在线，不在线切换到另一台SQL节点上，保证了高可用性。
3.2 劣势
1.成本提高，因为MySQL集群至少需要三台服务器；
2.运维难度增强，因为服务器数量增加。
四 搭建MySQL环境
4.1 实验环境简介
属性 IP 主机名
管理节点 192.168.1.11 mgmd
存储节点 192.168.1.14 ndb01
存储节点 192.168.1.15 ndb02
SQL节点 192.168.1.12 sql01
SQL节点 192.168.1.13 sql02
MySQL集群网络拓扑图
4.2 操作系统版本
RHEL Server6.1 64位系统
4.3 使用到的软件包版本
mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz
4.4 准备工作
第一步，拷贝文件
1
[root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.11:/opt/[root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.12:/opt/[root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.13:/opt/[root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.14:/opt/[root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.15:/opt/登录后复制
第二步，修改主机名
1
[root@serv01 ~]# hostname mgmd.host.com[root@serv01 ~]# vim /etc/sysconfig/network[root@serv01 ~]# cat !$cat/etc/sysconfig/networkNETWORKING=yesHOSTNAME=mgmd.host.com[root@serv01 ~]# hostname mgmd.host.com[root@serv02 ~]# hostname sql01.host.com[root@serv02 ~]# vim /etc/sysconfig/network[root@serv02 ~]# cat !$cat/etc/sysconfig/networkNETWORKING=yesHOSTNAME=sql01.host.com[root@serv02 ~]# hostname sql01.host.com[root@serv03 ~]# hostname sql02.host.com[root@serv03 ~]# vim /etc/sysconfig/network[root@serv03 ~]# cat !$cat/etc/sysconfig/networkNETWORKING=yesHOSTNAME=sql02.host.com[root@serv03 ~]# hostnamesql02.host.com[root@serv04 ~]# hostname ndb01.host.com[root@serv04 ~]# vim /etc/sysconfig/network[root@serv04 ~]# cat !$cat/etc/sysconfig/networkNETWORKING=yesHOSTNAME=ndb01.host.com[root@serv04 ~]# hostname ndb01.host.com[root@serv05 ~]# hostname ndb02.host.com[root@serv05 ~]# vim /etc/sysconfig/network[root@serv05 ~]# cat !$cat/etc/sysconfig/networkNETWORKING=yesHOSTNAME=ndb02.host.com[root@serv05 ~]# hostname ndb02.host.com登录后复制
第三步，确定IP地址
1
[root@mgmd ~]# ifconfig | grep eth -A1eth0 Link encap:Ethernet HWaddr 00:0C:29:07:DD:3B inet addr:192.168.1.11 Bcast:192.168.1.255 Mask:255.255.255.0[root@sql01 ~]# ifconfig | grep eth -A1eth0 Link encap:Ethernet HWaddr 00:0C:29:6A:EC:97 inet addr:192.168.1.12 Bcast:192.168.1.255 Mask:255.255.255.0[root@sql02 ~]# ifconfig | grep eth -A1eth0 Link encap:Ethernet HWaddr 00:0C:29:BD:08:05 inet addr:192.168.1.13 Bcast:192.168.1.255 Mask:255.255.255.0[root@ndb01 ~]# ifconfig | grep eth -A1eth0 Link encap:Ethernet HWaddr 00:0C:29:0F:1A:09 inet addr:192.168.1.14 Bcast:192.168.1.255 Mask:255.255.255.0[root@ndb02 ~]# ifconfig | grep eth -A1eth0 Link encap:Ethernet HWaddr 00:0C:29:77:CB:2F inet addr:192.168.1.15 Bcast:192.168.1.255 Mask:255.255.255.0登录后复制
4.5 管理节点搭建
第一步，添加mysql组和用户
1
[root@mgmd opt]# groupadd -g 27 mysql[root@mgmd opt]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql[root@mgmd opt]# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)登录后复制
第二步，解压二进制包
1
[root@mgmd opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/[root@mgmd opt]# cd /usr/local/登录后复制
第三步，重命名安装目录，修改所有者和所属组
1
[root@mgmd local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql[root@mgmd local]#chownmysql. mysql/ -R[root@mgmd local]# ll mysql/ -ddrwxr-xr-x 13 mysql mysql 4096 Jun 11 2010 mysql/[root@mgmd local]# ll mysql/total 244drwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 bin-rw-r--r-- 1 mysql mysql 19071 Jun 11 2010 COPYINGdrwxr-x--- 4 mysql mysql 4096 Jun 11 2010 datadrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 docs-rw-r--r-- 1 mysql mysql 5139 Jun 11 2010 EXCEPTIONS-CLIENTdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010include-rw-r--r-- 1 mysql mysql 9748 Jun 11 2010 INSTALL-BINARYdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010 libdrwxr-xr-x 4 mysql mysql 4096 Jun 11 2010 mandrwxr-xr-x 10 mysql mysql 4096 Jun 11 2010 mysql-test-rw-r--r-- 1 mysql mysql 161295 Jun 11 2010 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 11 2010 sharedrwxr-xr-x 5 mysql mysql 4096 Jun 11 2010 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 support-files登录后复制
第四步，拷贝配置文件，编辑该文件，内容如下
1
[root@mgmd local]# cp /usr/local/mysql/support-files/ndb-config-2-node.ini /etc/ndb-config.ini[root@mgmd local]# vim /etc/ndb-config.ini [root@mgmd local]# cat /etc/ndb-config.ini # Example Ndbcluster storage engine config file.#[ndbddefault]NoOfReplicas= 2MaxNoOfConcurrentOperations= 10000DataMemory= 80MIndexMemory= 24MTimeBetweenWatchDogCheck= 30000DataDir= /var/lib/mysql-clusterMaxNoOfOrderedIndexes= 512[ndb_mgmddefault]#确定该目录存在DataDir= /var/lib/mysql-cluster[ndb_mgmd]Id=1HostName= 192.168.1.11[ndbd]Id= 2HostName= 192.168.1.14[ndbd]Id= 3HostName= 192.168.1.15[mysqld]Id= 4HostName= 192.168.1.12[mysqld]Id= 5HostName= 192.168.1.13# choose an unused port number# in this configuration 63132, 63133,and63134# will be used[tcpdefault]PortNumber= 63132登录后复制
第五步，创建数据目录，修改所有者和所属组
1
[root@mgmd local]#mkdir/var/lib/mysql-cluster[root@mgmd local]#chownmysql. /var/lib/mysql-cluster/ -R[root@mgmd local]# ll -d /var/lib/mysql-cluster/drwxr-xr-x 2 mysql mysql 4096 Nov 4 23:39 /var/lib/mysql-cluster/登录后复制
第六步，启动ndb_mgmd
1
[root@mgmd local]# /usr/local/mysql/bin/ndb_mgmd -f /etc/ndb-config.ini 2013-11-04 23:46:12 [MgmtSrvr] INFO -- NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.4b2013-11-04 23:46:12 [MgmtSrvr] INFO -- Thedefaultconfig directory '/usr/local/mysql/mysql-cluster' does not exist. Trying to create it...2013-11-04 23:46:12 [MgmtSrvr] INFO -- Sucessfully created config directory2013-11-04 23:46:12 [MgmtSrvr] INFO -- Reading cluster configuration from '/etc/ndb-config.ini'2013-11-04 23:46:12 [MgmtSrvr] WARNING -- at line 39: [tcp] PortNumber is depricated,usePort usedforthis transporter instead2013-11-04 23:46:13 [MgmtSrvr] INFO -- Reading cluster configuration from '/etc/ndb-config.ini'2013-11-04 23:46:13 [MgmtSrvr] WARNING -- at line 39: [tcp] PortNumber is depricated,usePort usedforthis transporter instead--确定进程和端口号[root@mgmd local]# ps -ef | grep mgm | grep -v greproot 1070 1 0 23:46 ? 00:00:00 /usr/local/mysql/bin/ndb_mgmd -f /etc/ndb-config.ini[root@mgmd local]# netstat -langput | grep mgmtcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 1070/ndb_mgmd tcp 0 0 127.0.0.1:1186 127.0.0.1:55252 ESTABLISHED 1070/ndb_mgmd tcp 0 0 127.0.0.1:55252 127.0.0.1:1186 ESTABLISHED 1070/ndb_mgmd [root@mgmd local]# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 (not connected, accepting connect from 192.168.1.14)id=3 (not connected, accepting connect from 192.168.1.15)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.1.12)id=5 (not connected, accepting connect from 192.168.1.13)登录后复制
4.6 存储节点搭建
第一步，ndb01添加mysql组和用户
1
[root@ndb01 ~]# groupadd -g 27 mysql[root@ndb01 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql[root@ndb01 ~]# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)[root@ndb01 ~]# cd /opt/登录后复制
第二步，ndb01解压二进制包
1
[root@ndb01 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/[root@ndb01 opt]# cd /usr/local/登录后复制
第三步，ndb01重命名安装目录，修改所有者和所属组
1
[root@ndb01 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql[root@ndb01 local]#chownmysql. mysql/ -R[root@ndb01 local]# ll -d mysql/drwxr-xr-x 13 mysql mysql 4096 Jun 11 2010 mysql/[root@ndb01 local]# ll mysql/total 244drwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 bin-rw-r--r-- 1 mysql mysql 19071 Jun 11 2010 COPYINGdrwxr-x--- 4 mysql mysql 4096 Jun 11 2010 datadrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 docs-rw-r--r-- 1 mysql mysql 5139 Jun 11 2010 EXCEPTIONS-CLIENTdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010include-rw-r--r-- 1 mysql mysql 9748 Jun 11 2010 INSTALL-BINARYdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010 libdrwxr-xr-x 4 mysql mysql 4096 Jun 11 2010 mandrwxr-xr-x 10 mysql mysql 4096 Jun 11 2010 mysql-test-rw-r--r-- 1 mysql mysql 161295 Jun 11 2010 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 11 2010 sharedrwxr-xr-x 5 mysql mysql 4096 Jun 11 2010 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 support-files登录后复制
第四步，ndb01进入mysql目录，拷贝模板文件，修改该文件，内容如下
1
[root@ndb01 local]# cd mysql/[root@ndb01 mysql]# cp support-files/my-medium.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y[root@ndb01 mysql]# vim /etc/my.cnf [root@ndb01 mysql]# grep"^#/|^$"/etc/my.cnf -v[client]port = 3306socket = /tmp/mysql.sock[mysqld]port = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixedserver-id = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysql_cluster]ndb_connectstring=192.168.1.11#下划线或者横线都可以#ndb-connectstring=192.168.1.11登录后复制
第五步，ndb01创建数据目录，修改所有者和所属组
1
[root@ndb01 mysql]#mkdir/var/lib/mysql-cluster[root@ndb01 mysql]#chownmysql.mysql !$ -Rchown mysql.mysql /var/lib/mysql-cluster -R[root@ndb01 mysql]# ll -d /var/lib/mysql-cluster/drwxr-xr-x 2 mysql mysql 4096 Nov 4 23:54 /var/lib/mysql-cluster/登录后复制
第六步，ndb01初始化ndbd
1
[root@ndb01 mysql]# /usr/local/mysql/bin/ndbd --initial2013-11-04 23:57:06 [ndbd] INFO -- Configuration fetched from '192.168.1.11:1186', generation: 1#查看状态[root@ndb01 mysql]# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: 192.168.1.11:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)id=3 (not connected, accepting connect from 192.168.1.15)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.1.12)id=5 (not connected, accepting connect from 192.168.1.13)登录后复制
第七步，ndb02和ndb01执行相同的操作，如下：
1
[root@ndb02 ~]# groupadd -g 27 mysql[root@ndb02 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql[root@ndb02 ~]# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)[root@ndb02 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/ [root@ndb02 opt]# cd /usr/local/[root@ndb02 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql[root@ndb02 local]#chownmysql. mysql/ -R[root@ndb02 local]# ll -d mysql/drwxr-xr-x 13 mysql mysql 4096 Jun 11 2010 mysql/[root@ndb02 local]# ll mysql/total 244drwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 bin-rw-r--r-- 1 mysql mysql 19071 Jun 11 2010 COPYINGdrwxr-x--- 4 mysql mysql 4096 Jun 11 2010 datadrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 docs-rw-r--r-- 1 mysql mysql 5139 Jun 11 2010 EXCEPTIONS-CLIENTdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010include-rw-r--r-- 1 mysql mysql 9748 Jun 11 2010 INSTALL-BINARYdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010 libdrwxr-xr-x 4 mysql mysql 4096 Jun 11 2010 mandrwxr-xr-x 10 mysql mysql 4096 Jun 11 2010 mysql-test-rw-r--r-- 1 mysql mysql 161295 Jun 11 2010 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 11 2010 sharedrwxr-xr-x 5 mysql mysql 4096 Jun 11 2010 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 support-files[root@ndb02 local]#mkdir/var/lib/mysql-cluster[root@ndb02 local]#chownmysql. /var/lib/mysql-cluster/ -R[root@ndb02 local]# ll -d /var/lib/mysql-cluster/drwxr-xr-x 2 mysql mysql 4096 Nov 5 00:00 /var/lib/mysql-cluster/[root@ndb02 local]# cd mysql/[root@ndb02 mysql]# cp support-files/my-medium.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y[root@ndb02 mysql]# vim /etc/my.cnf [root@ndb02 mysql]# grep"^#/|^$"!$ -vgrep"^#/|^$"/etc/my.cnf -v[client]port = 3306socket = /tmp/mysql.sock[mysqld]port = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixedserver-id = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysql_cluster]ndb_connectstring=192.168.1.11[root@ndb02 mysql]# /usr/local/mysql/bin/ndbd --initial2013-11-05 00:02:18 [ndbd] INFO -- Configuration fetched from '192.168.1.11:1186', generation: 1[root@ndb02 mysql]# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: 192.168.1.11:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.1.12)id=5 (not connected, accepting connect from 192.168.1.13)登录后复制
4.7 SQL节点搭建
第一步，sql01添加mysql组和用户
1
[root@sql01 ~]# groupadd -g 27 mysql[root@sql01 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql[root@sql01 ~]# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)[root@sql01 ~]# cd /opt/登录后复制
第二步，sql01解压二进制包
1
[root@sql01 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/登录后复制
第三步，sql01重命名安装目录，修改所有者和所属组
1
[root@sql01 opt]# cd /usr/local/[root@sql01 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql[root@sql01 local]#chownmysql. mysql/ -R[root@sql01 local]# ll -d mysql/drwxr-xr-x 13 mysql mysql 4096 Jun 11 2010 mysql/[root@sql01 local]# ll mysql/total 244drwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 bin-rw-r--r-- 1 mysql mysql 19071 Jun 11 2010 COPYINGdrwxr-x--- 4 mysql mysql 4096 Jun 11 2010 datadrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 docs-rw-r--r-- 1 mysql mysql 5139 Jun 11 2010 EXCEPTIONS-CLIENTdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010include-rw-r--r-- 1 mysql mysql 9748 Jun 11 2010 INSTALL-BINARYdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010 libdrwxr-xr-x 4 mysql mysql 4096 Jun 11 2010 mandrwxr-xr-x 10 mysql mysql 4096 Jun 11 2010 mysql-test-rw-r--r-- 1 mysql mysql 161295 Jun 11 2010 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 11 2010 sharedrwxr-xr-x 5 mysql mysql 4096 Jun 11 2010 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 support-files登录后复制
第四步，sql01拷贝配置文件，并修改，如下
1
[root@sql01 local]# cd mysql/[root@sql01 mysql]# cp support-files/my-medium.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y[root@sql01 mysql]# vim /etc/my.cnf [root@sql01 mysql]# grep"^#/|^$"!$ -vgrep"^#/|^$"/etc/my.cnf -v[client]port = 3306socket = /tmp/mysql.sock[mysqld]datadir=/var/lib/mysql-clusterndbclusterdefault-storage-engine=ndbclusterport = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixedserver-id = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysql_cluster]ndb_connectstring=192.168.1.11登录后复制
第五步，sql01拷贝运行脚本，添加可执行权限
1
[root@sql01 mysql]# cp support-files/mysql.server /etc/init.d/mysqld[root@sql01 mysql]#chmod+x /etc/init.d/mysqld登录后复制
第六步，sql01创建数据目录，修改所有者和所属组
1
[root@sql01 mysql]#mkdir/var/lib/mysql-cluster[root@sql01 mysql]#chownmysql. !$ -Rchown mysql. /var/lib/mysql-cluster -R[root@sql01 mysql]# ll -d /var/lib/mysql-cluster/drwxr-xr-x 2 mysql mysql 4096 Nov 5 00:06 /var/lib/mysql-cluster/登录后复制
第七步，sql01初始化数据库
1
[root@sql01 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysqlWARNING: The host 'sql01.host.com' could not be looked up with resolveip.This probably means that your libc libraries are not 100 % compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that host name resolving will not work.This means that you shoulduseIP addresses instead of hostnameswhen specifying MySQL privileges !Installing MySQL system tables...131105 0:07:32 [Warning] The syntax '--skip-locking' is deprecatedandwill be removed in a future release. Pleaseuse--skip-external-locking instead.OKFilling help tables...131105 0:07:32 [Warning] The syntax '--skip-locking' is deprecatedandwill be removed in a future release. Pleaseuse--skip-external-locking instead.OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right placeforyour systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !Todoso, start the server, then issue the following commands:./bin/mysqladmin -u root password 'new-password'./bin/mysqladmin -u root -h sql01.host.com password 'new-password'Alternatively you can run:./bin/mysql_secure_installationwhich will also give you the option of removing the testdatabasesandanonymous user created bydefault. This isstrongly recommendedforproduction servers.See the manualformore instructions.You can start the MySQL daemon with:cd . ; ./bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd ./mysql-test ; perl mysql-test-run.plPlease report any problems with the ./bin/mysqlbug script!登录后复制
第八步，sql01启动mysql，并加入ndb
1
[root@sql01 mysql]# /etc/init.d/mysqld startStarting MySQL. SUCCESS! [root@sql01 mysql]# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: 192.168.1.11:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)[mysqld(API)] 2 node(s)id=4 @192.168.1.12 (mysql-5.1.44 ndb-7.1.4)id=5 (not connected, accepting connect from 192.168.1.13)登录后复制
第九步，sql02执行和sql01相同的操作，如下：
1
[root@sql02 ~]# groupadd -g 27 mysql[root@sql02 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql[root@sql02 ~]# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)[root@sql02 ~]# cd /opt/[root@sql02 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/[root@sql02 opt]# cd !$cd/usr/local/[root@sql02 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql[root@sql02 local]#chownmysql. mysql/ -R[root@sql02 local]# ll -d mysql/drwxr-xr-x 13 mysql mysql 4096 Jun 11 2010 mysql/[root@sql02 local]# ll mysql/total 244drwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 bin-rw-r--r-- 1 mysql mysql 19071 Jun 11 2010 COPYINGdrwxr-x--- 4 mysql mysql 4096 Jun 11 2010 datadrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 docs-rw-r--r-- 1 mysql mysql 5139 Jun 11 2010 EXCEPTIONS-CLIENTdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010include-rw-r--r-- 1 mysql mysql 9748 Jun 11 2010 INSTALL-BINARYdrwxr-xr-x 3 mysql mysql 4096 Jun 11 2010 libdrwxr-xr-x 4 mysql mysql 4096 Jun 11 2010 mandrwxr-xr-x 10 mysql mysql 4096 Jun 11 2010 mysql-test-rw-r--r-- 1 mysql mysql 161295 Jun 11 2010 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 11 2010 sharedrwxr-xr-x 5 mysql mysql 4096 Jun 11 2010 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 11 2010 support-files[root@sql02 local]#mkdir/var/lib/mysql-cluster[root@sql02 local]#chownmysql. /var/lib/mysql-cluster -R[root@sql02 local]# ll -d /var/lib/mysql-cluster/drwxr-xr-x 2 mysql mysql 4096 Nov 5 00:10 /var/lib/mysql-cluster/[root@sql02 local]# cd mysql/[root@sql02 mysql]# cp support-files/my-medium.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y [root@sql02 mysql]# vim /etc/my.cnf [root@sql02 mysql]# grep"^#/|^$"/etc/my.cnf -v[client]port = 3306socket = /tmp/mysql.sock[mysqld]datadir=/var/lib/mysql-clusterndbclusterdefault-storage-engine=ndbclusterport = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixedserver-id = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysql_cluster]ndb_connectstring=192.168.1.11[root@sql02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld[root@sql02 mysql]#chmod+x !$chmod+x /etc/init.d/mysqld[root@sql02 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysqlWARNING: The host 'sql02.host.com' could not be looked up with resolveip.This probably means that your libc libraries are not 100 % compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that host name resolving will not work.This means that you shoulduseIP addresses instead of hostnameswhen specifying MySQL privileges !Installing MySQL system tables...131105 0:14:20 [Warning] The syntax '--skip-locking' is deprecatedandwill be removed in a future release. Pleaseuse--skip-external-locking instead.OKFilling help tables...131105 0:14:20 [Warning] The syntax '--skip-locking' is deprecatedandwill be removed in a future release. Pleaseuse--skip-external-locking instead.OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right placeforyour systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !Todoso, start the server, then issue the following commands:./bin/mysqladmin -u root password 'new-password'./bin/mysqladmin -u root -h sql02.host.com password 'new-password'Alternatively you can run:./bin/mysql_secure_installationwhich will also give you the option of removing the testdatabasesandanonymous user created bydefault. This isstrongly recommendedforproduction servers.See the manualformore instructions.You can start the MySQL daemon with:cd . ; ./bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd ./mysql-test ; perl mysql-test-run.plPlease report any problems with the ./bin/mysqlbug script![root@sql02 mysql]# /etc/init.d/mysqld startStarting MySQL. SUCCESS! [root@sql02 mysql]# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: 192.168.1.11:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)[mysqld(API)] 2 node(s)id=4 @192.168.1.12 (mysql-5.1.44 ndb-7.1.4)id=5 @192.168.1.13 (mysql-5.1.44 ndb-7.1.4)登录后复制
4.8 测试
第一步，sql02创建测试数据库，sql01可以发现
1
[root@sql02 mysql]# /usr/local/mysql/bin/mysqlWelcome to the MySQL monitor. Commandsendwith ;or/g.Your MySQL connection id is 2Server version: 5.1.44-ndb-7.1.4b-cluster-gpl-log MySQL Cluster Server (GPL)Type 'help;'or'/h'forhelp. Type '/c' to clear the current input statement.mysql> create database larrydb;Query OK, 1 row affected (0.21 sec)mysql> [root@sql01 mysql]# /usr/local/mysql/bin/mysqlWelcome to the MySQL monitor. Commandsendwith ;or/g.Your MySQL connection id is 2Server version: 5.1.44-ndb-7.1.4b-cluster-gpl-log MySQL Cluster Server (GPL)Type 'help;'or'/h'forhelp. Type '/c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || larrydb || mysql || ndbinfo || test |+--------------------+5 rows in set (0.00 sec)登录后复制
第二步，sql01创建测试表，插入测试数据，sql02可以发现变化
1
mysql>uselarrydb;Database changedmysql> create table user(id int,name varchar(30));Query OK, 0 rows affected (0.62 sec)mysql> insert into user values(1,'larry');Query OK, 1 row affected (0.03 sec)sql02mysql> select * from larrydb.user;+------+-------+| id | name |+------+-------+| 1 | larry |+------+-------+1 row in set (0.04 sec)登录后复制
如果只能使用三台服务器，可以这样配置：sql节点和ndb节点放在一起。管理节点做如下配置：
bitsCN.com
1
#管理节点[ndb_mgmd]Id=1HostName= 192.168.1.11[ndbd]Id= 2HostName= 192.168.1.14[ndbd]Id= 3HostName= 192.168.1.15[mysqld]Id= 4[mysqld]Id= 5#其他节点做sql节点的配置即可登录后复制
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片
Jul 18, 2024 am 05:48 AM
苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能，旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册，当用户设备中存在未纳入其照片库的图片或视频时，该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步
Hibernate 如何实现多态映射？
Apr 17, 2024 pm 12:09 PM
Hibernate多态映射可映射继承类到数据库，提供以下映射类型：joined-subclass：为子类创建单独表，包含父类所有列。table-per-class：为子类创建单独表，仅包含子类特有列。union-subclass：类似joined-subclass，但父类表联合所有子类列。
在PHP中使用MySQLi建立数据库连接的详尽教程
Jun 04, 2024 pm 01:42 PM
如何在PHP中使用MySQLi建立数据库连接：包含MySQLi扩展（require_once）创建连接函数（functionconnect_to_db）调用连接函数（$conn=connect_to_db()）执行查询（$result=$conn->query()）关闭连接（$conn->close()）
如何在PHP中处理数据库连接错误
Jun 05, 2024 pm 02:16 PM
PHP中处理数据库连接报错，可以使用以下步骤：使用mysqli_connect_errno()获取错误代码。使用mysqli_connect_error()获取错误消息。通过捕获并记录这些错误信息，可以轻松识别并解决数据库连接问题，确保应用程序的顺畅运行。
深入解析HTML如何读取数据库
Apr 09, 2024 pm 12:36 PM
HTML无法直接读取数据库，但可以通过JavaScript和AJAX实现。其步骤包括建立数据库连接、发送查询、处理响应和更新页面。本文提供了利用JavaScript、AJAX和PHP来从MySQL数据库读取数据的实战示例，展示了如何在HTML页面中动态显示查询结果。该示例使用XMLHttpRequest建立数据库连接，发送查询并处理响应，从而将数据填充到页面元素中，实现了HTML读取数据库的功能。
搭载 AMD EPYC 霄龙 4004 系列处理器，华硕推出多款服务器与工作站产品
Jul 23, 2024 pm 09:34 PM
本站7月23日消息，华硕推出多款由AMDEPYC霄龙4004系列处理器驱动的服务器与工作站级产品。本站注：AMD于5月推出AM5平台、Zen4架构的EPYC霄龙4004系列处理器，最高提供16核3DV-Cache规格。ASUSProER100AB6服务器ASUSProER100AB6是一款搭载EPYC霄龙4004系列处理器的1U机架式服务器产品，适用于IDC及中小型企业需求。ASUSExpertCenterProET500AB6工作站ASUSExpertCenterProET500AB6是一款A
如何用 Golang 连接远程数据库？
Jun 01, 2024 pm 08:31 PM
通过Go标准库database/sql包，可以连接到MySQL、PostgreSQL或SQLite等远程数据库：创建包含数据库连接信息的连接字符串。使用sql.Open()函数打开数据库连接。执行SQL查询和插入操作等数据库操作。使用defer关闭数据库连接以释放资源。
如何在 Golang 中使用数据库回调函数？
Jun 03, 2024 pm 02:20 PM
在Golang中使用数据库回调函数可以实现：在指定数据库操作完成后执行自定义代码。通过单独的函数添加自定义行为，无需编写额外代码。回调函数可用于插入、更新、删除和查询操作。必须使用sql.Exec、sql.QueryRow或sql.Query函数才能使用回调函数。
