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

Mysql主从复制到分库分表再到读写分离

backend 2025/7/1 10:48:11
docker run --rm -p 8090:80 -d --name mywebsql rizo928/mywebsql-flex
一.主从复制
1.创建docker环境
2.拉取mysql镜像 最新版本
3.创建对应的映射目录和配置文件
[mysqld]
port=3306
datadir=/var/lib/mysql/data
max_connections=200
max_connect_errors=10
character-set-server=utf8
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
default-time-zone='+08:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#Master1
server-id=1
read-only=0
[mysql]
default-character-set=utf8
[client]
default-character-set=utf8
4.启动镜像执行命令（一主一从）
docker network create -d bridge mysql
第一组：
docker run --restart=always -itd --name mysql_master3306  --network mysql  -p 3306:3306 -v /home/mysqlMaster/log/:/var/log -v /home/mysqlMaster/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlMaster/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
docker run --restart=always -itd --name mysql3306 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
docker exec -it mysql_master3306 /bin/bash
docker run --restart=always -itd --name  mysql_slaver3307  --network mysql  -p 3307:3306 -v /home/mysqlSlave/log/:/var/log -v /home/mysqlSlave/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlSlave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
docker exec -it mysql_slaver3307 /bin/bash
第二组：
docker run --restart=always -itd --name mysql_master3308  --network mysql  -p 3308:3306 -v /home/mysqlMaster1/log/:/var/log -v /home/mysqlMaster1/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlMaster1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
docker run --restart=always -itd --name mysql_slaver3309  --network mysql  -p 3309:3306 -v /home/mysqlSlave1/log/:/var/log -v /home/mysqlSlave1/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlSlave1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
第三组：
docker run --restart=always -itd --name mysql_master3310  --network mysql  -p 3310:3306 -v /home/mysqlMaster2/log/:/var/log -v /home/mysqlMaster2/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlMaster2/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
docker run --restart=always -itd --name  mysql_slaver3311  --network mysql  -p 3311:3306 -v /home/mysqlSlave2/log/:/var/log -v /home/mysqlSlave2/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysqlSlave2/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=121314@wq mysql:latest
5.在Master服务中给Slaver创建一个账号并赋予可以复制的权限
CREATE USER 'slaver_user'@'%'IDENTIFIED WITH mysql_native_password BY '123456@wq’;
CREATE USER ‘root'@'%'IDENTIFIED WITH mysql_native_password BY '123456@wq';
GRANT REPLICATION SLAVE ON *.* TO 'slaver_user'@'%'；
6 在Master服务中查看二进制文件的复制节点
show master status;
7.查看Master节点数据的ip地址
docker inspect mysql_master3306
8.在Slaver节点中 绑定与Master主节点的关系并开启复制
docker inspect  mysql_slaver3307
CHANGE REPLICATION SOURCE TO SOURCE_HOST='mysql_master3310',SOURCE_USER='slaver_user',SOURCE_PASSWORD='123456@wq',SOURCE_LOG_FILE='binlog.000002',SOURCE_LOG_POS=674;
start replica;
9.检查是否操作成功
show replica status\G;
如果操作失误可以重新操作第8-9步骤，前提是先执行以下命令
stop slave;
reset master;
10.测试
CREATE TABLE t1(id int not null, name char(20));
二.分库分表之 Mycat入门
ALTER  USER  'root'  IDENTIFIED  WITH  mysql_native_password  BY  '121314@wq’;
Mycat水平分库分别
<?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="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<!--指定master的数据库-->
<dataNode name="dn1" dataHost="dhost1" database="db01"/>
<dataNode name="dn2" dataHost="dhost2" database="db01"/>
<dataNode name="dn3" dataHost="dhost3" database="db01"/>
<!--指定mastet的ip -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" >
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc: mysql://mysql_master3306:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="121314@wq">
</writeHost>
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" >
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc: mysql://mysql_master3308:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="121314@wq">
</writeHost>
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" >
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc: mysql://mysql_master3310:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="121314@wq">
</writeHost>
</dataHost>
</mycat:schema>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat=" http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">64k</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">false</property>
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
</system>
<user name="root" defaultAccount="true">
<property name="password">121314@wq</property>
<property name="schemas">DB01</property>
</user>
<user name="user">
<property name="password">121314@wq</property>
<property name="schemas">DB01</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
rule.xml分片规则文件：mycat的默认分片规则auto-sharding-long
测试：进入一个mysql容器里面：执行命令：
docker exec -it mysql3309 /bin/bash
mysql -h 172.17.0.6 -P 8066 -u root -p
Server version: 5.6.29-mycat-1.6.6.1-release
数据ID为0-500万的数据是存放在第一个节点中。
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(1,'wangqiang1',18);
INSERT INTO  TB_ORDER (id,`name`,age)VALUES(5000000,'wangqiang1',18);
数据ID为500万-1000万的数据存放在第二个节点中
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(5000001,'wangqiang1',18);
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(10000000,'wangqiang1',18);
数据ID为1000万-15000万的数据存放在第二个节点
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(10000001,'wangqiang1',18);
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(15000000,'wangqiang1',18);
ID超过15000万的数据无法存储。
INSERT INTO 
TB_ORDER(id,`name`,age)VALUES(15000001,'wangqiang1',18);
1 下载mycat压缩包
wget https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
2 创建mycat镜像Dockerfile
FROM java:8
MAINTAINER wayne
ADD mycat mycat
EXPOSE 8066 9066
ENTRYPOINT ["./mycat/bin/mycat","console"]
3 执行创建镜像命令
docker build -t mycat:latest .
4 启动mycat容器
docker run -itd --name mycat- horizontal   --network mysql  -v /home/mycat/schema.xml:/mycat/conf/schema.xml -v /home/mycat/server.xml:/mycat/conf/server.xml --privileged=true -p 8066:8066 -p 9066:9066  mycat-init:latest
5 在数据mysql容器内部执行：链接mycat 
mysql -h mycat -P 8066 -u root -p
6 创建虚拟表和给虚拟表添加数据
CREATE TABLE TB_ORDER(
id INT PRIMARY KEY AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL,
age INT NOT NULL
);
INSERT INTO TB_ORDER(id,`name`,age)VALUES(1,'wangqiang1',18);
INSERT INTO TB_ORDER(id,`name`,age)VALUES(2,'wangqiang2',19);
INSERT INTO TB_ORDER(id,`name`,age)VALUES(3,'wangqiang3',20);
INSERT INTO TB_ORDER(id,`name`,age)VALUES(4,'wangqiang4',21);
7 检查是否同步到其他库
分库分表之Mycat垂直分库分表
schema.xml:没有涉及到rule.  xml因为只有分表数据才会用到规则
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat=" http://io.mycat/ ">
    <schema name="DB02" checkSQLschema="true" sqlMaxLimit="100">
         <!--分库就无需分片规则 改为主键-->
        <table name="TB_ORDER" dataNode="dn1" primaryKey="id" />
        <table name="TB_USER" dataNode="dn1" primaryKey="id" />
        <table name="TB_REGION" dataNode="dn2" primaryKey="id" />
        <table name="TB_GOODS" dataNode="dn2" primaryKey="id" />
        <table name="TB_PAY" dataNode="dn3" primaryKey="id" />
        <table name="TB_LOG" dataNode="dn3" primaryKey="id" />
    </schema>
    <!--指定master的数据库-->
    <dataNode name="dn1" dataHost="dhost1" database="db02"/>
    <dataNode name="dn2" dataHost="dhost2" database="db02"/>
    <dataNode name="dn3" dataHost="dhost3" database="db02"/>
   <!--指定mastet的ip -->
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" >
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc: mysql://mysql_master3306:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
        </writeHost>
    </dataHost>
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" >
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc: mysql://mysql_master3308:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
        </writeHost>
    </dataHost>
    <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" >
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc: mysql://mysql_master3310:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
        </writeHost>
    </dataHost>
</mycat:schema>
server.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat=" http://io.mycat/ ">
    <system>
        <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
    </system>
    <user name="root" >
        <property name="password">121314@wq</property>
        <!--逻辑数据库名-->
        <property name="schemas">DB02</property>
    </user>
    <!--只读用户-->
    <user name="user">
        <property name="password">121314@wq</property>
        <property name="schemas">DB02</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>
docker run -itd --name mycat- vertical   --network mysql  -v /home/mycat/vertical/ schema.xml:/mycat/conf/schema.xml -v /home/mycat/vertical /server.xml:/mycat/conf/server.xml --privileged=true -p 8077:8066 -p 9077:9066  mycat-init:latest
三.读写分离
docker run -itd --name mycat-divide  --network mysql -v /home/mycat/ readwrite /schema.xml:/mycat/conf/schema.xml -v /home/mycat/ readwrite /server.xml:/mycat/conf/server.xml --privileged=true -p 8077:8066 -p 9077:9066  mycat-init:latest
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat=" http://io.mycat/ ">
    <schema name="DB02" checkSQLschema="true" sqlMaxLimit="100">
         <!--分库就无需分片规则 改为主键-->
        <table name="TB_ORDER" dataNode="dn1" primaryKey="id" />
        <table name="TB_USER" dataNode="dn1" primaryKey="id" />
        <table name="TB_REGION" dataNode="dn2" primaryKey="id" />
        <table name="TB_GOODS" dataNode="dn2" primaryKey="id" />
    </schema>
    <!--指定master的数据库-->
    <dataNode name="dn1" dataHost="dhost1" database="db02"/>
    <dataNode name="dn2" dataHost="dhost2" database="db02"/>
   <!--指定mastet的ip -->
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" >
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc: mysql://mysql_master3306:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
            <readHost host="slaver" url="jdbc: mysql://mysql_slaver3307:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
            </readHost>
        </writeHost>
    </dataHost>
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" >
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc: mysql://mysql_master3308:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
            <readHost host="slaver" url="jdbc: mysql://mysql_slaver3309:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8 " user="root" password="121314@wq">
            </readHost>
        </writeHost>
    </dataHost>
</mycat:schema>
<?xml version="1.0"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat=" http://io.mycat/ ">
    <system>
        <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
    </system>
    <user name="root" >
        <property name="password">121314@wq</property>
        <!--逻辑数据库名-->
        <property name="schemas">DB02</property>
    </user>
    <!--只读用户-->
    <user name="user">
        <property name="password">121314@wq</property>
        <property name="schemas">DB02</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>
http://www.xdnf.cn/news/3129.html

相关文章：

  • 详解操作系统是如何管理计算机软硬件资源的，以及Linux中进程状态的观察与解释
  • 串口驱动打印下载官网
  • AimRT 从零到一：官方示例精讲 —— 二、HelloWorld示例.md
  • OpenCV-Python (官方)中文教程(部分一)_Day18
  • UVA1537 Picnic Planning
  • transform-实现Encoder 编码器模块
  • NFS-网络文件系统
  • 【codeforces 2086d】背包+组合数学
  • Java之BigDecimal
  • 杭电oj（1015、1016、1072、1075）题解
  • 在线文章系统自动化测试报告
  • MIT6.S081-lab7前置
  • 免费超好用的电脑操控局域网内的手机（多台，无线）
  • Leetcode 3530. Maximum Profit from Valid Topological Order in DAG
  • CSS：编写位置分类及优先级
  • 从Markdown到专业文档：如何用Python打造高效格式转换工具
  • Qwen3-8B安装与体验-速度很快！
  • Yaml文件
  • 数字逻辑--期末大复习
  • 激光雷达点云去畸变
  • ctf.show 卷王杯 pwn签到
  • DDI0487--A1.7
  • onlyoffice部署
  • Ignoring query to other database
  • Elasticsearch：ES|QL lookup JOIN 介绍 - 8.18/9.0
  • STP学习
  • 排序版研究方向
  • docker部署的Nextcloud，处于维护模式，如何解决
  • 华为自研的仓颉编程语言介绍
  • Qwen3 系列的后训练技术