Shulou(Shulou.com)06/01 Report--

1 服务器

mysql00 192.168.20.104

mysql01 192.168.20.102

mysql02 192.168.20.103

haproxy 192.168.20.105

haproxy1 192.168.20.106

2 yum源

vi /etc/yum.repos.d/galera.repo

[galera]

name=Galera

baseurl=http://releases.galeracluster.com/centos/7/x86_64/

gpgkey=http://releases.galeracluster.com/GPG-KEY-galeracluster.com

gpgcheck=1

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/5.5/centos7-amd64/

enabled = 1

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

3 修改hosts hostname

vi /etc/hosts

mysql00 192.168.20.104

mysql01 192.168.20.102

mysql02 192.168.20.103

vi /etc/hostname

mysql00(01,02)

4 yum install MariaDB-client MariaDB-Galera-server galera -y

5 修改数据库配置文件

vi /etc/my.cnf

[mysqld]

datadir=/home/mysql #修改mysql数据目录

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

user=mysql

binlog_format=ROW

bind-address=0.0.0.0

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=122M

innodb_log_file_size =48M

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

#wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"

wsrep_cluster_name="cluster"

wsrep_cluster_address="gcomm://192.168.20.104,192.168.20.102,192.168.20.103" #配置集群中所有节点IP

wsrep_node_name=mysql00 #本节点NAME

wsrep_node_address="192.168.20.104" #本节点IP地址

wsrep_sst_method=rsync #全量同步方式

[mysql_safe]

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

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

6 启动第一个节点mysql00

必须按顺序启动，先启动第一个节点（/etc/init.d/mysql start --wsrep-new-cluster），然后启动其他节点（/etc/init.d/mysql start ）

/etc/init.d/mysql start --wsrep-new-cluster

成功显示success

在数据库中查看：

MariaDB [(none)]> show global status like 'wsrep_cluster%';

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

| Variable_name | Value |

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

| wsrep_cluster_conf_id | 20 |

| wsrep_cluster_size | 1 |

| wsrep_cluster_state_uuid | f5845fb3-abd6-11e6-82e5-627a888c772f |

| wsrep_cluster_status | Primary |

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

wsrep_cluster_size 1 表示已有一个节点

7 设置数据库root密码

/usr/bin/mysql_secure_installation

8 配置cluster_check

cd /tmp

wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck

chmod +x clustercheck

mv clustercheck /usr/bin/

vi /etc/xinetd.d/mysqlchk

# default: on

# description: mysqlchk

service mysqlchk

{

disable = no

flags = REUSE

socket_type = stream

port = 9200 # This port used by xinetd for clustercheck

wait = no

user = nobody

server = /usr/bin/clustercheck

log_on_failure += USERID

only_from = 0.0.0.0/0

per_source = UNLIMITED

}

systemctl start xinetd

安装HAproxy

登录192.168.20.105 192.168.20.106

vi /etc/hosts

mysql00 192.168.20.104

mysql01 192.168.20.102

mysql02 192.168.20.103

haproxy 192.168.20.105

haproxy1 192.168.20.106

yum -y install haproxy

vi /etc/rsyslog.conf(为haproxy配置log)

# Provides UDP syslog reception$ModLoad imudp$UDPServerRun 51

vi /etc/rsyslog.d/haproxy.conf

local2.=info /var/log/haproxy-access.loglocal2.notice /var/log/haproxy-info.log

systemctl restart rsyslog

Create a new HAProxy configuration file

cd /etc/haproxy/

mv haproxy.cfg haproxy.cfg.orig （将之前的配置文件备份）

vi /etc/haproxy/haproxy.cfg （创建新的配置文件）

global

log 127.0.0.1 local2

maxconn 1024

user haproxy

group haproxy

daemon

stats socket /var/run/haproxy.sock mode 600 level admin # Make sock file for haproxy

defaults

log global

mode http

option tcplog

option dontlognull

retries 3

option redispatch

maxconn 1024

timeout connect 5000ms

timeout client 50000ms

timeout server 50000ms

listen mariadb_cluster 0.0.0.0:3030

## MariaDB balance leastconn - the cluster listening on port 3030.

mode tcp

# balance leastconn

balance roundrobin #轮询

option tcpka

option mysql-check user haproxy #健康检查

server mysql00 192.168.20.104:3306 check weight 1 #主用

server mysql01 192.168.20.102:3306 check weight 1 #主用

server mysql02 192.168.20.103:3306 check weight 1 backup #备用

listen stats 0.0.0.0:9000

## HAProxy stats web gui running on port 9000 - username and password: howtoforge. WEB界面管理

mode http

stats enable

stats uri /stats

stats realm HAProxy\ Statistics

stats auth howtoforge:howtoforge

stats admin if TRUE

KEEPALIVED 配置 （虚拟地址为192.168.20.107）

HAproxy

yum install -y keepalived

global_defs {

notification_email {

# cloud_data@163.com # e-mail地址

}

# notification_email_from cloud_data@163.com

# smtp_server smtp.163.com # 邮件服务器地址

# smtp_connect_timeout 30 # 连接超时时间

router_id LVS_Master

}

vrrp_script chk_http_port {

script "/etc/keepalived/check_haproxy.sh" # haproxy运行检测脚本[haproxy宕掉重启haproxy服务]

interval 5 # 脚本执行间隔

weight -5 # 执行脚本后优先级变更：5表示优先级+5;-5则表示优先级-5

}

vrrp_instance VI_A {

state MASTER # 主上此值为MASTER,从上为BACKUP

interface eth0

virtual_router_id 50 # 此值主从必须一致

priority 100

advert_int 1

authentication { # authentication两个参数值,主从也必须一致

auth_type PASS

auth_pass kongzhong

}

track_script {

chk_http_port

}

virtual_ipaddress {

192.168.20.107 # haproxy提供的虚拟IP地址

}

}

HAproxy1

global_defs {

notification_email {

# cloud_data@163.com

}

# notification_email_from cloud_data@163.com

#smtp_server smtp.163.com

# smtp_connect_timeout 30

router_id LVS_Master

}

vrrp_script chk_http_port {

script "/etc/keepalived/check_haproxy.sh"

interval 5

weight -5

}

vrrp_instance VI_A {

state BACKUP

interface eth0

virtual_router_id 50

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass kongzhong

}

track_script {

chk_http_port

}

virtual_ipaddress {

192.168.20.107

}

}

验证：

在浏览器输入 http://192.168.20.107:9000/stats

用户名/密码：howtoforge/howtoforge

状态显示正常

断开192.168.20.104 192.168.20.102其中一台 ，登录数据库虚拟地址 mysql -uroot -p -P 3030 -h 192.168.20.107 ，是否可以正常登录

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.