Mycat垂直分库配置(单主机配置、多主多从配置)

Mycat垂直分库配置

1-1.停止读写分离配置后(否则报错ERROR 2013 (HY000): Lost connection to MySQL server during query),修改schema.xml配置文件

1. master1和master2上

mysql>stop slave;
mysql>reset slave;

2.mycat服务器上

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 开启binlog
# 日志文件前缀
log-bin=mysql-bin
# binlog日志保留的天数，清除超过10天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10
# 跳过错误操作
slave-skip-errors=all
#此处开始必须添加
skip-name-resolve
default-authentication-plugin=mysql_native_password
max_allowed_packet = 500M
net_read_timeout = 120
net_write_timeout = 900

• 否则报错

mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Enter password: 
Connection id:    2
Current database: TESTDB

• 修改schema.xml配置文件

<?xtiveml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        	<!--配置表名为orders的表自动路由到dn2的数据库中-->
                <table name = "orders" dataNode="dn2"></table>
        </schema>
        	<!--表位于数据库tests中-->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test2" />
        	<!-- 修改两节点的balance="0"-->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.148.134:3306" user="root"
                                   password="password">
                </writeHost>               
        </dataHost>
        	<!-- 修改两节点的balance="0"-->
           <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.148.133:3306" user="root"
                                   password="password">
                </writeHost>
           </dataHost>
</mycat:schema>
~                                 

1.2 保留读写分离配置并修改schema配置文件

• dn1节点为双主双从
• dn2节点仅为table="orders"服务,为单服务主机
• 修改schema.xml配置文件,配置可以单设,也可多主多从读写分离

<?xtiveml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        	<!--配置表名为test2的表自动路由到dn2的数据库中-->
                <table name = "orders" dataNode="dn2"></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="order1" />
        <dataNode name="dn2" dataHost="host2" database="order2" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.148.134:3306" user="root"
                                   password="password">
                          <readHost host="hostS1" url="192.168.148.133:3306"  user="root" password="password"/>
                </writeHost>
                 <writeHost host="hostM2" url="192.168.148.136:3306" user="root"
                                   password="password">
                          <readHost host="hostS2" url="192.168.148.137:3306"  user="root" password="password"/>
                </writeHost>
        </dataHost>
                   <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.148.139:3306" user="root"
                                   password="password">
                </writeHost>
           </dataHost>
</mycat:schema>
~                     

2.创建数据库(本文测试中仅讨论1.1的情况)

• 134上

create database test1;

• 133上

create database test2;

3.mycat服务器上测试

use TESTDB
create table test(name varchar(32));
create table orders(name varchar(32));

• 结论
• 可以看到在节点master1的数据库order1生成表test
• 在节点master2的数据库order2生成表orders
• 垂直分库成功