LVS+KeepAlived+Mycat+MySQL主从集群

发布于 2018-01-28  2k 次阅读


LVS+KeepAlived+Mycat+MySQL主从集群

架构图

机器规划

Hostname IP System Software
lb1 192.168.198.128 CentOS 7.2 Keepalived1 v1.3.5 、LVS1 version: 2.02.171
lb2 192.168.198.129 CentOS 7.2 Keepalived2 、LVS2
mycat1 192.168.198.130 CentOS 7.2 Mycat 1 、MySQL Master 1
mycat2 192.168.198.131 CentOS 7.2 Mycat 2 、MySQL Master 2
slave1 192.168.198.132 CentOS 7.2 MySQL Slave 1
slave2 192.168.198.133 CentOS 7.2 MySQL Slave 2

准备工作

修改6台node的hosts

192.168.198.135 lb1
192.168.198.136 lb2
192.168.198.137 mycat1
192.168.198.138 mycat2
192.168.198.139 slave1
192.168.198.140 slave2

[root@lb1 ~]# for i in {lb2,mycat1,mycat2,slave1,slave2}; do scp /etc/hosts root@$i:/etc ;done

安装keepalived 和 ipvsadmin

[root@lb1 ~]# yum install -y keepalived ipvsadm
[root@lb2 ~]# yum install -y keepalived ipvsadm
[root@lb1 ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     zhuxh@hcis.com.cn
   }
   notification_email_from mail@hcis.com.cn
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
vrrp_instance VI_1 {
    state MASTER
    interface ens37
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.198.134
    }
}

virtual_server 192.168.198.134 8066 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.198.130 8066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 8066
        }
    }
    real_server 192.168.198.131 8066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 8066
        }
    }
}
  • scp同步配置文件到lb2
    [root@lb1 ~]# scp /etc/keepalived/keepalived.conf root@lb2:/etc/keepalived/
  • 修改keepalived配置文件
    (BACKUP)配置文件的差异一共只有3 处: 全局定义的route_id、vrrp_instance state 以及 vrrp_instance 的优先级priority。

mycat安装

  • JAVA环境安装
    [root@mycat1 ~]# rpm -ivh jdk-8u151-linux-x64.rpm
    [root@mycat1 ~]# vim /etc/profile

JAVA_HOME=/usr/java/jdk1.8.0_151
JRE_HOME=/usr/java/jdk1.8.0_151/jre
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
export JAVA_HOME JRE_HOME PATH CLASSPATH 

[root@mycat1 ~]# source /etc/profile
[root@mycat1 ~]# java -version

java version "1.8.0_151"
Java(TM) SE Runtime Environment (build 1.8.0_151-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.151-b12, mixed mode)

[root@mycat1 ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat1 ~]# tar xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat1 ~]# mkdir /usr/local/Mycat
[root@mycat1 ~]# mv mycat/* /usr/local/Mycat/
[root@mycat1 ~]# ls /usr/local/Mycat/
bin catlet conf lib logs version.txt
[root@mycat1 ~]# groupadd mycat
[root@mycat1 ~]# useradd -r -g mycat mycat
[root@mycat1 ~]# echo 'HcisHcis' | passwd --stdin mycat
Changing password for user mycat.
passwd: all authentication tokens updated successfully.
[root@mycat1 ~]# chown -R mycat:mycat /usr/local/Mycat/

安装supervisor守护mycat程序

[root@mycat1 bin]# yum install -y supervisor
[root@mycat1 bin]# vim /etc/supervisord.conf

[program:mycat]
command = /usr/local/Mycat/bin/mycat start
user = root
autostart = true
autorestart = true

[root@mycat1 ~]# systemctl start supervisord
[root@mycat1 ~]# systemctl enable supervisord
[root@mycat1 ~]# ps -ef | grep mycat

双主双从模式,Mycat1配置


[root@mycat1 Mycat]# vim conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 双主双从模式-->
        <schema name="hcis" dataNode="mycat1">
        <table name="g2_t_attach_config" dataNode="dn1,dn2" rule="sharding-by-murmur" primaryKey="config_id"/>
        </schema>
        <dataNode name="dn1" dataHost="192.168.198.137" database="hcis" />
        <dataNode name="dn2" dataHost="192.168.198.138" database="hcis" />
        <!-- 分片 1 -->
        <dataHost name="192.168.198.137" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <!-- 读写 分离-->
                <writeHost host="hostM1" url="192.168.137:3306" user="root" password="HcisHcis">
                <readHost host="hostS1" url="192.168.198.139:3306" user="root" password="HcisHcis" />
                </writeHost>
                <writeHost host="hostM2" url="192.168.198.138:3306" user="root" password="HcisHcis">
                <readHost host="hostS2" url="192.168.198.140:3306" user="root" password="HcisHcis" />
                </writeHost>
        </dataHost>
        <!-- 分片 2 -->
        <dataHost name="192.168.198.138" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="hostM11" url="192.168.198.138:3306" user="root" password="HcisHcis">
                <readHost host="hostS11" url="192.168.198.140:3306" user="root" password="HcisHcis" />
                </writeHost>
                <writeHost host="hostM22" url="192.168.198.137:3306" user="root" password="HcisHcis">
                <readHost host="hostS22" url="192.168.198.139:3306" user="root" password="HcisHcis" />
                </writeHost>
        </dataHost>
</mycat:schema>

安装MySQL

设置MySQL的yum源


[root@mycat1 ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
[root@mycat1 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-release-el7-5 ################################# [100%]
[root@mycat1 ~]# yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64 MySQL Connectors Community 42
mysql-tools-community/x86_64 MySQL Tools Community 53
mysql56-community/x86_64 MySQL 5.6 Community Server 361
[root@mycat1 ~]# yum install -y mysql-server
[root@mycat1 ~]# systemctl start mysqld
[root@mycat1 ~]# systemctl enable mysqld
[root@mycat1 ~]# mysql_secure_installation
[root@mycat1 ~]# mysql_secure_installation

配置主从

master配置

  • 先创建一个数据库
    mysql> create database hcis;
    Query OK, 1 row affected (0.00 sec)
    mysql> grant all on hcis.* to hcis@localhost identified by 'HcisHcis';
    Query OK, 0 rows affected (0.00 sec)
    mysql> use hcis;
    Database changed
    mysql> create table hello(id int,name varchar(20));
    Query OK, 0 rows affected (0.03 sec)
  • 对slave进行授权
    mysql> grant replication slave on *.* to hcis@slave1 identified by 'HcisHcis';
    Query OK, 0 rows affected (0.01 sec)
    mysql> flush privileges;
    mysql> exit
    Bye

  • 编辑/etc/my.conf配置binlog
    [root@mycat1 ~]# vim /etc/my.cnf

log-bin = mysql-bin-master
server-id = 1
binlog-do-db = hcis
binlog-ignore-db = mysql
  • 重启数据库服务
    [root@mycat1 ~]# systemctl restart mysqld

  • 查看主数据库状态

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      120 | hcis         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin-master.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.38-log, Binlog ver: 4
1 row in set (0.00 sec)

slave配置

  • 创建跟主库一样的数据库
    mysql> create database hcis;
    Query OK, 1 row affected (0.00 sec)
    mysql> grant all on hcis.* to hcis@localhost identified by 'HcisHcis';
    Query OK, 0 rows affected (0.00 sec)
    mysql> use hcis;
    Database changed
    mysql> create table hello(id int,name varchar(20));
    Query OK, 0 rows affected (0.03 sec)
  • 编辑my.cnf文件
    [root@node9 ~]# vim /etc/my.cnf
server-id = 2
  • 重启数据库
    [root@slave1 ~]# systemctl restart mysqld

  • 配置从数据库

    mysql> change master to
    master_host='mycat1',
    master_user='hcis',
    master_password='HcisHcis',
    master_log_file='mysql-bin-master.000001',
    master_log_pos=120;

  • 启动从数据库
    mysql> start slave;
    Query OK, 0 rows affected (0.28 sec)

  • 查看主从同步状态

mysql> show slave status\G
mysql> show processlist\G

遇到的问题

1 mysql安装

file /usr/share/mysql/charsets/swe7.xml from install of mysql-community-common-5.6.38-2.el7.x86_64 conflicts with file from package MariaDB-common-10.2.1-1.el7.centos.x86_64
file /etc/my.cnf from install of mysql-community-server-5.6.38-2.el7.x86_64 conflicts with file from package MariaDB-common-10.2.1-1.el7.centos.x86_64
Error Summary

解决办法:centos7自带的是MariaDB,之前想用它来着,后来改MySQL了,需要把跟它有关的依赖都卸载了。
[root@mycat1 ~]# yum remove MariaDB*

2 systemd 开机自启动

[root@mycat2 ~]# systemctl enable mysqld
Failed to execute operation: Too many levels of symbolic links
yum安装/编译安装的MySQL5.6,设置开机启动的时候都存在这个问题,搜索了很久都不行,最后换虚拟机系统版本为centos7.2后解决。猜想是centos7.4最新版本的systemd的service语法,跟安装的MySQL5.6使用的systemd不一样。待后续深入研究。

3 mysql主从,master做完授权,slave依旧连不上。

解决办法:master grant后切记刷新授权。mysql> flush privileges;

4 配置主从,重启mysqld,没有binlog

mysql> show master status; Empty set (0.00 sec)
问题原因:bin-log配置在了my.cnf文件最底下,要配置到[mysqld]里面!!!

5 mycat服务启不来

STATUS | wrapper | 2017/12/05 09:10:31 | --> Wrapper Started as Daemon
STATUS | wrapper | 2017/12/05 09:10:31 | Launching a JVM…
INFO | jvm 1 | 2017/12/05 09:10:31 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64
M; support was removed in 8.0
INFO | jvm 1 | 2017/12/05 09:10:32 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2017/12/05 09:10:32 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2017/12/05 09:10:32 |
INFO | jvm 1 | 2017/12/05 09:10:33 |
INFO | jvm 1 | 2017/12/05 09:10:33 | WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionI
nInitializerError
INFO | jvm 1 | 2017/12/05 09:10:33 | java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.MycatStartup.main(MycatStartup.java:53)
INFO | jvm 1 | 2017/12/05 09:10:33 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2017/12/05 09:10:33 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessor
Impl.java:62)
INFO | jvm 1 | 2017/12/05 09:10:33 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethod
AccessorImpl.java:43)
INFO | jvm 1 | 2017/12/05 09:10:33 | at java.lang.reflect.Method.invoke(Method.java:498)
INFO | jvm 1 | 2017/12/05 09:10:33 | at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp
.java:240)
INFO | jvm 1 | 2017/12/05 09:10:33 | at java.lang.Thread.run(Thread.java:748)
INFO | jvm 1 | 2017/12/05 09:10:33 | Caused by: io.mycat.config.util.ConfigException: java.lang.NullPointerExc
eption
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.
java:126)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.(XMLSchemaLoade
r.java:83)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.(XMLSchemaLoade
r.java:87)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.ConfigInitializer.(ConfigInitializer.java:
74)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.MycatConfig.(MycatConfig.java:72)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.MycatServer.(MycatServer.java:144)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.MycatServer.(MycatServer.java:96)
INFO | jvm 1 | 2017/12/05 09:10:33 | … 7 more
INFO | jvm 1 | 2017/12/05 09:10:33 | Caused by: java.lang.NullPointerException
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.getDbType(XMLSchemaLo
ader.java:458)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.loadTables(XMLSchemaL
oader.java:387)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.loadSchemas(XMLSchema
Loader.java:173)
INFO | jvm 1 | 2017/12/05 09:10:33 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.
java:122)
INFO | jvm 1 | 2017/12/05 09:10:33 | … 13 more
STATUS | wrapper | 2017/12/05 09:10:35 | <-- Wrapper Stopped
[root@mycat1 logs]#
[root@mycat1 logs]# java -version
java version "1.8.0_151"
Java(TM) SE Runtime Environment (build 1.8.0_151-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.151-b12, mixed mode)
[root@mycat1 logs]#


Flyfish's Blog - 飞鱼博客| 点滴记忆,记录成长---flyfish