MySQL_主从复制读写分离

主从复制

概述

• 主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

• MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

• MySQL复制的有点主要包含以下三个方面：

  • 主库出现问题，可以快速切换到从库提供服务。

  • 实现读写分离，降低主库的访问压力。

  • 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

• 从上图来看，复制分为三步：

  • Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。

  • 从库读取主库的二进制日志文件Binlog，写入到从库中继日志Relay Log。

  • slave重做中继日志中的事件，将改变反映它自己的数据。

搭建

• 服务器准备

  • 开放指定的3306端口号：

    • firewail-cmd --zone=public --add-port=3306/tcp -permanent

    • firewail-cmd -reload

  • 关闭服务器的防火墙

    • systemctl stop firewalld

    • systemctl disable firewalld

  • 准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备工作。

• 主库配置

  • 修改配置文件/etc/my.cnf

  # mysql服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1，默认为1
  server-id=1
  # 是否只读；1代表只读，0代表读写
  read-only=0
  # 忽略的数据，只不需要同步的数据库
  # binlog-ignore-db=mysql
  # 指定同步的数据库 binlog-do-db=db01
  

  • 重启MySQL服务器

  systemctl restart mysqld
  

  • 登录mysql，创建远程连接的账号，并授予主从复制权限

  # 创建itcast用户，并设置密码，该用户可在任意主机连接MySQL服务
  create user ‘itcast’@'%'IDENTIFIED with mysql_native_password by 'Root@123456';
  # 为‘itcast’@'%'用户分配主从复制权限
  crant replication slave on *.* to 'itcast'@'%';
  

  • 通过指令，查看二进制日志坐标
    • 字段含义说明：
      • file:从哪个日志文件开始推送日文件
      • position：从哪个位置开始推送日志
      • binlog_ignore_db：指定不需要同步的数据库

  show master status;
  

• 从库配置

  • 修改配置文件/etc/my.cnf

  # mysql服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1,和主库不一样即可
  server-id=2
  # 是否只读；1代表只读，0代表读写
  read-only=1
  

  • 重启MySQL服务

  systemctl restart mysqld
  

  • 登录mysql，设置主库配置

  change replication source to source_host='xxx.xxx',source_user='xxx',
  source_password='xxx',source_log_file='xxx',source_log_pos=xxx;
  

  • 上述是8.0.23中的语法。如果mysql是8.0.23之前的版本，执行如下sql

  change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',
  master_log_file='xxx',master_log_pos=xxx;
  

  参数名	含义
  source_host	主库IP地址
  source_user	连接主库的用户名
  source_password	连接主库的密码
  source_log_file	binlog日志文件名
  source_log_pos	binlog日志文件位置

  • 开启同步操作

  start replica; # 8.0.22之后
  start slave; # 8.0.22之前
  

  • 查看主从同步状态

  show replica status; # 8.0.22之后
  show slave status; # 8.0.22之前
  

  读写分离

读写分离，简单地说是把对数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

通过MyCat即可轻易实现上述功能，不仅可以支持mysql，也可以支持Oracle和Sql Server。

一主一从

• 原理

  • MySQL的主从复制，是基于二进制日志（binlog）实现的。

    

一主一从读写分离

• 配置

  • MyCat控制后台数据库的读写分离和负载均衡有schema.xml文件datahost标签的balance属性控制

    

• balance配置参数

  参数值	含义
  0	不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
  1	全部的readHost与备用的writeHost都参与select语句的负载均衡（主要针对于双主双从模式）
  2	所有的读写操作都随机在writeHost，readHost上分发
  3	所有的读请求随机分发到writeHost对应的readHost上执行，writeHost不负担读压力。

双主双从

• 介绍

  • 一个主机Master1用于处理所有写请求，它的从机slave1和另一台主机Master2还有它的从机Slave2负责所有读请求。

  • 当Master1主机宕机后，Master2主机负责写请求，Master1、Master2互为备机。
    

• 搭建

  • 主库配置(m1/m2)

    • 修改配置文件/etc/my.cnf

    # mysql服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1,默认为1
    server-id=1
    # server-id=3 # m2
    # 指定同步的数据库
    binlog-do-db=db01
    binlog-do-db=db02
    binlog-do-db=db03
    # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    log-slave-updates
    

    • 重启MySQL服务器

     systemctl restart mysqld
    

    • 主库中创建账户并授权

    # 创建itcast用户，并设置密码，噶用户可以任意主机连接该MySQL服务
    create user 'itcast'@'%' identified with mysql_native_password by 'Root@123456'；
    # 为‘itcast’@'%'用户分配主从复制权限
    grant replication slave on *.* to 'itcast'@'%';
    

    • 通过指令，查看两台主库的二进制日志坐标

    show master status;
    

  • 从库配置（s1\s2）

    • 修改配置文件/etc/my.cnf

    server-id=2
    # server-id=4
    

    • 重启mysql服务器

    • 两台从库配置关联的主库（m2\s1\s2）

      master_log_file和master_log_pos可以通过在主机上执行show master status;获得。

      change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',
      master_log_file='xxx',master_log_pos=xxx;
      

    • 启动两台从库主从复制，查看从库状态

      start slave;
      show slave status \G;
      

      

双主双从读写分离

• 配置

  Mycat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制，通过writeType即switchType来完成失败自动切换的。

  • 参数说明：

    • balance=“1”

      • 代表全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式（M1->S1,M2->S2，并且M1与M2互为主备），正常情况下，M2,S1,S2都参与select语句的负载均衡。

    • writeType

      • 0：写操作都转发到第1台writeHost、WriteHost1挂了，会切换到WriteHost2上。

      • 1：所有的写操作都随机地发送到配置的writeHost上

    • switchType

      • -1:不自动切换

      • 1：自动切换