MySQL的主从复制

MySQL的主从复制

什么是主从复制

​ 介绍：

​ master（主服务器）

​ slave (从服务器)

​ mysql主从是异步复制过程

​ master开启bin-log功能，日志文件用于记录数据库的读写增删 ​ 需要开启3个线程，master IO线程，slave开启 IO线程 SQL线程， ​ Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。 ​ MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。 ​ slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。 ​ slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

准备工作

两台数据库，分布在不同主机上，相互可以通信。（相同版本数据库）

MySQL配置

主数据库配置 修改 my.cnf配置文件

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld]

# 开启bin-log日志 记录数据库的读写增删
log-bin=mysql-bin

# 设置服务id, 主从不能一致
server_id=1

# 设置需要同步的数据库
binlog-do-db=user_db

# 屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

从数据库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]

# binlog文件名
log-bin=mysql-bin
# 选择ROW 模式
binlog_format=ROW 
# 设置服务id 主从不能一致
server_id=2 

# 设置需要同步的数据库
replicate_wild_do_table=user_db.%
# 屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

主从搭建

1. 重启 主、从数据库

2. 在主数据新增用户，用于从数据库读取bin-log 日志

   -- 创建用户
   GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
   
   CREATE USER 'db_sync'@'%' IDENTIFIED BY  'db_sync'
   
   -- 授权
   GRANT ALL PRIVILEGES ON *.* TO 'db_sync'@'%' WITH GRANT OPTION
   
   -- 刷新权限
   FLUSH PRIVILEGES;
   
   -- 显示主数据库状态
   SHOW MASTER STATUS;
   

   

​

FIle	Posision	Binloh_Do_DB	Binloh_Ignore_DB	Executed_Gtid_Set
bin-log 日志文件名称	当前bin-log 已记录坐标	进行主从复制的数据库	已忽略不同步的数据库	事务集

3. 从数据库连接主数据库

   -- 停止同步策略
   STOP SLAVE;
   
   -- 与主数据库建立连接 
   CHANGE MASTER TO 
   MASTER_HOST = '172.25.3.199',  -- 主数据库IP
   MASTER_USER = 'db_sync',	   -- 访问用户名
   MASTER_PASSWORD = 'db_sync',   -- 访问密码
   MASTER_LOG_FILE = 'mysql-bin.000001', -- bin-log 文件名称
   MASTER_LOG_POS = 255615584;			-- bin-log日志坐标（决定从哪个坐标的记录开始复制）
   
   -- 启动从库 主从复制
   START SLAVE
   
   -- 显示从库状态
   SHOW SLAVE STATUS;
   

   

   | Mster_Host | Master_User | Master_Port | Master_Log_File | Read_Master_Log_pos | Slave_IO-Running | Slave_SQL_Runing | | ---------- | ------------ | ------------ | --------------- | ---------------------- | ---------------- | ----------------------- | | 主机IP | 访问主机用户 | 主数据库端口 | 日志文件名 | 已读取主机日志文件坐标 | 从机IO是否运行 | 从机读取日志SQL是否执行 |

4. 检查主从连接是否成功

   | Slave_IO-Running | Slave_SQL_Runing | | ---------------- | ---------------- | | YES | YES |

   **如： ** Slave_IO-Running 与 Slave_SQL_Runing 都为YES状态。则代表主从复制搭建成功

5. 主从复制不成功，检查

   • 排查主从数据库配置文件是否遗漏
   • 排查主数据库用户账号密码是否正确
   • 排查主数据BIN-LOG 是否已开启

填坑

​ 我在进行搭建主从复制数据库的时候遇到过这样一个问题，配置都是对的但是主从复制时 Slave_IO-Running 一直为NO 状态。在仔细检查了我的配置文件、数据库连接、防火墙、用户名密码、BIN-LOG 后发现都没有问题。最后的问题是：因为我的两台数据库所在的服务器是我使用镜像复制的。造成了两台数据库的 server-uuid 是相同的。

linux：/var/lib/mysql/auto.cnf

​ 解决办法就是：这行代码删掉，重启MYSQL。让它自己重新生成。

接入ShardingJDBC

创建maven工程，引入依赖

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

数据库，主从一致

properties配置

# sharingJDBC 垂直分表 专库专表
spring.shardingsphere.datasource.names=master0,slave0

# 一个实体类对应两张表 覆盖
spring.main.allow-bean-definition-overriding=true


# master数据源基础配置
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://81.68.101.110:3306/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=xxxxx

# slave数据源基础配置
spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://113.110.223.106:3306/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=xxxxx


# 主从库 配置
#spring.shardingsphere.masterslave.master-data-source-name=master0
#spring.shardingsphere.masterslave.slave-data-source-names=slave0

spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave0


# 配置公共表  公共表的策略,每个数据源中都有这个表 增删改 数据源同步
spring.shardingsphere.sharding.broadcast-tables=t_udict
# 指定主键 生成策略 SNOWFLAKE 雪花算法
spring.shardingsphere.sharding.tables.t_udict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_udict.key-generator.type=SNOWFLAKE

# 专库专表
#spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{1}.t_user
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user

# 指定主键 生成策略 SNOWFLAKE 雪花算法
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE


# 指定表主键, 表名称
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user



# 指定数据库分片策略 , 约定 user_id 为偶数 添加 m1 表, 奇数添加 m2 表
# 配置方式1 指定所有表 按此规则划分数据
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2}


# 配置方式 2 指定表 使用该策略
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}



# 查看底层 SQL 执行
spring.shardingsphere.props.sql.show=true

YAML 配置

spring:
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://119.215.145.270:3306/order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer1986.
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://119.215.145.271:3306/order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://114.205.165.208:3306/order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave节点
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡均衡策略，默认机制是：random_robin 如果你修改成随机RANDOM
      load-balance-algorithm-type: RANDOM

代码

// 写入数据
	@Test
    public void addUser(){
        User user = new User();
        user.setUserName("lisa").setUserStatus(1L);
        userMapper.insert(user);
    }

// 读取数据
    @Test
    public void findByUser(){

        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("user_id",718149835269603329L);

        User user = userMapper.selectOne(queryWrapper);

        System.out.println(user);

    }

测试结果

// 插入
Actual SQL: master0 ::: INSERT INTO t_user   (user_name, user_status, user_id) VALUES (?, ?, ?) ::: [b, 1, 718149835269603329]

// 读取
Actual SQL: slave0 ::: SELECT  user_id,user_name,user_status  FROM t_user 
User(userId=718149835269603329, userName=b, userStatus=1)