162

162

关注
发信
关注(28)粉丝(399)

galera+mycat高可用集群部署

galera+mycat高可用集群部署-LMLPHP

环境描述

10.30.162.29  client

环境描述
10.30.162.29 client
10.30.162.72 mysql1
10.30.162.73 mysql2
10.30.162.74 mysql3
10.30.162.78 proxy1
10.30.162.77 proxy2
环境准备
1）关闭SELinux
2）关闭防火墙
3）ntp同步时间 ntpdate time1.aliyun.com
4）主机之间相互做好解析 /etc/hosts

一、安装galera

1)配置Yum源

[root@mysql1 ~]# vim /etc/yum.repos.d/galera.repo

[galera]

name=galera

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

gpgcheck=0

[root@mysql1 ~]# vim /etc/yum.repos.d/mysql_wsrep.repo

[mysql_wsrep]

name=mysql_wsrep

baseurl=http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/

gpgcheck=0

[root@mysql1 ~]# yum list | grep -E "wsrep|galera"

galera.x86_64 25.3.12-2.el7 epel

galera-3.x86_64 25.3.24-2.el7 galera

mysql-wsrep-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-client-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-common-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-devel-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-libs-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-libs-compat-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-server-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

mysql-wsrep-test-5.7.x86_64 5.7.23-25.15.el7 mysql_wsrep

[root@mysql1 ~]# yum -y install mysql-wsrep-5.7.x86_64 galera-3.x86_64

　　此处可以只在一台机器上做，然后开启缓存(keepcache=1)，下载下来包之后，搭建一个ftp服务器，另外两台机器直接做一个yum源即可安装


2)开启服务并且数据库修改密码

[root@mysql1 ~]# systemctl start mysqld

[root@mysql1 ~]# systemctl enable mysqld

[root@mysql1 ~]# grep "temporary password" /var/log/mysqld.log | awk '{print $NF}'

[root@mysql1 ~]# mysqladmin -p'uT1K:*kkeDtZ' password "Qfcloud123."

3)创建并授权用于数据同步的用户

[root@mysql1 ~]# mysql -p'Qfcloud123.'

mysql> grant all on *.* to galera@'10.30.162.%' identified by 'Qfcloud123.';

mysql> flush privileges;

　　

二、配置galera

[root@mysql1 ~]# vim /etc/my.

server_id=1                          //每个节点的server_id不同

default_storage_engine=InnoDB

wsrep_on=on

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

wsrep_cluster_name="GaleraCluster"

wsrep_cluster_address="gcomm://"      //后两个节点要在//后添加另外两台的ip，做了解析可以直接写主机名，逗号分隔

wsrep_node_name="galera1"             //节点名，不要重复

wsrep_node_address="10.30.162.72"     //每个节点的ip

wsrep_sst_auth=galera:Qfcloud123.

wsrep_sst_method=rsync

　　

=====================================================================================
注释：
wsrep_on=on：启用wsrep，=on或者=1都可以
wsrep_provider：指定集群同步数据使用的库文件
wsrep_cluster_name：自定义集群的名字
wsrep_cluster_address：mysql节点加入集群需要的地址，第一个节点的写法比较特殊
wsrep_node_name：指定集群节点名称，一般写本机的主机名即可
wsrep_node_address：指定本机地址
wsrep_sst_auth：指定三台服务器之间进行数据同步时使用的帐号和密码，需要创建
wsrep_sst_method：数据复制的两种方式：rsync和xtrabackup，rsync是系统自带，比较常用。libgalera_smm.so库只是提供了复制的功能，而真正进行数据同步时要靠rsync机制实现

==========================================================================================

[root@mysql1 ~]# yum -y install rsync

[root@mysql1 ~]# systemctl restart mysqld

[root@mysql1 ~]# ss -antp | grep -E "3306|4567"

LISTEN 0 128 *:4567 *:* users:(("mysqld",pid=17071,fd=12))

ESTAB 0 0 10.30.162.72:4567 10.30.162.73:35708 users:(("mysqld",pid=17071,fd=55))

ESTAB 0 0 10.30.162.72:4567 10.30.162.75:55016 users:(("mysqld",pid=17071,fd=41))

LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=17071,fd=27))

　　

三个节点配置完成之后，可以通过如下命令查询是否成功

[root@mysql1 ~]# mysql -pQfcloud123.

mysql> show status like "wsrep%";

galera+mycat高可用集群部署-LMLPHP

三、安装mycat

1)安装JDK环境

[root@tomcat1 ~]# tar xf jdk-8u181-linux-x64.tar.gz -C /usr/local/

[root@tomcat1 ~]# ln -s /usr/local/jdk1.8.0_181/ /usr/local/java

[root@tomcat1 ~]# vim /etc/profile

export JAVA_HOME=/usr/local/java

export PATH=$PATH:$JAVA_HOME/bin

[root@tomcat1 ~]# source /etc/profile

[root@tomcat1 ~]# java -version

java version "1.8.0_181"

Java(TM) SE Runtime Environment (build 1.8.0_181-b13)

Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

　　关于JDK下载链接的问题，可以看我另外一篇文章《tomcat启用中8005端口未被占用》

2)安装mycat

[root@proxy1 ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@proxy1 ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local

3)创建库，并且授权给mycat一个账户

[root@mysql1 ~]# mysql -pQfcloud123.

mysql> create database galera;

mysql> grant all on galera.* to mycat@'10.30.162.%' identified by 'Qfcloud123.';

mysql> flush privileges;

4）配置mycat

[root@proxy1 ~]# cd /usr/local/mycat/conf/

[root@proxy1 conf]# vim server.xml

　　

<user name="galera">

<property name="password"></property>

<property name="schemas">galera_test</property>

</user>
[root@proxy1 conf]# vim schema.xml

　　

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="">http://io.mycat/">

<schema name="galera_test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

<dataNode name="dn1" dataHost="localhost1" database="galera" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="3" slaveThreshold="100">

<heartbeat>show status like 'wsrep%'</heartbeat>

<writeHost host="galera1" url="10.30.162.72:3306" user="galera"

password="Qfcloud123.">

</writeHost>

<writeHost host="galera2" url="10.30.162.73:3316" user="galera"

password="Qfcloud123." >

</writeHost>

<writeHost host="galera3" url="10.30.162.75:3326" user="galera"

password="Qfcloud123." >

</writeHost>

</dataHost>

</mycat:schema>

　　

5)验证

[root@proxy1 ~]# /usr/local/mycat/bin/mycat start

Starting Mycat-server...

[root@proxy1 ~]# ss -antp | grep java

LISTEN 0 1 127.0.0.1:32000 *:* users:(("java",pid=1665,fd=4),("wrapper-linux-x",pid=1663,fd=4))

LISTEN 0 50 :::1984 :::* users:(("java",pid=1665,fd=55))

LISTEN 0 50 :::41825 :::* users:(("java",pid=1665,fd=54))

LISTEN 0 50 :::44913 :::* users:(("java",pid=1665,fd=56))

[root@proxy1 ~]# jps

1350 WrapperSimpleApp

1367 Jps

　　

四、keepalived

1)安装keepalived

[root@proxy1 ~]# yum -y install keepalived

2)配置keepalived

[root@proxy1 ~]# vim /etc/keepalived/keepalived.conf

Configuration File for keepalived

global_defs {

    router_id galera1

}

vrrp_instance VI_1 {

    state MASTER

    interface ens33

    virtual_router_id 51

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        10.30.162.110

    }

}

virtual_server 10.30.162.100 80 {

    delay_loop 3

    lb_algo rr

    lb_kind DR

    persistence_timeout 50

    protocol TCP

    real_server 10.30.162.73 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

        }

    }

    real_server 10.30.162.75 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

        }

    }

    real_server 10.30.162.72 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

        }

    }

}

　　

BACKUP端
修改router_id priority state

[root@proxy1 ~]# systemctl restart keepalived

3)验证

[root@proxy1 ~]# ip a | grep ens33

2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

inet 10.30.162.78/24 brd 10.30.162.255 scope global noprefixroute dynamic ens33

inet 10.30.162.110/32 scope global ens33

[root@physical ~]# mysql -ugalera -p123456 -h 10.30.162.110 -P8066

mysql> show databases;

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

| DATABASE       |

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

| galera_test    |

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

1 row in set (0.00 sec)

mysql> select * from test2;

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

| id            |

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

| 100000000     |

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

1 rows in set (0.01 sec)

　　

4)编写脚本，达到mycat如果宕机后，尝试重启服务，未启动则关闭keepalived

[root@proxy1 ~]# vim /etc/keepalived/mycat.sh

#/bin/bash

/usr/local/mycat/bin/mycat status &>/dev/null

if [ $? -ne 0 ];then

    /usr/local/mycat/bin/mycat restart &>/dev/null

    sleep 5

    if [ "$state" = "running" ];then

        systemctl restart keepalived

    else

        systemctl stop keepalived

    fi

fi

[root@proxy1 ~]# chmod +x /etc/keepalived/mycat.sh

[root@proxy1 ~]# vim /etc/keepalived/keepalived.conf

···

vrrp_script check_mycat {

    script "/etc/keepalived/mycat.sh"

    interval 5

}

track_script {

    check_mycat

}

···

　　

问题

galera+mycat高可用集群部署-LMLPHP

[root@mysql1 ~]# vim /etc/my.cnf

max_allowed_packet=16M

[root@mysql1 ~]# systemctl restart mysqld

　　

如果将三台数据库服务器都宕机了，想要再启动的话，会报如下错误

galera+mycat高可用集群部署-LMLPHP

[root@mysql1 ~]# vim /var/lib/mysql/grastate.dat

safe_to_bootstrap: 1

修改完第一个节点，第一个节点起来后，再重启其他两个节点，即可启动

第一个节点因为没有"推荐人"，所以一旦他宕机，如果想要让它重新加入到之前的集群中，必须
在它的推荐人填上另外机器的ip(解析的话，可以直接填解析名)

05-04 00:38
Powered by LMLPHP ©2024 162 0.061912