galera+mycat高可用集群部署
环境描述
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%";
三、安装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">123456</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
}
···
问题
[root@mysql1 ~]# vim /etc/my.cnf max_allowed_packet=16M [root@mysql1 ~]# systemctl restart mysqld
如果将三台数据库服务器都宕机了，想要再启动的话，会报如下错误
[root@mysql1 ~]# vim /var/lib/mysql/grastate.dat safe_to_bootstrap: 1
修改完第一个节点，第一个节点起来后，再重启其他两个节点，即可启动
第一个节点因为没有"推荐人"，所以一旦他宕机，如果想要让它重新加入到之前的集群中，必须
在它的推荐人填上另外机器的ip(解析的话，可以直接填解析名)
