Mysql主从复制；读写分离

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

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台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标准的数据库。



    org.apache.shardingsphere
    sharding-jdbc-spring-boot-starter
    4.1.1

配置文件

  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

Mysql主从复制；读写分离

Java相关栏目本月热门文章