分库分表技术之MyCat（4）

RG
VIP.1 初学乍练
855 阅读8分钟

「这是我参与11月更文挑战的第23天，活动详情查看：2021最后一次更文挑战」

MyCat分库分表

分片规则配置(水平分库)

  • 水平分库：把一张表的数据按照一定规则，分配到不同的数据库，每一个库只有这张表的部分数据。
  • 在rule.xml配置，自动分片
    • 每个datanode中保存一定数量的数据。根据id进行分片

<!-- schema 逻辑库 -->
<schema name="rgtest" checkSQLschema="true" sqlMaxLimit="100" >
    <table name="pay_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" primaryKey="id" autoIncrement="true" >
    </table>
</schema>


<!-- 自动分片 -->
<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>
  • autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-1k=0
1k-2k=1
2k-3k=2
  • 对分片规则进行测试id范围为：
    Datanode1：1~1000
    Datanode2：1000~2000
    Datanode3：2000~3000

启动MyCat 进行测试

  • 重启MyCat
停止命令：./mycat stop
重启命令：./mycat restart
  • 在MyCat中创建逻辑表
DROP TABLE IF EXISTS pay_order;

CREATE TABLE pay_order (
    id BIGINT(20) PRIMARY KEY,
    user_id INT(11) NOT NULL ,
    product_name VARCHAR(128) ,
    amount DECIMAL(12,2)
);

  • MyCat中创建好表之后，我们的MySQL节点中也会对应的创建表

  • 插入数据，观察数据被插入到哪张表中

INSERT INTO pay_order(id,user_id,product_name,amount) VALUES(2001,1,"面试宝典",15.8);
  • 注意: 解决MyCat乱码问题

全局序列号

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat提供了全局sequence，并且提供了包含本地配置和数据库配置等多种实现方式。

  • server.xml文件中
<system>
    <property name="sequnceHandlerType">0</property>
</system>

0 表示是表示使用本地文件方式。
1 表示的是根据数据库来生成
2 表示时间戳的方式 ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)

本地文件

此方式Mycat将sequence配置到文件中，当使用到sequence中的配置后，Mycat会更新classpath中的sequence_conf.properties文件中sequence当前的值。

PAY_ORDER.HISIDS=
PAY_ORDER.MINID=101
PAY_ORDER.MAXID=10000000
PAY_ORDER.CURID=100

其中HISIDS表示使用过的历史分段(一般无特殊需要可不配置)，MINID表示最小ID值，MAXID表示最大ID值，CURID 表示当前ID值

重启MyCat，插入一条数据，不用指定id

INSERT INTO pay_order(user_id,product_name,amount) VALUES(1,"xiao",12.8);

MyCat读写分离

什么是读写分离

在实际的生产环境中，数据的读写操作如果都在同一个数据库服务器中进行，当遇到大量的并发读或者写操作的时候，是没有办法满足实际需求的，数据库的吞吐量将面临巨大的瓶颈压力。

  • 主从复制
    通过搭建主从架构，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

  • 读写分离
    读写分离就是让主库处理事务性操作，从库处理select查询。数据库复制被用来把事务性查询导致的数据变更同步到从库，同时主库也可以select查询。

读写分离的数据节点中的数据内容是一致。

image.png

MySQL主从复制(同步)

MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制架构。

image.png

主从复制的用途

  • 实时灾备，用于故障切换
  • 读写分离，提供查询服务
  • 备份，避免影响业务

主从部署必要条件

  • 主库开启binlog日志（设置log-bin参数）
  • 主从server-id不同
  • 从库服务器能连通主库

主从复制的原理

  • Mysql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（insert,update,delete,create/alter/drop table,grant等等）。
  • 主从复制的原理其实就是把主服务器上的bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。
    • 主库db的更新事件(update、insert、delete)被写到binlog
    • 主库创建一个binlog dump thread，把binlog的内容发送到从库
    • 从库启动并发起连接，连接到主库
    • 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
    • 从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

主从复制架构搭建

Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

第一步 master中创建数据库和表

-- 创建数据库
CREATE DATABASE test CHARACTER SET utf8;

-- 创建表
CREATE TABLE users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) DEFAULT NULL,
    age INT(11) DEFAULT NULL
);

-- 插入数据
INSERT INTO users VALUES(NULL,'user1',20);
INSERT INTO users VALUES(NULL,'user2',21);
INSERT INTO users VALUES(NULL,'user3',22);

第二步 修改主数据库的配置文件my.cnf

vim /etc/my.cnf

插入下面的内容

lower_case_table_names=1

log-bin=mysql-bin
server-id=1
binlog-do-db=test
binlog_ignore_db=mysql
  • server-id=1中的1可以任定义，只要是唯一的就行
  • log-bin=mysql-bin表示启用binlog功能，并制定二进制日志的存储目录
  • binlog-do-db=test是表示只备份test数据库
  • binlog_ignore_db=mysql表示忽略备份mysql
  • 不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库

第三步 重启MySQL

service mysqld restart

第四步 在主数据库上, 创建一个允许从数据库来访问的用户账号

用户：slave
密码：123456

主从复制使用REPLICATION SLAVE赋予权限

-- 创建账号
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.52.11' IDENTIFIED BY 'Qwer@1234';

第五步 停止主数据库的更新操作，并且生成主数据库的备份

-- 执行以下命令锁定数据库以防止写入数据。
FLUSH TABLES WITH READ LOCK;

导出数据库，恢复写操作

使用SQLYog导出，主数据库备份完毕，恢复写操作

unlock tables;

将刚才主数据库备份的test.sql导入到从数据库

导入后，主库和从库数据会追加相平，保持同步！此过程中，若主库存在业务，并发较高，在同步的时候要先锁表，让其不要有修改！等待主从数据追平，主从同步后在打开锁！

接着修改从数据库的my.cnf

  • 增加server-id参数，保证唯一
server-id=2

-- 重启
service mysqld restart

在从数据库设置相关信息

  • 执行以下SQL
STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='192.168.52.10',
MASTER_USER='slave',
MASTER_PASSWORD='Qwer@1234',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0,
MASTER_CONNECT_RETRY=10;

修改auto.cnf中的UUID，保证唯一

-- 编辑auto.cnf
vim /var/lib/mysql/auto.cnf

-- 修改UUID的值
server-uuid=a402ac7f-c392-11ea-ad18-000c2980a208

-- 重启
service mysqld restart

在从服务器上，启动slave进程

start slave;

-- 查看状态
SHOW SLAVE STATUS;

-- 命令行下查看状态 执行
SHOW SLAVE STATUS \G;

注意：这两个参数的值，必须是Yes，否则就要进行错误的排查。

现在可以在我们的主服务器做一些更新的操作，然后在从服务器查看是否已经更新

-- 在主库插入一条数据,观察从库是否同步
INSERT INTO users VALUES(NULL,'user4',23);

实现读写分离

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置

image.png

在schema.xml文件中配置Mycat读写分离。使用前需要搭建MySQL主从架构，并实现主从复制，Mycat不负责数据同步问题。

  • server.xml

修改用户可以访问的逻辑表为test

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">test</property>
    <property name="defaultSchema">test</property>
</user>
  • schema
    • 逻辑库name="test"
    • 逻辑表name="users"
    • 读写分离不设置分片规则ruleRequired=false
    • 分片节点dataNode="dn4"

<schema name="test" checkSQLschema="true" sqlMaxLimit="100">
    <table name="users" dataNode="dn4" ruleRequired="false" primaryKey="id" autoIncrement="true" >
    </table>
</schema>
  • dataNode
<!-- 读写分离 -->
<dataNode name="dn4" dataHost="localhost3" database="test" />
  • dataHost
<!-- 读写分离 -->
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 主 -->
    <writeHost host="M1" url="192.168.52.10:3306" user="root" password="123456">
        <!-- 从 -->
        <readHost host="S1" url="192.168.52.11:3306" user="root" password="123456" weight="1" />
    </writeHost>
</dataHost>

balance参数：

  • 0：所有读操作都发送到当前可用的writeHost
  • 1：所有读操作都随机发送到readHost和stand by writeHost
  • 2：所有读操作都随机发送到writeHost和readHost
  • 3：所有读操作都随机发送到writeHost对应的readHost上，但是writeHost不负担读压力

writeType参数：

  • 0：所有写操作都发送到可用的writeHost

  • 1：所有写操作都随机发送到readHost

  • 2：所有写操作都随机发送到writeHost，readHost


  • 重启MyCat

./mycat restart
  • 执行查询和插入操作

插入一条数据，观察否两个表都同时新增了，如果同时新增，证明插入的是主库的表

INSERT INTO users(NAME,age) VALUES('测试abc',26);

在从库插入一条数据，然后进行查询，查询的是从库中的数据，证明查询操作在从库进行

SELECT * FROM users;
avatar
文章
阅读
粉丝