cover
DBAMySQL主从复制读写分离分布式MyCAT高可用

MySQL-MyCAT基础架构&实现高可用和读写分离

2020年5月5日--

环境准备：

两台虚拟机 db01 db02 创建四个mysql实例：3307 3308 3309 3310 架构图： 基础架构

删除历史环境

pkill mysqld rm -rf /data/* mv /etc/my.cnf /etc/my.cnf.bak

创建数据目录，并初始化数据

mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/application/mysql

准备配置文件和启动脚本

-db01:

cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

-db02

cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF


cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

修改权限，启动多实例

chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"

ps -ef | grep mysqld

节点主从规划

箭头指向谁是主库：

    10.0.0.51:3307    <----->  10.0.0.52:3307
    10.0.0.51:3309    ------>  10.0.0.51:3307
    10.0.0.52:3309    ------>  10.0.0.52:3307

    10.0.0.52:3308    <----->  10.0.0.51:3308
    10.0.0.52:3310    ------>  10.0.0.52:3308
    10.0.0.51:3310    ------>  10.0.0.51:3308
2.6 分片规划
shard1：
    Master：10.0.0.51:3307
    slave1：10.0.0.51:3309
    Standby Master：10.0.0.52:3307
    slave2：10.0.0.52:3309
shard2：
    Master：10.0.0.52:3308
    slave1：10.0.0.52:3310
    Standby Master：10.0.0.51:3308
    slave2：10.0.0.51:3310

开始配置

shard1
10.0.0.51:3307    <----->  10.0.0.52:3307

#db02
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

[root@db02 ~]# mysql  -S /data/3307/mysql.sock -e "select host,user,authentication_string from mysql.user;"
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| 10.0.0.%  | repl          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 10.0.0.%  | root          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |


#db01
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3307/mysql.sock -e "start slave;"
mysql  -S /data/3307/mysql.sock -e "show slave status\G" | grep Running:

ps：构建了 从库db01，主库db02的主从关系


#db02
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3307/mysql.sock -e "start slave;"
mysql  -S /data/3307/mysql.sock -e "show slave status\G"  | grep Running:

ps：构建了 从库db02，主库db01的主从关系


10.0.0.51:3309    ------>  10.0.0.51:3307
#db01
mysql  -S /data/3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3309/mysql.sock  -e "start slave;"
mysql  -S /data/3309/mysql.sock  -e "show slave status\G"  | grep Running:

10.0.0.52:3309    ------>  10.0.0.52:3307
db02
mysql  -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3309/mysql.sock -e "start slave;"
mysql  -S /data/3309/mysql.sock -e "show slave status\G"  | grep Running:

shard2
10.0.0.52:3308    <----->  10.0.0.51:3308
db01
mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

db02
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3308/mysql.sock -e "start slave;"
mysql  -S /data/3308/mysql.sock -e "show slave status\G"  | grep Running:

db01
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3308/mysql.sock -e "start slave;"
mysql  -S /data/3308/mysql.sock -e "show slave status\G"  | grep Running:

10.0.0.52:3310    ----->       10.0.0.52:3308

db02
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3310/mysql.sock -e "start slave;"
mysql  -S /data/3310/mysql.sock -e "show slave status\G"  | grep Running:

10.0.0.51:3310  ----->     10.0.0.51:3308

db01
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql  -S /data/3310/mysql.sock -e "start slave;"
mysql  -S /data/3310/mysql.sock -e "show slave status\G"  | grep Running:
检测主从状态
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Running:

Mycat介绍

  • Mycat主要是做数据分布式存储，也有Atlas普通版的读写分离功能，其最重要还是分布式
  • Mycat是java开发的

mycat管理端口：9066 mycat数据端口：8066

安装Mycat

mycat下载地址：http://dl.mycat.org.cn/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz jdk下载地址：https://download.oracle.com/otn/java/jdk/8u60-b27/jdk-8u60-linux-x64.tar.gz

安装java

[root@db02 ~]# tar -xf  jdk-8u60-linux-x64.tar.gz -C /application/
[root@db02 ~]# ln -s /application/jdk1.8.0_60/ /application/jdk
[root@db02 ~]# ll /application/
lrwxrwxrwx 1 root root  25 5月   4 20:20 jdk -> /application/jdk1.8.0_60/
drwxr-xr-x 8   10  143 255 8月   5 2015 jdk1.8.0_60

安装mycat

[root@db02 ~]# tar -xf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz -C /application/
[root@db02 ~]# cd /application/
[root@db02 /application]# ls
jdk  jdk1.8.0_60  mycat  mysql  mysql-5.7.26-linux-glibc2.12-x86_64
[root@db02 /application]# ll
总用量 0
lrwxrwxrwx 1 root root  25 5月   4 20:20 jdk -> /application/jdk1.8.0_60/
drwxr-xr-x 8   10  143 255 8月   5 2015 jdk1.8.0_60
drwxr-xr-x 7 root root  85 5月   4 20:26 mycat
lrwxrwxrwx 1 root root  36 3月  18 02:19 mysql -> mysql-5.7.26-linux-glibc2.12-x86_64/
drwxr-xr-x 9 root root 129 3月  18 02:13 mysql-5.7.26-linux-glibc2.12-x86_64
日志文件说明：
#'/usr/local/mycat/logs目录
mycat.log     Mycat工作日志
mycat.pid     pid文件
switch.log
wrapper.log   Mycat启动相关日志
配置文件说明：
#'/usr/local/mycat/conf目录
schema.xml    主配置文件（读写分离、高可用、分表、节点控制）
server.xml    mycat软件本身相关的配置
rule.xml      分片规则配置文件（分片规则列表、使用方法）

添加jdk和mycat到环境变量

#man bash
#PATH 存放命令的路径
cat >>/etc/bashrc <<'EOF'
export JAVA_HOME=/application/jdk 
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH 
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$JAVA_HOME/lib/tools.jar

export PATH=/application/mycat/bin:$PATH
EOF

. /etc/bashrc 

[root@db02 ~]# java -version
java version "1.8.0_60"
Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

安装javajdk也可直接执行
yum install java-openjdk -y

启动mycat

mycat start
[root@db02 ~]# netstat -lntup | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      2644/java

进入Mycat程序：（默认用户root、密码123456） mysql -uroot -p123456 -h127.0.0.1 -P8066

日志文件说明：

#/application/mycat/logs目录
mycat.log     Mycat工作日志
mycat.pid     pid文件
switch.log
wrapper.log   Mycat启动相关日志

配置文件说明：

#/application/mycat/conf目录
schema.xml    主配置文件（读写分离、高可用、分表、节点控制）
server.xml    mycat软件本身相关的配置
rule.xml      分片规则配置文件（分片规则列表、使用方法）

测试数据准备

db01:
mysql -S /data/3307/mysql.sock 
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql

mysql -S /data/3308/mysql.sock 
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql

核心配置文件使用介绍

schema.xml

[root@db02 /application/mycat/conf]# pwd
/application/mycat/conf
[root@db02 /application/mycat/conf]# cat >schema.xml <<'EOF'
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  
        <dataNode name="sh1" dataHost="oldguo1" database= "world" />         
        <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123"> 
                        <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>
EOF
1.逻辑库定义
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  //mycat级别的逻辑对象                                 //对应的数据节点

2.数据节点定义
        <dataNode name="sh1" dataHost="oldguo1" database= "world" />

3.数据主机定义         
        <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123"> //写节点
                        <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> //读节点
        </writeHost> 
        </dataHost>

实现简单的读写分离

基础架构 定义完成后，使用上面的配置文件重启mycat。 涉及节点：机构图中左侧（10.0.0.51）红色节点【1主1从】

根据先前配置，此次读写分离的结果应当涉及到两个节点：
10.0.0.52:3307 /主节点 serverid=7 负责写入
10.0.0.52:3309 /从节点 serverid=9 负责读取

mysql -uroot -p123456 -h10.0.0.52 -P8066
mysql> begin;select @@server_id;commit;  //模拟写
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;  //模拟读
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

读写分离+高可用实现：

涉及节点： 架构图中所有红色部分【2主2从】

配置示例：

[root@db02 /application/mycat/conf]# cat >schema.xml <<'EOF'
<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  
    <dataNode name="sh1" dataHost="oldguo1" database= "world" />  
    <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1"> 
        <heartbeat>select user()</heartbeat> 
    <writeHost host="db1" url="10.0.0.51:3307" user="root" password="123"> 
            <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> 
    </writeHost> 
    <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123"> 
            <readHost host="db4" url="10.0.0.52:3309" user="root" password="123" /> 
    </writeHost>    
    </dataHost>  
</mycat:schema>
EOF

mycat restart

工作过程：

  • 1.数据主机定义下，添加了4个真实的物理节点。
  • 2.正常情况下的读写状态为
    • 2.1写操作，由主库db1（10.0.0.51:3307）承担，处于Real状态
    • 2.2读操作，由db2、db3、db4做负载分担，默认为轮询
    • 2.2其中db3同时作为备份主库处于Standby状态。当db1作为主库宕机时，立刻进行接管
  • 3.主库db1宕机情况下的读写状态为
    • 3.1 db3直接成为新的主库，承担写操作
    • 3.2 主库db1宕机时，其下面挂的从库db2也被置为不可用状态。
    • 3.3 读操作，只被分配给db4
根据先前配置，此次读写分离的结果应当涉及到两个节点：
10.0.0.52:3307 /主节点 serverid=7 负责写入
10.0.0.52:3309 /从节点 serverid=9 负责读取
10.0.0.52:3307 /主节点 serverid=17 负责读取，宕机时接替主库
10.0.0.52:3309 /从节点 serverid=19 负责读取

mysql -uroot -p123456 -h10.0.0.52 -P8066
模拟读：
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.04 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+
1 row in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)

模拟写：
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

模拟主库db01上的3307宕机：
[root@db01 ~]# systemctl stop mysqld3307

再次连接测试：
读操作：
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)

写操作：
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)

//由于serverid为9的从库是挂在了7上，所以当7宕机时。9也一并被置为了不可用状态

配置中的属性介绍:

balance属性

负载均衡类型，目前的取值有3种：

  1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 [其他三个节点就白瞎了]
  2. balance="1"，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说， 当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
  3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。即写节点也要负责一些读操作。

writeType属性

负载均衡类型，目前的取值有2种：

  1. writeType="0", 所有写操作发送到配置的第一个writeHost， 第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties .
  2. writeType="1"，所有写操作都随机的发送到配置的writeHost，但不推荐使用(mycat处理分布式事务效果不理想。锁相关问题)

switchType属性

  • -1 表示不自动接管
  • 1 默认值，自动接管
  • 2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status

datahost其他配置

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

maxCon="1000"：最大的并发连接数 minCon="10" ：mycat在启动之后，会在后端节点上自动开启的连接线程 tempReadHostAvailable="1"：临时允许已经宕机的主库下面的从库进行读操作。没必要，原因：主库宕机从库数据已经落后了。让从库继续读没意义 这个一主一从时（1个writehost，1个readhost时），可以开启这个参数，如果2个writehost，2个readhost时 <heartbeat>select user()</heartbeat> 监测心跳

MySQL-MyCAT基础架构&实现高可用和读写分离
转载前请阅读本站 版权协议，文章著作权归 饼铛 所有，转载请注明出处。