使用pacemaker配置mariadb高可用集群

Galera cluster是一个多主同步数据库集群,基于同步复制技术和 Oracle 的 MYSQL/InnoDB。使用Galera Cluster时,您可以直接任意节点读取和写入。并且在丢失任何单个节点时可以不中断操作且无需处理复杂故障转移过程。
galera
下面介绍如何为openstack环境配置mariadb galera高可用集群。

系统环境

主机配置

配置三个节点,使用openstack的控制节点
192.168.100.161 controller01
192.168.100.162 controller02
192.168.100.163 controller03

Haproxy配置

haproxy 添加以下配置

frontend vip-db
bind 192.168.100.160:3306
timeout client 90m
default_backend db-galera
backend db-galera
option httpchk
option tcpka
stick-table type ip size 1000
stick on dst
timeout server 90m
server controller01 192.168.100.161:3306 check inter 1s port 9200 backup on-marked-down shutdown-sessions
server controller02 192.168.100.162:3306 check inter 1s port 9200 backup on-marked-down shutdown-sessions
server controller03 192.168.100.163:3306 check inter 1s port 9200 backup on-marked-down shutdown-sessions

其中 192.168.100.160 是 vip 地址

数据库集群的安装

安装和配置组件(所有节点)

  1. 安装软件包

    # yum install -y mariadb mariadb-galera-server mariadb-galera-common galera rsync xinetd
  2. 创建 /etc/my.cnf.d/openstack.cnf 文件,加入以下内容

    # vim /etc/my.cnf.d/openstack.cnf

    [mysqld]
    bind-address = 192.168.100.161 #本机管理ip

    default-storage-engine = innodb
    innodb_file_per_table = on
    max_connections = 4096
    collation-server = utf8_general_ci
    character-set-server = utf8
  3. 修改 mariadb 最大连接数

  • 修改 /usr/lib/systemd/system/mariadb.service 文件加入以下内容

    # vim /usr/lib/systemd/system/mariadb.service

    [Service]
    LimitNOFILE=10000
    LimitNPROC=10000
  • 重新加载服务

    # systemctl --system daemon-reload

配置galera cluster(所有节点)

编辑 /etc/my.cnf.d/galera.cnf 文件,修改如下内容

# cat > /etc/my.cnf.d/galera.cnf << EOF
[mysqld]
skip-name-resolve=1
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=192.168.100.161
wsrep_on=1
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="pc.recovery=TRUE;gcache.size=300M"
wsrep_cluster_name="openstack_cluster"
wsrep_cluster_address="gcomm://controller01,controller02,controller03"
wsrep_node_name="controller01" #主机名
wsrep_node_address="192.168.100.161" #ip地址
wsrep_slave_threads=1
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=
wsrep_sst_method=rsync
wsrep_sst_auth=root:
EOF

配置 haproxy 健康检查(所有节点)

  1. 安装 xinted 服务

    # yum install xinetd -y
  2. 登录数据库,创建 clustercheck 用户,并设置其本地访问数据库的权限

    # systemctl start mariadb.service
    # mysql -e "CREATE USER 'clustercheck'@'localhost' IDENTIFIED BY 'root1234';"
    # systemctl stop mariadb.service
  3. 为 clustercheck 用户创建配置文件 /etc/sysconfig/clustercheck

    # cat > /etc/sysconfig/clustercheck << EOF
    MYSQL_USERNAME="clustercheck"
    MYSQL_PASSWORD="root1234"
    MYSQL_HOST="localhost"
    MYSQL_PORT="3306"
    EOF
  4. 创建个配置 HAProxy 监控服务 /etc/xinetd.d/galera-monitor

    # cat > /etc/xinetd.d/galera-monitor << EOF
    service galera-monitor
    {
    port = 9200
    disable = no
    socket_type = stream
    protocol = tcp
    wait = no
    user = root
    group = root
    groups = yes
    server = /usr/bin/clustercheck
    type = UNLISTED
    per_source = UNLIMITED
    log_on_success =
    log_on_failure = HOST
    flags = REUSE
    }
    EOF
  5. 启动 xineted 并设置开机自启

    # systemctl daemon-reload
    # systemctl enable xinetd
    # systemctl start xinetd

完成安装

创建数据库集群

# pcs resource create galera-cluster ocf:heartbeat:galera enable_creation=true wsrep_cluster_address="gcomm://controller01,controller02,controller03" additional_parameters='--open-files-limit=16384' enable_creation=true meta master-max=3 ordered=true op promote timeout=300s on-fail=block --master

设置资源依赖

# pcs constraint order start haproxy-clone then galera-cluster-master

验证 mariadb-galera 集群

查看 pacemaker 资源

# pcs resource
……
Master/Slave Set: galera-cluster-master [galera-cluster]
Masters: [ controller01 controller02 controller03 ]

查看集群状态

MariaDB [(none)]> SHOW STATUS like 'wsrep_cluster_%';
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 83d58f96-e6e3-11e8-82ab-87f7bf579cc6 |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+

wsrep_cluster_size 为 3 集群成功创建。

-------------本文结束感谢阅读-------------
0%