• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

Mysql中间件Mycat的部署及应用

MySQL zkinogg 2年前 (2020-08-06) 293次浏览 0个评论

Mycat部署及应用

一.节点主从规划

# 节点主从规划(箭头指向谁,谁就是主库)
172.16.1.51:3306 <------> 172.16.1.53:3306

172.16.1.52:3306 --------> 172.16.1.51:3306

172.16.1.54:3306 ---------> 172.16.1.53:3306

---------------------------------------------

172.16.1.55:3306 <---------> 172.16.1.57:3306

172.16.1.56:3306 ----------> 172.16.1.55:3306

172.16.1.58:3306 ----------> 172.16.1.57:3306   

二.分片规划

shard1:
        Master: 172.16.1.51:3306
        slave1: 172.16.1.52:3306
        Standby Master: 172.16.1.53:3306
        slave2: 172.16.1.54:3306
shard2:
        Master:172.16.1.55:3306
        slave1: 172.16.1.56:3306
        Standby Master: 172.16.1.57:3306
        slave2: 172.16.1.58:3306 

三.环境准备

主机名 内网ip 外网ip 作用
db01 172.16.1.51 10.0.0.51 分片一的主库1
db02 172.16.1.52 10.0.0.52 分片一的从库1
db03 172.16.1.53 10.0.0.53 分片一的主库2
db04 172.16.1.54 10.0.0.54 分片一的从库2
db05 172.16.1.55 10.0.0.55 分片二的主库1
db06 172.16.1.56 10.0.0.56 分片二的从库1
db07 172.16.1.57 10.0.0.57 分片二的主库2
db08 172.16.1.58 10.0.0.58 分片二的从库2
m01 172.16.1.61 10.0.0.61 ansible管理端
##先用ansible安装mysql到8台机器,基于GTID的主从复制
# 0.发送秘钥
[root@m01 ~]# ssh-keygen
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.51
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.52
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.53
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.54
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.55
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.56
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.57
[root@m01 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.16.1.58
# 1.准备文件
[root@m01 files]# ll
total 393752
-rw-r--r-- 1 root root 403177622 Aug  6 03:13 mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root       325 Aug  6 03:14 mysqld.service
-rwxr-xr-x 1 root root     10565 Aug  6 03:13 mysql.server
-rw-r--r-- 1 root root        39 Aug  6 03:13 mysql.sh


[root@m01 mycat]# cat templates/my-default.cnf.j2 
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
gtid-mode=on
enforce-gtid-consistency=true
binlog_format=row
{% if ansible_fqdn == 'db01' %}
server_id = 1
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
log_bin=/usr/local/mysql/data/mysql-bin
{% elif ansible_fqdn == 'db02' %}
server_id = 2
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% elif ansible_fqdn == 'db03' %}
server_id = 3
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% elif ansible_fqdn == 'db04' %}
server_id = 4
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% elif ansible_fqdn == 'db05' %}
server_id = 5
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% elif ansible_fqdn == 'db06' %}
server_id = 6
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% elif ansible_fqdn == 'db07' %}
server_id = 7
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% else %}
server_id = 8
log_bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
relay_log_purge=0
read_only=1
skip_name_resolve
{% endif %}
[client]
socket = /usr/local/mysql/mysql.sock


# 2.编写任务
[root@m01 tasks]# cat Yum_mysql.yml 
- name: yum rely on
  yum:
    name: "{{ item }}"
    state: present
  loop:
    - ncurses-devel
    - libaio-devel
    - autoconf
  when: ansible_fqdn is match 'db*'

- name: useradd mysql
  user:
    name: mysql
    shell: /sbin/nologin
    create_home: false
    state: present
  when: ansible_fqdn is match 'db*'

- name: Unarchive mysql tgz
  unarchive:
    src: mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
    dest: /usr/local/
    owner: mysql
    group: mysql
  tags: unarchive
  when: ansible_fqdn is match 'db*'

- name: Link mysql
  file:
    src: /usr/local/mysql-5.6.46-linux-glibc2.12-x86_64
    dest: /usr/local/mysql
    owner: mysql
    group: mysql
    state: link
  when: ansible_fqdn is match 'db*'

- name: mysql config overwrite
  template:
    src: my-default.cnf.j2
    dest: /etc/my.cnf
  when: ansible_fqdn is match 'db*'

- name: Push sh mysql server
  copy: 
    src: mysql.server
    dest: /etc/init.d/mysqld
  when: ansible_fqdn is match 'db*'

- name: initialization mysql
  shell: 'cd /usr/local/mysql/scripts/ && ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --socket=/usr/local/mysql/mysql.sock'
  when: ansible_fqdn is match 'db*'

- name: Push mysql sh
  copy: 
    src: mysql.sh
    dest: /etc/profile.d/mysql.sh
  when: ansible_fqdn is match 'db*'

- name: source
  shell: 'source /etc/profile'
  when: ansible_fqdn is match 'db*'

- name: Push mysqld service
  copy:
    src: mysqld.service
    dest: /usr/lib/systemd/system/mysqld.service
  when: ansible_fqdn is match 'db*'

- name: daemon reload
  shell: 'systemctl daemon-reload'
  when: ansible_fqdn is match 'db*'

- name: start mysqld
  service:
    name: mysqld
    state: started
    enabled: true
  when: ansible_fqdn is match 'db*'

- name: Yum Phthon
  yum:
    name: MySQL-python
    state: present
  when: ansible_fqdn is match 'db*'

- name: Update Root User Password
  mysql_user:
    name: root
    password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    update_password: always
    host: 'localhost'
    priv: '*.*:ALL,GRANT'
    state: present
  when: ansible_fqdn is match 'db*'
  


# 3.指定hosts文件
[root@m01 ansible_mycat]# cat hosts 
[db_group]
db01 ansible_ssh_host=172.16.1.51
db02 ansible_ssh_host=172.16.1.52 
db03 ansible_ssh_host=172.16.1.53
db04 ansible_ssh_host=172.16.1.54
db05 ansible_ssh_host=172.16.1.55
db06 ansible_ssh_host=172.16.1.56 
db07 ansible_ssh_host=172.16.1.57
db08 ansible_ssh_host=172.16.1.58
[root@m01 ansible_mycat]# cat site.yml 
- hosts: all
  roles:
    - { role: mycat , when: ansible_fqdn is match 'db*'}
[root@m01 mycat]# cat tasks/main.yml 
---
# tasks file for mycat
- include: Yum_mysql.yml

四.开始部署

分片一四节点部署

172.16.1.51:3306 <——> 172.16.1.53:3306

## db03执行
grant replication slave on *.* to repl@'172.16.1.%' identified by '123';
grant all  on *.* to root@'172.16.1.%' identified by '123'  with grant option;

## db01执行
CHANGE MASTER TO MASTER_HOST='172.16.1.53', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G


## db03执行
CHANGE MASTER TO MASTER_HOST='172.16.1.51', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

172.16.1.52:3306 ——–> 172.16.1.51:3306

## db02执行
CHANGE MASTER TO MASTER_HOST='172.16.1.51', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

172.16.1.54:3306 ———> 172.16.1.53:3306

##  db04执行
CHANGE MASTER TO MASTER_HOST='172.16.1.53', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

分片二 四节点部署

172.16.1.55:3306 <———> 172.16.1.57:3306

## db05执行
grant replication slave on *.* to repl@'172.16.1.%' identified by '123';
grant all  on *.* to root@'172.16.1.%' identified by '123'  with grant option;


##  db07执行
CHANGE MASTER TO MASTER_HOST='172.16.1.55', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

## db05执行
CHANGE MASTER TO MASTER_HOST='172.16.1.57', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

172.16.1.56:3306 ———-> 172.16.1.55:3306

##  db06执行
CHANGE MASTER TO MASTER_HOST='172.16.1.55', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

172.16.1.58:3306 ———-> 172.16.1.57:3306

##  db08执行
CHANGE MASTER TO MASTER_HOST='172.16.1.57', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
show slave status\G

五.检测主从状态

##db01-db08均执行以下命令
mysql -uroot -p123 -e "show slave status\G"|grep Yes
            Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

六.安装连接Mycat

2. MyCAT安装
2.1 预先安装Java运行环境
yum install -y java
2.2下载
Mycat-server-xxxxx.linux.tar.gz
http://dl.mycat.io/
2.3 解压文件
[root@db01 application]# tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz 
2.4 软件目录结构
ls
bin  catlet  conf  lib  logs  version.txt
2.5 启动和连接
配置环境变量
vim /etc/profile.d/mycat.sh
export PATH=/application/mycat/bin:$PATH
source /etc/profile
启动
mycat start
连接mycat:
mysql -uroot -p123456 -h 127.0.0.1 -P8066

七.Mycat基础应用

3. 数据库分布式架构方式
3.1 垂直拆分
3.2 水平拆分
    range
    取模
    枚举
    hash
    时间
    等等

4. Mycat基础应用
4.1 主要配置文件介绍
rule.xml    *****,分片策略定义
schema.xml  *****,主配置文件
server.xml  ***  ,mycat服务有关
log4j2.xml  ***  ,记录日志有关
*.txt            ,分片策略使用的规则  

导入数据

[root@db01 ~]# mysql -uroot -p123
mysql> source /root/world.sql;
[root@db07 ~]# mysql -uroot -p123
mysql> source /root/world.sql;

Mycat实现一主一从读写分离(两台节点)

[root@db01 ~]# cd /application/mycat/conf/
[root@db01 conf]# vim schema.xml
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  
        <dataNode name="sh1" dataHost="zx1" database= "world" />         
        <dataHost name="zx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        <writeHost host="db1" url="172.16.1.51:3306" user="root" password="123">
                        <readHost host="db2" url="172.16.1.52:3306" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>

Mycat实现高可用+读写分离(四台节点)

[root@db01 conf]# mv schema.xml schema.xml.rw1
[root@db01 conf]# vim schema.xml
[root@db01 conf]# vim schema.xml
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
</schema>  
        <dataNode name="sh1" dataHost="zx1" database= "world" />         
        <dataHost name="zx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    
                <heartbeat>select user()</heartbeat>  
        <writeHost host="db1" url="172.16.1.51:3306" user="root" password="123">
                        <readHost host="db2" url="172.16.1.52:3306" user="root" password="123" /> 
        </writeHost> 
         <writeHost host="db3" url="172.16.1.53:3306" user="root" password="123">
                        <readHost host="db4" url="172.16.1.54:3306" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>

说明:

说明:    
 <writeHost host="db1" url="172.16.1.51:3306" user="root" password="123">
            <readHost host="db2" url="10.0.0.52:3306" user="root" password="123" /> 
</writeHost> 
<writeHost host="db3" url="172.16.1.53:3306" user="root" password="123">
            <readHost host="db4" url="10.0.0.54:3306" user="root" password="123" /> 
 </writeHost> 

第一个 writehost: 172.16.1.51:3306   真正的写节点,负责写操作
第二个 writehost: 172.16.1.53:3306   准备写节点,负责读,当 172.16.1.51:3306宕掉,会切换为真正的写节点

测试

#写:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


# 读:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           4 |
+-------------+
1 row in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

配置文件中属性介绍

4.6 配置中的属性介绍:

balance属性
负载均衡类型,目前的取值有3种: 
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。 
2. balance="1",全部的readHost与standby writeHost参与select语句的负载均衡,简单的说,
  当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。 
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。

writeType属性
负载均衡类型,目前的取值有2种: 
1. writeType="0", 所有写操作发送到配置的第一个writeHost,
第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为主,切换记录在配置文件中:dnindex.properties . 
2. writeType=“1”,所有写操作都随机的发送到配置的writeHost,但不推荐使用


switchType属性
-1 表示不自动切换 
1 默认值,自动切换 
2 基于MySQL主从同步的状态决定是否切换 ,心跳语句为 show slave status 
datahost其他配置

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1"> 

maxCon="1000":最大的并发连接数
minCon="10" :mycat在启动之后,会在后端节点上自动开启的连接线程

tempReadHostAvailable="1"
这个一主一从时(1个writehost,1个readhost时),可以开启这个参数,如果2个writehost,2个readhost时
<heartbeat>select user()</heartbeat>  监测心跳

Mycat高级应用-分布式解决方案

垂直分表

[root@db01 conf]# mv schema.xml schema.xml.4rw
[root@db01 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" dataNode="sh1">
        <table name="user" dataNode="sh1"/>
        <table name="order_t" dataNode="sh2"/>
</schema>
    <dataNode name="sh1" dataHost="zx1" database= "taobao" />
    <dataNode name="sh2" dataHost="zx2" database= "taobao" />
    <dataHost name="zx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="172.16.1.51:3306" user="root" password="123">
            <readHost host="db2" url="172.16.1.52:3306" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="172.16.1.53:3306" user="root" password="123">
            <readHost host="db4" url="172.16.1.54:3306" user="root" password="123" />
    </writeHost>
    </dataHost>
    
    <dataHost name="zx2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="172.16.1.55:3306" user="root" password="123">
            <readHost host="db2" url="172.16.1.56:3306" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="172.16.1.57:3306" user="root" password="123">
            <readHost host="db4" url="172.16.1.58:3306" user="root" password="123" />
    </writeHost>
    </dataHost> 
</mycat:schema>

测试

[root@db01 conf]# mysql -uroot p123
mysql> create database taobao charset utf8;
mysql> use taobao;
mysql> create table user(id int,name varchar(20));

[root@db05 conf]# mysql -uroot p123
mysql> create database taobao charset utf8;
mysql> use taobao;
mysql> create table user(id int,name varchar(20));


[root@db01 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

[root@db01 conf]# mysql -uroot -p123456 -h 172.16.1.51 -P 8066

mysql> use TESTDB
mysql> insert into user(id ,name ) values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into order_t(id ,name ) values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


##db01登录mysql
mysql> use taobao;
mysql> show tables from taobao;
+------------------+
| Tables_in_taobao |
+------------------+
| user             |
+------------------+
1 row in set (0.00 sec)

##db05登录mysql
mysql> use taobao;
mysql> show tables from taobao;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
+------------------+
1 row in set (0.00 sec)

Mycat分布式-水平拆分(分片)介绍

分片:对一个"bigtable",比如说t3表
(1)行数非常多,800w
(2)访问非常频繁

分片的目的:
(1)将大数据量进行分布存储
(2)提供均衡的访问路由

分片策略:
范围 range  800w  1-400w 400w01-800w
取模 mod    取余数
枚举 
哈希 hash 
时间 流水

优化关联查询
全局表
ER分片

5.3 Mycat分布式-范围分片

比如说t3表
(1)行数非常多,2000w(1-1000w:sh1   1000w01-2000w:sh2)
(2)访问非常频繁,用户访问较离散

#db01操作
[root@db01 conf]# cp schema.xml schema.xml.8rw
[root@db01 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" dataNode="sh1">
    <table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</schema>
    <dataNode name="sh1" dataHost="zx1" database= "taobao" />
    <dataNode name="sh2" dataHost="zx2" database= "taobao" />
    <dataHost name="zx1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="172.16.1.51:3306" user="root" password="123">
            <readHost host="db2" url="172.16.1.52:3306" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="172.16.1.53:3306" user="root" password="123">
            <readHost host="db4" url="172.16.1.54:3306" user="root" password="123" />
    </writeHost>
    </dataHost>
    
    <dataHost name="zx2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="172.16.1.55:3306" user="root" password="123">
            <readHost host="db2" url="172.16.1.56:3306" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="172.16.1.57:3306" user="root" password="123">
            <readHost host="db4" url="172.16.1.58:3306" user="root" password="123" />
    </writeHost>
    </dataHost> 
</mycat:schema>


[root@db01 conf]# vim autopartition-long.txt 
1-10=0
10-20=1


[root@db01 conf]# mysql -uroot -p123
mysql> use taobao;
mysql> create table t3 (id int not null primary key auto_increment,name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)

##db05操作
[root@db05 ~]# mysql -uroot -p123
mysql> use taobao;
mysql> create table t3 (id int not null primary key auto_increment,name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)


##db01操作
[root@db01 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...


[root@db01 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
mysql> use TESTDB
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| t3               |
| user             |
+------------------+
2 rows in set (0.01 sec)

mysql> insert into t3(id,name) values(1,'a');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t3(id,name) values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(id,name) values(3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3(id,name) values(10,'d');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(id,name) values(11,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3(id,name) values(12,'bb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(id,name) values(13,'cc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(id,name) values(14,'dd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(id,name) values(20,'dd');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)



##退出登录 ,正常登录mysql
[root@db01 conf]# mysql -uroot -p123
mysql> use taobao
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
| 10 | d    |
+----+------+
4 rows in set (0.01 sec)


##db05查看
[root@db05 ~]# mysql -uroot -p123
mysql> use taobao;
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 11 | aa   |
| 12 | bb   |
| 13 | cc   |
| 14 | dd   |
| 20 | dd   |
+----+------+
5 rows in set (0.00 sec)


##总结:
vim autopartition-long.txt
1-10=0   -----> >=1 , <=10
10-20=1  -----> >10 ,<=20
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!