用MyCat来做垂直分库

1.配置schema.xml cd /usr/local/mycat/conf

1.1由小到大先配置dataHost

                          writeType="0" dbType="mysql" dbDriver="native" switchType="1">

                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->


                <writeHost host="106.54.86.44" url="106.54.86.44:3306" user="im_mycat" 
                                   password="123456">

                </writeHost>

                <!-- 配置相应的节点ip-->
        </dataHost>

1.2接下来配置DataNode标签，将dataHost加入
<dataNode name="ordb" dataHost="mysql0102" database="order_db" />
database就是该节点上的物理库

1.3配置schema标签

<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <table name="order_master" dataNode="ordb" primaryKey="order_id" />

                <!-- global table is auto cloned to all defined data nodes ,so can join
                        with any table whose sharding node is in the same data node -->
                <table name="order_detail" primaryKey="order_detail_id"  dataNode="ordb" />
                <table name="order_cart" primaryKey="cart_id"  dataNode="ordb" />
                <!-- random sharding using mod sharind rule -->
                <table name="order_customer_addr" primaryKey="customer_addr_id"  dataNode="ordb" />
                <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
                        rule="mod-long" /> -->
                <table name="region_info" primaryKey="region_id" dataNode="ordb"
                            />
                <table name="shipping_info" primaryKey="ship_id" dataNode="ordb"
                          />
                 <table name="warehouse_info" primaryKey="w_id" dataNode="ordb"
                            />
                 <table name="warehouse_product" primaryKey="wp_id" dataNode="ordb"
                            />
                 <table name="product_brand_infos" primaryKey="brand_id" dataNode="prodb"
                            />
                 <table name="product_category" primaryKey="category_id" dataNode="prodb"
                            />
                 <table name="product_comment" primaryKey="comment_id" dataNode="prodb"
                            />
                 <table name="product_info" primaryKey="product_id" dataNode="prodb"
                            />
                 <table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb"
                            />
                 <table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb"
                            />
                 <table name="customer_balance_log" primaryKey="balance_id" dataNode="prodb"
                            />
                 <table name="customer_inf" primaryKey="customer_inf_id" dataNode="prodb"
                            />
                 <table name="customer_level_inf" primaryKey="customer_level" dataNode="prodb"
                            />
                 <table name="customer_login" primaryKey="customer_id" dataNode="prodb"
                            />
                 <table name="customer_login_log" primaryKey="login_id" dataNode="prodb"
                            />
                 <table name="customer_point_log" primaryKey="point_id" dataNode="prodb"
                            />



                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>

将各个表加入到逻辑库中

2配置server.xml

2.1配置system标签

        <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需>要指定默认账户-->
        <property name="useHandshakeV10">1</property>
        <property name="managerPort">9066</property>
        <property name="serverPort">8066</property>
        <property name="idleTimeout">300000</property>
        <property name="frontWriteQueueSize">2048</property>
        <property name="bindIp">0.0.0.0</property>
        <property name="charset">utf8</property>
        <property name="txIsolation">2</property>
        <property name="processors">8</property>
        <property name="sqlExecuteTimeout">300</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
        <property name="defaultMaxLimit">100</property>
        <property name="maxPacketSize">104857600</property>
                <property name="sequnceHandlerType">2</property>
        <property name="subqueryRelationshipCheck">false</property>

2.2配置user标签

                <property name="password">19980504Jcy@</property>

                <property name="schemas">imooc_db</property>
        </user>

schemas一定是schema.xml里配置的逻辑库，不能是物理库不然会报错
3.启动MyCat
3.1放开8066端口
3.2启动MyCatcd /usr/local/mycat/bin ./mycat start
查看是否启动成功cd ../logs cat wrapper.log
成功

image.png

image.png

启动不成功可查看wrapper.log排查

3.3mysql -uapp_imooc -p -h49.234.222.222 -P8066通过MyCat连接到逻辑库
成功

image.png