一、环境准备:
| 主机名 | 角色 | IP地址 |
|---|---|---|
| client50 | 客户端 | 192.168.88.50/24 |
| Mycat60 | 分片服务器 | 192.168.88.60/24 |
| Mysql63 | 数据库服务器 | 192.168.88.63/24 |
| Mysql64 | 数据库服务器 | 192.168.88.64/24 |
| Mysql65 | 数据库服务器 | 192.168.88.65/24 |
拓扑图.png
二、操作流程:
- server.xml添加要创建的新库名
- schema.xml添加要创建的表名
- 重启服务
- 客户端登录验证配置
三、实现操作:
第一步:server.xml添加要创建的新库名
- 将server.xml中的二个用户进行添加新库“GAMEDB”
[root@host60 conf]# vim /usr/local/mycat/conf/server.xml <user name="root"> <property name="password">123456</property> <property name="schemas">TESTDB,GAMEDB</property> 定义库名 </user> <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB,GAMEDB</property> <property name="readOnly">true</property> </user>
第二步:schema.xml添加要创建的表名
- 添加一组schema标签,定义表名和存储算法
[root@host60 conf]# vim /usr/local/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> # 在此行下方添加如下行 其他行不能动 <schema name="GAMEDB" checkSQLschema="false" sqlMaxLimit="100"> 定义库名 <table name="user" dataNode="dn1,dn2,dn3" rule="mod-long" /> # type="global" 不分片存储,全部存储 <table name="salary" dataNode="dn1,dn2,dn3" type="global" /> </schema> ... </mycat:schema>
第三步:重启服务
# 重启mycat服务 [root@host60 conf]# /usr/local/mycat/bin/mycat restart # 查看端口号 [root@host56 conf]# netstat -utnlp | grep 8066 tcp6 0 0 :::8066 :::* LISTEN 3301/java
第四步:客户端登录验证配置
- 客户端登录分片服务器查看新建的库表
[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456 mysql> show databases; +----------+ | DATABASE | +----------+ | GAMEDB | | TESTDB | +----------+ mysql> use GAMEDB; mysql> show tables; +------------------+ | Tables in GAMEDB | +------------------+ | salary | | user | +------------------+
- 存储或操作数据测试
[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456 Mysql> use GAMEDB; # 创建user表并存储数据 mysql> create table user( id int , username char(10) , password char(6) ); mysql> insert into user(id,username,password) values(17,"a","123456"),(17,"b","654321"),(17,"c","121123"); mysql> insert into user(id,username,password) values(4,"a","123456"),(4,"b","654321"),(4,"c","121123"); mysql> insert into user(id,username,password) values(9,"a","123456"),(9,"b","654321"),(9,"c","121123"); # 在数据库服务器本机查看数据 [root@host63 ~]# mysql -uroot -p123qqq...A -e 'select * from db1.user' mysql: [Warning] Using a password on the command line interface can be insecure. +------+----------+----------+ | id | username | password | +------+----------+----------+ | 9 | a | 123456 | | 9 | b | 654321 | | 9 | c | 121123 | +------+----------+----------+ [root@host64 ~]# mysql -uroot -p123qqq...A -e 'select * from db2.user' mysql: [Warning] Using a password on the command line interface can be insecure. +------+----------+----------+ | id | username | password | +------+----------+----------+ | 4 | a | 123456 | | 4 | b | 654321 | | 4 | c | 121123 | +------+----------+----------+ [root@host65 ~]# mysql -uroot -p123qqq...A -e 'select * from db3.user' mysql: [Warning] Using a password on the command line interface can be insecure. +------+----------+----------+ | id | username | password | +------+----------+----------+ | 17 | a | 123456 | | 17 | b | 654321 | | 17 | c | 121123 | +------+----------+----------+ # 创建salary表并存储数据 mysql> create table salary( name char(10), pay int ); mysql> insert into salary (name,pay) values("a",10); mysql> insert into salary (name,pay) values("b",20); mysql> select * from salary; +------+------+ | name | pay | +------+------+ | a | 10 | | b | 20 | +------+------+ 2 rows in set (0.09 sec) # 在数据库服务器本机查看数据 [root@host63 ~]# mysql -uroot -p123qqq...A -e 'select * from db1.salary' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+ | name | pay | +------+------+ | a | 10 | | b | 20 | +------+------+ [root@host64 ~]# mysql -uroot -p123qqq...A -e 'select * from db2.salary' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+ | name | pay | +------+------+ | a | 10 | | b | 20 | +------+------+ [root@host65 ~]# mysql -uroot -p123qqq...A -e 'select * from db3.salary' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+ | name | pay | +------+------+ | a | 10 | | b | 20 | +------+------+
- 由于salary表的type=global,所以每台数据库服务器存储的数据都是一致的