SharingProxy——基本知识
固定分区
事先设定一个比较大的库基数,但是这些库可以部署在同一个物理机上;
这样即使未来需要扩展物理机也不需要做过多的数据迁移
当物理机扩展时,原有分片规则不变,直接将库指向远程机而非本地的机的库即可
分库意义
如果库在不同的物理机上,多台机器的IO比一台机器的IO大,所以需要库级分片
分表意义
数据量越来越大时,索引B+树的高度越来越多,插入和查询性能都变慢所以需要表级分片
SharingProxy——基本准备
搭模板机
先搭一个mysql单个模板服务器,然后复制他的配置文件
https://blog.csdn.net/weixin_44275259/article/details/108694506
分发文件
scp -r /usr/local/mysql slave1:/usr/local/mysql
scp -r /usr/local/mysql slave2:/usr/local/mysql
scp -r /usr/local/mysql slave3:/usr/local/mysql
scp /etc/my.cnf slave1:/etc/my.cnf
scp /etc/my.cnf slave2:/etc/my.cnf
scp /etc/my.cnf slave3:/etc/my.cnf
scp /etc/init.d/mysql slave1:/etc/init.d/mysql
scp /etc/init.d/mysql slave2:/etc/init.d/mysql
scp /etc/init.d/mysql slave3:/etc/init.d/mysql
批量增加用户组
mkdir /tmp/mysql
groupadd mysql
useradd -g mysql mysql
批量增加权限
chown -R mysql:mysql /tmp/mysql
chown -R mysql:mysql /usr/local/mysql
chmod -R 755 /usr/local/mysql/
chmod -R 755 /usr/local/mysql/data
chmod -R 755 /tmp/mysql/
批量挂载服务
chmod 755 /etc/init.d/mysql
cd /etc/init.d
chkconfig --add mysql
chkconfig --level 345 mysql on
ln -s /usr/local/mysql/bin/mysql /usr/bin
批量初始化
cd /usr/local/mysql
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
批量执行
service mysql restart
批量增加mysql权限
mysql -uroot -proot
GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';
SharingProxy——基本环节
随便选择一个节点,一般来讲,中间件和数据库都是不同的物理节点
下载
wget https://mirrors.tuna.tsinghua.edu.cn/apache/shardingsphere/4.1.0/apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz
安装
tar zxvf apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz
mv apache-shardingsphere-4.1.0-sharding-proxy-bin /usr/local/sharding-proxy
SharingProxy——配置/启动
config-sharding.yaml
# 逻辑数据库名
schemaName: sharding_db
# 真实数据库配置(注意,数据库需要手动先创建好)
dataSources:
ds_0:
url: jdbc:mysql://172.168.233.128:3306/db_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://172.168.233.129:3306/db_2?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
# 分片规则配置
shardingRule:
tables:
t_order:
# 数据元表达式(库+表的变量表达式)
actualDataNodes: ds_${0..1}.t_order_${0..1}
# 表的映射规则
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
# 库的映射规则
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
defaultTableStrategy:
none:
启动
cd /usr/local/sharding-proxy
./bin/start.sh
./bin/start.stop
日志
查看启动日志的错误
tail -n 1000 /usr/local/sharding-proxy/logs/stdout.log
SharingProxy——测试
利用navicat连接代理地址
172.168.233.128:3307
账号:sharding
密码:sharding
创建表
CREATE TABLE `t_order` (
`order_id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT,
`user_id` INT ( 11 ) NOT NULL,
`status` VARCHAR ( 50 ) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY ( `order_id` )
) ENGINE = INNODB AUTO_INCREMENT = 279205305122816001 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
插入数据
INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (11, 0, '2');
INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (12, 1, '2');
INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (13, 0, '2');
查看结果
SharingProxy——高可用(待更新)
SharingProxy——UI管理(待更新)
wget https://mirror.bit.edu.cn/apache/shardingsphere/shardingsphere-ui-4.1.0/apache-shardingsphere-4.1.0-shardingsphere-ui-bin.tar.gz
tar -zxvf apache-shardingsphere-4.1.0-shardingsphere-ui-bin.tar.gz
mv apache-shardingsphere-4.1.0-shardingsphere-ui-bin /usr/local/shardinUI