MySQL集群(Galera Cluster+HAProxy+k

服务器	IP
负载均衡VIP	10.10.129.100
负载均衡(一)	10.10.140.49
负载均衡(二)	10.10.142.212
DB(一)	10.10.133.13
DB(二)	10.10.141.182
DB(三)	10.10.132.91

1.修改计算机名,并将计算机名--IP对应关系添加每个服务器的hosts文件内容如下:

cat > /etc/hosts << EOF
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.133.13 MySQL_Cluster_DB_1
10.10.141.182 MySQL_Cluster_DB_2
10.10.132.91 MySQL_Cluster_DB_3
10.10.140.49 MySQL_Cluster_HA_1
10.10.142.212 MySQL_Cluster_HA_2
EOF

2.配置Yum源(添加Percona、epel和MariaDB)

1.添加Percona源:
rpm -ivh http://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm
2.添加epel源：yum -y install epel-release
3.添加MariaDB源:
rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

cat > /etc/yum.repos.d/MariaDB.repo << EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

3.安装MariaDB、galera、xtrabackup

1.安装前,需要先卸载掉mysql-libs:
rpm -e --nodeps mysql-libs
2.安装MariaDB、galera、xtrabackup:
yum -y install MariaDB-Galera-server galera MariaDB-client xtrabackup
3.设置启动mariadb,加固安全
chkconfig mysql on && service mysql start
mysql_secure_installation
4.mysql -uroot -p登录后,检查maryiadb的启动信息中,是否有wsrep的信息:
Server version: 10.0.20-MariaDB-wsrep MariaDB Server, wsrep_25.10.r4144

4.设置基于wsrep的Galera Cluster:

1.创建用于同步数据库的SST帐号:
登录MariaDB后执行:

GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';
GRANT ALL PRIVILEGES on *.* to sst@'%';
FLUSH PRIVILEGES;
quit

2.创建wsrep.cnf配置文件:
在/etc/my.cnf中加入如下一行
cp /usr/share/mysql/wsrep.cnf /etc/my.cnf.d/
编辑vi /etc/my.cnf.d/wsrep.cnf 以下内容:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_auth=sst:sstpass123
wsrep_sst_method=xtrabackup
wsrep_cluster_address = "gcomm://node1IP,node2IP,node3IP"
wsrep_node_name = node1
wsrep_node_address=10.10.133.13

注意:
"gcomm://" 是特殊的地址,仅仅是Galera cluster初始化启动时候使用。如果集群启动以后，节点有变化，再次启动的时候必须先修改
"gcomm://"为其他节点的集群地址,对应各节点的hostname或者dns名称
启动第一个节点的时候,"gcomm://"为空值,不指定任何机器
设置第二个节点的时候,指定第一个节点的IP"gcomm://10.10.133.13:4567"
设置第三个节点的时候,指定第一和第二节点的IP:"gcomm://10.10.133.13:4567,10.10.141.182:4567"
wsrep_node_name 本节点计算机名
wsrep_node_address 本节点IP

将此文件复制到数据库节点，注意要把 wsrep_node_name 和 wsrep_node_address 改成相应节点的 hostname 和 ip。

3.初始化集群
mysqld --wsrep-new-cluster --user=root &
警告⚠：--wsrep-new-cluster 这个参数只能在初始化集群使用，且只能在一个节点使用。

150723 17:04:36 [Note] mysqld: ready for connections.
Version: '10.0.20-MariaDB-wsrep'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server, wsrep_25.10.r4144

出现mysqld: ready for connections.则证明启动正常

---

查看当前集群状态:

show status like 'wsrep_%';

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_incoming_addresses     | 10.10.133.13:3306,10.10.141.182:3306 |
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
| wsrep_local_bf_aborts        | 0                                    |
| wsrep_local_index            | 1                                    |
| wsrep_provider_version       | 25.3.9(r3385)                        |
| wsrep_ready                  | ON                                   |
| wsrep_thread_count           | 2                                    |
+------------------------------+--------------------------------------+
57 rows in set (0.00 sec)

修改节点的wsrep_cluster_address有两种方式:

1.修改配置文件中的wsrep_cluster_address，然后重启MySQL
2.直接登录mysql命令行修改MySQL全局变量

查看当前节点列表:

SHOW VARIABLES LIKE 'wsrep_cluster_address';

+-----------------------+----------------------------+
| Variable_name         | Value                      |
+-----------------------+----------------------------+
| wsrep_cluster_address | gcomm://192.168.0.172:4567 |
+-----------------------+----------------------------+
1 row in set (0.00 sec)

保存新的节点列表:

set global wsrep_cluster_address="gcomm://192.168.0.172:4567,192.168.0.173:4567";

Query OK, 0 rows affected (2.20 sec)

查看修改结果:

SHOW VARIABLES LIKE 'wsrep_cluster_address';

+-----------------------+-------------------------------------------------------+
| Variable_name         | Value                                                 |
+-----------------------+-------------------------------------------------------+
| wsrep_cluster_address | gcomm://192.168.0.172:4567,192.168.0.173:4567         |
+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)

部署haproxy(负载均衡):

yum -y install haproxy xinetd
固定服务端口:
vi /etc/service 添加以下内容
mysqlchk 9200/tcp # mysqlchk