Mysql主从复制；读写分离

最新推荐文章于 2023-02-11 11:41:43 发布
最新推荐文章于 2023-02-11 11:41:43 发布
阅读量210 收藏
点赞数
分类专栏： 学习笔记 文章标签： mysql 集成测试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_50965791/article/details/125031224
版权

为什么要主从复制、读写分离

        主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL，一台mater只负责写操作，两台salve只负责读操作，性能不就能大大提高了吗？

所以主从复制、读写分离就是为了数据库能支持更大的并发。

随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。
 

介绍

        MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的二进制日志功能。就是一台 或多台MySQL数据库(slave, 即从库)从另一台MySQL数据库(master, 即主库)进行日志的复制然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。 MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。 

原理

①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。

②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。

③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。

④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

 

Mysql复制分三步:

  •         master将改变记录到二进制日志(binary log )
  •         slave将master的binary log拷贝到它的中继日志(relay log)
  •         slave重做中继日志中的事件，将改变应用到自己的数据库中

（一）Mysql主从复制（1主1从）搭建

1.配置主库-Master

第一步：

       两台服务器，安装好Mysql

       修改Mysql数据配置文件：/etc/my.cnf

[mysqld]

## 设置server_id，同一局域网中需要唯一

server_id=100

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql

## 开启二进制日志功能

log-bin=mysql-bin

## 设置二进制日志使用内存大小（事务）

binlog_cache_size=1M  

## 设置使用的二进制日志格式（mixed,statement,row）

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0，表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

 第二步

重启Mysql

systemctl restart mysqld

第三步:登录Mysql数据库,执行下面SQL

GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';

        注:上面SQL的作用是创建一个用户 xiaoming,密码为Root@123456,并且给xiaoming用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。

第四步:登录Mysql数据库,执行下面SQL,记录下结果中File和Position的值

show master status;

 注:上面SQL的作用是查看Master的状态，执行完此SQL后不要再执行任何操作

配置从库

第一步:修改Mysq1数据库的配置文件/etc/my.cnf

[mysq1d]
server-id=101 #[必须]服务器唯一-ID

第二步：重启Mysql

systemctl restart mysqld

第三步:登录Mysq1数据库，执行下面SQL
 

change master to
master_host='192.168.138.100',master_user='xiaoming',master_password='Root@123456',master_log_file='mall_mysql-bin.000003',master_log_pos=439;
start slave;

第四步:登录Mysql数据库,执行下面SQL,查看从数据库的状态

show slave status;

（二）Docker实现Mysql（1主2从）主从复制

 1.新建主服务器3308

docker run -p 3308:3306 --name mysql-master \
-v /app/mysql/mysql-master/log:/var/log/mysql \
-v /app/mysql/mysql-master/data:/var/lib/mysql \
-v /app/mysql/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

2.进入 /app/mysql/mysql-master/conf目录新建my.cnf

[mysqld]

## 设置server_id，同一局域网中需要唯一

server_id=101

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能

log-bin=mall-mysql-bin  

## 设置二进制日志使用内存大小（事务）

binlog_cache_size=1M  

## 设置使用的二进制日志格式（mixed,statement,row）

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0，表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

        修改完后重启mysql-master 

docker restart mysql-master

3.进入mysql-master容器创建数据同步用户

docker exec -it mysql-master /bin/bash
mysql -uroot -p123456
CREATE USER 'slave'@'%' IDENTIFIED BY 'java@123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';

1.新建3309从服务器容器实例

docker run -p 3309:3306 --name mysql-slave1 \
-v /app/mysql/mysql-slave1/log:/var/log/mysql \
-v /app/mysql/mysql-slave1/data:/var/lib/mysql \
-v /app/mysql/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=654321 \
-d mysql:5.7

 2.进入 /app/mysql/mysql-slave1/conf目录新建my.cnf

[mysqld]

## 设置server_id，同一局域网中需要唯一

server_id=102

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用

log-bin=mall-mysql-slave1-bin  

## 设置二进制日志使用内存大小（事务）

binlog_cache_size=1M  

## 设置使用的二进制日志格式（mixed,statement,row）

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0，表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062  

## relay_log配置中继日志

relay_log=mall-mysql-relay-bin  

## log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1  

## slave设置为只读（具有super权限的用户除外）

read_only=1

        从启mysql-slave

docker restart mysql-slave1

        在主数据库mysql客户端查看主从同步状态（暂时不要关闭）

show master status;

3.进入从数据库3309配置主从复制

docker exec -it mysql-slave1 /bin/bash

mysql -uroot -p654321;

change master to master_host='192.168.8.100', master_user='slave', master_password='java@123', master_port=3308, master_log_file='mall-mysql-bin.000003', master_log_pos=154, master_connect_retry=30;


###
master_host：主数据库的IP地址；

master_port：主数据库的运行端口；

master_user：在主数据库创建的用于同步数据的用户账号；

master_password：在主数据库创建的用于同步数据的用户密码；

master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；

master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；

master_connect_retry：连接失败重试的时间间隔，单位为秒。

        在从数据库查看主从同步状态

show slave status \G;

 

         在从数据库开始主从复制，再次查看主从同步状态

start slave;

show slave status \G;

 1.新建3310从服务器容器实例

docker run -p 3310:3306 --name mysql-slave2 \
-v /app/mysql/mysql-slave2/log:/var/log/mysql \
-v /app/mysql/mysql-slave2/data:/var/lib/mysql \
-v /app/mysql/mysql-slave2/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

 2.进入 /app/mysql/mysql-slave2/conf目录新建my.cnf

[mysqld]

## 设置server_id，同一局域网中需要唯一

server_id=103

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用

log-bin=mall-mysql-slave2-bin  

## 设置二进制日志使用内存大小（事务）

binlog_cache_size=1M  

## 设置使用的二进制日志格式（mixed,statement,row）

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0，表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062  

## relay_log配置中继日志

relay_log=mall-mysql-relay-bin  

## log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1  

## slave设置为只读（具有super权限的用户除外）

read_only=1

 3.进入从数据库3310配置主从复制

docker exec -it mysql-slave2 /bin/bash

mysql -uroot -proot;

change master to master_host='192.168.8.100', master_user='slave', master_password='java@123', master_port=3308, master_log_file='mall-mysql-bin.000003', master_log_pos=154, master_connect_retry=30;


###
master_host：主数据库的IP地址；

master_port：主数据库的运行端口；

master_user：在主数据库创建的用于同步数据的用户账号；

master_password：在主数据库创建的用于同步数据的用户密码；

master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；

master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；

master_connect_retry：连接失败重试的时间间隔，单位为秒。

        开始主从复制

start slave;

show slave status \G;

读写分离

背景

        面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说,将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作,能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

 Sharding-JDBC介绍

        Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离。

  • 适用于任何基于JDBC的ORM框架，如: JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
  • 支持任何第三方的数据库连接池，如: DBCP, C3P0, BoneCP, Druid, HikariCP等。
  • 支持任意实现JDBC规范的数据库。目前支持MySQL, Oracle, SQL .Server, PostgreSQL以及任何遵循SQL92标准的数据库。
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

 配置文件

  server:
  port: 8080
spring:
  shardingsphere:
    datasource:
      names:
        master,slave
      # 主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.138.100:3306/rw?characterEncoding=utf-8
        username: root
        password: root
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.138.101:3306/rw?characterEncoding=utf-8
        username: root
        password: root
    masterslave:
      # 读写分离配置
      load-balance-algorithm-type: round_robin    #轮询
      # 最终的数据源名称
      name: dataSource
      # 主库数据源名称
      master-data-source-name: master
      # 从库数据源名称列表，多个逗号分隔
      slave-data-source-names: slave
    props:
      sql:
        show: true #开启SQL显示，默认false
  main:
    allow-bean-definition-overriding: true    #shareding需要创建数据源 druid也需要创建数据源，开启此配置，可覆盖数据源

mybatis-plus:
  configuration:
    #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: ASSIGN_ID

非余摆编程
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL主从复制 读写分离
06-24
经本人测试已经成功实现
MySQL主从复制读写分离
11-06
在数 据 管理 中，如果 对 MySQL数据库 的读 写操 作 都 在 同 一 个 数 据 库 服 务 器 中的 进 行 ，往 往 是 无 法 满足 实 际 需要 的 。 因为 ，这 样 做 不 仅 容 易 造 成 数 据 的 丢 失和 损 坏 ，而且 ...
mysqldump导出报错
xiexiest的博客
11-10 525
mysqldump
【mysql报错】ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘
weixin_59539033的博客
11-03 225
重新安装完美运行
sqlserver2022附加数据库时报错
进步一点点
10-07 1986
sqlserver2022附加时出现访问权限错误的解决办法
启动Mysql时，提示error 2002 的解决办法
weixin_33696822的博客
05-26 453
故障描述 启动时提示ERROR 2002（HY000） 启动服务时，提示the server quit without updating PID file. 解决方法一： 1。由于mysql是卸载后重装的，查看日志文件发现端口3306被占用。 2、查看哪个进程占用，把进程结束掉，重启服务。 # lsof -i :3306 # kill -9 4420 #4420为占...
MYSQL主从复制常见问题
weixin_39534345的博客
08-19 1187
一、主库挂了，怎么判断从库是否同步完成？ show slave status \G; 查看master_log_file和relay_master_log_file这两个文件是否相等，如果不相等，肯定有延迟，没有同步完，如果相等，再比较偏移量，如下： 查看exec_master_log_pos这个位置与read_master_log_pos这两个偏移量是否相等，不相等则不同步，相等则同步了...
MySQL主从复制读写分离.zip
03-14
MySQL主从复制与读写分离是数据库架构中的重要策略，用于提高系统性能和可用性。在高并发的互联网应用中，这种设计模式尤为常见。本文将深入探讨这两个概念及其实施方法。 **一、MySQL主从复制** MySQL主从复制是...
详解MySQL主从复制读写分离搭建
12-16
MySQL主从复制，读写分离的设置非常简单： 修改配置my.cnf文件 master 和 slave设置的差不多： [mysqld] log-bin=mysql-bin server-id=222 log-bin=mysql-bin的意思是：启用二进制日志。 server-id=222的意思是...
mysql 主从复制读写分离实现
07-26
mysql 主从复制读写分离实现
Windows下搭建MySQL Master Slave
weixin_33936401的博客
08-09 410
一、背景 服务器上放了很多MySQL数据库，为了安全，现在需要做Master/Slave方案，因为操作系统是Window的，所以没有办法使用keepalived这个HA工具，但是我们可以接受人工进行切换，有什么好的方案呢？   二、几种Master/Slave逻辑架构图 （Figure1：单Master-单Slave） （Figure2：单Master-多Slave） （F...
mysqldump: Error 2020
NULL
01-16 1961
前阵子在进行mysqldump数据导出的时候遇到了Error 2020的问题: mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `txt_blt_bas_txt` at row: 141529 这个报错是由于数据超出max_allowed_packet的限制导致的...
mysql8.0.18相关配置
D_pfei的博客
11-25 885
1.配置初始化my.ini文件 [mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=C:\Program Files\MySQL # 设置mysql数据库的数据的存放目录 datadir=C:\Program Files\MySQL\Data # 允许最大连接数 max_connections=200 # 允许连接失败的次数。 max_con...
半小时搞定mysql主从复制
chhichi的专栏
02-11 227
为了解决Mysql的单点故障提高MySQL的服务性能，一般采用主从复制。 通过异步复制，主从的数据保持一致性，当单台服务器压力比较大时，另一台服务器可以分担一部分压力。
Docker高级篇_mysql主从复制、redis集群
YiRenGengShangBuQi的博客
01-04 573
简单说，就是C挂了，受到影响的只是B、C之间的数据，并且这些数据会转移到D进行存储。当我们需要存储一个kv键值对时，首先计算key的hash值，hash(key)，将这个key使用相同的函数Hash计算出哈希值并确定此数据在环上的位置，从此位置沿环顺时针“行走”，第一台遇到的服务器就是其应该定位到的服务器，并将该键值对存储在该节点上。解决均匀分配的问题，在数据和节点之间又加入了一层，把这层称为哈希槽（slot），用于管理数据和节点之间的关系，现在就相当于节点上放的是槽，槽里放的是数据。
玩转MySQL：都2022年了，这些数据库技术你都知道吗
Java_LingFeng的博客
11-24 1693
MySQL数据库从1995年诞生至今，已经过去了二十多个年头了，到2022.04.26日为止，MySQL8.0.29正式发行了GA版本，在此之前版本也发生了多次迭代，发行了大大小小N多个版本，其中每个版本中都有各自的新特性，所有版本的特性加起来，用一本书的篇幅也无法完全阐述清楚，因此本章主要会挑重点特性来讲，具体各版本的特性可参考MySQL官网的开发手册： 《MySQL官网-5.6版手册》 《MySQL官网-5.7版手册》 《MySQL官网-8.0版手册》
mysql error 2002
m0_67390963的博客
04-21 5069
1.好几天没登陆mysql 了，登陆mysql时报错2002； 2、先查看 /etc/rc.d/init.d/mysqld status或者用ps -aux|grep mysql 看看mysql是否已经启动. 3.用service mysql start 启动mysql，启动失败 4.sudo service mysql restart 重启mysql,还是失败 5.sudo systemctl start mysql.service 使用系统控制启动还是失败 6.查看mysql log日志 7
master_log_file 及 master_log_pos的值
clcj66885的博客
05-12 6311
我们通过innobackupex 备份好数据之后，需要重新启动从库 此时需要设置master_log_file，master_log_pos change master to master_host='10.50.28.23...
2022最新超详MySQL（很适合初学者，基础篇）
热门推荐
Javascript_tsj的博客
04-26 1万+
一.Mysql下载安装启动（适合没有下载按照好MySQL的小伙伴，超详图文教程） 二.图形化界面工具（更方便操作数据库,超详细图文安装教程） 三.SQL（MySQL的核心）1.事务简介 2.事务操作 1.未控制事务 1). 测试正常情况 2). 测试异常情况 2.控制事务一 3.控制事务二 3.事务四大特性 4.并发事务问题 5.事务隔离级别...
mysql主从复制读写分离
最新发布
08-04
MySQL主从复制和读写分离是一种常用的数据库架构设计。主从复制是指将一个MySQL数据库服务器作为主服务器，将其他MySQL数据库服务器作为从服务器，通过复制主服务器上的数据和操作日志，实现数据的同步。读写分离是...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值