一、環(huán)境準(zhǔn)備：

拓?fù)鋱D.png

二、操作流程：

• server.xml添加要創(chuàng)建的新庫名
• schema.xml添加要創(chuàng)建的表名
• 重啟服務(wù)
• 客戶端登錄驗證配置

三、實現(xiàn)操作：

第一步：server.xml添加要創(chuàng)建的新庫名

• 將server.xml中的二個用戶進(jìn)行添加新庫“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標(biāo)簽，定義表名和存儲算法

[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ù)器本機(jī)查看數(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ù)器本機(jī)查看數(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ù)都是一致的