镜缘浮影 小人本住在 苏州的城外 家里有屋又有田 生活乐无边

Mycat 运行与基础操作

2016-02-27
wilmosfang
原文地址 http://soft.dog/2016/02/27/mycat-ops/

前言

Mycat 是一个数据库分库分表中间件

这里使用最简单的取模分片作为示例,简单分享一下 Mycat 的运行与基础操作

详细内容可以参考 官方文档Mycat-ServerGet Start

Tip: 当前的最新版本为 Mycat server 1.5 GA


概要


配置

以下是关键配置

--[server.xml]--------
	<user name="cc">
		<property name="password">cc</property>
		<property name="schemas">cctest</property>
	</user>
--[schema.xml]--------
	<schema name="cctest" checkSQLschema="false" sqlMaxLimit="100">
	        <table name="catworld"  dataNode="sd1,sd2,sd3"  rule="mod-long" />
	        <table name="catworld4"  dataNode="sd1,sd2,sd3,sd4"  rule="mod4-long" />
	</schema>
	...
	...
	<dataNode name="sd1" dataHost="h101" database="my1" />
	<dataNode name="sd2" dataHost="h101" database="my2" />
	<dataNode name="sd3" dataHost="h101" database="my3" />
	<dataNode name="sd4" dataHost="h202" database="my4" />
	...
	...
	<dataHost name="h101" maxCon="100" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="h101M1" url="192.168.100.101:3306" user="root" password="mysql">
		<!-- can have multi read hosts -->
		</writeHost>
	</dataHost>
	<dataHost name="h202" maxCon="100" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="h202M1" url="192.168.100.202:3306" user="root" password="mysql">
		<!-- can have multi read hosts -->
		</writeHost>
	</dataHost>
--[rule.xml]--------
	<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
	<tableRule name="mod4-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod4-long</algorithm>
		</rule>
	</tableRule>
	...
	...
	<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">3</property>
	</function>
	<function name="mod4-long" class="org.opencloudb.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">4</property>
	</function>

准备

巧妇难为无米之炊,Mycat 本身并没有存储引擎,所以得事先准备好,主要是以下几点

  • 在192.168.100.101提前创建三个数据库
mysql> \! ip a | grep eth2
3: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.100.101/24 brd 192.168.100.255 scope global eth2
mysql> show databases like "my_";
+----------------+
| Database (my_) |
+----------------+
| my1            |
| my2            |
| my3            |
+----------------+
3 rows in set (0.00 sec)

mysql> 
  • 在192.168.100.202提前创建一个数据库
mysql> \! ip a | grep eth2
3: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.100.202/24 brd 192.168.100.255 scope global eth2
mysql> show databases like "my_";
+----------------+
| Database (my_) |
+----------------+
| my4            |
+----------------+
1 row in set (0.01 sec)

mysql> 
  • 在192.168.100.101提前创建一个拥有足够权限的用户
mysql> \! ip a | grep eth2
3: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.100.101/24 brd 192.168.100.255 scope global eth2
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
  • 在192.168.100.202提前创建一个拥有足够权限的用户
mysql> \! ip a | grep eth2
3: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.100.202/24 brd 192.168.100.255 scope global eth2
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
  • 防火墙上开放192.168.100.101 和 192.168.100.202 的3306端口
[root@h101 ~]# iptables -L -nv | grep 3306
   13   780 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:3306 
[root@h101 ~]# 

日志

Mycat的日志存储在 logs

[root@h102 logs]# ll mycat.log
-rw-r--r-- 1 root root 50693 Feb 26 18:22 mycat.log
[root@h102 logs]# ll wrapper.log 
-rw-r--r-- 1 root root 38339 Feb 26 10:12 wrapper.log
[root@h102 logs]# 
  • wrapper.log:启动脚本的相关日志记录在这里面
  • mycat.log:java进程的相关日志记录在这里面

我修改一下 schema.xmldataNodedataHost 的位置,让 dataHost 放在前面,然后尝试启动 mycat 服务

[root@h102 bin]# ./mycat  start  
Starting Mycat-server...
[root@h102 bin]# ps faux | grep MYCAT
root     33761  0.0  0.0 103256   828 pts/0    S+   22:11   0:00  |       \_ grep MYCAT
[root@h102 bin]# 

其实mycat并未成功启动,wrapper.log 中会产生如下报错

INFO   | jvm 1    | 2016/02/26 22:07:37 | Caused by: org.opencloudb.config.util.ConfigException: org.xml.sax.SAXParseException; lineNumber: 106; columnNumber: 16; The content of element type "mycat:schema" must match "(schema*,dataNode*,dataHost*)".
...
...
INFO   | jvm 1    | 2016/02/26 22:07:37 | Caused by: org.xml.sax.SAXParseException; lineNumber: 106; columnNumber: 16; The content of element type "mycat:schema" must match "(schema*,dataNode*,dataHost*)".
...
...
STATUS | wrapper  | 2016/02/26 22:07:40 | <-- Wrapper Stopped

修改回来,就好了

Tip: 所以在Mycat中配置的位置也很重要,我们可以通过这两个日志文件查看出错信息


启动服务

[root@h102 bin]# ./mycat  start
Starting Mycat-server...
[root@h102 bin]# ps faux | grep MYCAT
root     33875  0.0  0.0 103256   824 pts/0    S+   22:19   0:00  |       \_ grep MYCAT
root     33836  6.6  4.3 1840888 84056 ?       Sl   22:18   0:05  \_ java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=256m -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx512m -Xms128m -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/Mycat-server-1.5-GA.jar:lib/curator-framework-2.9.0.jar:lib/slf4j-log4j12-1.7.12.jar:lib/libwrapper-linux-ppc-64.so:lib/sequoiadb-java-driver-1.0-20150615.070208-1.jar:lib/guava-18.0.jar:lib/wrapper.jar:lib/mongo-java-driver-2.11.4.jar:lib/jline-0.9.94.jar:lib/libwrapper-linux-x86-32.so:lib/xml-apis-1.0.b2.jar:lib/log4j-1.2.17.jar:lib/ehcache-core-2.6.11.jar:lib/snakeyaml-1.16.jar:lib/libwrapper-linux-x86-64.so:lib/slf4j-api-1.7.12.jar:lib/leveldb-0.7.jar:lib/curator-client-2.9.0.jar:lib/netty-3.7.0.Final.jar:lib/druid-1.0.14.jar:lib/json-20151123.jar:lib/dom4j-1.6.1.jar:lib/zookeeper-3.4.6.jar:lib/mapdb-1.0.7.jar:lib/univocity-parsers-1.5.4.jar:lib/leveldb-api-0.7.jar:lib/fastjson-1.2.7.jar -Dwrapper.key=bBUxeRol6R_P7Pab -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=33834 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp org.opencloudb.MycatStartup start
[root@h102 bin]# 

正常启动,wrapper.log 中会产生如下日志

STATUS | wrapper  | 2016/02/26 22:18:01 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2016/02/26 22:18:01 | Launching a JVM...
INFO   | jvm 1    | 2016/02/26 22:18:02 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2016/02/26 22:18:02 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2016/02/26 22:18:02 | 
INFO   | jvm 1    | 2016/02/26 22:18:03 | log4j 2016-02-26 22:18:03 [./conf/log4j.xml] load completed.
INFO   | jvm 1    | 2016/02/26 22:18:07 | MyCAT Server startup successfully. see logs in logs/mycat.log
...
...
...

连接服务

连接方法和mysql一样,里面呈现出的CLI界面也与mysql非常相似

[root@h101 ~]# mysql -u cc -p -P 8066 -h 192.168.100.102
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5-GA-20160217103036 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, 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 |
+----------+
| cctest   |
+----------+
1 row in set (0.01 sec)

mysql> use cctest;
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 cctest |
+------------------+
| catworld         |
| catworld4        |
+------------------+
2 rows in set (0.00 sec)

mysql> 

创建表

我虽然在mycat里定义了,但在真实库中并没有创建过这两张表

mysql> show tables;
+------------------+
| Tables in cctest |
+------------------+
| catworld         |
| catworld4        |
+------------------+
2 rows in set (0.00 sec)

mysql> desc catworld4;
ERROR 1146 (42S02): Table 'my3.catworld4' doesn't exist
mysql> 

Tip: 此时在后端真实库里,是找不到这两张表的

这张表是要我们手动创建的,可以有两种方式

  • 在每个数据节点里手动创建出一样的数据表
  • 直接在mycat里创建,mycat会再分别去各数据节点创建

这里,我使用第二种方式,直接在mycat里创建

mysql> create table catworld4(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.69 sec)

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> explain desc catworld4;
+-----------+----------------+
| DATA_NODE | SQL            |
+-----------+----------------+
| sd1       | desc catworld4 |
+-----------+----------------+
1 row in set (0.04 sec)

mysql> 

Tip: 此时如果去每个数据节点上查看,会发现,本地已经创建了 catworld4

mysql> show tables;
+---------------+
| Tables_in_my4 |
+---------------+
| catworld4     |
+---------------+
1 row in set (0.00 sec)

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table catworld4\G
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| my4        |
+------------+
1 row in set (0.00 sec)

mysql> 

插入数据

这里尝试插入一条数据到表里

mysql> insert into catworld4 values(1,"abc");
ERROR 1064 (HY000): partition table, insert must provide ColumnList
mysql> 

提示这是分区表,必须指定列名

mysql> insert into catworld4(name,id) values("abc",1);
Query OK, 1 row affected (0.09 sec)

mysql> select count(*) from catworld4;
+--------+
| COUNT0 |
+--------+
|      1 |
+--------+
1 row in set (0.15 sec)

mysql> select * from catworld4;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
+----+------+
1 row in set (0.04 sec)

mysql> insert into catworld4(name,id) values("abc",2);
Query OK, 1 row affected (0.04 sec)

mysql> insert into catworld4(name,id) values("abc",3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into catworld4(name,id) values("abc",4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into catworld4(name,id) values("abc",5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into catworld4(name,id) values("abc",6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into catworld4(name,id) values("abc",7);
Query OK, 1 row affected (0.01 sec)

mysql> insert into catworld4(name,id) values("abc",8);
Query OK, 1 row affected (0.04 sec)

mysql> insert into catworld4(name,id) values("abc",9);
Query OK, 1 row affected (0.01 sec)

mysql> insert into catworld4(name,id) values("abc",10);
Query OK, 1 row affected (0.03 sec)

mysql> select count(*) from catworld4;
+--------+
| COUNT0 |
+--------+
|     10 |
+--------+
1 row in set (0.05 sec)

mysql> select * from catworld4;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  5 | abc  |
|  9 | abc  |
|  4 | abc  |
|  8 | abc  |
|  2 | abc  |
|  6 | abc  |
| 10 | abc  |
|  3 | abc  |
|  7 | abc  |
+----+------+
10 rows in set (0.03 sec)

mysql> 

删除数据

mysql> select *  from catworld4 where id=9;
+----+------+
| id | name |
+----+------+
|  9 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> delete from catworld4 where id=9;
Query OK, 1 row affected (0.05 sec)

mysql> select *  from catworld4 where id=9;
Empty set (0.00 sec)

mysql> 

查询数据

mysql> select *  from catworld4 ;
+----+------+
| id | name |
+----+------+
|  4 | abc  |
|  8 | abc  |
|  1 | abc  |
|  3 | abc  |
|  7 | abc  |
|  5 | abc  |
|  2 | abc  |
|  6 | abc  |
| 10 | abc  |
+----+------+
9 rows in set (0.01 sec)

mysql> select count(*) from catworld4;
+--------+
| COUNT0 |
+--------+
|      9 |
+--------+
1 row in set (0.01 sec)

mysql> select *  from catworld4 order by id;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | abc  |
|  3 | abc  |
|  4 | abc  |
|  5 | abc  |
|  6 | abc  |
|  7 | abc  |
|  8 | abc  |
| 10 | abc  |
+----+------+
9 rows in set (0.08 sec)

mysql> select distinct(name) from catworld4;
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.09 sec)

mysql> insert into catworld4(name,id) values("def",9);
Query OK, 1 row affected (0.02 sec)

mysql> select distinct(name) from catworld4;
+------+
| name |
+------+
| abc  |
| def  |
+------+
2 rows in set (0.01 sec)

mysql> 

包括聚合函数,操作体验和mysql单机操作几乎没有什么差异,但实际是分片处理的

mysql> explain select *  from catworld4;
+-----------+-----------------------------------+
| DATA_NODE | SQL                               |
+-----------+-----------------------------------+
| sd1       | SELECT * FROM catworld4 LIMIT 100 |
| sd2       | SELECT * FROM catworld4 LIMIT 100 |
| sd3       | SELECT * FROM catworld4 LIMIT 100 |
| sd4       | SELECT * FROM catworld4 LIMIT 100 |
+-----------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> explain select count(*) from catworld4;
+-----------+----------------------------------------------------+
| DATA_NODE | SQL                                                |
+-----------+----------------------------------------------------+
| sd1       | SELECT COUNT(*) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd2       | SELECT COUNT(*) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd3       | SELECT COUNT(*) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd4       | SELECT COUNT(*) AS COUNT0 FROM catworld4 LIMIT 100 |
+-----------+----------------------------------------------------+
4 rows in set (0.00 sec)

mysql> explain select distinct(name) from catworld4;
+-----------+----------------------------------------------------+
| DATA_NODE | SQL                                                |
+-----------+----------------------------------------------------+
| sd1       | SELECT name FROM catworld4 GROUP BY name LIMIT 100 |
| sd2       | SELECT name FROM catworld4 GROUP BY name LIMIT 100 |
| sd3       | SELECT name FROM catworld4 GROUP BY name LIMIT 100 |
| sd4       | SELECT name FROM catworld4 GROUP BY name LIMIT 100 |
+-----------+----------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

Tip: 后面的 LIMIT 100 就是由定义 schemasqlMaxLimit 参数配置的

我们可以去其中一个分片节点里查看本地数据

mysql> select database();
+------------+
| database() |
+------------+
| my4        |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_my4 |
+---------------+
| catworld4     |
+---------------+
1 row in set (0.00 sec)

mysql> select * from catworld4;
+----+------+
| id | name |
+----+------+
|  3 | abc  |
|  7 | abc  |
+----+------+
2 rows in set (0.00 sec)

mysql>

修改数据

mysql> select *  from catworld4 where id=9;
+----+------+
| id | name |
+----+------+
|  9 | def  |
+----+------+
1 row in set (0.01 sec)

mysql> update catworld4 set name="xxx" where id=9;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *  from catworld4 where id=9;
+----+------+
| id | name |
+----+------+
|  9 | xxx  |
+----+------+
1 row in set (0.01 sec)

mysql> 

修改表结构

添加列

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table catworld4 add test int;
Query OK, 0 rows affected (4.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| test  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> 

修改列类型

mysql> alter table catworld4 modify test varchar(10);
Query OK, 10 rows affected (0.91 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| test  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql>

添加索引

mysql> create index idx_test on catworld4(test);
Query OK, 0 rows affected (6.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table catworld4\G
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `test` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test` (`test`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> 

删除索引,删除列

mysql> show create table catworld4\G
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `test` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test` (`test`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop index idx_test on catworld4;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table catworld4\G
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `test` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table catworld4 drop column test;
Query OK, 0 rows affected (4.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table catworld4\G
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

Note: 来一个危险的操作 rename table

mysql> alter table catworld4  rename to abc;
Query OK, 0 rows affected (0.14 sec)

mysql> desc catworld4;
ERROR 1146 (42S02): Table 'my4.catworld4' doesn't exist
mysql> show tables;
+------------------+
| Tables in cctest |
+------------------+
| catworld         |
| catworld4        |
+------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> alter table abc rename to catworld4;
ERROR 1064 (HY000): op table not in schema----ABC
mysql> 

其中一个dataNode上名字已经变了

mysql> select database();
+------------+
| database() |
+------------+
| my4        |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_my4 |
+---------------+
| abc           |
+---------------+
1 row in set (0.00 sec)

mysql>

发现在Mycat中再也rename不回来了,只能去数据节点本地,一个个手动rename回来,如果表的分片特别多,想想就蛋疼,当然也可以修改mycat配置,让rename后的表名也成为逻辑表,但是生产环境下N多应用正连接着的情况下,不是能够那么随便就启停mycat的,要协调停机窗口,可见在这里rename是一个极其危险的操作,我认为Mycat在执行此类操作时,至少应该给个提醒,而不是简单的路由请求


命令汇总

  • iptables -L -nv | grep 3306
  • ll mycat.log
  • ll wrapper.log
  • ./mycat start
  • ps faux | grep MYCAT
  • mysql -u cc -p -P 8066 -h 192.168.100.102

原文地址 http://soft.dog/2016/02/27/mycat-ops/

评论