苏道桢的博客

MySQL互为主从 mycat分库分表 haproxy负载均衡 keepalived高可用

2022/08/01 MySQL 高可用 共 11060 字，约 32 分钟
电子技术小屋

实现目标

MySQL 分库分表，实现高可用，单点故障不受影响，恢复后，互为主从的数据库自动同步。

整体架构

一共6台服务器：

  1. 2台MySQL互为主从；
  2. 2台mycat中间件。mycat负责拆分数据库和表，读写分离，对外形成一个虚拟数据库（逻辑库）；然后重复配置一台做冗余，防止单点故障。此拆分方法为水平拆分即拆分同类表到不同的数据库里，垂直拆分适用于微服务，是一个服务单独一个数据库。
  3. 2台haproxy负责mycat的负载均衡，并检测mycat存活。再在haproxy服务器上安装keepalived，留出虚拟IP接口供应用访问，组成高可用集群。

部署准备

准备6台设备

IPMySQLmycathaproxykeepalived依赖包
192.168.48.210部署—————— 
192.168.48.211部署—————— 
192.168.48.212——部署————jdk
192.168.48.213——部署————jdk
192.168.48.214————部署部署 
192.168.48.215————部署部署 

6台设备均：关闭防火墙、SELinux；配置固定ip地址；ntp时间同步。

安装、配置MySQL

使用lnmp脚本安装MySQL

下载地址：http://soft1.vpser.net

cd lnmp1.8/
./install.sh db

默认安装5.5.62版。

登录MySQL1，增加root远程访问权限

mysql -u root -p
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
mysql> select host, user from user;

新增主从同步用户backup密码为password，允许的访问网段为192.168.48.0

mysql> GRANT REPLICATION slave,reload,super ON *.* TO 'backup'@'192.168.48.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

使用Navicat登录MySQL1，创建两个测试数据库db01和db02，并在两个数据库中均创建测试表test，两个测试字段，id和name。

修改MySQL1配置文件，配置主从同步：

vim /etc/my.cnf

[mysqld]
# 错误日志文件
log-error = mysql-err
	
# 忽略主从同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 需要主从同步的数据库
binlog-do-db=db01
binlog-do-db=db02
	
# 开启二进制日志文件
log-bin=mysql-bin
# 主从id号不同
server-id       = 1

注意：log-bin和server-id参数，原文件中有，取消注释即可，其余参数手动添加。

重启mysql

systemctl restart mysql

配置主从同步

登录MySQL1查看主库状态

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                |
+------------------+----------+--------------+-------------------------------------------------+
| mysql-bin.000032 |     1405 | test,test2   | mysql,information_schema,performance_schema,sys |
+------------------+----------+--------------+-------------------------------------------------+
1 row in set (0.00 sec)

记录下file名和position位置，配置从库时需要用到。

接下来安装、部署从MySQL2服务器，使用以上相同的步骤，配置文件（/etc/my.cnf）除了server-id其他都相同； MySQL2参考配置文件：

[mysqld]
# 错误日志文件
log-error = mysql-err
	
# 忽略主从同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 需要主从同步的数据库
binlog-do-db=db01
binlog-do-db=db02
	
# 开启二进制日志文件
log-bin=mysql-bin
# 主从id号不同
server-id       = 2

登录MySQL2，停止slave，重置slave，设定slave参数（主库ip，用户，密码；二进制文件和位置为刚才在主库上查到的）：

mysql -u root -p
mysql>stop slave;
mysql>reset slave;
mysql>CHANGE MASTER TO MASTER_HOST = '192.168.48.210', MASTER_USER = 'backup', MASTER_PASSWORD = 'password', MASTER_PORT = 3306, master_log_file='mysql-bin.000032', master_log_pos=1405;

在MySQL2上开启主从同步，并查看从库状态：

mysql> start slave；
mysql> show slave status \G

注意以下字段是否为yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果有no，查看错误日志。

至此MySQL1到MySQL2的主从同步已做完。

接下来配置MySQL2到MySQL1的主从同步。

登录MySQL2在MySQL2上查看主库状态：

mysql> show master status;

登录MySQL1在MySQL1中配置从库参数，具体参数根据实际修改：

mysql -u root -p
mysql> stop slave;
mysql> reset slave;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.48.211', MASTER_USER = 'backup', MASTER_PASSWORD = 'password', MASTER_PORT = 3306, master_log_file='mysql-bin.000032', master_log_pos=1405;

在MySQL1上开启主从同步，并查看从库状态：

mysql> start slave；
mysql> show slave status \G

注意以下字段是否为yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果有no，查看错误日志。

至此MySQL1和MySQL2的互为主从配置完成。

可以分别在MySQL1和MySQL2的db01和db02数据库里的test表中添加测试数据并查看是否能互相同步。

安装配置mycat

mycat下载地址：

http://dl.mycat.org.cn/

mycat1官网:

http://www.mycat.org.cn/mycat1.html

mycat2官网:

http://www.mycat.org.cn/

mycat依赖jdk，安装jdk

yum install -y java-1.8.0-openjdk.x86_64

本教程使用 Mycat-server-1.6.7.6-release-20220419132943-linux.tar.gz

解压mycat

tar -xzvf Mycat-server-1.6.7.6-release-20220419132943-linux.tar.gz -C /usr/local/
cd /usr/local/mycat/

修改配置文件

cd /usr/local/src/mycat/conf/

修改 server.xml 文件，这里主要修改逻辑数据库名（默认为TESTDB，多个数据库用逗号分开）和用户名及密码。

<user name="root" defaultAccount="true">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
	<property name="defaultSchema">TESTDB</property>
	<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
	<!-- 表级 DML 权限设置 -->
	<!--
	<privileges check="false">
		<schema name="TESTDB" dml="0110" >
			<table name="tb01" dml="0000"></table>
			<table name="tb02" dml="1111"></table>
		</schema>
	</privileges>
	-->
</user>
<user name="user">
	<property name="password">user</property>
	<property name="schemas">TESTDB</property>
	<property name="readOnly">true</property>
	<property name="defaultSchema">TESTDB</property>
</user>

修改 schema.xml 文件，这里主要是逻辑表、数据库节点的配置，该文件很重要，读写分离、分库分表在此文件中配置。

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<!-- name 为逻辑数据库名，须和server.xml里定义一样 -->
    <!-- auto sharding by id (long) -->
    <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
    <table name="test" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true" fetchStoreNodeByJdbc="true">
        <!-- name 为逻辑表名；dataNode 拆分后的数据库节点名；rule拆分规则名，需要和rule.xml文件中对应； -->
    </table>
</schema>


<dataNode name="dn1" dataHost="localhost1" database="db01" />
<dataNode name="dn2" dataHost="localhost1" database="db02" />
<!-- 数据库节点对应主机名的物理数据库配置，即dn1对应localhost1主机的db01数据库； -->
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
    <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->


<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" balanceType="1"
                    writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
<!-- 主机配置；
balance：读请求的负载均衡
    0：不开启读写分离，所有读操作发给writeHost
    1：读操作发给readHost和待命writeHost
    2：读请求随机发给readHost和writeHost
    3：读请求随机发给writeHost中的readHost （一般推荐这个）
writeType：写请求负载均衡
    0：写请求先发送给schema.xml配置文件中的第一个writeHost中，当第一个宕机发给后面的。切换记录记录在conf/dnindex.properties。（推荐这个）
    1：写请求随机发送到writeHost中
switchType：
    1：允许“读操作”在readHost和writeHost上自动切换（解决读延迟问题，readHost卡住从writeHost中读）
    -1：不允许
    2：根据“主从同步状态（心跳延迟）”自动选择是否切换。前提必须发送检测延迟语句<heartbeat>“show slave status”（推荐这个，但本例中使用1选项自动切换）

    -->
    <heartbeat>select user()</heartbeat>  
        <!-- 心跳检测语句； -->
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="jdbc:mysql://192.168.48.210:3306" user="root"
                        password="123456">
            <readHost host="hostS1" url="jdbc:mysql://192.168.48.211:3306" password="123456" user="root" weight="1"></readHost>
    </writeHost>
    <writeHost host="hostM2" url="jdbc:mysql://192.168.48.211:3306" user="root" password="123456"/>
</dataHost>
        <!-- 读写主机配置，writehostM1使用MySQL1，readhostS1使用MySQL2，形成读写分离；writehostM2使用MySQL2，防止MySQL1宕机后无法写数据库；-->

修改 rule.xml 文件，此文件定义拆分规则；tableRule 标签定义规则名字，function 标签定义具体拆分算法。

由于这里只拆两个库，所以修改mod-long算法的count为2，表示拆成两个。

<tableRule name="mod-long">
    <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
    </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">2</property>
</function>

拆分结果：

逻辑库为TESTDB，逻辑表为test，逻辑表分成dn1和dn2两个节点；

dn1-> 物理localhost1 中的 test表 ，dn2 -> 物理localhost1 中的 test2表；

localhost1中分 写库（MySQL1），和读库（MySQL2）；

写入到物理MySQL服务器中的数据，通过MySQL的binlog日志同步到MySQL2中，最终完成读写分离。

启动mycat并验证数据读写：

启动mycat

bin/mycat start

停止mycat

bin/mycat stop

查看状态

bin/mycat status

mycat启动错误日志：

logs/wrapper.log

mycat数据库操作错误：

logs/mycat.log

登录mycat

借助MySQL客户端登录，或使用navicat，数据库端口8066，管理端口9066。

注意：低版本mycat使用navicat15会出错，本例中使用的新版mycat无此问题。

mycat1配置完成后使用同样的配置，完成mycat2的部署。

安装配置 haproxy 和 keepalived

创建一个 haproxy 来管理 mycat 集群

安装 haproxy

查看haproxy版本

yum list | grep haproxy
yum install haproxy.x86_64 -y

配置haproxy：

更改haproxy的所有者和所属组为haproxy：haproxy

chown -R haproxy:haproxy /etc/haproxy/

配置日志：

vim /etc/rsyslog.conf

# 开启以下两行
$ModLoad imudp
$UDPServerRun 514
# local7本地日志预留服务，
local7.*                          /var/log/haproxy.log

重启日志服务

systemctl restart rsyslog.service

配置haproxy：

vim /etc/haproxy/haproxy.cfg

#日志文件local7改成和rsyslog.conf中一致
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local7

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

# haproxy管理web界面
listen stats
    mode http
    bind 0.0.0.0:5000                    # 端口5000
    stats uri /haproxy                    # 路径
    stats realm Global\ statistics
    stats auth admin:admin         #登录用户名和密码

#mycat SQL事务
listen mycat
    bind 0.0.0.0:8067
    mode tcp
    balance     roundrobin
    option tcplog
    server  mycat1    192.168.48.212:8066 check port 8066 maxconn 2000
    server  mycat2    192.168.48.213:8066 check port 8066 maxconn 2000

# mycat 管理界面
listen mycat_admin
    bind 0.0.0.0:9067
    mode tcp
    balance     roundrobin
    option tcplog
    server  mycatadmin1    192.168.48.212:9066 check port 8066 maxconn 2000
    server  mycatadmin2    192.168.48.213:9066 check port 8066 maxconn 2000

启动haproxy

systemctl restart haproxy.service

检查haproxy配置文件

haproxy -f /etc/haproxy/haproxy.cfg -c

错误：cannot bind socket [0.0.0.0:8066]

权限问题，需要设置haproxy的连接策略，或关闭SELinux

setsebool -P haproxy_connect_any=1

登陆haproxy的web管理界面

浏览器打开 http://192.168.48.214:5000/haproxy

再用相同的配置配置另一个haproxy组成集群

haproxy开机自启

systemctl enable haproxy.service

安装keepalived

yum install -y keepalived.x86-64

修改keepalived配置文件

vim /etc/keepalived/keepalived.conf

主haproxy 配置文件：

! Configuration File for keepalived
		
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
#   router_id LVS_DEVEL
   router_id haproxy_master
}
vrrp_script chk_haproxy {
    script "/usr/local/src/chk_haproxy.sh"
    interval 2
    weight -20
}
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 {
        192.168.48.111/24 dev ens33 label ens33:0
    }
}

备haproxy 配置文件：

! Configuration File for keepalived
		
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
#   router_id LVS_DEVEL
   router_id haproxy_backup
}
vrrp_script chk_haproxy {
    script "/usr/local/src/chk_haproxy.sh"
    interval 2
    weight -20
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.48.111/24 dev ens33 label ens33:0
    }
}

chk_haproxy.sh 脚本

用于循环检测 haproxy 服务

#!/bin/bash
haproxy_ps() {
    return $(ps -C haproxy --no-header | wc -l);
}
start_haproxy() {
    systemctl restart haproxy.service;
}
if haproxy_ps; then
    start_haproxy;
    sleep 2;
    if haproxy_ps; then
        killall keepalived;
    fi
fi

keepalived加入开机自启

systemctl enable keepalived.service

文档信息

Search

    Table of Contents