好久没写博客了,今天写一下这个mycat的使用和需要注意的知识点。先说一下mycat是个什么东东,能干什么用,下面来看一下。
一,什么是mycat
- 国内最活跃的、性能最好的开源数据库分库分表中间件
- 一个彻底开源的,面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
二,mycat能干什么
mycat是一个开源的分布式数据库系统,它主要的一个作用就是能分库,实现读写分离,所以它就会比单一的单库运行速度会快。直接上配置吧
schema.xml配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="data_center" checkSQLschema="false" sqlMaxLimit="15000">
<table name="mycat_sequence" primaryKey="name" dataNode="node_db01" />
<table name="t_buy_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,,node_db12" />
<table name="t_sale_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,,node_db12" />
<table name="t_category" primaryKey="category_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_config" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_config_working_time" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_department" primaryKey="dept_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_dic_city" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_dic_district" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_dic_province" primaryKey="PROVINCE_ID" autoIncrement="true" dataNode="node_db01" />
<table name="t_dictionary" primaryKey="dict_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_dictionary_category" primaryKey="dict_category_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_group" type="global" primaryKey="group_id" autoIncrement="true"
dataNode="node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,,node_db12" />
<table name="t_group_department" primaryKey="dept_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_group_index" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_organ" primaryKey="organ_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_permission" primaryKey="perms_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_product" primaryKey="p_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_role" primaryKey="role_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_role_perms" primaryKey="role_perms_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_system_setting" dataNode="node_db01" />
<table name="t_target_existing_expand_set" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_month" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_month_personnel_set" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_month_set" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_quarter" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_quarter_set" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_target_year" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_test" primaryKey="sale_mingxi_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user" primaryKey="user_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_department" primaryKey="user_department_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_keys" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_role" primaryKey="user_role_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_simple" primaryKey="user_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_view" primaryKey="view_id" autoIncrement="true" dataNode="node_db01" />
<table name="t_user_view_fuwu" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_data_update_config" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_year_month_total_money" primaryKey="id" autoIncrement="true" dataNode="node_db01" />
<table name="t_customer" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_stock_waring" primaryKey="id" autoIncrement="true" type="global"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_begin_stock" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_stock_other_outin" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_voucher" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="T_BEGIN_IN" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_begin_out" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_advance_payments" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_cost" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_sale_order_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_buy_order_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_stock" primaryKey="id" autoIncrement="true" type="global"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_stock_log" primaryKey="id" autoIncrement="true" type="global"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
<table name="t_stock_log2019" primaryKey="id" autoIncrement="true" type="global"
dataNode="node_db00,node_db01,node_db02,node_db03,node_db04,node_db05,node_db06,node_db07,node_db08,node_db09,node_db10,node_db11,node_db12" />
</schema>
<!-- 第二数据源 -->
<schema name="data_center2" checkSQLschema="false" sqlMaxLimit="15000">
<table name="mycat_sequence" primaryKey="name" dataNode="node2_db00" />
<table name="t_buy_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_sale_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_category" primaryKey="category_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_config" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_config_working_time" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_department" primaryKey="dept_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_dic_city" primaryKey="ID" autoIncrement="true" dataNode="node2_db00" />
<table name="t_dic_district" primaryKey="ID" autoIncrement="true" dataNode="node2_db00" />
<table name="t_dic_province" primaryKey="PROVINCE_ID" autoIncrement="true" dataNode="node2_db00" />
<table name="t_dictionary" primaryKey="dict_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_dictionary_category" primaryKey="dict_category_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_group" type="global" primaryKey="group_id" autoIncrement="true"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_group_department" primaryKey="dept_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_group_index" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_organ" primaryKey="organ_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_permission" primaryKey="perms_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_product" primaryKey="p_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_role" primaryKey="role_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_role_perms" primaryKey="role_perms_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_system_setting" dataNode="node2_db00" />
<table name="t_target_existing_expand_set" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_month" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_month_personnel_set" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_month_set" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_quarter" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_quarter_set" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_target_year" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_test" primaryKey="sale_mingxi_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user" primaryKey="user_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_department" primaryKey="user_department_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_keys" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_role" primaryKey="user_role_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_simple" primaryKey="user_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_view" primaryKey="view_id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_user_view_fuwu" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_data_update_config" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_year_month_total_money" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_stock_log" primaryKey="id" autoIncrement="true" dataNode="node2_db00" />
<table name="t_customer" type="global" primaryKey="id" autoIncrement="true"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_stock_waring" primaryKey="id" autoIncrement="true" type="global"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_begin_stock" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_stock_other_outin" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_voucher" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="T_BEGIN_IN" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12"/>
<table name="t_begin_out" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_advance_payments" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_cost" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_sale_order_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_buy_order_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_stock" primaryKey="id" autoIncrement="true" type="global"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<!-- <table name="t_stock_log" primaryKey="id" autoIncrement="true" type="global"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" /> -->
<table name="t_stock_log2019" primaryKey="id" autoIncrement="true" type="global"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
<table name="t_sale_brand_target" primaryKey="id" autoIncrement="true" type="global"
dataNode="node2_db00,node2_db01,node2_db02,node2_db03,node2_db04,node2_db05,node2_db06,node2_db07,node2_db08,node2_db09,node2_db10,node2_db11,node2_db12" />
</schema>
<!-- 第三数据源 -->
<schema name="data_center3" checkSQLschema="false" sqlMaxLimit="15000">
<table name="mycat_sequence" primaryKey="name" dataNode="node3_db00" />
<table name="t_buy_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_sale_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_category" primaryKey="category_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_config" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_config_working_time" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_department" primaryKey="dept_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_dic_city" primaryKey="ID" autoIncrement="true" dataNode="node3_db00" />
<table name="t_dic_district" primaryKey="ID" autoIncrement="true" dataNode="node3_db00" />
<table name="t_dic_province" primaryKey="PROVINCE_ID" autoIncrement="true" dataNode="node3_db00" />
<table name="t_dictionary" primaryKey="dict_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_dictionary_category" primaryKey="dict_category_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_group" type="global" primaryKey="group_id" autoIncrement="true"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_group_department" primaryKey="dept_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_group_index" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_organ" primaryKey="organ_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_permission" primaryKey="perms_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_product" primaryKey="p_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_role" primaryKey="role_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_role_perms" primaryKey="role_perms_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_system_setting" dataNode="node3_db00" />
<table name="t_target_existing_expand_set" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_month" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_month_personnel_set" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_month_set" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_quarter" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_quarter_set" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_target_year" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_test" primaryKey="sale_mingxi_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user" primaryKey="user_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_department" primaryKey="user_department_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_keys" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_role" primaryKey="user_role_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_simple" primaryKey="user_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_view" primaryKey="view_id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_user_view_fuwu" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_data_update_config" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_year_month_total_money" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_stock_log" primaryKey="id" autoIncrement="true" dataNode="node3_db00" />
<table name="t_customer" type="global" primaryKey="id" autoIncrement="true"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_stock_waring" primaryKey="id" autoIncrement="true" type="global"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_begin_stock" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_stock_other_outin" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_voucher" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="T_BEGIN_IN" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12"/>
<table name="t_begin_out" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_advance_payments" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_cost" primaryKey="id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_sale_order_mingxi" primaryKey="sale_mingxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_buy_order_mingxi" primaryKey="buy_minxi_id" autoIncrement="true" rule="sharding-by-month"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_stock" primaryKey="id" autoIncrement="true" type="global"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<!-- <table name="t_stock_log" primaryKey="id" autoIncrement="true" type="global"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" /> -->
<table name="t_stock_log2019" primaryKey="id" autoIncrement="true" type="global"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
<table name="t_sale_brand_target" primaryKey="id" autoIncrement="true" type="global"
dataNode="node3_db00,node3_db01,node3_db02,node3_db03,node3_db04,node3_db05,node3_db06,node3_db07,node3_db08,node3_db09,node3_db10,node3_db11,node3_db12" />
</schema>
<dataNode name="node_db00" dataHost="dataHost01" database="data_center201712" />
<dataNode name="node_db01" dataHost="dataHost01" database="data_center201801" />
<dataNode name="node_db02" dataHost="dataHost01" database="data_center201802" />
<dataNode name="node_db03" dataHost="dataHost01" database="data_center201803" />
<dataNode name="node_db04" dataHost="dataHost01" database="data_center201804" />
<dataNode name="node_db05" dataHost="dataHost01" database="data_center201805" />
<dataNode name="node_db06" dataHost="dataHost01" database="data_center201806" />
<dataNode name="node_db07" dataHost="dataHost01" database="data_center201807" />
<dataNode name="node_db08" dataHost="dataHost01" database="data_center201808" />
<dataNode name="node_db09" dataHost="dataHost01" database="data_center201809" />
<dataNode name="node_db10" dataHost="dataHost01" database="data_center201810" />
<dataNode name="node_db11" dataHost="dataHost01" database="data_center201811" />
<dataNode name="node_db12" dataHost="dataHost01" database="data_center201812" />
<!-- 第二数据源 -->
<dataNode name="node2_db00" dataHost="dataHost01" database="data2_center201901" />
<dataNode name="node2_db01" dataHost="dataHost01" database="data2_center201902" />
<dataNode name="node2_db02" dataHost="dataHost01" database="data2_center201903" />
<dataNode name="node2_db03" dataHost="dataHost01" database="data2_center201904" />
<dataNode name="node2_db04" dataHost="dataHost01" database="data2_center201905" />
<dataNode name="node2_db05" dataHost="dataHost01" database="data2_center201906" />
<dataNode name="node2_db06" dataHost="dataHost01" database="data2_center201907" />
<dataNode name="node2_db07" dataHost="dataHost01" database="data2_center201908" />
<dataNode name="node2_db08" dataHost="dataHost01" database="data2_center201909" />
<dataNode name="node2_db09" dataHost="dataHost01" database="data2_center201910" />
<dataNode name="node2_db10" dataHost="dataHost01" database="data2_center201911" />
<dataNode name="node2_db11" dataHost="dataHost01" database="data2_center201912" />
<dataNode name="node2_db12" dataHost="dataHost01" database="data2_center201812" />
<!-- 第三数据源 -->
<dataNode name="node3_db00" dataHost="dataHost01" database="data3_center202001" />
<dataNode name="node3_db01" dataHost="dataHost01" database="data3_center202002" />
<dataNode name="node3_db02" dataHost="dataHost01" database="data3_center202003" />
<dataNode name="node3_db03" dataHost="dataHost01" database="data3_center202004" />
<dataNode name="node3_db04" dataHost="dataHost01" database="data3_center202005" />
<dataNode name="node3_db05" dataHost="dataHost01" database="data3_center202006" />
<dataNode name="node3_db06" dataHost="dataHost01" database="data3_center202007" />
<dataNode name="node3_db07" dataHost="dataHost01" database="data3_center202008" />
<dataNode name="node3_db08" dataHost="dataHost01" database="data3_center202009" />
<dataNode name="node3_db09" dataHost="dataHost01" database="data3_center202010" />
<dataNode name="node3_db10" dataHost="dataHost01" database="data3_center202011" />
<dataNode name="node3_db11" dataHost="dataHost01" database="data3_center202012" />
<dataNode name="node3_db12" dataHost="dataHost01" database="data3_center201912" />
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat> select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root"></writeHost>
</dataHost>
<!-- 第二数据源 -->
</mycat:schema>
注释: 从这个配置文件中,可以看出我一共配置了三个数据源,先解释这句,<schema name="data_center2" checkSQLschema="false" sqlMaxLimit="15000">
name就是一个别名,没啥好解释的;checkSQLschema:当该值为true时,例如我们执行语句select * from t_user.company 。mycat会把语句修改为 select * from company 去掉t_user。sqlMaxLimit :比如例子里边写的值为15000,如果你的sql里边没有limit,那么它会自动给你加上limit 15000。如果不设置的话,默认返回所有值。
<table name="mycat_sequence" primaryKey="name" dataNode="node2_db00" />
table标签定义了mycat的逻辑表,所有需要拆分的表都要在这儿定义- dataNode 属性的值需要和dataNode标签中的name值相对应。
- primaryKey 属性意思就是逻辑表对应真实表的主键。
- type 属性定义了逻辑表的类型,分为全局表和分库的表,global就是全局表,如果不是全局表,就不用设置type属性。
- autoIncrement 属性,mysql对非自增长主键,使用last_insert_id() 是不会返回结果的,只会返回0.所以,只有定义了自增长主键的表,才可以用last_insert_id()返回主键值。
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat> select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root"></writeHost> </dataHost>
这段代码是用来配置读写分离的,我这个例子里边并没有配置,所以就不区分读写分离;如果需要区分读写分离,则在写上readHost标签即可。
这只是我对mycat的一些理解,所以有任何不足的地方,欢迎的大家给我留言,有不懂的也可以给我留言,谢谢大家!