mysql分库分表

分库分表的思想：将数据分散存储，让单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略：

垂直拆分：垂直分库 垂直分表

水平拆分：水平分库 水平发表

水平拆分：以字段为依据拆分，特点为每个库/表的结构都一样，每个库/表的数据都不一样，所有库/表的并集是全量数据。

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：每个库/表的结构不一样，每个库/表的数据不一样，所有库/表的并集是全量数据。

实现技术：mycat，是一个数据库分库分表的中间件。

mycat的安装 

mycat是基于java的，所以需要先安装jdk，mycat跟jdk的安装包我是从别人的网盘上下载的。

解压jdk安装包到/usr/local：tar -xzf jdk-8u341-linux-x64.tar.gz -C /usr/local

配置环境变量，，修改/etc/profile文件，文件末尾添加下面两行：

export JAVA_HOME=/usr/local/jdk1.8.0_341
export PATH=$JAVA_HOME/bin:$PATH

重新加载配置：source /etc/profile

可以看见，配置成功生效

解压mycat： tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/

进入mycat目录：cd /usr/local/mycat

mycat各个文件的作用：

bin：存放可执行文件，用于启动和停止mycat

conf：存放mycat的配置文件

lib：存放mycat的项目依赖包（jar）

logs：存放mycat的日志文件

在lib目录下，mysql的驱动包版本过低，需要更换

删除原来的驱动包：rm -rf mysql-connector-java-5.1.35.jar

再上传到lib目录下即可

可以看见，驱动包是红色的，权限不对

修改权限：chmod 777 mysql-connector-java-8.0.29.jar 

修改之后颜色正常

mycat入门

环境：

mycat服务器   192.168.249.192

mysql服务器1 192.168.249.193

mysql服务器2 192.168.249.194

关闭三台服务器的防火墙之后，分别创建库db1：create database db1;

在mycat服务器上配置conf目录下的schema.xml：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">   #逻辑库
                <table name="orders" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> #逻辑表和分片规则
        </schema>
        <dataNode name="dn1" dataHost="dhost1" database="db1" />
        <dataNode name="dn2" dataHost="dhost2" database="db1" />
        <dataNode name="dn3" dataHost="dhost3" database="db1" />    #以上三列为数据节点
        <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">                                                                          #节点主机，下面有三台
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://192.168.249.192:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://192.168.249.193:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://192.168.249.194:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>

</mycat:schema>

在mycat服务器上配置conf目录下的server.xml，把这段配置中的TESTDB改为DB01：  

      配置完成之后可以启动mycat：

        在mycat目录下执行：bin/mycat start

        通过日志查看启动成功：

连接mycat：mysql -h 192.168.249.192 -P 8066 -uroot -p123456

可以看见只有逻辑库跟逻辑表

show databases;
+----------+
| DATABASE |
+----------+
| DB01     |
+----------+
1 row in set (0.01 sec)

mysql> use DB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tabels;
ERROR 1064 (HY000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabels' at line 1
mysql> show tables;
+----------------+
| Tables in DB01 |
+----------------+
| orders         |
+----------------+
1 row in set (0.01 sec)

创建表结构之前，每一个mysql服务器都要有远程连接的功能，不然会提示错误，无法创建表结构，执行如下命令

mysql> alter user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)         
 

创建表结构和插入数据：

mysql> CREATE TABLE orders (
    ->   id INT NOT NULL,
    ->   product_name VARCHAR(50) NOT NULL,
    ->   price DECIMAL(10,2) NOT NULL,
    ->   quantity INT NOT NULL,
    ->   order_date DATE NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)
 OK!
 

mysql> INSERT INTO orders (id, product_name, price, quantity, order_date) VALUES
    -> (1, 'Product 1', 10.50, 2, '2023-06-06'),
    -> (2, 'Product 2', 25.00, 1, '2023-06-05'),
    -> (3, 'Product 3', 15.75, 3, '2023-06-04'),
    -> (5000001, 'Product 4', 5.99, 5, '2023-06-03'),
    -> (5000002, 'Product 5', 5.99, 4, '2023-06-01'),
    -> (10000003, 'Product 6', 5.99, 5, '2023-05-03');
Query OK, 6 rows affected (0.02 sec)
 OK!
 

所有的数据变更操作在mycat服务器

分片规则：schema.xml中的rule：auto-sharding-long

此规则是基于id进行分片的

0-500M id的数据在节点0 192.168.249.192，500M-1000M id的数据在节点1 192.168.249.193， 1000M-1500M id的数据在节点2 192.168.249.194。

如果数据id超过了1500M，则需要增加新的节点。

mycat配置

schema.xml

主要内容为Mycat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置

rule.xml

定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，他让分片过程可配置化。

server.xml

配置文件包含mycat的系统配置信息，包含逻辑库跟逻辑表的权限相关配置。

mycat分片规则（水平拆分）

范围分片

取模分片

一致性hash算法

枚举分片

应用指定算法

固定hash算法

字符串hash

按天分片

按自然月分片

mycat管理及监控

MyCat默认开通2个端口：8066、9066，可以在server.xml文件中进行修改

8066数据访问端口（平常进行数据的修改时使用），9066数据库管理端口，管理mycat的整个集群的状态。

在登录mycat可以通过指定这两个端口。

使用9066登录时，可以通过以下命令管理mycat：

show @@help; 查看Mycat管理工具帮助文档

show @@version; 查看mycat的版本

show @@datasource; 查看mycat数据源信息

show @@config; 重新加载mycat配置文件

show @@datanode; 查看mycat现有的分片节点信息

show @@threadpool; 查看mycat的线程池信息

show @@sql; 查看执行的sql

show @@sql.sum;   查看执行的sql统计

监控：mycat-eye，需要安装mycat-web跟zookeeper