| 主机名 | IP | 角色 | 数据库 |
|---|---|---|---|
| node1 | 192.168.114.129 | Mycat | node_seq |
| node2 | 192.168.114.133 | Mysql | student_db01 student_db02 |
| node3 | 192.168.114.136 | Mysql | student_db03 student_db04 |
假设我们数据库中有一张学生表(stduent)，和一张学生详情表(student_detail)。因为数据量太大要分表，把原来一张表能够存储的数据，水平扩展到4个数据库中。
#node2create database student_db01 default charset utf8;create database student_db02 default charset utf8;#node3create database student_db03 default charset utf8;create database student_db04 default charset utf8;#stduent01,student02,student03,student04 节点CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL COMMENT '学生姓名', `class_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '班级id', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';CREATE TABLE `student_detail` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `student_id` varchar(32) NOT NULL COMMENT '学生表id', `address` varchar(50) DEFAULT '' COMMENT '学生地址', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生详情表';进行水平分片是才用到这个xml
<tableRule name="student_sharding"> <rule> <columns>class_id</columns> <algorithm>mod-long</algorithm> </rule></tableRule>全局唯一id的用处是，即使主键是自增的情况下，保证各个节点不会出现重复主键
vim my.conf log_bin_trust_function_creators=1mysql> mysql -uroot -pmysql> create database node_seq default charset utf8;导入mysql -uroot -p node_seq < /usr/local/mycat/conf/dbseq.sqlmysql > insert into MYCAT_SEQUENCE values('STUDENT',1,1);mysql > insert into MYCAT_SEQUENCE values('STUDENT_DETAIL',1,1);mysql > grant all on *.* to 'mycat_connect'@'192.168.114.%';mysql > flush privileges; vim /usr/local/mycat/conf/sequence_db_conf.properties STUDENT=node_seq STUDENT_DETAIL=node_seqvim /usr/local/mycat/conf/server.xml <property name="sequnceHandlerType">1</property> <!-- sequnceHandlerType = 1 --- 表示采用数据库对象-->vim /usr/local/mycat/conf/schema.xml
<schema name="school_db" checkSQLschema="false" sqlMaxLimit="100"> <table name="student" primaryKey="id" dataNode="student_db01,student_db02,student_db03,student_db04" rule="student_sharding" autoIncrement="true"><childTable name="student_detail" primaryKey="id" joinKey="student_id" parentKey="student_id" autoIncrement="true"></childTable></table> </schema><dataNode name="student_db01" dataHost="mysql133" database="student_db01" /><dataNode name="student_db02" dataHost="mysql133" database="student_db02" /><dataNode name="student_db03" dataHost="mysql136" database="student_db03" /><dataNode name="student_db04" dataHost="mysql136" database="student_db04" /><!--使用全角自增id--><dataNode name="node_seq" dataHost="mysql129" database="node_seq" /><dataHost name="mysql133" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"><heartbeat>select user()</heartbeat><writeHost host="192.168.114.133" url="192.168.114.133:3306" user="mycat_connect" password="123456" /></dataHost><dataHost name="mysql136" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"><heartbeat>select user()</heartbeat><writeHost host="192.168.114.136" url="192.168.114.136:3306" user="mycat_connect" password="123456" /></dataHost><dataHost name="mysql129" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.114.129" url="192.168.114.129:3306" user="mycat_connect" password="123456" /></dataHost><!--autoIncrement = true 表示表开启全局自增id --><system> <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户--> <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --> <property name="sequnceHandlerType">1</property> <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">4</property> <property name="charset">utf8</property> <property name="txIsolation">2</property> <property name="sqlExecuteTimeout">300</property> <property name="defaultMaxLimit">100</property> <property name="maxPacketSize">104857600</property> <property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property> <property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property> <property name="sqlInterceptorFile">/tmp/sql.txt</property></system> <user name="mycat_connect" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">school_db</property></user>mycat restart先清除以往订单mysql -umycat_connect -p -h 192.168.114.129 -P 8066插入数据#student表中插入数据insert into student(`name`,`class_id`) values('tom',1);insert into student(`name`,`class_id`) values('lily',2);insert into student(`name`,`class_id`) values('jack',4);insert into student(`name`,`class_id`) values('json',3);insert into student(`name`,`class_id`) values('hok',3);insert into student(`name`,`class_id`) values('haolly',3);insert into student(`name`,`class_id`) values('jerry',3);insert into student(`name`,`class_id`) values('wrrol',3);insert into student(`name`,`class_id`) values('brant',2);insert into student(`name`,`class_id`) values('james',4);#stduent_detail 中插入数据填写真实的student_idinsert into student_detail(student_id,address) values('','北京');#分别进入各个分片数据库中查看数据和分片对应情况 stduent_db01, stduent_db02, stduent_db03, stduent_db04select id,mod(class_id,4) from student;select student_id from student_detail;#mycat 关联查询select * from student as a join student_detail b on a.id = b.student_id;联系客服
