部署MyCAT服务
至于今天的mycat部署傻到什么地步,只要你不是真的傻,只要你按着本文一模一样的敲,那么绝对可以成功,博主当天24小时在线,欢迎各位在职人员以及小白提出问题并一起解决!
本文理念: 先敲出成功的案例在慢慢理解,就像手机先是买下来会用了才有可能会制作手机,学习嘛,晦涩难懂的部分多多少少必须去攻克的,加油↖(^ω^)↗!!!!
以下是本次做实验所用到的虚拟机:
一、部署MyCat运行环境
#因为MyCat是有java写的所以需要安装java环境
[root@mysql56 ~]# yum -y install java-1.8.0-openjdk
[root@mysql56 ~]# which java #查看其路径(用于检验是否安装)
/usr/bin/java
[root@mysql56 ~]# java -version #查看java版本
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)
[root@mysql56 ~]# tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mysql56 ~]# mv mycat /usr/local/ #移动目录(非必须操作)
[root@mysql56 ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt
二、修改MyCat配置文件
1.设置连账号(server.xml)
[root@mysql56 ~]# 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
2.配置数据分片(schema.xml)
#使用sed删除不需要的配置行(可选操作)
[root@mysql56 ~]# wc -l /usr/local/mycat/conf/schema.xml #查看配置文件总行数
77 /usr/local/mycat/conf/schema.xml
[root@mysql56 ~]#sed -i '56,77d' schema.xml #删除无关的配置行
[root@mysql56 ~]#sed -i '39,42d' schema.xml
[root@mysql56 ~]#sed -i '16,18d' schema.xml
[root@mysql56 ~]#wc -l /usr/local/mycat/conf/schema.xml #删除后查看总行数
47 /usr/local/mycat/conf/schema.xml
[root@mysql56 ~]# 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库下的表做分片存储-->
<table name="travelrecord" dataNode="dn1,dn2,dn3"
rule="auto-sharding-long" /> <!--对travelrecord表做分片存储-->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <!--对company表做分片存储-->
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<table name="hotnews" primaryKey="ID" autoIncrement="true" 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>
</schema>
<!--定义数据库主机名及存储数据的库-->
<dataNode name="dn1" dataHost="mysql53" database="db1" /> <!--dataHost的名字保持和下面一致-->
<dataNode name="dn2" dataHost="mysql54" database="db2" />
<dataNode name="dn3" dataHost="mysql55" database="db3" />
<!--定义mysql53主机名对应的数据库服务器ip地址-->
<dataHost name="mysql53" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="mysql53:3306" user="root" <!--host随意写不重复即可-->
password="123456"> <!--url写对应主机ip,如果写主机名必须修改hosts文件,密码为数据库密码必须一致-->
</writeHost>
</dataHost>
<!--定义mysql54主机名对应的数据库服务器ip地址-->
<dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="mysql54:3306" user="root"
password="123456">
</writeHost>
</dataHost>
<!--定义mysql55主机名对应的数据库服务器ip地址-->
<dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="mysql55:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
:wq
3.配置数据库服务器
根据分片文件的设置在对应的数据库服务器上创建存储数据的数据库并。
#配置mysql53
[root@mysql53 ~]# mysql -p123456
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 3
Server version: 5.7.17 MySQL Community Server (GPL)
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> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#配置mysql54
[root@mysql54 ~]# mysql -p123456
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 3
Server version: 5.7.17 MySQL Community Server (GPL)
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> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#配置mysql55
[root@mysql55 ~]# mysql -p123456
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 4
Server version: 5.7.17 MySQL Community Server (GPL)
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> create database db3;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
根据分片文件配置,在对应的数据库服务器上创建授权用户(3台数据库服务器都要添加,在数据库服务器本机管理员root用户登录后执行授权命令)
#mysql53 mysql54 mysql55 分别配置以下内容
mysql> grant all on *.* to csdnak identified by "csdn666...B";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for csdnak; #查看是否写入配置成功
+---------------------------------------------+
| Grants for csdnak@% |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'csdnak'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)
4.启动MyCat服务
测试授权用户:在192.168.4.56主机,使用授权用户分别连接3台数据库服务器,若连接失败,请检查数据库服务器是否有对应的授权用户。
[root@mysql56 ~]# yum -y install mariadb-server #安装mariadb服务提供mysql命令的软件包
[root@mysql56 ~]# systemctl start mariadb #启动数据库服务
[root@mysql56 ~]# mysql -h192.168.4.53 -ucsdnak -p"csdn666...B"
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> exit
Bye
[root@mysql56 ~]# mysql -h192.168.4.54 -ucsdnak -p"csdn666...B"
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> exit
Bye
[root@mysql56 ~]# mysql -h192.168.4.55 -ucsdnak -p"csdn666...B"
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> exit
Bye
[root@mysql56 ~]# /usr/local/mycat/bin/mycat start #开启mycat服务
Starting Mycat-server...
[root@mysql56 ~]# netstat -ntlupa |grep :8066 #查看开启是否成功
tcp6 0 0 :::8066 :::* LISTEN 2447/java
三、测试配置
1.客户端访问
在客户端192.168.4.50 连接分片服务器,访问数据
命令: mysql -hmycat主机的IP -P端口号 -u用户 -p密码
[root@mysql50 ~]# mysql -hmysql56 -P8066 -uroot -p123456
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 |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 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> 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
Bye
2.连接分片服务器存储数据
由于阿坤时间有限所以在此只为大家演示其中1.枚举法、4.求模法两种分片规则,剩下的交给大家自己练习用,俗话说师傅领进门修行靠69嘛!
1.sharding-by-intfile
1)查看配置文件,得知使用sharding-by-intfile分片规则的表名(功能如同建表时str参数enum枚举类型)
[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 ~]# netstat -utnlp | grep :8066 //无端口
[root@mycat56 ~]# ps –C java //无进程
[root@mycat56 ~]#
[root@mycat56 ~]# /usr/local/mycat/bin/mycat start //启动服务
Starting Mycat-server...
[root@mycat56 ~]#
[root@mycat56 ~]# netstat -utnlp | grep :8066 //有端口
tcp6 0 0 :::8066 :::* LISTEN 1364/java
[root@mycat56 ~]#
[root@mycat56 ~]# ps –C java //有进程
PID TTY TIME CMD
1125 ? 00:00:01 java
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@mysql53 ~]# mysql -uroot -p123456 -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@mysql53 ~]#
在数据库服务器192.168.4.54 查看数据
[root@mysql54 ~]# mysql -uroot -p123456 -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@mysql54 ~]#
在数据库服务器192.168.4.55 查看数据
[root@mysql55 ~]# mysql -uroot -p123456 -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@mysql55 ~]#
2.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>
: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]#
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 |
+------+-------+---------+
3 rows in set (0.01 sec)
5)在数据库服务器本机,查看表记录
在数据库服务器192.168.4.53 查看数据
[root@mysql53 ~]# 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@mysql53 ~]#
在数据库服务器192.168.4.54 查看数据
[root@mysql54 ~]# mysql -uroot -p123qqq...A -e "select * from db2.hotnews"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
| id | title | comment |
+------+-------+---------+
| 10 | xx | haha |
+------+-------+---------+
[root@mysql54 ~]#
在数据库服务器192.168.4.55 查看数据
[root@mysql55 ~]# mysql -uroot -p123qqq...A -e "select * from db3.hotnews"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
| id | title | comment |
+------+-------+---------+
| 11 | yy | zz |
+------+-------+---------+
[root@mysql55 ~]#
四、加库加表配置
1.逻辑库名BBSDB
2.逻辑表名company2数据不分片,把数据存储到3台数据库服务器上
3.加粗样式逻辑表名employee2 使用枚举法分片规则把数据存储到3台数据库服务器上
步骤一:修改配置
1)添加新库
[root@mycat56 ~]# vim /usr/local/mycat/conf/server.xml
<user name="root">
……
<property name="schemas">TESTDB,BBSDB</property>//指定逻辑库名
</user>
:wq
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 >
: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@mysql50 ~]# 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@mysql53 ~]# 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@mysql53 ~]#
3)在数据库服务器54本机查看
[root@mysql54 ~]# mysql -uroot -p123qqq...A -e "select * from db2.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@mysql54 ~]#
4)在数据库服务器55本机查看
[root@mysql55 ~]# 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 |
+----+--------+---------+
[root@mysql55 ~]#