目录

一、知识介绍

1.MySQL主从复制原理

2.MySQL读写分离原理

二、资源清单

三、案例实施

1.修改主机名

2.搭建MySQL主从复制

3.搭建MySQL读写分离

一、知识介绍

1.MySQL主从复制原理

• MySQL支持的复制类型
  • 基于语句的复制
  • 基于行的复制
  • 混合模型复制
• 工作过程

  • 主（master）数据库启动bin二进制日志，这样会有一个Dump线程，这个线程是把主（master）数据库的写入操作都会记录到这个bin的二进制文件中。

  • 然后从（slave）数据库会启动一个I/O线程（监控主服务器的二进制日志的变化），这个线程主要是把主（master）数据库的bin二进制文件读取到本地，并写入到中继日志（Relay log）文件中。

  • 最后从（slave）数据库其他SQL线程，把中继日志（Relay log）文件中的事件再执行一遍，更新从（slave）数据库的数据，保持主从数据一致，重新写入数据库

2.MySQL读写分离原理

• 分类
  • 基于程序代码内部实现
    • 根据select、insert进行路由分配，在目前生产环境应用广泛。
    • 优点：性能较好，不需要增加额外的设备作为硬件开支
    • 缺点：需要开发人员来实现，运维人员无从下手
  • 基于中间代理层实现
    • 位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后数据
    • 代表性程序：
      • MySQL-Proxy：为MySQL开源项目，通过自带的lua脚本进行SQL判断
      • Amoeba：该程序由Java语言进行开发，用于生成环境，不支持事务和存储过程
      • MyCAT是一款开源的分布式关系型数据库中间件，主要用于解决大规模数据存储和高效查询的需求。它支持分布式SQL查询，兼容MySQL通信协议，能够通过数据分片提高数据查询处理能力。
• 工作过程

  • 读写分离就是只在主服务器上写，只在从服务器上读

  • 基本原理是让主数据库处理事务性查询（select，update，insert），而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。

二、资源清单

三、案例实施

hostnamectl set-hostname Master
hostnamectl set-hostname Slave1
hostnamectl set-hostname Slave2
hostnamectl set-hostname mycat

dnf install -y tar

tar zxf autoinstall-mysql.tar.gz

cd autoinstall-mysql
./start.sh
cd
source /etc/profile
mysql -uroot -p'临时密码'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql -uroot -p123456

dnf -y install jdk-8u171-linux-x64.rpm

• 解压软件包到/usr/local/，并重命名为”mycat“

  unzip mycat2-install-template-1.20.zip -d /usr/local
  ls /usr/local/mycat

• 把依赖包复制到指定目录

  cp mycat2-1.21-release-jar-with-dependencies.jar mysql-connector-java-8.0.18.jar /usr/local/mycat/lib

• 为Mycat命令添加执行权限

  chmod -R +x /usr/local/mycat/bin

• 添加环境变量

  echo 'export PATH=$PATH:/usr/local/mycat/bin' >>/etc/profile 
  source /etc/profile

• 验证安装正确性

  mycat -h

• 创建Mycat2工作所需要的账号（Master上）

  mysql -uroot -p123456
  
  create user 'mycat'@'%' identified by 'pwd123';
  grant all on *.* to 'mycat'@'%';
  ALTER USER 'mycat'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd123';
  flush privileges;

• 调整Mycat配置文件（Mycat上）

  vi /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
  
  {
          "dbType":"mysql",
          "idleTimeout":60000,
          "initSqls":[],
          "initSqlsGetConnection":true,
          "instanceType":"READ_WRITE",
          "maxCon":1000,
          "maxConnectTimeout":3000,
          "maxRetryCount":5,
          "minCon":1,
          "name":"prototypeDs",
          "password":"pwd123",
          "type":"JDBC",
          "url":"jdbc:mysql://192.168.16.142:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
          "user":"mycat",
          "weight":0
  }

  

• 启动Mycat服务（Master上）

  mycat start
  ss -tnl | grep 8066
  cat /usr/local/mycat/conf/users/root.user.json

• Mycat配置读写分离
  • 验证连接（Slave2上）

    mysql -uroot -p123456 -P8066 -h192.168.16.145

  • Mycat增加数据源（Slave2上）
    • 增加主库master

      /*+ mycat:createDataSource{ "name":"master","url":"jdbc:mysql://192.168.16.142:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"WRITE","user":"mycat","password":"pwd123"} */;

    • 增加从库slave1和slave2

      #增加slave1
      /*+ mycat:createDataSource{ "name":"slave1","url":"jdbc:mysql://192.168.16.143:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;
      #增加slave2
      /*+ mycat:createDataSource{ "name":"slave2","url":"jdbc:mysql://192.168.16.144:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;

    • 查看数据源信息

      /*+ mycat:showDataSources{} */\G
      
      #如果配置有误可使用重置：/*+ mycat:resetConfig{} */; 

  • 创建Mycat集群
    • 创建Mycat集群的SQL语句（Slave2上）

      /*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */;

    • 查看并修改集群配置（Mycat上）

      vi /usr/local/mycat/conf/clusters/cls01.cluster.json
      
              "readBalanceType":"BALANCE_ALL_READ",
              "balance": 1,

      

    • 修改负载均衡给策略为轮询（Mycat上）

      vi /usr/local/mycat/conf/server.json
      
      BalanceRoundRobin

      

    • 重启Mycat服务（Mycat上）

      mycat restart

• 登录mycat集群，创建测试库（客户端上）

  dnf -y install mariadb
  mysql -uroot -p123456 -P8066 -h192.168.16.145
  
  create database kgc;

• 修改配置文件（mycat上）

  cd /usr/local/mycat/conf/schemas
  vi kgc.schema.json 
  #添加 "targetName":"cls01",
  
  {
          "customTables":{},
          "globalTables":{},
          "normalProcedures":{},
          "normalTables":{},
          "schemaName":"kgc",
          "targetName":"cls01",
          "shardingTables":{},
          "views":{}
  }
  
  #重启服务
  mycat restart
  ss -tnl | grep 8066

• 登录mycat集群，创建测试表（客户端上）

  
  mysql -uroot -p123456 -P8066 -h192.168.16.145
  
  use kgc;
  create table kcs74(id int);

• 在master和slave1、slave2创建测试数据
  • master上

    mysql -uroot -p123456 
    
    use kgc;
    insert into kcs74 values(1);

  • slave1上

    mysql -uroot -p123456 
    
    use kgc;
    insert into kcs74 values(2);

  • slave2上

    mysql -uroot -p123456
    
    use kgc;
    insert into kcs74 values(3);

• 测试读操作（客户端上）

  mysql -uroot -p123456 -P8066 -h192.168.16.145
  
  select * from kgc.kcs74;

• 测试写操作（客户端上）

  mysql -uroot -p123456 -P8066 -h192.168.16.145
  use kgc;
  insert into kcs74 values(6);

• 测试（Master、Slave1、Slave2上）

  mysql -uroot -p123456
  
  use kgc;
  select * from kgc.kcs74;