Mycat 路由转发解析学习

来源：互联网发布：dede整站源码编辑：程序博客网时间：2024/05/21 13:58

1：测试环境
192.168.1.21 mycat1
192.168.1.22 mysql1
192.168.1.23 mysql2

2：在mysql1、mysql2上安装mysql
use mysql
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "oracle";
update user set Password = password('oracle') where User='root';
GRANT replication slave ON *.* TO 'repluser'@'%' identified by 'oracle';
flush privileges;
exit;

3：配置mysql复制
在mysql1：
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 401 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+

在mysql2：向主库做同步操作，开启复制
change master to master_host='192.168.10.22',
master_port=3306, master_user='repluser',
master_password='oracle', master_log_file='mysql-bin.000003',master_log_pos=401;
start slave;

show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 2095
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在mysql1创建测试库、用户：
create database testdb1;

create database testdb2;

create database testdb3;
GRANT ALL PRIVILEGES ON testdb1.* TO 'zhang'@"%" IDENTIFIED BY "oracle";
flush privileges;
exit

4：安装好mycat后，编辑schema.xml

cd /usr/local/mycat/conf
vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>


<dataNode name="dn1" dataHost="mysql1" database="testdb1" />
<dataNode name="dn2" dataHost="mysql1" database="testdb2" />
<dataNode name="dn3" dataHost="mysql1" database="testdb3" />


<dataHost name="mysql1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="mysql1" url="192.168.10.22:3306" user="zhang" password="oracle">
</writeHost>
</dataHost>

<dataHost name="mysql1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="mysql2" url="192.168.10.23:3306" user="zhang" password="oracle">
</writeHost>
</dataHost>
</mycat:schema>

开启日志debug模式：
vi log4j.xml
<root>
<level value="debug" />
<appender-ref ref="FILE" />

</root>

vi server.xml
<user name="root">
<property name="password">oracle</property>
<property name="schemas">TESTDB</property>
</user>

安装配置完成，登录测试：
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P8066 -DTESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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 table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE,fee decimal,days int);
Query OK, 0 rows affected (0.43 sec)

录入数据在分片1上：
mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'xiaohong','2014-01-06',510.5,3);
+-----------+----------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------------------+
| dn1 | insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'xiaohong','2014-01-06',510.5,3) |
+-----------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'xiaohong','2014-01-06',510.5,3);
Query OK, 1 row affected, 1 warning (0.08 sec)

录入数据在分片2上：
mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'mazi','2014-01-06',510.5,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn2 | insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'mazi','2014-01-06',510.5,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'mazi','2014-01-06',510.5,3);
Query OK, 1 row affected, 1 warning (0.03 sec)

录入数据在分片3上：
mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'suqi','2014-01-06',510.5,3);
+-----------+--------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------+
| dn3 | insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'suqi','2014-01-06',510.5,3) |
+-----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'suqi','2014-01-06',510.5,3);
Query OK, 1 row affected, 1 warning (0.06 sec)

查询数据，在mycat后台的db上面，路由是在3个分片上：
mysql> explain select * from Travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------+
| dn1 | SELECT * FROM Travelrecord LIMIT 100 |
| dn2 | SELECT * FROM Travelrecord LIMIT 100 |
| dn3 | SELECT * FROM Travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.07 sec)

单个数据路由查询，都在不同的分片datanode上：

mysql> explain select * from Travelrecord where id=1000001;

+-----------+---------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------+
| dn1 | SELECT * FROM Travelrecord WHERE id = 1000001 LIMIT 100 |
+-----------+---------------------------------------------------------+

1 row in set (0.06 sec)

mysql> explain select * from Travelrecord where id=8000004;
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------+
| dn2 | SELECT * FROM Travelrecord WHERE id = 8000004 LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.02 sec)

mysql> explain select * from Travelrecord where id=10000004;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------+
| dn3 | SELECT * FROM Travelrecord WHERE id = 10000004 LIMIT 100 |
+-----------+----------------------------------------------------------+
1 row in set (0.01 sec)

去真实的mysql上测试，查询数据，已经分到3个分片testdb1、testdb2、testdb3上面了：
[root@mysql1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> select * from testdb1.Travelrecord;
+--------+----------+------------+------+------+
| id | user_id | traveldate | fee | days |
+--------+----------+------------+------+------+
| 100001 | xiaohong | 2014-01-06 | 511 | 3 |
+--------+----------+------------+------+------+
1 row in set (0.03 sec)

mysql> select * from testdb2.Travelrecord;
+---------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+---------+---------+------------+------+------+
| 8000004 | mazi | 2014-01-06 | 511 | 3 |
+---------+---------+------------+------+------+
1 row in set (0.00 sec)

mysql> select * from testdb3.Travelrecord;
+----------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+----------+---------+------------+------+------+
| 10000004 | suqi | 2014-01-06 | 511 | 3 |
+----------+---------+------------+------+------+
1 row in set (0.00 sec)

参考文档：

http://blog.csdn.net/mchdba/article/details/50616527

0 0