分库/分表(分布式存储)
一、什么是分库分表
将存放在一台数据库服务器中的数据,按照特定方式(mycat提供的算法-分片规则)进行拆分,分散存放到多台数据库服务器中,以达到分散单台服务器负载的效果
二、垂直分割
1、纵向切分
将单个数据库的多个表按业务类型分类,分散存储到不同的数据库服务器
三、水平分割
1、横向切分
按照表中指定字段的分片规则,将表记录按行切分,分散存储到多个数据库服务器中
四、分片规则
配置第一对主从
master
[root@mysql59 ~]# yum -y install mysql mysql-server
[root@mysql59 ~]# systemctl enable mysqld --now
[root@mysql59 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=59
log-bin=mysql59
... ...
[root@mysql59 ~]# systemctl restart mysqld.service
[root@mysql59 ~]# mysql
mysql> create user repluser@"%" identified by '123456';
mysql> grant replication slave on *.* to repluser@"%";
mysql> flush privileges;
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql59.000001 | 834 | | | |
+----------------+----------+--------------+------------------+-------------------+
slave
[root@mysql60 ~]# yum -y install mysql mysql-server
[root@mysql60 ~]# systemctl enable mysqld --now
[root@mysql60 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=60
... ...
[root@mysql60 ~]# systemctl restart mysqld.service
[root@mysql60 ~]# mysql
mysql> change master to master_host="192.168.88.59",master_user="repluser",master_password="123456",master_log_file="mysql59.000001",master_log_pos=834;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.59
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql59.000001
Read_Master_Log_Pos: 834
Relay_Log_File: mysql60-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql59.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
... ...
配置第二对主从
master
[root@mysql61 ~]# yum -y install mysql mysql-server
[root@mysql61 ~]# systemctl enable mysqld --now
[root@mysql61 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=61
log-bin=mysql61
... ...
[root@mysql61 ~]# systemctl restart mysqld.service
[root@mysql61 ~]# mysql
mysql> create user repluser@"%" identified by '123456';
mysql> grant replication slave on *.* to repluser@"%";
mysql> flush privileges;
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql61.000001 | 834 | | | |
+----------------+----------+--------------+------------------+-------------------+
slave
[root@mysql62 ~]# yum -y install mysql mysql-server
[root@mysql62 ~]# systemctl enable mysqld --now
[root@mysql62 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=62
... ...
[root@mysql62 ~]# systemctl restart mysqld.service
[root@mysql62 ~]# mysql
mysql> change master to master_host="192.168.88.61",master_user="repluser",master_password="123456",master_log_file="mysql61.000001",master_log_pos=834;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.61
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql61.000001
Read_Master_Log_Pos: 834
Relay_Log_File: mysql62-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql61.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
... ...
安装
[root@mycat63 ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@mycat63 ~]# which java
/usr/bin/java
[root@mycat63 ~]# yum -y install unzip
[root@mycat63 ~]# unzip mycat2-install-template-1.21.zip
[root@mycat63 ~]# mv mycat /usr/local/
[root@mycat63 ~]# mv mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
[root@mycat63 ~]# chmod -R 777 /usr/local/mycat/
修改配置文件
定义客户端连接mycat服务使用的用户名和密码
[root@mycat63 ~]# vim /usr/local/mycat/conf/users/root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"123456",
"transactionType":"proxy",
"username":"mycat"
}
定义mycat服务启动时连接的数据库服务器
[root@mycat63 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"hmy",
"weight":0
}
[root@mycat63 ~]# yum -y install mysql mysql-server
[root@mycat63 ~]# systemctl enable mysqld --now
[root@mycat63 ~]# mysql
mysql> create user hmy@"localhost" identified by '123456';
mysql> grant all on *.* to hmy@"localhost";
启动mycat服务
[root@mycat63 ~]# /usr/local/mycat/bin/mycat start
[root@mycat63 ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=22509,fd=71))
连接mycat服务
[root@mycat63 ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> show databases;
+--------------------+
| `Database` |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.12 sec)
添加数据源
[root@mycat63 ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> /*+ mycat:createdatasource{
-> "name":"dw0",
"url":"jdbc:mysql://192.168.88.59:3306","user":"hmy","password":"123456"}*/;
mysql> /*+ mycat:createdatasource{
-> "name":"dr0",
"url":"jdbc:mysql://192.168.88.60:3306","user":"hmy","password":"123456"}*/;
mysql> /*+ mycat:createdatasource{"name":"dw1","url":"jdbc:mysql://192.168.88.61:3306","user":"hmy","password":"123456"}*/;
mysql> /*+ mycat:createdatasource{"name":"dr1","url":"jdbc:mysql://192.168.88.62:3306","user":"hmy","password":"123456"}*/;
[root@mycat63 ~]# ls /usr/local/mycat/conf/datasources/
dr0.datasource.json dw0.datasource.json prototypeDs.datasource.json
dr1.datasource.json dw1.datasource.json
配置数据库服务器添加hmy用户
[root@mysql59 ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";
mysql> select user,host from mysql.user where user="hmy";
+------+------+
| user | host |
+------+------+
| hmy | % |
+------+------+
1 row in set (0.00 sec)
[root@mysql60 ~]# mysql
mysql> select user,host from mysql.user where user="hmy";
+------+------+
| user | host |
+------+------+
| hmy | % |
+------+------+
1 row in set (0.00 sec)
[root@mysql61 ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";
mysql> select user,host from mysql.user where user="hmy";
+------+------+
| user | host |
+------+------+
| hmy | % |
+------+------+
1 row in set (0.00 sec)
[root@mysql62 ~]# mysql
mysql> select user,host from mysql.user where user="hmy";
+------+------+
| user | host |
+------+------+
| hmy | % |
+------+------+
1 row in set (0.00 sec)
创建集群
[root@mycat63 ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> /*!mycat:createcluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]}*/;
mysql> /*!mycat:createcluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]}*/;
[root@mycat63 ~]# ls /usr/local/mycat/conf/clusters/
c0.cluster.json c1.cluster.json prototype.cluster.json
[root@mycat63 ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@mycat63 ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=22772,fd=71))
创建表时定义表使用的分片规则
存储数据时会根据表使用的分片规则的计算结果存储数据
全局表 broadcast
所有库都有表的全部数据
[root@client50 ~]# yum -y install mysql
[root@client50 ~]# mysql -h192.168.88.63 -P8066 -umycat -p123456
mysql> create database testdb;
[root@mycat63 ~]# ls /usr/local/mycat/conf/schemas/
information_schema.schema.json mysql.schema.json testdb.schema.json
mysql> create table testdb.dept(dept_id int , dept_name char(10),primary key(dept_id)) default charset utf8 broadcast;
[root@mycat63 ~]# cat -b /usr/local/mycat/conf/schemas/testdb.schema.json
1 {
2 "customTables":{},
3 "globalTables":{
4 "dept":{
5 "broadcast":[
6 {
7 "targetName":"c0"
8 },
9 {
10 "targetName":"c1"
11 }
12 ],
13 "createTableSQL":"CREATE TABLE testdb.dept (\n\tdept_id int,\n\tdept_name char(10),\n\tPRIMARY KEY (dept_id)\n) BROADCAST CHARSET = utf8"
14 }
15 },
16 "normalProcedures":{},
17 "normalTables":{},
18 "schemaName":"testdb",
19 "shardingTables":{},
20 "views":{}
21 }
mysql> insert into testdb.dept values(1,"开发部"),(2,"运维部"),(3,"测试部");
mysql> select * from testdb.dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 测试部 |
+---------+-----------+
3 rows in set (0.02 sec)
分片表
[root@client50 ~]# mysql -h192.168.88.63 -P8066 -umycat -p123456
mysql> create table testdb.employees(employee_id int primary key,name char(10),dept_id int ,mail varchar(30)) default charset utf8 dbpartition BY mod_hash(employee_id) tbpartition BY mod_hash(employee_id) tbpartitions 1 dbpartitions 2;
[root@mycat63 ~]# cat -b /usr/local/mycat/conf/schemas/testdb.schema.json
1 {
2 "customTables":{},
3 "globalTables":{
4 "dept":{
5 "broadcast":[
6 {
7 "targetName":"c0"
8 },
9 {
10 "targetName":"c1"
11 }
12 ],
13 "createTableSQL":"CREATE TABLE testdb.dept (\n\tdept_id int,\n\tdept_name char(10),\n\tPRIMARY KEY (dept_id)\n) BROADCAST CHARSET = utf8"
14 }
15 },
16 "normalProcedures":{},
17 "normalTables":{},
18 "schemaName":"testdb",
19 "shardingTables":{
20 "employees":{
21 "createTableSQL":"CREATE TABLE testdb.employees (\n\temployee_id int PRIMARY KEY,\n\tname char(10),\n\tdept_id int,\n\tmail varchar(30)\n) CHARSET = utf8\nDBPARTITION BY mod_hash(employee_id) DBPARTITIONS 2\nTBPARTITION BY mod_hash(employee_id) TBPARTITIONS 1",
22 "function":{
23 "properties":{
24 "dbNum":"2",
25 "mappingFormat":"c${targetIndex}/testdb_${dbIndex}/employees_${index}",
26 "tableNum":"1",
27 "tableMethod":"mod_hash(employee_id)",
28 "storeNum":2,
29 "dbMethod":"mod_hash(employee_id)"
30 }
31 },
32 "shardingIndexTables":{}
33 }
34 },
35 "views":{}
36 }
在2台master上查看表
[root@mysql59 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb_0 |
+--------------------+
[root@mysql59 ~]# mysql -e 'use testdb_0 ;show tables'
+--------------------+
| Tables_in_testdb_0 |
+--------------------+
| employees_0 |
+--------------------+
[root@mysql61 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb_1 |
+--------------------+
[root@mysql61 ~]# mysql -e 'use testdb_1 ;show tables'
+--------------------+
| Tables_in_testdb_1 |
+--------------------+
| employees_1 |
+--------------------+
存储数据
[root@client50 ~]# mysql -h192.168.88.63 -P8066 -umycat -p123456
mysql> insert into testdb.employees values (9,"a","1","a@163.com");
mysql> insert into testdb.employees values (8,"B","3","B@QQ.com");
mysql> insert into testdb.employees values (7,"C","2","c@QQ.com");
mysql> insert into testdb.employees values (6,"C","2","c@QQ.com");
mysql> select * from testdb.employees;
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 6 | C | 2 | c@QQ.com |
| 8 | B | 3 | B@QQ.com |
| 7 | C | 2 | c@QQ.com |
| 9 | a | 1 | a@163.com |
+-------------+------+---------+-----------+
4 rows in set (0.13 sec)
在数据库服务器本机查看数据
[root@mysql59 ~]# mysql -e 'select * from testdb_0.employees_0'
+-------------+------+---------+----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+----------+
| 6 | C | 2 | c@QQ.com |
| 8 | B | 3 | B@QQ.com |
+-------------+------+---------+----------+
[root@mysql61 ~]# mysql -e 'select * from testdb_1.employees_1'
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 7 | C | 2 | c@QQ.com |
| 9 | a | 1 | a@163.com |
+-------------+------+---------+-----------+
ER表
ER表,称为关联表,表示数据逻辑上有关联性的两个或多个表,例如工资表和员工表。对于关联表,通常希望他们能够有相同的分片规则,这样在进行关联查询时,能够快速定位到同一个数据分片中。MyCat2中对于关联表,不需要有过多的声明,他可以根据分片规则自行判断
[root@client50 ~]# mysql -h192.168.88.63 -P8066 -umycat -p123456
mysql> create table testdb.salary(employee_id int primary key, p_date date , basic int , bonus int ) DEFAULT CHARSET=utf8 dbpartition BY mod_hash(employee_id) tbpartition BY mod_hash(employee_id) tbpartitions 1;
[root@client50 ~]# mysql -h192.168.88.63 -P8066 -umycat -p123456
mysql> insert into testdb.salary values(7,20230210,25000,2500);
mysql> insert into testdb.salary values(8,20230311,25000,2500);
mysql> select * from testdb.salary;
+-------------+------------+-------+-------+
| employee_id | p_date | basic | bonus |
+-------------+------------+-------+-------+
| 7 | 2023-02-10 | 25000 | 2500 |
| 8 | 2023-03-11 | 25000 | 2500 |
+-------------+------------+-------+-------+
2 rows in set (0.02 sec)
[root@mysql59 ~]# mysql -e 'select * from testdb_0.salary_0'
+-------------+------------+-------+-------+
| employee_id | p_date | basic | bonus |
+-------------+------------+-------+-------+
| 8 | 2023-03-11 | 25000 | 2500 |
+-------------+------------+-------+-------+
[root@mysql61 ~]# mysql -e 'select * from testdb_1.salary_1'
+-------------+------------+-------+-------+
| employee_id | p_date | basic | bonus |
+-------------+------------+-------+-------+
| 7 | 2023-02-10 | 25000 | 2500 |
+-------------+------------+-------+-------+