当前位置： 首页 > news >正文

22-09-04 西安 谷粒商城（01）MySQL主从复制、MyCat读写分离、MyCat分库分表

news 来源：原创 2024/5/16 9:58:17

人人尽说江南好，游人只合江南老。

春水碧于天，画船听雨眠。

MySQL主从复制

mysql主从复制：分摊读写压力（cpu计算压力）

写交给主库，读有主从分摊处理（原因是写操作较少，读操作较多）

1、主从复制原理图

2、搭建主库mysql_master

1.主库准备工作

1.拉取mysql5.7镜像

docker pull mysql:5.7

2.准备主库配置

# 创建主需要的配置目录
mkdir -p /etc/mysql/master
mkdir -p /etc/mysql/master/conf
mkdir -p /etc/mysql/master/data

# 给文件夹设置所有权限!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
chmod -R 777 /etc/mysql
# 创建主需要的配置文件my.cnf
vim /etc/mysql/master/conf/my.cnf

my.cnf内容如下：

[mysqld]
# 集群环境该节点的唯一的id值
server-id=1
# 主从复制：主有写操作时 主会记录写日志到该日志文件中
log-bin=master-bin
# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=mydb
# 设置需要复制的数据库 主会将该库的写操作记录到log-bin日志文件中
binlog-do-db=guli_oms
# 字符编码
character_set_server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-time_zone='+8:00'
# logbin日志文件的格式：statement->记录写sql(批量操作主从效率高,@@hostname之类系统变量获取主从执行时不一致) ,row->记录写后的数据(和前面相反) 
binlog_format=STATEMENT

3.创建mysql_master容器

docker run -d -p 3316:3306 \
--name mysql_master \
--privileged=true \
--restart=always \
-v /etc/mysql/master/data:/var/lib/mysql:rw \
-v /etc/mysql/master/conf/my.cnf:/etc/mysql/my.cnf:rw \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

4.远程连接主机

使用sqlyog分别连接docker启动的mysql主库：注意修改端口号，Master对外映射的端口号是3316

2.binlog日志三种格式

1.statement

statement->记录写sql(批量操作主从效率高,@@hostname之类系统变量获取主从执行时不一致)

2.row

row->记录写后的数据

3.mixed

可以根据有没有函数自动切换前俩种格式，但是它也识别不了系统变量，主机名字不一样就会造成主从不一致

-- "@@"首先标记会话系统变量，如果会话变量不存在，则标记全局系统变量
SELECT @@hostname;

系统变量  规范：以“@@”开头

启动MySQL服务，生成MySQL实例期间，MySQL将为MySQL服务器内存中的系统变量赋值。
这些系统变量定义了当前MySQL服务实例的属性、特征。

3.查看主mysql的状态

这个时候，要是你也能出的来效果就最好了，出不来也没事。。因为我们很多人都出不来，拷别人虚拟机的。。。

-- 查看主mysql的状态
SHOW MASTER STATUS

接入点：从机从接入点开始复制。之前主机发生的任何事情跟我从机无关，我只从接入这个点开始复制

3、搭建从库mysql_slave

搭建从库准备工作

1.准备从库配置

# 创建从需要的配置目录
mkdir -p /etc/mysql/slave/conf
mkdir -p /etc/mysql/slave/data

# 给文件夹设置所有权限!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
chmod -R 777 /etc/mysql

这里要再重写设置权限，不然sqlyog登录不上去。

# 创建从需要的配置文件my.cnf
vim /etc/mysql/slave/conf/my.cnf

my.cnf内容如下：

[mysqld]
server-id=2
relay-log=relay-bin
character_set_server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-time_zone='+8:00'

2.创建mysql_slave容器

docker run -d -p 3326:3306 \
--name mysql_slave \
--restart=always \
-v /etc/mysql/slave/data:/var/lib/mysql:rw \
-v /etc/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf:rw \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

3.远程连接主机

Slave对外映射的端口号是3326

4、建立主从关系

mysql用户可以分为普通用户和root用户，root用户是超级管理员，拥有所有权限。包括创建用户、删除用户、修改用户密码等管理权限；普通用户只拥有被授予的各种权限

1.主库创建slave账户

# 创建slave用户
CREATE USER 'slave01'@'%';
# 设置密码
ALTER USER 'slave01'@'%' IDENTIFIED BY '123456';
# 授权
GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%';
flush privileges;

在user表中

----------------------------------------------

2.从库配置主从关系

如果从机中搭建过主从，要重新设置主从

stop slave;

reset master

从库中执行sql

CHANGE MASTER TO 
 MASTER_HOST = '192.168.2.108',
 MASTER_PORT = 3316,
 MASTER_USER = 'slave01',
 MASTER_PASSWORD = '123456',
 MASTER_LOG_FILE = 'master-bin.000004',
 MASTER_LOG_POS = 13988;

master_port：主库的ip地址

master_port：主库的端口

master_user：用户名 

master_password：密码

master_log_file：主库查询的file项对应的值

master_log_pos：主库查询的的值

-- 查看主mysql的状态
SHOW MASTER STATUS

---------------------

3.开启主从复制

#启动slave同步
START SLAVE;
#查看同步状态
SHOW SLAVE STATUS;

主从搭建成功，如下：

------------------

4.测试主从

主库中创建guli_oms,从库也会自动的跟着创建该数据库。创建别的库，从库不会跟的，因为主库的my.conf配置过。。SHOW MASTER STATUS也看到过

在主库中的guli_oms中创建表，从库也会跟着。达到主从复制的目的

MyCat读写分离

通过MyCat和MySQL的主从复制配合搭建数据库的读写分离，实现MySQL的高可用性。

java项目连接数据库连接mycat，sql交给mycat接收，由它根据配置好的参数，判断读写应该交给哪个数据库来处理。

大体就是下面这个样子

<writeHost host="hostM1" url="jdbc:mysql://192.168.1.171:3316" user="root"
		   password="123456">
     <readHost host="hostS1" url="jdbc:mysql://192.168.1.171:3326" user="root" 
               password="123456" />
</writeHost>

1、安装MyCat

把压缩包ftp上传到/opt目录下并解压（压缩包我也是老师的资料中给的。。。）

tar -zxvf /opt/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz

解压后的mycat/conf目录下存放的是它的配置文件：

其中核心的配置有3个：


server.xml：配置MyCat作为虚拟数据库的信息（地址、数据库名、用户名、密码等信息）

mycat启动时初始化的配置

schemal.xml：mycat代理的真实数据库的信息，实现读写分离   

mycat通过逻辑库 将真实数据库表加载到该逻辑库中 展示给连接mycat的用户

rule.xml：分库分表规则配置 

2、MyCat初步认识

重要：mycat本身不存储数据，只是在中间做一些业务逻辑

1、mycat是我们的程序与mysql数据库之间的桥梁，我们的请求不再直接到达数据库，而是到达mycat（解耦）

2、mycat中需要配置好主库从库关系，mycat根据配置决定读和写的去向。我们的Jdbc连接是连接mycat，程序代码没有任何变化。

3、mycat基本原理就是拦截转发

它拦截用户发送过来的SQL语句，对SQL做了一些特定的分析后，将此SQL发送到真实的数据库。

3、MyCat常用命令

进入 mycat/bin目录：cd /opt/mycat/bin

启动： ./mycat start

控制台启动： ./mycat console

停止： ./mycat stop

重启： ./mycat restart

状态： ./mycat status

日志文件：mycat/logs/wrapper.log

4、schema.xml 配置读写分离

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
        <table name="oms_order,oms_order_item,oms_order_operate_history,oms_order_return_apply,oms_order_return_reason,oms_order_setting,oms_refund_info" dataNode="dn1"  />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="guli_oms" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
                  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.2.108:3316" user="root"
                           password="123456">
                <readHost host="hostS1" url="jdbc:mysql://192.168.2.108:3326" user="root" password="123456" />
        </writeHost>
</dataHost>

writeHost和readHost分别指定写和读，balance=0的话 代表不使用读写分离

启动mycat

 启动失败的话，查看

tail -30 /opt/mycat/logs/wrapper.log

测试mycat读写分离

1.sqlyog连接mycat

2.插入SQL，再读取

INSERT INTO  oms_order_operate_history
VALUES('2' , '10011' , '9528' , NOW() ,0 , @@hostname);


SELECT * FROM oms_order_operate_history;

balance=2的情况下，很不幸我他喵的都是从主库中读的

修改一下schema.xml 中balance=4。重启mycat

很遗憾，还是没有读写分离。。。 

分库分表

ali开发手册

1、分库分表准备工作

1.在mysql_mycat库中删除上面的测试数据。

DELETE FROM oms_order_operate_history;

2.创建新的容器mysql_master2

# 创建主2需要的配置目录
mkdir /etc/mysql/master2
mkdir /etc/mysql/master2/conf
mkdir /etc/mysql/master2/data

# 设置权限
chmod -R 777 /etc/mysql/

# 创建主需要的配置文件my.cnf
vim /etc/mysql/master2/conf/my.cnf

my.cnf内容如下：

[mysqld]
server-id=3
log-bin=master-bin
# 设置不要复制的数据库
binlog-ignore-db=mysql
# 设置需要复制的数据库
binlog-do-db=guli_oms
character_set_server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-time_zone='+8:00'
binlog_format=STATEMENT

创建容器mysql_master2启动：

docker run -d -p 3336:3306 \
--name mysql_master2 \
--privileged=true \
--restart=always \
-v /etc/mysql/master2/data:/var/lib/mysql:rw \
-v /etc/mysql/master2/conf/my.cnf:/etc/mysql/my.cnf:rw \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

sqlyog连接

在该数据库中导入guli_oms整个库

注意：3316/3326/3336 三个mysql的guli_oms库中的表都必须是空的

2、数据分片

 数据分片是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式：

1.垂直（纵向）切分：是按照不同的表来切分到不同的数据库（主机）之上

将原本一个库中的多张表存到多个不同的数据库（主机），将一个库的读写压力分摊到多个库中，分布式每个微服务只连接自己的数据库，已经实现。

 2.水平（横向）切分：是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）的多张表中，这些多张表结构一样，多张表数据组合起来代表所有的数据。

分表可以减少单表数据量，查询时提高速度

逻辑库：在mycat中的数据库，但是真实物理不存在。

将多个库中的表合并到逻辑库中，项目中只要连接mycat就可以操作所有表

逻辑表：逻辑库中包含多个逻辑表，逻辑表可以映射mysql真实存在的物理表，逻辑表和物理表可 以一多一，也可以一对多（数据分片）

将拆分的多张表数据合并到一张逻辑表中，增删改查（指定条件查询）时，mycat可以根据rule规则快速判断到哪张表中操作数据

3、mycat配置分表

1.在rule.xml中配置分表规则

<tableRule name="oms_order_rule">
    <rule>
        <columns>user_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">2</property>
</function>

2.在schema.xml中新增配置

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
    <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
    <!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
    <table name="oms_order_return_reason,oms_order_setting,oms_refund_info" dataNode="dn1"  />                
    <table name="oms_order" dataNode="dn1,dn2" fetchStoreNodeByJdbc="true" rule="oms_order_rule"  >
    </table>
</schema>

<dataNode name="dn2" dataHost="localhost2" database="guli_oms" />

<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->                
    <writeHost host="hostM2" url="jdbc:mysql://192.168.1.171:3336" user="root"
                                                                  password="123456">
    </writeHost>
    <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

重启mycat，测试分库分表

INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('1' , '1001' ,'1' , NOW() , 'zhangsan');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('2' , '1002' ,'2' , NOW() , 'zhaosi');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('3' , '1003' ,'3' , NOW() , 'wangwu');
INSERT INTO oms_order(id,user_id,order_sn,create_time,username)
VALUES('4' , '1004' ,'4' , NOW() , 'qianliu');



INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('1' ,'2' , '小米手机1');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('2' ,'4' , '小米手机2');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('3' ,'1' , '小米手机3');
INSERT INTO oms_order_item(id,order_id,spu_name)
VALUES('4' ,'3' , '小米手机4');

测试联查

SELECT *
FROM oms_order t1
LEFT JOIN oms_order_item t2
ON t1.id = t2.order_id;

4、跨库join问题

两个数据库再同一台主机上可以做关联查询;两张表在不同的主机上是不能做join关联查询的

解决思路：子表的记录与所关联的父表记录存放在同一个数据分片上

在schema.xml这解决的，设置了ER表，ER表的分片字段就是那个外键“order_id”

<table name="oms_order" dataNode="dn1,dn2" fetchStoreNodeByJdbc="true" rule="oms_order_rule"  >
    <!--name: 定义子表的表名；primaryKey：子表的主键；
     joinKey：与父表建立关联关系的列名；parentKey>：父表的主键名-->
    <childTable name="oms_order_item" primaryKey="id" joinKey="order_id" parentKey="id" />
    <childTable name="oms_order_return_apply" primaryKey="id" joinKey="order_id" parentKey="id" />
    <childTable name="oms_order_operate_history" primaryKey="id" joinKey="order_id" parentKey="id" />

</table>

ER表：指父子表中的子表，该表依赖于别的另外一张表。比如订单详情表（子表）和订单表 （父表）

相关文章：

  • 猿创征文｜Python3，10分钟写了一个WIFI 万(破) 能 (解) 钥 (神) 匙 (器)，YYDS。
  • 【每日一练】图解：链表内指定区间反转
  • Java 进阶多线程(一)
  • Softing物联网（IoT）方案之OT/IT数据集成
  • 第13讲：DCL类型的SQL语句之用户管理
  • Python实战回归模型-消费者人群画像-信用智能评分（基于中国移动用户数据）
  • 【尚硅谷】MyBatis
  • 用动图详细讲解——栈
  • Spring事务传播性
  • Pinia不就是Vuex5？
  • Scrapy + selenium + 超级鹰验证码识别爬取网站
  • 【高等数学基础进阶】常微分方程-part1
  • AI学习教程：AI(Adobe lliustrator)快速入门
  • lombok中使用@Accessors和@Builder的区别
  • 【Vue瀑布流布局】vue简单实现瀑布流布局，循环数据自动排列到最短的列上【详细注释，直接使用】
  • android百种动画侧滑库、步骤视图、TextView效果、社交、搜房、K线图等源码
  • centos安装java运行环境jdk+tomcat
  • Create React App 使用
  • el-input获取焦点 input输入框为空时高亮 el-input值非法时
  • emacs初体验
  • express.js的介绍及使用
  • extjs4学习之配置
  • JavaScript创建对象的四种方式
  • Mac 鼠须管 Rime 输入法 安装五笔输入法 教程
  • oldjun 检测网站的经验
  • python学习笔记－类对象的信息
  • Redis学习笔记 - pipline（流水线、管道）
  • spring cloud gateway 源码解析（4）跨域问题处理
  • SpringCloud集成分布式事务LCN (一)
  • Spring框架之我见（三）——IOC、AOP
  • Xmanager 远程桌面 CentOS 7
  • 订阅Forge Viewer所有的事件
  • 思维导图—你不知道的JavaScript中卷
  • 腾讯优测优分享 | 你是否体验过Android手机插入耳机后仍外放的尴尬？
  • 小程序开发之路（一）
  • 学习笔记TF060:图像语音结合，看图说话
  • 一道闭包题引发的思考
  • 一文看透浏览器架构
  • 责任链模式的两种实现
  • ionic异常记录
  • #include＜初见C语言之指针（5）＞
  • $ git push -u origin master 推送到远程库出错
  • (04)odoo视图操作
  • （2）(2.10) LTM telemetry
  • （LeetCode C++）盛最多水的容器
  • （二）丶RabbitMQ的六大核心
  • （分布式缓存）Redis哨兵
  • （附源码）spring boot基于小程序酒店疫情系统 毕业设计 091931
  • （亲测有效）解决windows11无法使用1500000波特率的问题
  • （四） Graphivz 颜色选择
  • （转）linux 命令大全
  • （转）Scala的“=”符号简介
  • （转）淘淘商城系列——使用Spring来管理Redis单机版和集群版
  • （最简单，详细，直接上手）uniapp/vue中英文多语言切换
  • .NET C# 使用 SetWindowsHookEx 监听鼠标或键盘消息以及此方法的坑