mysql galera haproxy_mysql+galera+haproxy 安装配置

1. 安装相关插件并修改iptables，禁用selinux

修改iptables:

#vim /etc/sysconfig/iptables

添加以下

-A INPUT -m state --state NEW -m tcp -p tcp --dport mysql端口号 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 4567 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 4444 -j ACCEPT

#service iptables restart

2.主配置文件

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

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

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links=0

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

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

wsrep_cluster_name=mycluster

wsrep_sst_receive_address="172.16.1.151"

wsrep_node_address="172.16.1.151"

wsrep_node_name = 172.16.1.151

#wsrep_node_name = host8

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

wsrep_sst_method = rsync

wsrep_sst_auth=root:123456

bind-address="0.0.0.0"

user=mysql

default-storage-engine=INNODB

character_set_server=utf8

lower_case_table_names=1

port = 33061

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=0

server-id=151

binlog_format=ROW

初始启动：

#service mysqld start --wsrep_cluster_address=gcomm://

#netstat -plantu | grep mysqld

#netstat -plantu | grep mysqld

tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 29209/mysqld

tcp 0 0 0.0.0.0:33061 0.0.0.0:* LISTEN 29209/mysqld

3.加入其他节点

配置文件：

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

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

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links=0

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

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

wsrep_cluster_name=mycluster

wsrep_cluster_address="gcomm://172.16.1.151:4567" #第一次初始化启动的机器的地址

wsrep_sst_receive_address="172.16.1.152"#本机地址

wsrep_node_address="172.16.1.152"

wsrep_node_name = 172.16.1.152

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

wsrep_sst_method = rsync

wsrep_sst_auth=root:123456

user=mysql

default-storage-engine=INNODB

character_set_server=utf8

lower_case_table_names=1

port = 33062

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=0

server-id=152

binlog_format=ROW

启动：service mysqld start

4.登录与查看

mysql -uroot -p123456 --socket=/var/lib/mysql/mysql.sock

查看MySQL版本:   mysql> SHOW GLOBAL VARIABLES LIKE 'version';

查看wsrep版本: mysql> SHOW GLOBAL STATUS LIKE 'wsrep_provider_version';

查看wsrep有关的所有变量: mysql> SHOW VARIABLES LIKE 'wsrep%' \G

查看Galera集群状态: mysql> show status like 'wsrep%';

5.haproxy配置

global

log 127.0.0.1 local0 ##记日志的功能

maxconn 30000

chroot /usr/local/haproxy

user haproxy

group haproxy

daemon

pidfile /usr/local/haproxy/sbin/haproxy.pid

defaults

log global

retries 3

option redispatch

timeout connect 5000ms

timeout client 50000ms

timeout server 50000ms

listen admin_status

bind 172.16.1.157:48800 ##VIP

stats uri /admin-status ##统计页面

stats auth admin:admin

mode http

option httplog

listen allora_service

bind 172.16.1.157:8096

mode tcp

log 127.0.0.1 local0 err

option tcplog

option tcpka

balance roundrobin

option mysql-check user haproxy2 #mysql 需要创建一个无密码的haproxy2的用户

server mysql_151 172.16.1.151:33061 check weight 1

server mysql_152 172.16.1.152:33062 check weight 1

server mysql_153 172.16.1.153:33063 check weight 1

timeout server 50000ms