Top

NSD RDBMS2 DAY03

  1. 案例1：部署MyCAT服务
  2. 案例2：连接分片服务器存储数据
  3. 案例3：连接分片服务器存储数据

1 案例1：部署MyCAT服务

1.1 问题

1.2 方案

准备5台虚拟主机；其中主机192.168.4.56作为mycat服务器，192.168.4.53、192.168.4.54、192.168.4.55运行数据库服务，192.168.4.50作为客户端。具体如图-1所示：

图-1

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：配置分片服务器（192.168.4.56）

1）部署MyCat 运行环境

]# yum -y install java-1.8.0-openjdk //安装JDK

已安装:

java-1.8.0-openjdk.x86_64 1:1.8.0.161-2.b14.el7

作为依赖被安装:

alsa-lib.x86_64 0:1.1.4.1-2.el7

copy-jdk-configs.noarch 0:3.3-2.el7

giflib.x86_64 0:4.1.6-9.el7

java-1.8.0-openjdk-headless.x86_64 1:1.8.0.161-2.b14.el7

javapackages-tools.noarch 0:3.4.1-11.el7

libXtst.x86_64 0:1.2.3-1.el7

libxslt.x86_64 0:1.1.28-5.el7

lksctp-tools.x86_64 0:1.0.17-2.el7

python-javapackages.noarch 0:3.4.1-11.el7

python-lxml.x86_64 0:3.2.1-4.el7

tzdata-java.noarch 0:2018c-1.el7

完毕！

[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 //解压源码

mycat/bin/wrapper-linux-ppc-64

……

……

mycat/version.txt

mycat/conf/log4j2.xml

mycat/bin/init_zk_data.sh

mycat/bin/startup_nowrap.sh

mycat/bin/dataMigrate.sh

mycat/bin/rehash.sh

mycat/logs/

mycat/catlet/

[root@mycat56 ~]#

[root@mycat56 ~]# mv mycat /usr/local/ //移动目录（非必须操作）

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

bin catlet conf lib logs version.txt

[root@mycat56 ~]#

3）设置连账号

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

4）配置数据分片

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

  1. [root@mycat56 conf]# wc -l schema.xml  //删除前查看总行数
  2. 77 /root/schema.xml
  3.  
  4. [root@mycat56 conf]# sed -i  '56,77d' schema.xml //删除无关的配置行
  5. [root@mycat56 conf]# sed -i  '39,42d' schema.xml
  6. [root@mycat56 conf]# sed -i  '16,18d' schema.xml
  7.  
  8. [root@mycat56 conf]# wc -l schema.xml //删除后查看总行数
  9. 48 schema.xml
  10.  
  11. [root@mycat56 conf]# vim  /usr/local/mycat/conf/schema.xml 
  12. <?xml version="1.0"?>
  13. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  14. <mycat:schema xmlns:mycat="http://io.mycat/">
  15.           
  16.        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">//对TESTDB库下的表做分片存储
  17.                 <!-- auto sharding by id (long) -->
  18.                   
  19.                 <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />  //对travelrecord表做分片存储
  20.  
  21.                 <!-- global table is auto cloned to all defined data nodes ,so can join
  22.                         with any table whose sharding node is in the same data node -->
  23.                 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />  //对company表做分片存储
  24.  
  25.                 <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />  
  26.  
  27.                 <!-- random sharding using mod sharind rule -->
  28.  
  29.                 <table name="hotnews"  dataNode="dn1,dn2,dn3"
  30.                            rule="mod-long" /> 
  31.  
  32.                 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
  33.                            rule="sharding-by-intfile" /> 
  34.  
  35.                 <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
  36.                            rule="sharding-by-intfile"> 
  37.                         <childTable name="orders" primaryKey="ID" joinKey="customer_id"
  38.                                                 parentKey="id">
  39.                                 <childTable name="order_items" joinKey="order_id"
  40.                                                         parentKey="id" />
  41.                         </childTable>
  42.                         <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
  43.                                                 parentKey="id" />
  44.                 </table>
  45.                 <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
  46.                         /> -->
  47.         </schema>
  48.  
  49.  
  50. //定义数据库主机名及存储数据的库
  51. <dataNode name="dn1" dataHost="localhost53" database="db1" /> 
  52. <dataNode name="dn2" dataHost="localhost54" database="db2" />
  53. <dataNode name="dn3" dataHost="localhost55" database="db3" />
  54.  
  55. //定义localhost53主机名对应的数据库服务器ip地址
  56. <dataHost name="localhost53" maxCon="1000" minCon="10" balance="0"
  57.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"> 
  58.                 <heartbeat>select user()</heartbeat>
  59.                 <writeHost host="hostM53" url="192.168.4.53:3306" user="adminplj"
  60.                                    password="123qqq...A">
  61.                 </writeHost> 
  62.  </dataHost>
  63.     
  64.      //定义localhost54主机名对应的数据库服务器ip地址
  65.     <dataHost name="localhost54" maxCon="1000" minCon="10" balance="0"
  66.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  67.                 <heartbeat>select user()</heartbeat>
  68.                 <writeHost host="hostM54" url="192.168.4.54:3306" user="adminplj"
  69.                                    password="123qqq...A">
  70.                 </writeHost> 
  71.      </dataHost> 
  72.  
  73.     //定义localhost54主机名对应的数据库服务器ip地址
  74.     <dataHost name="localhost55" maxCon="1000" minCon="10" balance="0"
  75.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  76.                 <heartbeat>select user()</heartbeat>
  77.                 <writeHost host="hostM55" url="192.168.4.55:3306" user="adminplj"
  78.                                    password="123qqq...A">
  79.                 </writeHost>
  80.      </dataHost>
  81. :wq

5）配置数据库服务器

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

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

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

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

6）启动mycat服务

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

  1. [root@mycat56 ~]# which  mysql || yum  -y  install  mariadb //安装提供mysql命令的软件包
  2.  
  3. //连接数据库服务器192.168.4.53
  4. [root@mycat56 ~]# mysql -h192.168.4.53 -uadminplj -p123qqq...A
  5. mysql: [Warning] Using a password on the command line interface can be insecure.
  6. Welcome to the MySQL monitor.  Commands end with ; or \g.
  7. Your MySQL connection id is 54
  8. Server version: 5.7.17 MySQL Community Server (GPL)
  9.  
  10. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  11.  
  12. Oracle is a registered trademark of Oracle Corporation and/or its
  13. affiliates. Other names may be trademarks of their respective
  14. owners.
  15.  
  16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  17.  
  18. mysql> exit;   //连接成功 断开连接
  19. Bye
  20. [root@mycat56 ~]# 
  21.  
  22. //连接数据库服务器192.168.4.54
  23. [root@mycat56 ~]# mysql -h192.168.4.54 -uadminplj -p123qqq...A
  24. mysql: [Warning] Using a password on the command line interface can be insecure.
  25. Welcome to the MySQL monitor.  Commands end with ; or \g.
  26. Your MySQL connection id is 47
  27. Server version: 5.7.17 MySQL Community Server (GPL)
  28.  
  29. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  30.  
  31. Oracle is a registered trademark of Oracle Corporation and/or its
  32. affiliates. Other names may be trademarks of their respective
  33. owners.
  34.  
  35. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  36.  
  37. mysql> exit;   //连接成功 断开连接
  38. Bye
  39. [root@mycat56 ~]# 
  40.  
  41. //连接数据库服务器192.168.4.54
  42. [root@mycat56 ~]# mysql -h192.168.4.55 -uadminplj -p123qqq...A
  43. mysql: [Warning] Using a password on the command line interface can be insecure.
  44. Welcome to the MySQL monitor.  Commands end with ; or \g.
  45. Your MySQL connection id is 49
  46. Server version: 5.7.17 MySQL Community Server (GPL)
  47.  
  48. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  49.  
  50. Oracle is a registered trademark of Oracle Corporation and/or its
  51. affiliates. Other names may be trademarks of their respective
  52. owners.
  53.  
  54. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  55.  
  56. mysql> exit ;   //连接成功 断开连接

启动服务

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

查看服务状态

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

步骤二：测试配置

1）客户端访问

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

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

  1. [root@client50 ~]# mysql -h192.168.4.56 -P8066 -uroot –p123456
  2. mysql> show databases; //显示已有的库
  3. +----------+
  4. | DATABASE |
  5. +----------+
  6. | TESTDB   |
  7. +----------+
  8. 1 row in set (0.00 sec)
  9.  
  10. mysql> USE TESTDB; //进入TESTDB库
  11. Reading table information for completion of table and column names
  12. You can turn off this feature to get a quicker startup with -A
  13.  
  14. Database changed
  15. mysql> 
  16. mysql> show tables; //显示已有的表，配置文件里定义的表名
  17. +------------------+
  18. | Tables in TESTDB |
  19. +------------------+
  20. | company          |
  21. | customer         |
  22. | customer_addr    |
  23. | employee         |
  24. | goods            |
  25. | hotnews          |
  26. | orders           |
  27. | order_items      |
  28. | travelrecord     |
  29. +------------------+
  30. 9 rows in set (0.00 sec)
  31. mysql>exit；  //断开连接

2 案例2：连接分片服务器存储数据

2.1 问题：

具体要求如下：

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习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分片规则使用的函数

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

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

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

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

  1. [root@mycat56 ~]# /usr/local/mycat/bin/mycat  stop    //停止服务
  2. Stopping Mycat-server...
  3. Stopped Mycat-server.
  4. [root@mycat56 conf]# netstat -utnlp  | grep  :8066  //无端口
  5. [root@mycat56 conf]# ps –C  java //无进程
  6. [root@mycat56 conf]#
  7.  
  8. [root@mycat56 conf]# /usr/local/mycat/bin/mycat  start  //启动服务
  9. Starting Mycat-server...
  10. [root@mycat56 conf]# 
  11. [root@mycat56 conf]# netstat -utnlp  | grep  :8066 //有端口
  12. tcp6       0      0 :::8066                 :::*           LISTEN      1364/java
  13. [root@mycat56 conf]#
  14. [root@mycat56 conf]# ps –C  java //有进程
  15. PID TTY          TIME CMD
  16.  1125 ?        00:00:01 java
  17. [root@mycat56 conf]#

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

  1. ]#mysql -h192.168.4.56 -P8066 -uroot -p123456 //访问服务
  2. mysql> use TESTDB; //进入TESTDB库
  3. mysql> create table  employee( ID int primary key , sharding_id int,
  4.     -> name char(15) , age  int ); //建表
  5. Query OK, 0 rows affected (0.68 sec)
  6.  
  7. mysql> desc employee; //查看表结构
  8. +-------------+----------+------+-----+---------+-------+
  9. | Field       | Type     | Null | Key | Default | Extra |
  10. +-------------+----------+------+-----+---------+-------+
  11. | ID          | int(11)  | NO   | PRI | NULL    |       |
  12. | sharding_id | int(11)  | YES  |     | NULL    |       |
  13. | name        | char(15) | YES  |     | NULL    |       |
  14. | age         | int(11)  | YES  |     | NULL    |       |
  15. +-------------+----------+------+-----+---------+-------+
  16. 4 rows in set (0.00 sec)
  17.  
  18. Mysql>insert into employee(ID,sharding_id,name,age) //插入表记录
  19. values 
  20. (1,10000,"bob",19), //存储在53服务器的db1库的employee表里
  21. (2,10010,"tom",21), //存储在54服务器的db2库的employee表里
  22. (3,10020,"lucy2",16); //存储在55服务器的db3库的employee表里
  23. Query OK, 3 rows affected (0.07 sec)
  24. Records: 3  Duplicates: 0  Warnings: 0
  25. mysql> select  * from employee; //查看表记录
  26. +----+-------------+------+------+
  27. | ID | sharding_id | name | age  |
  28. +----+-------------+------+------+
  29. |  1 |       10000 | bob  |   19 |
  30. |  2 |       10010 | tom  |   21 |
  31. |  3 |       10020 | lucy |   16 |
  32. +----+-------------+------+------+
  33. 3 rows in set (0.06 sec)
  34.  
  35. mysql>insert into employee(ID,sharding_id,name,age)
  36. values 
  37. (4,10000,"bob2",19), //存储在53服务器的db1库的employee表里
  38. (5,10000,"tom2",21), //存储在53服务器的db1库的employee表里
  39. (6,10000,"lucy2",16); //存储在53服务器的db1库的employee表里
  40. Query OK, 3 rows affected (0.07 sec)
  41. Records: 3  Duplicates: 0  Warnings: 0
  42.  
  43. mysql> select  * from employee;   //查看表记录                                        +----+-------------+-------+------+
  44. | ID | sharding_id | name  | age  |
  45. +----+-------------+-------+------+
  46. |  1 |       10000 | bob   |   19 |
  47. |  4 |       10000 | bob2  |   19 |
  48. |  5 |       10000 | tom2  |   21 |
  49. |  6 |       10000 | lucy2 |   16 |
  50. |  3 |       10020 | lucy  |   16 |
  51. |  2 |       10010 | tom   |   21 |
  52. +----+-------------+-------+------+
  53. 6 rows in set (0.00 sec)

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

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

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

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

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

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

  1. [root@host55 ~]#  mysql -uroot -p123qqq...A -e "select * from db3.employee"
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. +----+-------------+------+------+
  4. | ID | sharding_id | name | age  |
  5. +----+-------------+------+------+
  6. |  3 |       10020 | lucy |   16 |
  7. +----+-------------+------+------+
  8. [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分片规则使用的函数

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

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

  1. [root@mycat56 ~]# /usr/local/mycat/bin/mycat  stop    //停止服务
  2. Stopping Mycat-server...
  3. Stopped Mycat-server.
  4. [root@mycat56 conf]# netstat -utnlp  | grep  :8066  //无端口
  5. [root@mycat56 conf]# ps –C  java //无进程
  6. [root@mycat56 conf]#
  7.  
  8. [root@mycat56 conf]# /usr/local/mycat/bin/mycat  start  //启动服务
  9. Starting Mycat-server...
  10. [root@mycat56 conf]# 
  11. [root@mycat56 conf]# netstat -utnlp  | grep  :8066 //有端口
  12. tcp6       0      0 :::8066                 :::*           LISTEN      1364/java
  13. [root@mycat56 conf]#
  14. [root@mycat56 conf]# ps –C  java //有进程
  15. PID TTY          TIME CMD
  16.  1125 ?        00:00:01 java
  17. [root@mycat56 conf]#

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

  1. ]#mysql -h192.168.4.56 -P8066 -uroot -p123456 //访问服务
  2. mysql> use TESTDB; //进入TESTDB库
  3. mysql> create table hotnews(id int ,title char(30),comment char(200)); //建表
  4. Query OK, 0 rows affected (0.79 sec)
  5.  
  6. mysql> desc hotnews; //查看表结构
  7. +---------+-----------+------+-----+---------+-------+
  8. | Field   | Type      | Null | Key | Default | Extra |
  9. +---------+-----------+------+-----+---------+-------+
  10. | id     | int(11)   | YES  |     | NULL    |       |
  11. | title   | char(30)  | YES  |     | NULL    |       |
  12. | comment | char(200) | YES  |     | NULL    |       |
  13. +---------+-----------+------+-----+---------+-------+
  14. 3 rows in set (0.00 sec)
  15.  
  16. mysql> insert into hotnews(id,title,comment)values(9,"sc","xxxxx"); //插入第1条表记录，9和3取余 余0 记录存储在53服务器的db1库里
  17. Query OK, 1 row affected (0.11 sec)
  18.  
  19. mysql> insert into hotnews(id,title,comment)values(10,"xx","haha");//插入第2条表记录，10和3取余 余1 记录存储在54服务器的db2库里
  20. Query OK, 1 row affected (0.05 sec)
  21.  
  22. mysql> insert into hotnews(id,title,comment)values(11,"yy","zz");//插入第3条表记录，11和3取余 余2 记录存储在55服务器的db3库里
  23. Query OK, 1 row affected (0.03 sec)
  24.  
  25. mysql> select  * from hotnews; //查看表记录
  26. +------+-------+---------+
  27. |   id | title | comment |
  28. +------+-------+---------+
  29. |   11 | yy    | zz      |
  30. |   10 | xx    | haha    |
  31. |    9 | sc    | xxxxx   |
  32. +------+-------+---------+
  33. 3 rows in set (0.01 sec)

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

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

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

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

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

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

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

3 案例3：连接分片服务器存储数据

3.1 问题：

具体要求如下：

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：配置Mycat服务器

1）添加新库

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

<user name="root">

……

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

</user>

:wq

2）添加新表

  1. [root@mycat56 ~]# vim /usr/local/mycat/conf/schema.xml
  2. <mycat:schema xmlns:mycat="http://io.mycat/">              
  3.             <schema name="BBSDB" checkSQLschema="false" sqlMaxLimit="100">
  4.                     <table name="company2" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> //指定逻辑表名company2
  5.                     <table name="employee2" primaryKey="ID" dataNode="dn1,dn2,dn3"
  6.                            rule="sharding-by-intfile" /> //指定逻辑表名employee2
  7.           </schema>
  8.           <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
  9.                 ……
  10.                 ……
  11. </mycat:schema >
  12. ：wq

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本机查看。

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

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

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

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

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