hydd的Linux笔记Day60

Day60

数据分片概述

分库/分表

​ 概念：将存放在在一台数据库服务器中的数据，按照特定的方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果。

水平分割

水平切割又叫横向切分：

​ 按照表中指定字段的分片规则，将表记录按行切分，分散存储在多个数据库中。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qRUYuLhC-1606436619115)(E:\图片\捕获.PNG)]

垂直分割

也叫纵向分割

​ 将单个数据库的多个表按业务类型分类，分散存储到不同的数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y4FKysp1-1606436619118)(E:\图片\2.PNG)]

MyCAT介绍

软件介绍

​ mycat是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案

​ 优点：

​ 适合数据大量写入的存储需求

​ 支持MySQL、Oracle、Sqlserver、Mongodb等

​ 提供数据读写分离服务

​ 提供数据分片服务

​ 基于阿里巴巴Cobar进行研发的开源软件

分片规则

mycat支持提供10种分片规则

1. 枚举法 sharding-by-intfile

2. 固定分片rule1

3. 范围约定 auto-sharding-log

4. 求模法 mod-long

5. 日期列分区分法 sharding-by-date

6. 通配取模 sharding-by-pattern

7. ASCII码求模通配 sharding-by-prefixpattern

8. 编程指定 sharding-by-substring

9. 字符串拆分hash解析 sharding-by-stringhash

10. 一致性hash sharding-by-murmur

工作过程

当mycat收到一个SQL命令时

1. 解析SQL命令涉及到的表
2. 然后看对表的配置，如果有分片规则，则获取SQL命令里分片字
3. 然后将SQL命令发往对应的数据库服务器去执行
4. 最后收集和处理所有分片结果数据，并返回到客户端

部署MyCAT服务

拓扑结构

数据分片拓扑结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mkumHh2x-1606436619119)(E:\图片\3.PNG)]

IP划分

配置分片服务器（192.168.4.56）

1）部署MyCat 运行环境

]# yum -y install java-1.8.0-openjdk //安装JDK
[root@mycat56 ~]# which java //查看命令

/usr/bin/java

[root@mycat56 ~]# java –version //显示版本

openjdk version "1.8.0_161"

OpenJDK Runtime Environment (build 1.8.0_161-b14)

OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)

2）安装提供服务的软件包

[root@mycat56 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz //解压源码
[root@mycat56 ~]# mv mycat /usr/local/ //移动目录（非必须操作）

[root@mycat56 ~]# ls /usr/local/mycat/ //查看文件列表

3）设置连账号

]# vim /usr/local/mycat/conf/server.xml
<user name="root">        //连接mycat服务时使用的用户名
     <property name="password">123456</property> //用户连接mycat用户时使用的密码
     <property name="schemas">TESTDB</property> //逻辑库名
</user>
<user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>    //只读权限，连接mycat服务后只有读记录的权限,不写这一行则是可读可写    
</user>
:wq

4）配置数据分片

使用sed删除不需要的配置行(可选操作)

[root@mycat56 conf]# wc -l schema.xml  //删除前查看总行数
77 /root/schema.xml
[root@mycat56 conf]# sed -i  '56,77d' schema.xml //删除无关的配置行
[root@mycat56 conf]# sed -i  '39,42d' schema.xml
[root@mycat56 conf]# sed -i  '16,18d' schema.xml
[root@mycat56 conf]# wc -l schema.xml //删除后查看总行数
48 schema.xml
[root@mycat56 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="TESTDB" checkSQLschema="false" sqlMaxLimit="100">//对TESTDB库下的表做分片存储
                <!-- auto sharding by id (long) -->
                  
                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />  //对travelrecord表做分片存储
                <!-- global table is auto cloned to all defined data nodes ,so can join
                        with any table whose sharding node is in the same data node -->
                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />  //对company表做分片存储
                <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />  
                <!-- random sharding using mod sharind rule -->
                <table name="hotnews"  dataNode="dn1,dn2,dn3"
                           rule="mod-long" /> 
                <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile" /> 
                <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile"> 
                        <childTable name="orders" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id">
                                <childTable name="order_items" joinKey="order_id"
                                                        parentKey="id" />
                        </childTable>
                        <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id" />
                </table>
                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>
//定义数据库主机名及存储数据的库
<dataNode name="dn1" dataHost="localhost53" database="db1" /> 
<dataNode name="dn2" dataHost="localhost54" database="db2" />
<dataNode name="dn3" dataHost="localhost55" database="db3" />
//定义localhost53主机名对应的数据库服务器ip地址
<dataHost name="localhost53" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"> 
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM53" url="192.168.4.53:3306" user="adminplj"
                                   password="123qqq...A">
                </writeHost> 
 </dataHost>
    
     //定义localhost54主机名对应的数据库服务器ip地址
    <dataHost name="localhost54" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM54" url="192.168.4.54:3306" user="adminplj"
                                   password="123qqq...A">
                </writeHost> 
     </dataHost> 
    //定义localhost54主机名对应的数据库服务器ip地址
    <dataHost name="localhost55" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM55" url="192.168.4.55:3306" user="adminplj"
                                   password="123qqq...A">
                </writeHost>
     </dataHost>
:wq

5）配置数据库服务器

根据分片文件的设置在对应的数据库服务器上创建存储数据的数据库

mysql> create database db1;   //在数据库53上，创建db1库
mysql> create database db2;   //在数据库54上，创建db2库
mysql> create database db3;   //在数据库55上，创建db3库  

根据分片文件配置，在对应的数据库服务器上创建授权用户(3台数据库服务器都要添加，在数据库服务器本机管理员root用户登录后执行授权命令)

mysql> grant all on  *.* to adminplj@"%" identified by "123qqq...A" ; //在数据库服务器192.168.4.53 执行
mysql> grant all on  *.* to adminplj@"%" identified by "123qqq...A" ; //在数据库服务器192.168.4.54 执行 
mysql> grant all on  *.* to adminplj@"%" identified by "123qqq...A" ; //在数据库服务器192.168.4.55 执行

6）启动mycat服务

测试授权用户：在192.168.4.56主机，使用授权用户分别连接3台数据库服务器，若连接失败，请检查数据库服务器是否有对应的授权用户。

[root@mycat56 ~]# which  mysql || yum  -y  install  mariadb //安装提供mysql命令的软件包
//连接数据库服务器192.168.4.53
[root@mycat56 ~]# mysql -h192.168.4.53 -uadminplj -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;   //连接成功 断开连接
Bye
[root@mycat56 ~]# 
//连接数据库服务器192.168.4.54
[root@mycat56 ~]# mysql -h192.168.4.54 -uadminplj -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;   //连接成功 断开连接
Bye
[root@mycat56 ~]# 
//连接数据库服务器192.168.4.54
[root@mycat56 ~]# mysql -h192.168.4.55 -uadminplj -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit ;   //连接成功 断开连接

启动服务

[root@mycat56 ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat56 ~]#

查看服务状态

[root@mycat56 ~]# netstat  -utnlp  | grep  :8066  //查看端口
tcp6       0      0 :::8066                 :::*       LISTEN      2924/java           
[root@mycat56 ~]# 
[root@mycat56 ~]# ps -C java  //查看进程
  PID TTY          TIME CMD
 2924 ?        00:00:01 java
[root@mycat56 ~]#

测试配置

1）客户端访问

在客户端192.168.4.50 连接分片服务器，访问数据

命令： mysql -hmycat主机的IP -P端口号 -u用户 -p密码

[root@client50 ~]#  mysql -uroot -p123456 -h192.168.4.56 -P8066
mysql> show databases; //显示已有的库
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
mysql> USE TESTDB; //进入TESTDB库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 
mysql> show tables; //显示已有的表，配置文件里定义的表名
+------------------+
| Tables in TESTDB |
+------------------+
| company          |
| customer         |
| customer_addr    |
| employee         |
| goods            |
| hotnews          |
| orders           |
| order_items      |
| travelrecord     |
+------------------+
9 rows in set (0.00 sec)
mysql>exit；  //断开连接

练习sharding-by-intfile分片规则的使用

sharding-by-intfile

枚举法

-字段值必须在规则文件定义的值里选择

1）查看配置文件，得知使用sharding-by-intfile分片规则的表名

[root@mycat56 ~]# vim /usr/local/mycat/conf/schema.xml

<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"

rule="sharding-by-intfile" />

:wq

2）查看规则文件，得知sharding-by-intfile分片规则使用的函数

[root@mycat56 ~]# vim /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile">
                <rule>
                        <columns>sharding_id</columns>  //数据分片字段名
                        <algorithm>hash-int</algorithm> //使用的函数名
                </rule>
</tableRule>
<function name="hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property> //函数调用的配置文件
</function>
：wq

3）修改函数配置文件,添加dn3 数据节点

[root@mycat56 ~]# vim  /usr/local/mycat/conf/partition-hash-int.txt
10000=0   //当sharding_id字段的值是10000时,数据存储在数据节点dn1里
10010=1   //当sharding_id字段的值是10010时,数据存储在数据节点dn2里
10020=2   //当sharding_id字段的值是10020时,数据存储在数据节点dn3里
:wq

4）重启mycat服务，使其修改有效

[root@mycat56 ~]# /usr/local/mycat/bin/mycat  stop    //停止服务
Stopping Mycat-server...
Stopped Mycat-server.
[root@mycat56 conf]# netstat -utnlp  | grep  :8066  //无端口
[root@mycat56 conf]# ps –C  java //无进程
[root@mycat56 conf]#
[root@mycat56 conf]# /usr/local/mycat/bin/mycat  start  //启动服务
Starting Mycat-server...
[root@mycat56 conf]# 
[root@mycat56 conf]# netstat -utnlp  | grep  :8066 //有端口
tcp6       0      0 :::8066                 :::*           LISTEN      1364/java
[root@mycat56 conf]#
[root@mycat56 conf]# ps –C  java //有进程
PID TTY          TIME CMD
 1125 ?        00:00:01 java
[root@mycat56 conf]#

5）客户端连接分片服务器，存取数据

]#mysql -h192.168.4.56 -P8066 -uroot -p123456 //访问服务
mysql> use TESTDB; //进入TESTDB库
mysql> create table  employee( ID int primary key , sharding_id int,
    -> name char(15) , age  int ); //建表
Query OK, 0 rows affected (0.68 sec)
mysql> desc employee; //查看表结构
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ID          | int(11)  | NO   | PRI | NULL    |       |
| sharding_id | int(11)  | YES  |     | NULL    |       |
| name        | char(15) | YES  |     | NULL    |       |
| age         | int(11)  | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Mysql>insert into employee(ID,sharding_id,name,age) //插入表记录
values 
(1,10000,"bob",19), //存储在53服务器的db1库的employee表里
(2,10010,"tom",21), //存储在54服务器的db2库的employee表里
(3,10020,"lucy2",16); //存储在55服务器的db3库的employee表里
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select  * from employee; //查看表记录
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  1 |       10000 | bob  |   19 |
|  2 |       10010 | tom  |   21 |
|  3 |       10020 | lucy |   16 |
+----+-------------+------+------+
3 rows in set (0.06 sec)
mysql>insert into employee(ID,sharding_id,name,age)
values 
(4,10000,"bob2",19), //存储在53服务器的db1库的employee表里
(5,10000,"tom2",21), //存储在53服务器的db1库的employee表里
(6,10000,"lucy2",16); //存储在53服务器的db1库的employee表里
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select  * from employee;   //查看表记录                                        +----+-------------+-------+------+
| ID | sharding_id | name  | age  |
+----+-------------+-------+------+
|  1 |       10000 | bob   |   19 |
|  4 |       10000 | bob2  |   19 |
|  5 |       10000 | tom2  |   21 |
|  6 |       10000 | lucy2 |   16 |
|  3 |       10020 | lucy  |   16 |
|  2 |       10010 | tom   |   21 |
+----+-------------+-------+------+
6 rows in set (0.00 sec)

6）在数据库服务器本机，查看表记录

在数据库服务器192.168.4.53 查看数据

[root@host53 ~]# mysql -uroot -p123qqq...A -e "select * from db1.employee"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+-------+------+
| ID | sharding_id | name  | age  |
+----+-------------+-------+------+
|  1 |       10000 | bob   |   19 |
|  4 |       10000 | bob2  |   19 |
|  5 |       10000 | tom2  |   21 |
|  6 |       10000 | lucy2 |   16 |
+----+-------------+-------+------+
[root@host53 ~]#

在数据库服务器192.168.4.54 查看数据

[root@host54 ~]#  mysql -uroot -p123qqq...A -e "select * from db2.employee"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  2 |       10010 | tom  |   21 |
+----+-------------+------+------+
[root@host54 ~]#

在数据库服务器192.168.4.55 查看数据

[root@host55 ~]#  mysql -uroot -p123qqq...A -e "select * from db3.employee"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  3 |       10020 | lucy |   16 |
+----+-------------+------+------+
[root@host55 ~]#

练习mod-long分片规则的使用

求模法

​ 根据字段值与设定的数字求模结果存储数据

1）查看配置文件，得知使用mod-long分片规则的表名

注意要删除 primaryKey=“ID” autoIncrement=“true” 不然无法存储数据

[root@mycat56 ~]# vim /usr/local/mycat/conf/schema.xml

<table name="hotnews" dataNode="dn1,dn2,dn3" rule="mod-long" />

:wq

2）查看规则文件，得知 mod-long分片规则使用的函数

[root@mycat56 ~]# vim /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
                <rule>
                        <columns>id</columns>  //数据分片字段
                        <algorithm>mod-long</algorithm> //函数名
                </rule>
        </tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">3</property> //指定求模数字
</function>

3）重启mycat服务，使其修改有效

[root@mycat56 ~]# /usr/local/mycat/bin/mycat  stop    //停止服务
Stopping Mycat-server...
Stopped Mycat-server.
[root@mycat56 conf]# netstat -utnlp  | grep  :8066  //无端口
[root@mycat56 conf]# ps –C  java //无进程
[root@mycat56 conf]#
[root@mycat56 conf]# /usr/local/mycat/bin/mycat  start  //启动服务
Starting Mycat-server...
[root@mycat56 conf]# 
[root@mycat56 conf]# netstat -utnlp  | grep  :8066 //有端口
tcp6       0      0 :::8066                 :::*           LISTEN      1364/java
[root@mycat56 conf]#
[root@mycat56 conf]# ps –C  java //有进程
PID TTY          TIME CMD
 1125 ?        00:00:01 java
[root@mycat56 conf]#

4）客户端连接分片服务器，存取数据

]#mysql -h192.168.4.56 -P8066 -uroot -p123456 //访问服务
mysql> use TESTDB; //进入TESTDB库
mysql> create table hotnews(id int ,title char(30),comment char(200)); //建表
Query OK, 0 rows affected (0.79 sec)
mysql> desc hotnews; //查看表结构
+---------+-----------+------+-----+---------+-------+
| Field   | Type      | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------+-------+
| id     | int(11)   | YES  |     | NULL    |       |
| title   | char(30)  | YES  |     | NULL    |       |
| comment | char(200) | YES  |     | NULL    |       |
+---------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into hotnews(id,title,comment)values(9,"sc","xxxxx"); //插入第1条表记录，9和3取余 余0 记录存储在53服务器的db1库里
Query OK, 1 row affected (0.11 sec)
mysql> insert into hotnews(id,title,comment)values(10,"xx","haha");//插入第2条表记录，10和3取余 余1 记录存储在54服务器的db2库里
Query OK, 1 row affected (0.05 sec)
mysql> insert into hotnews(id,title,comment)values(11,"yy","zz");//插入第3条表记录，11和3取余 余2 记录存储在55服务器的db3库里
Query OK, 1 row affected (0.03 sec)
mysql> select  * from hotnews; //查看表记录
+------+-------+---------+
|   id | title | comment |
+------+-------+---------+
|   11 | yy    | zz      |
|   10 | xx    | haha    |
|    9 | sc    | xxxxx   |
+------+-------+---------+

5）在数据库服务器本机，查看表记录

在数据库服务器192.168.4.53 查看数据

[root@host53 ~]# mysql -uroot -p123qqq...A -e "select * from db1.hotnews"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
|  id  | title | comment |
+------+-------+---------+
|    9 | sc    | xxxxx   |
+------+-------+---------+
[root@host53 ~]#

在数据库服务器192.168.4.54 查看数据

[root@host54 ~]#  mysql -uroot -p123qqq...A -e "select * mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
|   id  | title | comment |
+------+-------+---------+
|   10 | xx    | haha    |
+------+-------+---------+ db2.hotnews"

在数据库服务器192.168.4.55 查看数据

[root@host55 ~]#  mysql -uroot -p123qqq...A -e "select * from db3.hotnewsmysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
|   id   | title | comment |
+------+-------+---------+
|   11 | yy    | zz      |
+------+-------+---------+

连接分片服务器存储数据

配置Mycat服务器

1）添加新库

[root@mycat56 ~]# vim /usr/local/mycat/conf/server.xml

<user name="root">

……

<property name="schemas">TESTDB,BBSDB</property>//指定逻辑库名

</user>

2）添加新表

[root@mycat56 ~]# vim /usr/local/mycat/conf/schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">              
            <schema name="BBSDB" checkSQLschema="false" sqlMaxLimit="100">
                    <table name="company2" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> //指定逻辑表名company2
                    <table name="employee2" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile" /> //指定逻辑表名employee2
          </schema>
          <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                ……
                ……
</mycat:schema >

3）重启mycat服务

[root@mycat56 ~]# /usr/local/mycat/bin/mycat stop //停止服务

Stopping Mycat-server...

Stopped Mycat-server.

[root@mycat56 conf]# netstat -utnlp | grep :8066 //无端口

[root@mycat56 conf]# ps –C java //无进程

[root@mycat56 conf]#

[root@mycat56 conf]# /usr/local/mycat/bin/mycat start //启动服务

Starting Mycat-server...

[root@mycat56 conf]#

[root@mycat56 conf]# netstat -utnlp | grep :8066 //有端口

tcp6 0 0 :::8066 :::* LISTEN 1364/java

[root@mycat56 conf]#

[root@mycat56 conf]# ps –C java //有进程

PID TTY TIME CMD

1125 ? 00:00:01 java

[root@mycat56 conf]#

测试配置

1）连接mycat服务器、建表、插入记录

[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456 //连接mycat服务器

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; //显示已有的数据库

+----------+

| DATABASE |

+----------+

| BBSDB | //逻辑库BBSDB

| TESTDB |

+----------+

2 rows in set (0.00 sec)

mysql> use BBSDB; //切换到BBSDB库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables; //查看表

+-----------------+

| Tables in BBSDB |

+-----------------+

| company | //逻辑表

| employee |

+-----------------+

2 rows in set (0.00 sec)

mysql> create table company(ID int primary key,name char(50),addr char(50));//建表

Query OK, 0 rows affected (1.01 sec)

mysql> desc company; //查看表表结构

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| ID | int(11) | NO | PRI | NULL | |

| name | char(50) | YES | | NULL | |

| addr | char(50) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into company(ID,name,addr)values(1,"tarena","beijing");//插入记录

Query OK, 1 row affected (0.10 sec)

mysql> insert into company(ID,name,addr)values(2,"tmall","beijing");

Query OK, 1 row affected (0.15 sec)

mysql> insert into company(ID,name,addr)values(3,"sina","beijing");

Query OK, 1 row affected (0.13 sec)

mysql> select * from company; //查看表记录

+----+--------+---------+

| ID | name | addr |

+----+--------+---------+

| 1 | tarena | beijing |

| 2 | tmall | beijing |

| 3 | sina | beijing |

+----+--------+---------+

3 rows in set (0.04 sec)

2）在数据库服务器本机，查看表记录，在数据库服务器53本机查看。

 [root@host53 ~]# mysql -uroot -p123qqq...A -e "select * from db1.company2"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+---------+
| ID | name   | addr    |
+----+--------+---------+
|  1 | tarena | beijing |
|  2 | tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+
[root@host53 ~]#

3）在数据库服务器54本机查看

 [root@host54 ~]# mysql -uroot -p123qqq...A -e "select * from db2.company2mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+---------+
| ID | name   | addr    |
+----+--------+---------+
|  1 | tarena | beijing |
|  2 | tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+
[root@host54 ~]#
 

4）在数据库服务器55本机查看

 [root@host55 ~]# mysql -uroot -p123qqq...A -e "select * from db3.company"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+---------+
| ID | name   | addr    |
+----+--------+---------+
|  1 | tarena | beijing |
|  2 | tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+
 tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+
[root@host53 ~]#

3）在数据库服务器54本机查看

 [root@host54 ~]# mysql -uroot -p123qqq...A -e "select * from db2.company2mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+---------+
| ID | name   | addr    |
+----+--------+---------+
|  1 | tarena | beijing |
|  2 | tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+
[root@host54 ~]#
 

4）在数据库服务器55本机查看

 [root@host55 ~]# mysql -uroot -p123qqq...A -e "select * from db3.company"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+---------+
| ID | name   | addr    |
+----+--------+---------+
|  1 | tarena | beijing |
|  2 | tmall  | beijing |
|  3 | sina   | beijing |
+----+--------+---------+