一汤善、環(huán)境準備：
| 主機名 | 角色 | IP地址 |
|---|---|---|
| client50 | 客戶端 | 192.168.88.50/24 |
| Mycat60 | 分片服務(wù)器 | 192.168.88.60/24 |
| Mysql63 | 數(shù)據(jù)庫服務(wù)器 | 192.168.88.63/24 |
| Mysql64 | 數(shù)據(jù)庫服務(wù)器 | 192.168.88.64/24 |
| Mysql65 | 數(shù)據(jù)庫服務(wù)器 | 192.168.88.65/24 |
拓撲圖.png
二什猖、操作流程：
- server.xml添加要創(chuàng)建的新庫名
- schema.xml添加要創(chuàng)建的表名
- 重啟服務(wù)
- 客戶端登錄驗證配置
三、實現(xiàn)操作：
第一步：server.xml添加要創(chuàng)建的新庫名
- 將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添加要創(chuàng)建的表名
- 添加一組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>
第三步：重啟服務(wù)
# 重啟mycat服務(wù)
[root@host60 conf]# /usr/local/mycat/bin/mycat restart
# 查看端口號
[root@host56 conf]# netstat -utnlp | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 3301/java
第四步：客戶端登錄驗證配置
- 客戶端登錄分片服務(wù)器查看新建的庫表
[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 |
+------------------+
- 存儲或操作數(shù)據(jù)測試
[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456
Mysql> use GAMEDB;
# 創(chuàng)建user表并存儲數(shù)據(jù)
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");
# 在數(shù)據(jù)庫服務(wù)器本機查看數(shù)據(jù)
[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 |
+------+----------+----------+
# 創(chuàng)建salary表并存儲數(shù)據(jù)
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)
# 在數(shù)據(jù)庫服務(wù)器本機查看數(shù)據(jù)
[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，所以每臺數(shù)據(jù)庫服務(wù)器存儲的數(shù)據(jù)都是一致的
