MySql分布式存储添加新库新表

一、环境准备：

主机名	角色	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，所以每台数据库服务器存储的数据都是一致的