1、创建mycat用户
useradd -m mycat
#为mycat 设置密码
passwd mycat
2、创建mycat工作目录
mkdir -p /usr/local/mycat
3、下载mycat
http://dl.mycat.org.cn/1.6-RELEASE/
su mycat
cd /usr/local/mycat
wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#解压
tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#进入目录
cd mycat
ll
4、准备两个mysql数据源
① window上有个 192.168.100.123:3306
②虚拟机上有个 localhost:3307
5、在两个mysql中新建数据库:test_mycat
create database test_mycat
6、在test_mycat中新建数据库表 tb_user
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
7、配置 mycat/conf/schema.xml
root@ubuntu:/mysoft/mycat/mycat/conf# vim 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">
<!-- random sharding using mod sharind rule -->
<table name="tb_user" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="test_mycat" />
<dataNode name="dn2" dataHost="localhost2" database="test_mycat" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3307" user="your_user_name" password="your_password" >
<readHost host="hostS1" url="localhost:3307" user="your_user_name" password="your_password" />
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.100.81:3306" user="your_user_name" password="your_password" />
</dataHost>
</mycat:schema>
8、由于schema中rule="mod-long",所以要去设置一下 rule.xml,只需动一个地方,其他不变
9、配置server.xml
10、启动mycat
cd mycat/bin
./mycat console #以控制台的方式启动
注:mycat 支持的命令{ console | start | stop | restart | status | dump }
显示两个节点都链接成功
11、使用Navicat来测试
12、测试数据
13、测试成功。
14、集成spring boot
/*
* USER 实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private Integer age;
}
@Mapper
public interface UserDao {
public User getUser(int id);
public List<User> userList();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ming.springclould.dao.UserDao">
<resultMap id="BaseResultMap" type="com.ming.springcloud.entities.User" >
<result column="id" property="id" />
<result column="name" property="name" />
<result column="age" property="age" />
</resultMap>
<sql id="Base_Column_List">
id,
name,
age
</sql>
<select id="getUser" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM tb_user
WHERE id = #{id}
</select>
<select id="userList" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM tb_user
</select>
</mapper>
@Service
public class UserService {
@Resource
private UserDao userDao;
public User getUser(int id){
return userDao.getUser(id);
};
public List<User> userList(){
return userDao.userList();
}
}
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/user/get/{id}")
public CommonResult getUser(@PathVariable int id){
return new CommonResult(200,"",userService.getUser(id));
}
@GetMapping("/user/list")
public CommonResult<List<User>> userList(){
return new CommonResult(200,"",userService.userList());
}
}
server:
port: 2009
spring:
application:
name: MyCat-2009
datasource:
type: com.alibaba.druid.pool.DruidDataSource #当前数据源操作类型
#driver-class-name: com.mysql.cj.jdbc.Driver #mysql-connector-java 6以上
driver-class-name: com.mysql.jdbc.Driver #mysql-connector-java 6以下
url: jdbc:mysql://192.168.100.123:8066/TESTDB?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username: root
password: 123456
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.ming.springcloud.entities #所有entity别名所在包
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
15、启动测试