Xtradb+Haproxy高可用数据库集群（一）xtradb部署篇

部署xtradb cluster时，建议使用3台及以上服务器。原因有二：

  1、默认情况下，如果一个同伴死去或者两个节点之间的通信不稳定，两个节点都将不接受查询。当然这个可以通过添加忽略仲裁来解决：

set globalwsrep_provider_options=”pc.ignore_quorum=true”;

 

  2、当宕掉的那台启动时，会进行同步，负责提供数据的节点角色变为Donor， Donor会有一定的时间无法写入。断开情况如下：

    Mysqldump 适合小库

    Rsync      复制时间内断开，速度快

    XtraBackup 短时间内断开，速度略慢

节点信息

node #1

hostname:percona1

IP:192.168.70.71

 

node #2

hostname:percona2

IP:192.168.70.72

 

node #3

hostname:percona3

IP:192.168.70.73

 

前提条件：

三个节点都安装了centos系统

   防火墙放通了端口：3306,4444,4567和4568

   关闭selinux

 

安装percona xtradb集群

先安装percona官方源

yum installhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

安装epel源，以便安装依赖包socat

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

安装xtradb

yum installPercona-XtraDB-Cluster-56

 

官网安装参考地址：

https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html

 

配置节点

官网参考地址：

https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html

 

配置第一个节点

第一个节点的/etc/my.cnf配置内容如下：

[mysqld]

 

datadir=/var/lib/mysql

user=mysql

 

# Path to Galeralibrary

wsrep_provider=/usr/lib64/libgalera_smm.so

 

# Clusterconnection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

 

# In order forGalera to work correctly binlog format should be ROW

binlog_format=ROW

 

# MyISAM storageengine has only experimental support

default_storage_engine=InnoDB

 

# This changeshow InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

 

# Node #1address

wsrep_node_address=192.168.70.71

 

# SST method

wsrep_sst_method=xtrabackup-v2

 

# Cluster name

wsrep_cluster_name=my_centos_cluster

 

# Authenticationfor SST method

wsrep_sst_auth="sstuser:s3cret"

 

在此之后，第一个节点可以启动以下命令：

[root@percona1 ~]#/etc/init.d/mysql bootstrap-pxc

如果在centos7上运行本教程，系统服务如下：

[root@percona1 ~]#  systemctl start mysql@bootstrap.service

 

此命令将启动集群初步wsrep_cluster_address设置为gcomm://。这样的集群将自举以防后面的节点或MySQL重新开始，这不需要改变该配置文件。

 

第一个节点启动后，集群状态检测：

mysql> show status like 'wsrep%';

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

| Variable_name              |Value                                |

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

| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |

...

| wsrep_local_state          | 4                                    |

| wsrep_local_state_comment  | Synced                               |

...

| wsrep_cluster_size         | 1                                    |

| wsrep_cluster_status       | Primary                              |

| wsrep_connected            |ON                                   |

...

| wsrep_ready                |ON                                   |

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

40 rows in set (0.01 sec)

该输出显示集群已成功自举

 

 

修改root密码，以及删除空用户

UPDATE mysql.user SET password=PASSWORD("Passw0rd")where user='root';

delete from mysql.user whereuser='';

FLUSH PRIVILEGES;

 

 

为了使xtrabackup成功建立快照，需要给新用户建立适当的权限。

mysql@percona1> CREATE USER'sstuser'@'localhost' IDENTIFIED BY 's3cret';

mysql@percona1> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

mysql@percona1> FLUSH PRIVILEGES;

注：MySQL的root账户也可用于建立PerconaXtrabackup的SST，但建议使用不同用户做这一点。

 

配置第二个节点

第二个节点的/etc/my.cnf配置内容如下：

 

[mysqld]

 

datadir=/var/lib/mysql

user=mysql

 

# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so

 

# Cluster connection URLcontains IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

 

# In order for Galera to workcorrectly binlog format should be ROW

binlog_format=ROW

 

# MyISAM storage engine hasonly experimental support

default_storage_engine=InnoDB

 

# This changes how InnoDBautoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

 

# Node #2 address

wsrep_node_address=192.168.70.72

 

# Cluster name

wsrep_cluster_name=my_centos_cluster

 

# SST method

wsrep_sst_method=xtrabackup-v2

 

#Authentication for SSTmethod

wsrep_sst_auth="sstuser:s3cret"

 

第二个节点的启动命令

/etc/init.d/mysql start

 

服务器已经启动后，它会自动接收状态快照传送。因此，第二个节点不会有空root密码了。

 

 

这是第二个节点的状态

mysql> show status like 'wsrep%';

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

| Variable_name             | Value                               |

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

| wsrep_local_state_uuid    | c2883338-834d-11e2-0800-03c9c68e41ec |

...

| wsrep_local_state         | 4                                   |

| wsrep_local_state_comment | Synced                              |

...

| wsrep_cluster_size        | 2                                   |

| wsrep_cluster_status      | Primary                             |

| wsrep_connected           | ON                                  |

...

| wsrep_ready               | ON                                  |

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

40 rows in set (0.01 sec)

 

 

配置第三个节点

第三个节点的/etc/my.cnf配置文件

[mysqld]

 

datadir=/var/lib/mysql

user=mysql

 

# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so

 

# Cluster connection URLcontains IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

 

# In order for Galera to workcorrectly binlog format should be ROW

binlog_format=ROW

 

# MyISAM storage engine hasonly experimental support

default_storage_engine=InnoDB

 

# This changes how InnoDBautoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

 

# Node #3 address

wsrep_node_address=192.168.70.73

 

# Cluster name

wsrep_cluster_name=my_centos_cluster

 

# SST method

wsrep_sst_method=xtrabackup-v2

 

#Authentication for SSTmethod

wsrep_sst_auth="sstuser:s3cret"

 

然后启动节点

/etc/init.d/mysql start

 

服务器已经启动后，它会自动接收状态快照传送。

 

集群状态查看：

mysql> show status like 'wsrep%';

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

| Variable_name             | Value                               |

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

| wsrep_local_state_uuid    | c2883338-834d-11e2-0800-03c9c68e41ec |

...

| wsrep_local_state         | 4                                   |

| wsrep_local_state_comment | Synced                              |

...

| wsrep_cluster_size        | 3                                   |

| wsrep_cluster_status      | Primary                             |

| wsrep_connected           | ON                                  |

...

| wsrep_ready               | ON                                  |

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

40 rows in set (0.01 sec)

该输出证实了第三个节点加入集群。

 

测试复制

 

在节点2上创建数据库

mysql@percona2> CREATE DATABASE percona;

Query OK, 1row affected(0.01 sec)

 

在节点3上创建表

mysql@percona3> USE percona;

Database changed

 

mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

Query OK, 0rows affected(0.05 sec)

 

在节点1上插入数据

mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');

QueryOK, 1 rowaffected (0.02 sec)

 

在第二个节点上查看数据

mysql@percona2> SELECT * FROM percona.example;

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

| node_id | node_name |

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

|       1 | percona1  |

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

1 row in set (0.00 sec)

 

集群搭建完成