MySQL Cluster叢集負載配置

文章目錄

• MySQL Cluster叢集負載配置

MySQL Cluster叢集負載配置

一、軟體包

a) MySQL-ndb-management-5.0.90-1.i386.rpm

b) MySQL-ndb-management-5.0.90-1.i386.rpm

c) mysql-5.0.27.tar.gz

二、安裝［管理節點］

a) rpm -ivh MySQL-ndb-tools-5.0.90-1.i386.rpm

b) rpm -ivh MySQL-ndb-management-5.0.90-1.i386.rpm

c) mkdir /var/lib/mysql-cluster

d) cd /var/lib/mysql-cluster

e) touch config.ini

[NDBD DEFAULT]

NoOfReplicas=2

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# Managment Server

[NDB_MGMD]

HostName=192.168.1.67

# Storage Engines

[NDBD]

HostName=192.168.1.91

DataDir=/usr/local/mysql/data

#DataDir=/var/lib/mysql-cluster

[NDBD]

HostName=192.168.1.92

DataDir=/usr/local/mysql/data

#DataDir=/var/lib/mysql-cluster

[MYSQLD]

[MYSQLD]

f) /usr/bin/ndb_mgm –initial

g) /usr/bin/ndb_mgmd shutdown

h) /usr/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini［啟動管理節點］

i) /usr/sbin/ndb_mgmd -f /etc/ndb_mgmd.cnf

三、安裝［資料節點］

a) Cd /var/local/software

b) Tar zxvf mysql-5.0.27.tar.gz

c) Cd mysql-5.0.27

d) ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --disable-maintainer-mode --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --without-comment --without-debug --with-bench --with-ndbcluster;

e) Make & make install

f) Finger mysql

g) Useradd mysql

h) cd scripts

i) ./mysql_install_db

j) chown -R mysql:mysql /usr/local/mysql

k) chown -R mysql:mysql /usr/local/mysql/data

l) cd /usr/local/src/mysql-5.0.27/support-files/

m) cp my-medium.cnf /etc/my.cnf

n) killall mysqld

o) /usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

# /usr/local/mysql/bin/mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

修改root使用者遠端存取的許可權：
# killall mysqld
# /usr/local/mysql/bin/mysqld_safe –user=mysql &
# /usr/local/mysql/bin/mysql -u root –p123456 mysql

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

vi /etc/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=192.168.1.67

[mysql_cluster]

ndb-connectstring=192.168.1.67

killall mysqld

/usr/local/mysql/bin/mysqld_safe &

/usr/local/mysql/libexec/ndbd –initial（初始化資料節點）

四、 查看管理節點

a) /usr/bin/ndb_mgmd shutdown

b) /usr/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini

c) /usr/bin/ndb_mgm

-- NDB Cluster -- Management Client --

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

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

[ndbd(NDB)] 2 node(s)

id=2 @192.168.1.91 (Version: 5.0.27, Nodegroup: 0, Master)

id=3 @192.168.1.92 (Version: 5.0.27, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @192.168.1.67 (Version: 5.0.90)

[mysqld(API)] 2 node(s)

id=4 @192.168.1.92 (Version: 5.0.27)

id=5 @192.168.1.91 (Version: 5.0.27)

ndb_mgm>

以上為正常顯示

五、測試節點同步

a) 在其中一資料節點中/usr/local/mysql/bin/mysql –uroot –p

b) create database   member;
use member ;
create table   admin (id int ) engine=ndbcluster ;
insert into admin values(1) ;

c) 在另一資料節點中/usr/local/mysql/bin/mysql –uroot –p

d) Create database member;

e) Use member;

f) flush tables ;

g) Show tables;

h) 可以看到admin表已同步

i) Select * from admin

j) 可以看到有一條記錄已同步

k) 叢集正常

六、相關命令

a) /usr/local/mysql/libexec/ndbd –initial（初始化資料節點）

b) /usr/bin/ndb_mgmd shutdown(關閉管理節點)

c) /usr/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini(啟動管理節點)

d) /usr/bin/ndb_mgm查看叢集情況

e) Ps aux|grep mysql查看mysql是否啟動

f) Ps aux|grep ndb查看管理節點是否啟動

g) Ndb_mgm –e “show”;

h) Ndb_mgm>all stauts;

i) Ndb_mgm >2 status;(2為節點編號)

j) Ndbd –no-wait-nodes=2,3

k) Mysql>Show engine ndb status;

l) Ndb_mgm>clusterlog info;

m) Mysql>show status like ‘ndb%’;

n) Mysql>show warnings;

o) Ndb_mgm>2 stop;

p) SHOW ENGINES;

Vi /etc/ndb_mgmd.cnf

[NDBD DEFAULT]

NoOfReplicas=2

MaxNoOfConcurrentOperations= 10000

DataMemory=1800M

IndexMemory=200M

MaxNoOfAttributes=500000[無法建立表問題]

MaxNoOfTables=1760

MaxNoOfUniqueHashIndexes=5000

MaxNoOfOrderedIndexes=5000

MaxNoOfTriggers=4000

TimeBetweenLocalCheckpoints=2

NoOfFragmentLogFiles=100

DataDir= /var/lib/mysql-cluster

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

DataDir= /var/lib/mysql-cluster

[TCP DEFAULT]

# Managment Server

[NDB_MGMD]

HostName=192.168.1.67

# Storage Engines

[NDBD]

HostName=192.168.1.91

MaxNoOfAttributes=500000

DataDir=/usr/local/mysql/data

#DataDir=/var/lib/mysql-cluster

[NDBD]

HostName=192.168.1.92

MaxNoOfAttributes=500000

DataDir=/usr/local/mysql/data

#DataDir=/var/lib/mysql-cluster

[MYSQLD]

[MYSQLD]

七、IPVSADM安裝

a) Rpm –ivh ipvsadm-1.24-8.1.i386.rpm

b) Vi /opt/ipvsadm.sh

#!/bin/bash

ipvsadm -C

ipvsadm -A -t 59.64.28.94:3306 -s rr

ipvsadm -a -t 59.64.28.94:3306 -r 59.64.28.91:3306 -g -w 1

ipvsadm -a -t 59.64.28.94:3306 -r 59.64.28.92:3306 -g -w 1

/etc/rc.d/init.d/ipvsadm save

c) Chmod 755 ipvsadm.sh

d) Service ipvsadm restart

e) Vi /etc/sysconifg/ipvsadm

八、Keepalived安裝(監測心跳)

a) Tar zxvf keepalived-1.1.15.tar.gz

b) ln -s /usr/src/kernels/2.6.18-164.el5-PAE-i686 /usr/src/linux

c) Cd keepalived-1.1.15

d) ./configure --prefix=/usr/local/keepalive/ --with-kernel-dir=/usr/src/linux

e) Make

f) Make install

g) cd /usr/local/keepalive/

h) cp /usr/local/keepalive/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

i) cp /usr/local/keepalive/etc/sysconfig/keepalived /etc/sysconfig/

j) mkdir /etc/keepalived

k) cp /usr/local/keepalive/etc/keepalived/keepalived.conf /etc/keepalived/

l) cp /usr/local/keepalive/sbin/keepalived /usr/sbin/

m) chkconfig --add keepalived

n) chkconfig keepalived on

o) service keepalived restart

p) vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state MASTER

interface eth1

lvs_sync_daemon_interface eth1

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

59.64.28.94 dev eth1 label eth1:1(虛擬IP)

}

}

virtual_server 59.64.28.94 3306 {

delay_loop 6

lb_algo rr

lb_kind DR

nat_mask 255.255.255.0

persistence_timeout 60

protocol TCP

real_server 59.64.28.91 3306 {

weight 1

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 59.64.28.92 3306 {

weight 1

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

q) service keepalived restart

r) ipvsadm

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port Forward Weight ActiveConn InActConn

TCP 59.64.28.94:mysql rr persistent 60

-> 59.64.28.92:mysql Route 1 0 0

-> 59.64.28.91:mysql Route 1 0 0

s) 應用程式資料庫串連主機改成59..64.28.94測試。

九、相關命令

a) service ipvsadm restart

b) service keepalived restart

c) ipvsadm

d) vi /usr/local/keepalive/etc/keepalived/keepalived.conf[/etc/keepalived/keepalived.conf]