对mycat读写分离主从切换策略的调研
当从库宕机后，读请求会全部转移到主库上，对业务无影响。
2、当主库宕机后
目前，我们的writehost只设置了一个主库，当主库宕机后，通过mycat的 读写请求会全部报错，如下所示：
读请求：
mysql> select * from t1;
ERROR
1184
(HY000): 拒绝连接
|
写请求：
mysql> insert into t1 values (
2
);
ERROR
1184
(HY000): 拒绝连接
|
3、设置多个writehost，实现主库宕机后，写请求自动切换到从库
上面看到，如果只设置一个writehost，主库宕机后，并不会自动切换，所有的 读写请求全部受影响。
为了解决这个我们，我们可以设置多个writehost，当主库宕机后，自动把写请求切换到从节点。
我们的生产环境是一主一从，我们可以把两个节点都设置为writehost，这样如果writehost1（主库）宕机后，mycat会自动把 写请求全部转移到writehost2（从库）上。
原来配置：
[root
@demo
-init conf]# cat
schema.xml
<?xml version=
"1.0"
?>
<!DOCTYPE mycat:schema SYSTEM
"
schema.dtd"
>
<mycat:schema xmlns:mycat=
"
http://io.mycat/"
>
<schema name=
"db1"
checkSQLschema=
"false"
sqlMaxLimit=
"500"
dataNode=
"dn1"
></schema>
<dataNode name=
"dn1"
dataHost=
"localhost1"
database=
"db1"
/>
<dataHost name=
"localhost1"
maxCon=
"1000"
minCon=
"10"
balance=
"3"
writeType=
"0"
dbType=
"mysql"
dbDriver=
"native"
switchType=
"-1"
slaveThreshold=
"100"
>
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host=
"hostM1"
url=
"
192.168.174.30:3306"
user=
"root"
password=
"123456"
>
<readHost host=
"hostS1"
url=
"
192.168.174.30:3307"
user=
"root"
password=
"123456"
/>
</writeHost>
</dataHost>
</mycat:schema>
|
修改后配置：
[root
@demo
-init conf]# cat
schema.xml
<?xml version=
"1.0"
?>
<!DOCTYPE mycat:schema SYSTEM
"
schema.dtd"
>
<mycat:schema xmlns:mycat=
"
http://io.mycat/"
>
<schema name=
"db1"
checkSQLschema=
"false"
sqlMaxLimit=
"500"
dataNode=
"dn1"
></schema>
<dataNode name=
"dn1"
dataHost=
"localhost1"
database=
"db1"
/>
<dataHost name=
"localhost1"
maxCon=
"1000"
minCon=
"10"
balance=
"3"
writeType=
"0"
dbType=
"mysql"
dbDriver=
"native"
switchType=
"1"
slaveThreshold=
"100"
>
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host=
"hostM1"
url=
"
192.168.174.30:3306"
user=
"root"
password=
"123456"
>
<readHost host=
"hostS1"
url=
"
192.168.174.30:3307"
user=
"root"
password=
"123456"
/>
</writeHost>
<writeHost host=
"hostM2"
url=
"
192.168.174.30:3307"
user=
"root"
password=
"123456"
/>
</dataHost>
</mycat:schema>
|
就改了两处：
a）把 switchType="-1"改成 switchType="1"；
说明： switchType：
<writeHost host=
"allinmd-mysql-3"
url=
"
192.168.174.30:3307"
user=
"root"
password=
"123456"
/>