MariaDB Galera Cluster with HA Proxy and Keepalived on Cents 6
Haproxy 实现数据库的负载均衡,健康监测.权重分发.IP规划:
192.168.0.221 os---221 db
192.168.0.222 os---222 db
192.168.0.223 os---223 db
192.168.0.224 os---224 haproxy
192.168.0.225 os---225 haproxy
VIP: 192.168.0.226
准备:
三台主机安装之前，必须先设置selinux 、hostname 和iptables; 如下：
(1)禁止selinux
selinux=Disabled
(2)配置防火墙
service iptables stop
chkconfigiptablesoff
iptables-F
(3)编辑hosts文件
vi /etc/hosts
192.168.0.221 os---221
192.168.0.222 os---222
192.168.0.223 os---223(4)reboot 1.添加mariadb.repo,yum 源
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1yum makecache 2.安装
yum install MariaDB-Galera-server MariaDB-client galera
3. 在三台主机上初始化mariadb，并且创建集群账户
Allnodes:
初始化如下：
service mysql start
mysql_secure_installation#提示创建密码，root是否远程登录，删除test数据库。
授权账户如下：
mysql-p
GRANT ALL PRIVILEGES ON *.* TO 'cp'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
flush privileges;
quit
然后停止All Nodes的数据
service mysql stop
4.建立集群：Setup Cluster Configuration on Client221
vi /etc/my.cnf.d/server.conf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.223,192.168.0.222
wsrep_cluster_name='cp'
wsrep_node_address='192.168.0.221'
wsrep_node_name='os6---221'
wsrep_sst_method=rsync
wsrep_sst_auth=cp:123 保存退出后，执行
/etc/init.d/mysql bootstrap
或者:
/usr/sbin/mysqld--wsrep-new-cluster --user=mysql& 说明:bootstrap的功能
mysql命令调用/usr/sbin/mysqld
bootstrap相当于--wsrep-new-cluster--user=mysql& 5. 建立集群：Setup Cluster Configuration on Client222,只有三处和上面不同
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.221,192.168.0.223
wsrep_cluster_name='cp'
wsrep_node_address='192.168.0.222'
wsrep_node_name='os6---222'
wsrep_sst_method=rsync
wsrep_sst_auth=cp:123 最后
/etc/init.d/mysql start 6. 建立集群：Setup Cluster Configuration on Client223,同样只有三处和上面不同
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.221,192.168.0.222
wsrep_cluster_name='cp'
wsrep_node_address='192.168.0.223'
wsrep_node_name='os6---223'
wsrep_sst_method=rsync
wsrep_sst_auth=cp:123 最后
/etc/init.d/mysql start 7. 测试集群
省略
8.安装haproxy,分别在192.168.0.224和192.168.0.225
yuminstallhaproxy-y
9.配置haproxy,编辑/usr/local/haproxy/etc/haproxy.cfg,分别在192.168.0.224和192.168.0.225
mv /usr/local/haproxy/etc/haproxy.cfg/usr/local/haproxy/etc/haproxy.cfg.bak
vi/usr/local/haproxy/etc/haproxy.cfgglobal
log 127.0.0.1 local0 notice
user haproxy
group haproxy
defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000
listen mariadb-cluster
bind 0.0.0.0:3306
mode tcp
option mysql-check user haproxy
balance roundrobin
server DB221 192.168.0.221:3306 check
server DB222 192.168.0.222:3306 check
server DB223 192.168.0.223:3306 check
listen webinterface
bind 0.0.0.0:8080
mode http
stats enable
stats uri /haproxy/stats
stats realm Strictly\ Private
stats auth haproxy:haproxy
10.在数据库上建立一个用户,用于haproxy对db的检测
主意: 再一台数据库上执行即可.不必要再3台DB上全部执行.
CREATE USER 'haproxy'@'192.168.0.224';
CREATE USER 'haproxy'@'192.168.0.225';
FLUSHPRIVILEGES;
11.启动haproxy
service haproxystart 开机启动
echo "/etc/init.d/haproxystart">>/etc/rc.local 访问haproxy的检测页面:
224:http://192.1680.224/haproxy/stats user:haproxypass:haproxy
225:http://192.168.0.225/haproxy/stats user:haproxypass:haproxy 试图登陆mysql:
mysql -h 192.168.0.224 -P 3306 -p
mysql -h 192.168.0.225 -P 3306 -p 测试:
任意关闭其中一个数据库/两个数据库,测试连通性
12.安装keepalived
脚本安装:省略
13.配置主haproxy(192.168.0.224)的keepalived配置文件
vi/etc/keepalived/keepalived.confglobal_defs {
notification_email {
15011254881@139.com
}
notification_email_from 15614119390@163.com
smtp_server smtp.163.com
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_haproxy {
script "killall -0 haproxy"
interval 1
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 8
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.226
}
track_script {
chk_haproxy
}
} 14.配置备haproxy(192.168.0.225)的keepalived配置文件
global_defs {
notification_email {
15011254881@139.com
}
notification_email_from 15614119390@163.com
smtp_server smtp.163.com
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_haproxy {
script "killall -0 haproxy"
interval 1
weight 2
}
vrrp_instance VI_1 {
state SLAVE
interface eth0
virtual_router_id 8
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.226
}
track_script {
chk_haproxy
}
}
15.启动haproxy和keepalived,测试haproxy的高可用
servicehaproxystart
servicekeepalived start 测试过程省略.
16.授权mysql的root用户可以在任意的IP地址下登陆
mysql-p
>grantallprivilegeson *.*toroot@'%'identifiedby '123'with grant option;
>flush privileges; 17.测试登陆,查看是否每次重新连接mysql实例,是否达到负载均衡的效果
# mysql -h 192.168.0.226-p
Enter password:
MariaDB [(none)]> showvariableslike'wsrep_node_name';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| wsrep_node_name | os6---222 |
+-----------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye# mysql -h 192.168.0.226-p
Enter password:
MariaDB [(none)]> showvariableslike'wsrep_node_name';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| wsrep_node_name | os6---223 |
+-----------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye# mysql -h 192.168.0.226-p
Enter password:
MariaDB [(none)]> showvariableslike'wsrep_node_name';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| wsrep_node_name | os6---221 |
+-----------------+-----------+
1 row in set (0.01 sec)
页:
[1]