关于mysql:MySQL-数据库集群PXC-方案四

58次阅读

共计 16034 个字符，预计需要花费 41 分钟才能阅读完成。

大数据归档 - 冷热数据拆散

尽管之前咱们的数据是扩散在不同的分片中，然而与日俱增分片中的数据越来越多，数据迁徙的老本就大大提高，所以能不能将数据进行拆散。

咱们能够将很少应用到的数据，从分片中归档到归档数据库中。

InnoDB 写入慢的起因

因为 InnoDB 自身应用的是 BTree 索引，正因为如此，每次写入都须要用 IO 进行索引树的重排。特地是当数据量特地大的时候，效率并不够高。

什么是 TokuDB

TokuDB 是一个反对事务的“新”引擎，有着杰出的数据压缩性能，由美国 TokuTek 公司（当初曾经被 Percona 公司收买）研发。领有杰出的数据压缩性能，如果咱们的数据 写多读少，而且数据量比拟大，咱们就能够应用 TokuDB，以节俭空间老本，并大幅度降低存储使用量和 IOPS 开销，不过相应的会减少 CPU 的压力。

特点：

  • 高压缩比，高写入性能，（能够达到压缩比 1：12，写入速度是 InnoDB 的 9~20 倍）
  • 在线创立索引和字段
  • 反对事务
  • 反对主从同步

装置 TokuDB

在之前的文章（一）中独自装置过 percona 数据库，咱们当初不再重新安装 Percona 数据库。

装置 jemalloc 库

yum install -y jemalloc

批改 my.cnf

vim /etc/my.cnf

在 mysqld_safe 节点下减少 malloc-lib。

……
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1
……

而后启动 MySQL 服务。

systemctl restart mysqld

开启 Linux 大页内存

为了保障 TokuDB 的写入性能，咱们须要敞开 linux 零碎的大页内存治理，默认状况下 linux 的大页内存治理是在系统启动时事后分配内存，零碎运行时不再扭转了。

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

装置 TokuDB

版本必须和 Percona 的版本统一，咱们后面装置的是 Percona5.7，所以此处也须要装置 toku5.7，否则提醒版本抵触。

yum install -y Percona-Server-tokudb-57.x86_64

输出 Mysql 的 root 帐号密码，实现启动。

ps-admin --enable -uroot -p

启动实现之后重启一下 mysql

systemctl restart mysqld

重启之后再激活一次 tokudb，从新执行一下命令

ps-admin --enable -uroot -p

查看 TokuDB 引擎是否装置胜利

进入 MySQL：

mysql -u root -p

执行 show engines;

show engines;

胜利之后，另一台虚拟机也是同样步骤。

应用 TokuDB 引擎

如果是 sql 语句建表, 只须要在语句的结尾加上ENGINE = TokuDB，留神只能应用 sql 语句创立表才无效。

CREATE TABLE student(.........) ENGINE = TokuDB;

归档库的双机热备

咱们选用两个 Percona 数据库节点组成 Replication 集群，这两个节点配置成 双向同步 ，因为 Replication 集群的主从同步是单向的，如果配置成单向的主从同步，主库挂掉当前，咱们还能够向从库写入数据，然而主库复原之后主库是不会像从库那同步数据的，所以两个节点的数据不统一，如果咱们配置成双向同步，无论哪一个节点宕机了，在上线的时候他都会从其余的节点同步数据。这就能够保障每一个节点的数据是统一的。当然这个一致性是 弱一致性，跟 PXC 集群的强一致性有本质区别的。

因为曾经启动了 8 台虚拟机了，为了节俭硬件资源，每个 PXC 节点我只启动一个 PXC 节点，和一个 MyCat 实例，这样就只有三台虚拟机同时运行。

另外别忘记了，在 MyCat 的配置文件中须要将 balance=0，而后将用不到的 PXC 节点删除掉。

vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 配置数据表 -->
    <schema name="test" checkSQLschema="false" sqlMaxLimit="100">
        <table name="t_test" dataNode="dn1,dn2" rule="mod-long" />
        <table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
        <table name="t_customer" dataNode="dn1,dn2" rule="sharding-customer">
                <childTable name="t_orders" primaryKey="ID" joinKey="customer_id" parentKey="id"/>
        </table>
    </schema>
    <!-- 配置分片关系 -->
    <dataNode name="dn1" dataHost="cluster1" database="test" />
    <dataNode name="dn2" dataHost="cluster2" database="test" />
    <!-- 配置连贯信息 -->
    <dataHost name="cluster1" maxCon="1000" minCon="10" balance="0"
                writeType="1" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="192.168.3.137:3306" user="admin"
                     password="Abc_123456">
        </writeHost>
    </dataHost>
    <dataHost name="cluster2" maxCon="1000" minCon="10" balance="0"
                writeType="1" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="192.168.3.141:3306" user="admin"
                   password="Abc_123456">
        </writeHost>
    </dataHost>
</mycat:schema>

最初重启 MyCat。

./mycat start

配置 Replication 集群

Replication 集群同步原理

当咱们在 Master 节点写入数据，Master 会把这次操作会记录到 binlog 日志里边，Slave 节点会有专门的线程去申请 Master 发送 binlog 日志，而后 Slave 节点上的线程会把收到的 Master 日志记录在本地 realy_log 日志文件中，slave 节点通过执行 realy_log 日志来实现数据的同步，最初把执行过的操作写到本地的 binlog 日志里。

通过上图咱们能总结出 Replication 集群的数据同步是单向的，咱们在 Master 上写入数据，在 slave 上能够同步到这些数据，然而反过来却不行，所以要实现 双向同步 ， 两个数据库节点互为主从关系 才行。

创立同步账户

咱们给两个节点的数据库都创立上一个同步数据的账户。

CREATE USER 'backup'@'%' IDENTIFIED BY 'Abc_123456' ;
GRANT super, reload, replication slave ON *.* TO 'backup'@'%' ;
FLUSH  PRIVILEGES ;

批改两个 TokuDB 的配置文件

vim /etc/my.cnf
[mysqld]
server_id = 101
log_bin = mysql_bin
relay_log = relay_bin
……
[mysqld]
server_id = 102
log_bin = mysql_bin
relay_log = relay_bin

重启 MySQL

systemctl restart mysqld

配置主从同步

我的 A 节点为：192.168.3.151

B 节点为：192.168.3.152

咱们先在 B 节点上敞开主从同步的服务。

# 敞开同步服务
stop slave;
#设置同步的 Master 节点
change master to master_host="192.168.3.151",master_port=3306,master_user="backup",
master_password="Abc_123456";
#启动同步服务
start slave;
#查看同步状态
show slave status\G;

如果看到下图 Slave_IO_Running 和 Slave_SQL_Running都为 yes 即阐明配置胜利。

而后咱们去 A 节点进行上述配置，将 master_host="192.168.3.152" 改为 152 即可。这样咱们就实现了 双向同步。

创立归档表

因为是双向同步，咱们在哪一个节点创立归档表，另一个节点都会同步到数据。

CREATE DATABASE test;
use test;
CREATE TABLE t_purchase_202011 (
    id INT UNSIGNED PRIMARY KEY,
    purchase_price DECIMAL(10,2) NOT NULL,
    purchase_num INT UNSIGNED NOT NULL,
    purchase_sum DECIMAL (10,2) NOT NULL,
    purchase_buyer INT UNSIGNED NOT NULL,
    purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    company_id INT UNSIGNED NOT NULL,
    goods_id INT UNSIGNED NOT NULL,
    KEY idx_company_id(company_id),
    KEY idx_goods_id(goods_id)
)engine=TokuDB;

搭建 Haproxy

之前文章曾经装置过 Haproxy 了，这里就不在啰嗦了。两个节点一样的步骤。

yum install -y haproxy

批改配置文件：

vim /etc/haproxy/haproxy.cfg
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

listen   admin_stats
    bind    0.0.0.0:4001
    mode  http
    stats uri       /dbs
    stats realm  Global\ statistics
    stats auth    admin:abc123456
listen   proxy-mysql
    bind    0.0.0.0:4002
    mode  tcp
    balance  roundrobin
    #日志格局
    option  tcplog
    server   backup_1  192.168.3.151:3306  check  port  3306  maxconn  2000
    server   backup_2  192.168.3.152:3306  check  port  3306  maxconn  2000
    #应用 keepalive 检测死链
    option  tcpka

开启防火墙的 VRRP 协定，开启 4001 端口和 4002 端口。

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT
firewall-cmd --zone=public --add-port=4001/tcp --permanent
firewall-cmd --zone=public --add-port=4002/tcp --permanent
firewall-cmd --reload

启动 Haproxy

service haproxy start

浏览器拜访如下地址即可拜访：

http://192.168.3.151:4001/dbs

如下图：即阐明胜利，两个节点都看一下。

搭建 Keepalived

还是在两台节点上一样的操作。

yum install -y keepalived

编辑配置文件：

vim /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
    state MASTER
    interface enp0s3
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {192.168.3.177}
}

配置阐明：

  • state MASTER：定义节点角色为 master，当角色为 master 时，该节点无需争抢就能获取到 VIP。集群内容许有多个 master，当存在多个 master 时，master 之间就须要争抢 VIP。为其余角色时，只有 master 下线能力获取到 VIP
  • interface enp0s3：定义可用于内部通信的网卡名称，网卡名称能够通过 ip addr 命令查看
  • virtual_router_id 51：定义虚构路由的 id，取值在 0-255，每个节点的值须要惟一，也就是不能配置成一样的
  • priority 100：定义权重，权重越高就越优先获取到 VIP
  • advert_int 1：定义检测间隔时间为 1 秒

  • authentication：定义心跳查看时所应用的认证信息

    • auth_type PASS：定义认证类型为明码
    • auth_pass 123456：定义具体的明码
  • virtual_ipaddress：定义虚构 IP（VIP），须要为同一网段下的 IP，并且每个节点须要统一

实现以上配置后，启动 keepalived 服务：

service keepalived start

咱们 ping 一下咱们的虚拟地址试试，也是 OK 的！

应用数据库连贯工具测试，也是 OK 的！

当初即便哪一台挂掉，都不会影响高可用。上一篇文章中曾经演示了，这里就不再演示了。

筹备归档数据

咱们在 PXC 的两个分片中，创立一个进货表，留神这里创立进货表时没指定 tokuDB 引擎。（如果不晓得两个 PXC 分片的话查看之前文章）

use test;
CREATE TABLE t_purchase (
      id INT UNSIGNED PRIMARY KEY,
      purchase_price DECIMAL(10,2) NOT NULL,
      purchase_num INT UNSIGNED NOT NULL,
      purchase_sum DECIMAL (10,2) NOT NULL,
      purchase_buyer INT UNSIGNED NOT NULL,
      purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      company_id INT UNSIGNED NOT NULL,
      goods_id INT UNSIGNED NOT NULL,
      KEY idx_company_id(company_id),
      KEY idx_goods_id(goods_id)
  )

批改 MyCat 配置文件

减少了表之后，咱们须要在 MyCat 的配置文件中减少该表：

<table name="t_purchase" dataNode="dn1,dn2" rule="mod-long" />

而后重启 MyCat。

./mycat restart

而后我应用 Java 代码来进行批量插入数据，这次没有应用 LoadData，大家能够比照一下 10 万条数据，和 1000 万条数据的时间差。

/**
 * @author 又坏又迷人
 * 公众号: Java 菜鸟程序员
 * @date 2020/11/27
 * @Description:
 */
public class InsertDB {public static void main(String[] args) throws SQLException {DriverManager.registerDriver(new Driver());
        String url = "jdbc:mysql://192.168.3.146:8066/test";
        String username = "admin";
        String password = "Abc_123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "insert into t_purchase(id, purchase_price, purchase_num, purchase_sum, purchase_buyer, purchase_date, company_id, goods_id)" +
                "VALUES (?,?,?,?,?,?,?,?)";
        connection.setAutoCommit(false);
        PreparedStatement pst = connection.prepareStatement(sql);
        for (int i = 0; i < 100000; i++) {pst.setObject(1, i);
            pst.setObject(2, 5.0);
            pst.setObject(3, 100);
            pst.setObject(4, 500.0);
            pst.setObject(5, 12);
            pst.setObject(6, "2020-11-27");
            pst.setObject(7, 20);
            pst.setObject(8, 9);
            pst.addBatch();
            if (i % 2000 == 0) {pst.executeBatch();
                connection.commit();}
        }
        pst.close();
        connection.close();
        System.out.println("执行完结");

    }
}

装置归档工具

Percona 公司为咱们提供了一套十分便捷的工具包 Percona-Toolkit，这个工具包蕴含了用于数据归档的pt-archiver, 用这个归档工具咱们能够轻松的实现数据的归档。

pt-archiver 的用处

  1. 导出线上数据，到线下数据作解决
  2. 清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器

下载：

yum install -y percona-toolkit --nogpgcheck

执行归档

pt-archiver --source h=192.168.3.146,P=8066,u=admin,p=Abc_123456,D=test,t=t_purchase --dest h=192.168.3.188,P=3306,u=root,p=123456,D=test,t=t_purchase_202011 --no-check-charset --where 'purchase_date="2020-11-27 00:00:00"' --progress 5000 --bulk-delete --bulk-insert --limit=10000 --statistics
  • –source h=192.168.3.146, P=8066, u=admin, p=Abc_123456, D=test, t=t_purchase 代表的是取哪个服务器的哪个数据库的哪张表的
  • –dest h=192.168.3.188, P=3306, u=root, p=123456, D=test, t=t_purchase_202011 代表的是归档库的连贯信息
  • –no-check-charset 代表的是归档过程中咱们不检查数据的字符集
  • –where 归档数据的判断条件
  • –progress 5000 每归档 5000 条数据往控制台打印一下状态信息
  • –bulk-delete 批量的删除归档数据
  • –bulk-insert 批量的新增归档数据
  • limit=10000 批量一万条数据进行一次归档
  • –statistics 打印归档的统计信息

执行实现后，能够看到在 MyCat 节点进行查问曾经没有数据了。

在咱们的 ToKuDB 的 Haproxy 虚构节点上查问能够看到数据曾经都过去了。

小结

  • 应用 TokuDB 引擎保留归档数据，领有高速写入个性
  • 应用双机热备计划搭建归档库，具备高可用性
  • 应用 pt-archiver 执行归档数据，简便易行

数据分区

什么是表分区

表分区就是依照特定的规定，将数据分成许多小块，存储在磁盘中不同的区域，通过晋升磁盘 IO 能力来放慢查问的速度。

分区不会更改数据表的构造，发生变化的只是存储形式。从逻辑上看还是一张表，但底层是由多个物理分区来组成的。咱们能够依照不同的形式来进行切分。

表分区的优缺点

长处：

  • 表分区的数据能够散布在不同的物理设施上，从而高效地利用多个硬件设施
  • 单表能够存储更多的数据
  • 数据写入和读取的效率进步，汇总函数计算速度也变快了
  • 不会呈现表锁，只会锁住相干分区

毛病：

  • 不反对存储过程、存储函数和某些非凡函数
  • 不反对按位运算符
  • 分区键不能子查问
  • 创立分区后，尽量不要批改数据库模式

为什么在集群中引入表分区

当热数据过多的状况下，咱们减少一个 PXC 分片，就要破费三台服务器。而且还要数据迁徙。

如果咱们应用表分区的话，咱们能够给每个分区减少一个硬盘。这样咱们就能够用大量的分片就能够存储更多的热数据。

挂载硬盘

这里咱们还是应用每个 PXC 集群中一个节点。

给虚拟机减少两个硬盘这里就不再演示了，依据不同的虚拟机进行配置就好。

实现后 执行命令能够看到，三块硬盘曾经被辨认。

fdisk -l

而后咱们进行分区

fdisk /dev/sdb
  • n：创立新分区
  • d：删除分区
  • p：列出分区表
  • w：把分区表写入硬盘并退出
  • q：退出而不保留

而后依据图中的步骤即可：

而后格式化分区

mkfs -t ext4 /dev/sdb1

接下来批改文件进行挂载。

vim /etc/fstab

最上面追加内容：

/dev/sdb1/  /mnt/p0   ext4  defaults 0 0

执行实现后进行重启

reboot

查看是否挂载胜利。

没有问题，之后咱们就把数据存储在 data 文件夹中。

之后再创立第三块硬盘分区

fdisk /dev/sdc

而后格式化分区

mkfs -t ext4 /dev/sdc1

接下来批改文件进行挂载。

vim /etc/fstab

最上面追加内容：

/dev/sdc1/  /mnt/p1   ext4  defaults 0 0

执行实现后进行重启

reboot

查看是否挂载胜利。

cd /mnt/p1
mkdir data

这样第一个 PXC 节点的就实现了，另外一台 PXC 节点也是一样的步骤。就不再演示了。

实现后调配权限在两台节点上。

chown -R mysql:mysql /mnt/p0/data
chown -R mysql:mysql /mnt/p1/data

PXC 节点应用表分区

vim /etc/my.cnf

设置为宽容模式，还有一种模式就是严格模式：DISABLED

pxc_strict_mode=PERMISSIVE

表分区类型

  • RANGE：依据间断区间值切分数据
  • LIST：依据枚举值切分数据
  • HASH：对整数求模切分数据
  • KEY：对任何数据类型求模切分数据

Range 分区

Range 分区是依照主键值范畴进行切分的，比方有 4 千万条数据。

依据上面语句咱们创立分区别，依据 ID 进行切分，p0 名字是能够轻易起的，切分规定就是小于 1 千万条的数据切分到 p0 中，1 千万到 2 千万条在 p1 中，以此类推。

CREATE TABLE t_range_1(
id INT UNSIGNED PRIMARY KEY ,
name VARCHAR(200) NOT NULL
)
PARTITION BY RANGE(ID)(PARTITION p0 VALUES LESS THAN (10000000),
PARTITION p1 VALUES LESS THAN (20000000),
PARTITION p2 VALUES LESS THAN (30000000),
PARTITION p3 VALUES LESS THAN (40000000)
);

最初咱们在查问的时候能够依据分区进行查问，就会提高效率。

SELECT * FROM t_range_1 PARTITION(p0);

因为 MySQL 只反对整数类型切分，如果想应用日期类型的话，咱们就要比方提取月份，进行分区。

须要留神的是：

分区字段必须是主键、联结主键的一部分，否则会报如下谬误：

A PRIMARY KEY must include all columns in the table's partitioning function

CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (6),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (12)
);

如果咱们想把表分区映射到不同的磁盘，须要应用上面的 SQL，p0 和 p1 就是方才咱们创立的分区。

CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY='/mnt/p0/data',
PARTITION p1 VALUES LESS THAN (12) DATA DIRECTORY='/mnt/p1/data'
);

咱们在 PXC 某个节点上演示一下表分区。

use test;
CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY='/mnt/p0/data',
PARTITION p1 VALUES LESS THAN (12) DATA DIRECTORY='/mnt/p1/data'
);

创立实现后咱们写入一些数据：

咱们去 PXC 节点的服务器上查问一下，能够看到咱们的数据曾经切分过去了。

cd /mnt/p0/data/test

而后咱们看一下 p1 的分区，也是没有问题的。

如果想查看每个分区保留了多少条数据的话，能够应用上面的 SQL：

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_range_2';

测试实现之后，咱们将创立表的语句和分区在另一台 PXC 节点上创立一下。因为咱们是应用 MyCat 来进行切分数据，但咱们还没有规定 MyCat 切分规定，所以把咱们方才创立的两条测试数据删除掉。

实现后咱们关上 MyCat 节点虚拟机。

vim /usr/local/mycat/conf/schema.xml

减少咱们新建的表：

<table name="t_test" dataNode="dn1,dn2" rule="mod-long" />

进入 bin 目录重启 MyCat：

./mycat restart

而后咱们连贯 MyCat 节点，插入一些数据。

insert into t_range_2 (id,name,birthday) values (1,'A','2020-01-01');
insert into t_range_2 (id,name,birthday) values (2,'B','2020-10-10');

之后咱们能够看到，数据被 MyCat 求模切分到了不同的 PXC 节点，随后又进行了日期的切分存储了不同的硬盘上，由此咱们能够晓得是能够共存的。

List 分区

LIST 分区和 RANGE 分区十分的类似，次要区别在于 LIST 是枚举值列表的汇合，RANGE 是间断的区间值的汇合。二者在语法方面十分的类似。同样倡议 LIST 分区列是非 null 列，否则插入 null 值如果枚举列表外面不存在 null 值会插入失败，这点和其它的分区不一样，RANGE 分区会将其作为最小分区值存储，HASH\KEY 分为会将其转换成 0 存储，次要 LIST 分区只反对整形，非整形字段须要通过函数转换成整形；5.5 版本之后能够不须要函数转换应用 LIST COLUMN 分区反对非整形字段。

咱们在第一个 PXC 节点上创立表，大体的代码还是没有太大区别，只是规定从范畴变成了固定的值。

create table t_list_1(
    id int unsigned,
    name varchar(200) not null,
    province_id int unsigned,
    primary key (id,province_id)
)
partition by list(province_id)(partition p0 values in (1,2,3,4) data directory = '/mnt/p0/data',
  partition p1 values in (5,6,7,8) data directory = '/mnt/p1/data'
);

而后在另一个 PXC 节点上创立，记得更改值。

create table t_list_1(
    id int unsigned,
    name varchar(200) not null,
    province_id int unsigned,
    primary key (id,province_id)
)
partition by list(province_id)(partition p0 values in (9,10,11,12) data directory = '/mnt/p0/data',
  partition p1 values in (13,14,15,16) data directory = '/mnt/p1/data'
);

随后咱们进入 MyCat 的虚拟机，批改配置文件。

vim /usr/local/mycat/conf/rule.xml

减少如下代码：

<tableRule name="sharding-province">
  <rule>
    <columns>province_id</columns>
    <algorithm>province-hash-int</algorithm>
  </rule>
</tableRule>
<function name="province-hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">province-hash-int.txt</property>
</function>

保留退出后咱们创立这个文件 province-hash-int.txt。这个文件创建在 mycat/conf 文件中

1=0
2=0
3=0
4=0
5=0
6=0
7=0
8=0
9=1
10=1
11=1
12=1
13=1
14=1
15=1
16=1

保留后咱们编辑 schema.xml，减少咱们新加的表：

<table name="t_list_1" dataNode="dn1,dn2" rule="sharding-province" />

随后重启 MyCat。

在 MyCat 节点上插入数据，咱们看看是什么成果：

insert into t_list_1 (id,name,province_id) values (1,'A',1);
insert into t_list_1 (id,name,province_id) values (2,'B',5);
insert into t_list_1 (id,name,province_id) values (3,'A',10);
insert into t_list_1 (id,name,province_id) values (4,'B',16);

这里我就用图解的形式了：

Hash 分区

基于给定的分区个数，将数据调配到不同的分区，HASH 分区只能针对整数进行 HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式能够是 mysql 中任意无效的函数或者表达式，对于非整形的 HASH 往表插入数据的过程中会多一步表达式的计算操作，所以不倡议应用简单的表达式这样会影响性能。

语法如下，基本上没有太大区别，只是咱们依照 2 取余数进行分区，如果等于 0 切分到 p0 如果等于 1 切分到 p1。

create table t_hash_1(
    id int unsigned primary key ,
    name varchar(200) not null,
    province_id int unsigned not null
)
partition by hash(id) partitions 2(
  partition p0 data directory = '/mnt/p0/data',
  partition p1 data directory = '/mnt/p1/data'
);

而后咱们还是进入 MyCat，编辑 schema.xml，减少咱们新加的表，切分算法还是应用咱们方才创立的。

<table name="t_hash_1" dataNode="dn1,dn2" rule="sharding-province" />

随后重启 MyCat。

咱们插入测试数据:

insert into t_hash_1 (id,name,province_id) values (1,'A',1);
insert into t_hash_1 (id,name,province_id) values (2,'B',5);

首先会依据 province_id 去进行 MyCat 切分，1-8 的 province_id 都会被存储在一个 PXC 节点上。

随后进行 Hash 分区，咱们查问一下看看是否调配到了两个硬盘上。

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_hash_1';

执行后果也是没有问题，正确的调配到了不同的硬盘上。

Key 分区

KEY 分区和 HASH 分区类似，然而 KEY 分区反对除 text 和 BLOB 之外的所有数据类型的分区，而 HASH 分区只反对数字分区，KEY 分区不容许应用用户自定义的表达式进行分区，KEY 分区应用零碎提供的 HASH 函数进行分区。当表中存在主键或者惟一键时，如果创立 KEY 分区时没有指定字段零碎默认会首选主键列作为分区字列, 如果不存在主键列会抉择非空惟一键列作为分区列, 留神 惟一列作为分区列时惟一列不能为 NULL。

在两个 PXC 分片中执行 sql。

create table t_key_1(
    id int unsigned not null ,
    name varchar(200) not null,
    job varchar(200) not null ,
    primary key (id,job)
)
partition by key(job) partitions 2(
  partition p0 data directory = '/mnt/p0/data',
  partition p1 data directory = '/mnt/p1/data'
);

MyCat 配置schema.xml，减少配置：

<table name="t_key_1" dataNode="dn1,dn2" rule="mod-long" />

而后重启 MyCat。

之后咱们测试数据：

insert into t_key_1 (id,name,job) values (1,'A','管理员');
insert into t_key_1 (id,name,job) values (2,'B','保洁');
insert into t_key_1 (id,name,job) values (3,'C','网管');

执行之后，咱们 id 为 1、3 的求模切分到了一个 PXC 分片中。而后咱们执行查问看看 Key 是怎么分区的。

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_key_1';

咱们能够看到管理员和网管都被分到了一个硬盘中。

治理表分区

治理 Range 表分区

咱们之前创立的 Range 表分区是到 4 千万，咱们想扩大的话，能够应用上面的语句：

alter table t_range_1 add partition (partition p4 values less than (50000000)
);

如果想删除表分区，能够应用上面的语句：

alter table t_range_1 drop partition p3,p4;

如果想拆分某一个区域的话能够应用上面的语句：

alter table t_range_1 reorganize partition p0 into (partition s0 values less than (5000000),
    partition s1 values less than (10000000)
)

对应的合并就是上面的语句：

alter table t_range_1 reorganize partition s0,s1 into (partition p0 values less than (10000000)
)

移除表分区，并不会失落数据，而是将数据放到主分区中。

alter table t_range_1 remove partitioning ;

对应的其余类型的分区语法都差不多，这里就不再演示了。

正文完
mysql
发表至： mysql
2020-11-28
 0
关于mysql:MySQL间隙锁nextkey锁
关于mysql:docker-创建mysql服务
关于mysql:day1-上升的温度三种方法
关于mysql:MySQL-表结构