MySQL数据库之读写分离
学习目标
1、了解mysql的读写分离的概念
2、掌握mycat读写分离的部署
一、数据库分类
数据库主要分为两大类：关系型数据库与 NoSQL 数据库。
关系型数据库，是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。主流的 MySQL、Oracle、MS SQL Server 和 DB2 都属于这类传统数据库。
NoSQL 数据库，全称为 Not Only SQL，意思就是适用关系型数据库的时候就使用关系型数据库，不适用的时候也没有必要非使用关系型数据库不可，可以考虑使用更加合适的数据存储。主要分为临时性键值存储（memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）面向列的数据库（Cassandra、HBase），每种 NoSQL 都有其特有的使用场景及优点。
Oracle，mysql 等传统的关系数据库非常成熟并且已大规模商用，为什么还要用 NoSQL 数据库呢？主要是由于随着互联网发展，数据量越来越大，对性能要求越来越高，传统数据库存在着先天性的缺陷，即单机（单库）性能瓶颈，并且扩展困难。这样既有单机单库瓶颈，却又扩展困难，自然无法满足日益增长的海量数据存储及其性能要求，所以才会出现了各种不同的 NoSQL 产品，NoSQL 根本性的优势在于在云计算时代，简单、易于大规模分布式扩展，并且读写性能非常高
RDBMS和NOSQL的特点及优缺点：
二、MySQL读写分离
2.1 什么是MySQL的读写分离？
Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba/Mycat）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。
2.2 MySQL读写分离的原理
基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
2.3 内部数据交换过程
通过部署MySQL主从同步集群，当有业务请求过来，会先进行语句分析，如果是查询的语句，会转发给从服务器进行处理，如果是写的数据，则转交给主服务器进行处理，在一般的网站上，绝大部分的请求是读请求要大于写的请求的，因此为了缓解从服务器的压力，因此可以部署成M-S-S的模式。
2.4 为什么要做读写分离？
面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载
1、 主从只负责各自的写和读，极大程度的缓解X锁（排它锁）和S锁（共享锁）争用 2、 从库可配置myisam引擎，提升查询性能以及节约系统开销 3、 增加冗余，提高可用性 4、应用程序和数据库强耦合
2.5 实现读写分离的方式
一般有两种方式实现：
1）应用程序层实现，网站的程序实现
应用程序层实现指的是在应用程序内部及连接器中实现读写分离
优点： 1：应用程序内部实现读写分离，安装既可以使用 2：减少一定部署难度 3：访问压力在一定级别以下，性能很好 缺点： 1：架构一旦调整，代码要跟着变 2：难以实现高级应用，如自动分库，分表 3：无法适用大型应用场景
2）中间件层实现
中间件：是一类连接软件组件和应用的计算机软件，以便于软件之间各部分之间的沟通
三、MYCAT
3.1 什么是mycat？
1）一个彻底开源的，面向企业应用开发的大数据库集群 2）支持事务、ACID、可以替代MySQL的加强版数据库 3） 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 4）一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 5）结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 6）一个新颖的数据库中间件产品
3.2 mycat的主要功能
3.2.1 mycat读写分离
3.2.2 分库分表
简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。
3.2.3 多数据源整合
MySQL中间件服务器可以通过将数据切分解决传统数据库的缺陷，又有了 NoSQL 易于扩展的优点。通过中间代理层规避了多数 据源的处理问题，对应用完全透明，同时对数据切分后存在的问题，也做了解决方案。
3.3 mycat原理
Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
3.4 部署读写分离（一主一从架构）
| 角色 | IP | 数据库版本 | 操作系统版本 |
|---|---|---|---|
| mycat | 192.168.1.101 | 无 | CentOS7.9 |
| master主机 | 192.168.1.102 | mysql5.7.36 | CentOS7.9 |
| slave从机 | 192.168.1.103 | mysql5.7.36 | CentOS7.9 |
MyCAT有提供编译好的安装包，支持windows、Linux、Mac、Solaris等系统上安装与运行。
官方下载主页：http://www.mycat.org.cn/
3.4.1 安装JDK 1.7 或者以上版本
[root@mycat ~]# tar zxvf jdk-8u311-linux-x64.tar.gz -C /usr/local/src/
[root@mycat ~]# vim /etc/profile #添加如下内容 JAVA_HOME=/usr/local/src/jdk1.8.0_311 JAVA_BIN=$JAVA_HOME/bin JRE_HOME=$JAVA_HOME/jre JRE_BIN=$JRE_HOME/bin PATH=$JAVA_BIN:$JRE_BIN:$PATH CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib export JAVA_HOME JRE_HOME PATH CLASSPATH
[root@mycat ~]# source /etc/profile [root@mycat ~]# java -version java version "1.8.0_311" Java(TM) SE Runtime Environment (build 1.8.0_311-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.311-b11, mixed mode)
3.4.2 部署Mycat
[root@mycat ~]# tar zxvf Mycat-server-1.6.7.6-test-20201124193646-linux.tar.gz -C /usr/local/
linux下可以下载Mycat-server-xxxxx.linux.tar.gz 解压在某个目录下，注意目录不能有空格，在Linux(Unix)下，建议放在usr/local/Mycat目录下，如下：
[root@mycat mycat]# ll total 12 drwxr-xr-x. 2 root root 190 Jun 15 14:36 bin drwxrwxrwx. 2 root root 6 Jul 18 2019 catlet drwxrwxrwx. 4 root root 4096 Jun 15 14:36 conf drwxr-xr-x. 2 root root 4096 Jun 15 14:36 lib drwxrwxrwx. 2 root root 6 Aug 28 2019 logs -rwxrwxrwx. 1 root root 227 Aug 28 2019 version.txt
3.4.2.1 常见目录含义
bin 程序目录：存放了window版本和linux版本，除了提供封装成服务的版本之外，也提供了nowrap的shell脚本命令，方便大家选择和修改，进入到bin目录
Linux下运行：./mycat console,首先要chmod +x *
注：mycat支持的命令{ console | start | stop | restart | status | dump }
conf目录：存放配置文件，server.xml是Mycat服务器参数调整和用户授权的配置文件，schema.xml是逻辑库定义和表以及分片定义的配置文件，rule.xml是分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下，配置文件修改，需要重启Mycat或者通过9066端口reload
lib目录：主要存放mycat依赖的一些jar文件.
log目录：每天一个文件，日志的配置是在conf/log4j.xml中，根据自己的需要，可以调整输出级别为debug，debug级别下，会输出更多的信息，方便排查问题.
3.4.2.2 mycat的几个配置文件
1）schema.xml:定义逻辑库，表，分片节点等内容 2）rule.xml:定义分片规则 3）server.xml:定义用户以及系统相关的变量，如端口，账号和密码等
3.4.2.3 配置环境变量
Mycat在Linux中部署启动时，首先需要在Linux系统的环境变量中配置MYCAT_HOME,操作方式如下：
1) vim /etc/profile,在系统环境变量文件中增加 MYCAT_HOME=/usr/local/mycat 2) 执行 source /etc/profile 命令，使环境变量生效。
如果是在多台Linux系统中组建的MyCAT集群，那需要在MyCAT Server所在的服务器上配置对其他ip和主机名的映射，配置方式如下：
vim /etc/hosts
例如：我有3台机器，配置如下：
192.168.1.101 mycat.com 192.168.1.102 master.com 192.168.1.103 slave.com
编辑完后，保存文件。
[root@slave ~]# ping mycat.com PING mycat.com (192.168.1.101) 56(84) bytes of data. 64 bytes from mycat.com (192.168.1.101): icmp_seq=1 ttl=64 time=0.271 ms [root@slave ~]# ping master.com PING master.com (192.168.1.102) 56(84) bytes of data. 64 bytes from master.com (192.168.1.102): icmp_seq=1 ttl=64 time=0.331 ms 64 bytes from master.com (192.168.1.102): icmp_seq=2 ttl=64 time=0.518 ms [root@slave ~]# ping slave.com PING slave.com (192.168.1.103) 56(84) bytes of data. 64 bytes from slave.com (192.168.1.103): icmp_seq=1 ttl=64 time=0.019 ms 64 bytes from slave.com (192.168.1.103): icmp_seq=2 ttl=64 time=0.043 ms
3.4.3 编辑mycat的读写分离规则
3.4.3.1 编辑conf/server.xml文件，这里是存放的数据库的账号密码信息
<user name="root" defaultAccount="true"> #连接数据库的用户名 <property name="password">123456</property> #数据库的密码 <property name="schemas">HA</property> #连接的逻辑数据库 <property name="defaultScheddma">HA</property> <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 --> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">user</property> <property name="schemas">HA</property> <property name="readOnly">true</property> <property name="defaultSchema">HA</property> </user>
3.4.3.2 编辑schema.xml
是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="HA" checkSQLschema="true" sqlMaxLimit="100" dataNode='dn1'> </schema> <dataNode name="dn1" dataHost="host1" database="HA" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.1.102:3306" user="root" #指定写主机 password="123456"> <!-- can have multi read hosts --> #指定读主机 <readHost host="hostS1" url="192.168.1.103:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>
schema.xml文件中配置的参数解释:
参数 说明 schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应 dataNode 分片信息，也就是分库相关配置 dataHost 物理数据库，真正存储数据的数据库 配置说明 name属性唯一标识dataHost标签，供上层的标签使用。 maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的 writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数。 minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小。 每个节点的属性逐一说明 schema: 属性 说明 name 逻辑数据库名，与server.xml中的schema对应 checkSQLschema 数据库前缀相关设置，建议看文档，这里暂时设为false sqlMaxLimit select 时默认的limit，避免查询全表 table 属性 说明 name 表名，物理数据库中表名 dataNode 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name primaryKey 主键字段名，自动生成主键时需要设置 autoIncrement 是否自增 rule 分片规则名，具体规则下文rule详细介绍 dataNode 属性 说明 name 节点名，与table中dataNode对应 datahost 物理数据库名，与datahost中name对应 database 物理数据库中数据库名 dataHost 属性 说明 name 物理数据库名，与dataNode中dataHost对应 balance 均衡负载的方式 writeType 写入方式 dbType 数据库类型 heartbeat 心跳检测语句，注意语句结尾的分号要加 schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1" schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型，目前的取值有 4 种： balance="0",不开启读写分离机制， 所有读操作都发送到当前可用的 writeHost 上。 balance="1"，全部的 readHost与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1， M2->S2，并且M1与M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。 balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。 balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力 writeType 属性，负载均衡类型，目前的取值有 3 种 writeType="0" 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost。 writeType="1" 所有写操作都随机的发送到配置的 writeHost。 writeType="2" 没实现。 schema.xml中的writeType的取值决定了负载均衡对写操作的处理： writeType="0"：所有的写操作都发送到配置文件中的第一个write host。（第一个write host故障切换到第二个后，即使之后修复了仍然维持第二个为写库）。推荐取0值，不建议修改. 主从切换（双主failover）：switchType 属性 如果细心观察schem.xml文件的话，会发现有一个参数：switchType，如下配置： <dataHost name="237_15" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> 参数解读 switchType="-1"： 不自动切换 switchType="1"： 默认值，自动切换 switchType="2"： 基于MySQL主从同步的状态来决定是否切换。需修改heartbeat语句（即心跳语句）：show slave status 指定后端连接的数据库类型，目前支持二进制的mysql协议，还有其他使用JDBC连接的数据库。例如：mongodb、oracle、spark等。 dbDriver属性指定连接后端数据库使用的 Driver，目前可选的值有native和JDBC。 使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和maridb。 其他类型的数据库则需要使用JDBC驱动来支持。从1.6版本开始支持postgresql的native原始协议。 这个标签内指明用于和后端数据库进行心跳检查的语句。例如,MYSQL可以使用select user()，Oracle可以使用select 1 from dual等。 这个标签还有一个connectionInitSql属性，主要是当使用Oracla数据库时，需要执行的初始化SQL语句就这个放到这里面来。例如：altersession set nls_date_format='yyyy-mm-dd hh24:mi:ss' writeHost标签、readHost标签 这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。 唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。 在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去。
3.4.4 调试mycat
经过以上两个步骤的配置，就可以到/usr/local/mycat/bin 目录下执行：
[root@mycat bin]# ./mycat start ##Usage: ./mycat { console | start | stop | restart | status | dump }
Starting Mycat-server...
也可以调试查看是否有报错，可以执行：
[root@mycat bin]# ./mycat console Running Mycat-server... wrapper | --> Wrapper Started as Console wrapper | Launching a JVM... jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. jvm 1 | jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log ##看到有“ successfully”说明启动成功
3.4.5 部署后端数据库
3.4.5.1安装Mysql(master和slave数据库都要安装)
[root@master ~]# ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm ##使用上面的命令就直接下载了安装用的Yum Repository，大概25KB的样子，然后就可以直接yum安装了。 [root@master ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm ##之后就开始安装MySQL服务器。 [root@master ~]# yum -y install mysql-community-server ##这步可能会花些时间，安装完成后就会覆盖掉之前的mariadb。
3.4.5.2 使用临时密码登录，并重置密码，并关闭密码审计强度
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
[root@master ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
[root@master ~]#
3.4.5.3 编辑主数据库My.cnf文件，并重启数据库
##加入下面，并重启服务 log-bin=mysql-bin-master #启用二进制日志 server-id=1 #本机数据库ID 标示 binlog-do-db=HA #可以被从服务器复制的库。二进制需要同步的数据库名 binlog-ignore-db=mysql #不可以被从服务器复制的库 validate-password=off
[root@master ~]# systemctl restart mysqld
3.4.5.4 配置主服务器，创建库和表，用于写操作
mysql> create database HA; Query OK, 1 row affected (0.00 sec) mysql> use HA; Database changed mysql> create table u1(id int,name varchar(128)); Query OK, 0 rows affected (0.00 sec) mysql> insert into u1 values(1,"zhangsan"); Query OK, 1 row affected (0.00 sec)
3.4.5.5 授权从服务器复制连接账号
mysql> grant replication slave on *.* to slave@"192.168.1.%" identified by "123456"; Query OK, 0 rows affected, 1 warning (0.00 sec)
3.4.5.6 将数据库信息导出，导入到从库
[root@master ~]# mysqldump -uroot -p123456 -B HA>HA.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master ~]# ls anaconda-ks.cfg HA.sql mysql57-community-release-el7-10.noarch.rpm
3.4.5.7 将数据库传给从库
[root@master ~]# scp HA.sql 192.168.1.103:/root The authenticity of host '192.168.1.102 (192.168.1.103)' can't be established. ECDSA key fingerprint is SHA256:N8H4e2TVds/6VVyoE1RgKuXAXwgRPODlnVzjGrcen28. ECDSA key fingerprint is MD5:1d:fd:db:8a:47:2c:a5:14:09:67:3e:e3:cf:8c:9e:39. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.103' (ECDSA) to the list of known hosts. root@192.168.1.103's password: HA.sql 100% 1937 6.1MB/s 00:00
3.4.5.8 在从库将数据库导入，用于实现读操作
mysql> create database HA; Query OK, 1 row affected (0.01 sec) mysql> quit Bye [root@slave ~]# mysql -uroot -p123456 HA <HA.sql mysql: [Warning] Using a password on the command line interface can be insecure.
3.4.5.9 配置My.cnf文件，并重启数据库
添加如下内容： validate-password=off server-id = 2
[root@slave ~]# systemctl restart mysqld
3.4.5.10 连接主数据库
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='192.168.1.102',master_user='slave',master_password='123456'; Query OK, 0 rows affected, 2 warnings (0.01 sec)
3.4.5.11 启动slave
mysql> start slave -> ; Query OK, 0 rows affected (0.00 sec)
3.4.6 启动Mycat
[root@mycat bin]# ./mycat start #启动数据库 Starting Mycat-server...
3.4.7 测试连接
3.4.7.1 在测试机上提前安装mysql的客户端
[root@client ~]# yum -y install mysql
3.4.7.2 连接mycat：（8066应用端口，9066管理端口）
[root@client ~]# mysql -uroot -p123456 -h 192.168.1.101 -P8066 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | HA | +----------+ 1 row in set (0.01 sec)
注意：mycat默认只兼容mysql5，要兼容mysql8需要
1）下载mysql8的驱动包，替换lib下默认的驱动包
2）修改客户端my.cnf
[mysqld] sql_mode="" default_authentication_plugin=mysql_native_password
3）登录时加上选项
mysql -utestuser -p -P8066 -h127.0.0.1 --default_auth=mysql_native_password
3.4.7.3 授权mycat可以连接数据库
注意：此操作需要在主从数据库都操作授权,否则连接不上数据库。
5.7 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'; 8.0 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1'; grant all on *.* to 'root'@'%'; flush privileges;
3.4.7.4 客户端主机上执行查看表数据
MySQL [(none)]> use HA; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [HA]> show tables; +--------------+ | Tables_in_HA | +--------------+ | u1 | +--------------+ 1 row in set (0.01 sec) MySQL [HA]> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | +------+----------+ 2 rows in set (0.02 sec) MySQL [HA]>
扩展：如果没有授权的话，会出现如下的报错：
具体表现，在切库时非常卡顿，同时查表提示失败。
3.4.8 测试
3.4.8.1 打开Mycat.log的日志，将日志级别调整到"debug"
[root@mycat conf]# vim log4j2.xml ##打开日志配置文件 <asyncRoot level="debug" includeLocation="true"> #日志级别调整成debug <AppenderRef ref="Console" /> # 打开控制台输出 打开控制台输出，可以在mycat控制台看日志 ./mycat console
可以看到当查询数据时，查询的后端服务器是从服务器，数据插入是主服务器，所以读写分离成功。
3.5 部署双主双从架构
3.5.1 环境要求
关闭防火墙和SELinux
| 角色 | IP | 数据库版本 | 系统版本 |
|---|---|---|---|
| client | 192.168.1.20 | 无 | CentOS7.9 |
| mycat | 192.168.1.101 | 无 | CentOS7.9 |
| master1 | 192.168.1.102 | mysql 5.7.36 | CentOS7.9 |
| slave1 | 192.168.1.103 | mysql5.7.36 | CentOS7.9 |
| master2 | 192.168.1.104 | mysql5.7.36 | CentOS7.9 |
| slave2 | 192.168.1.105 | mysql5.7.36 | CentOS7.9 |
3.5.2 master2和slave2安装mysql数据库（接上个实验）
[root@master2 ~]# ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm ##使用上面的命令就直接下载了安装用的Yum Repository，大概25KB的样子，然后就可以直接yum安装了。 [root@master2 ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm ##之后就开始安装MySQL服务器。 [root@master2 ~]# yum -y install mysql-community-server ##这步可能会花些时间，安装完成后就会覆盖掉之前的mariadb。
3.5.3 修改密码
安装完成后，启动数据库使用默认密码登录数据库进行基本设置
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
3.5.4 将原来的master1和slave1的主从配置重置一下
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.02 sec) #停止从服务器复制功能 mysql> reset slave; Query OK, 0 rows affected (0.03 sec) #重置主从配置
3.5.5 重新配置my.cnf
3.5.5.1 master1的my.cnf的配置
validate-password=off log-bin=mysql-bin-master server-id=1 binlog-do-db=HA #需要复制的数据库名字 binlog-ignore-db=mysql #忽略复制的数据库，如果有多个，可以写多行 binlog_format=STATEMENT #设置binlog的日志格式 log-slave-updates #在作为从数据库时，有写入操作也要更新二进制日志文件 auto-increment-increment=2 #表示自增长字段每次递增的量，指自增长的起始值，默认是1，取值是1-65535 auto-increment-offset=1 #表示自增长字段从那个数开始，指字段一次递增多少，取值是1-65535
3.5.5.2 master2的my.cnf的配置
validate-password=off log-bin=mysql-bin-master server-id=3 #服务器ID binlog-do-db=HA #需要复制的数据库名字 binlog-ignore-db=mysql #忽略复制的数据库，如果有多个，可以写多行 binlog_format=STATEMENT #设置binlog的日志格式 log-slave-updates #在作为从数据库时，有写入操作也要更新二进制日志文件 auto-increment-increment=2 #表示自增长字段每次递增的量，指自增长的起始值，默认是1，取值是1-65535 auto-increment-offset=2 #表示自增长字段从那个数开始，指字段一次递增多少，取值是1-65535
3.5.5.3 slave1的my.cnf的配置
validate-password=off #关闭密码强度审计 server-id=2 #服务器ID relay-log=mysql-relay #开启中继日志
3.5.5.4 slave2的my.cnf的配置
validate-password=off #关闭密码强度审计 server-id=4 #服务器ID relay-log=mysql-relay #开启中继日志
3.5.6 将4台数据库服务重启
[root@master ~]# systemctl restart mysqld [root@master2 ~]# systemctl restart mysqld [root@slave ~]# systemctl restart mysqld [root@slave2 ~]# systemctl restart mysqld
3.5.7 在2个master上建立账号并授权slave
3.5.7.1 在master1授权
mysql> grant replication slave on *.* to slave@'%' identified by '123456'; #授权 #查看状态 mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-master.000001 | 443 | HA | mysql | | +-------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3.5.7.2 在master2上授权
mysql> grant replication slave on *.* to slave@'%' identified by '123456'; #授权 mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-master.000001 | 443 | HA | mysql | | +-------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3.5.8 在2个slave服务器上指定主服务器
3.5.8.1 slave1上执行（这里的pos一定要和主数据库上的一致，下同）
mysql> change master to master_host='192.168.1.102',master_user='slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=443; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.102 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 443 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 327 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ....省略....
3.5.8.2 在slave2上执行
mysql> change master to master_host='192.168.1.104',master_user='slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=443; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.104 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 443 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 327 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ....省略....
3.5.9 2台master之间互为主备配置
3.5.9.1 在master1上配置master2为主
mysql> change master to master_host='192.168.1.104',master_user='slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=443; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.104 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 443 Relay_Log_File: master-relay-bin.000002 Relay_Log_Pos: 327 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ....省略....
3.5.9.2 在master2上指定master1为主
mysql> change master to master_host='192.168.1.102',master_user='slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=443; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.102 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 443 Relay_Log_File: master2-relay-bin.000002 Relay_Log_Pos: 327 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ....省略....
至此，双主双备已经搭建完成！
3.5.10 验证是否可以同步数据库
3.5.10.1 在master1上创建HA库
mysql> create database HA; Query OK, 1 row affected (0.01 sec) mysql> use HA; Database changed mysql> create table u1(id int,name varchar(20)); #创建表 Query OK, 0 rows affected (0.04 sec) mysql> insert into u1 values(1,'zhangsan'); #插入数据 Query OK, 1 row affected (0.11 sec) mysql> select * from u1; #查看数据 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.01 sec) mysql>
3.5.10.2 在slave1查看
mysql> use HA; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------+ | Tables_in_HA | +--------------+ | u1 | +--------------+ 1 row in set (0.01 sec) mysql> select * from u1; #查看数据 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql>
3.5.10.3 master2上查看
mysql> use HA; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------+ | Tables_in_HA | +--------------+ | u1 | +--------------+ 1 row in set (0.00 sec) mysql> select * from u1; #查看数据 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql>
3.5.10.4 slave2上查看
mysql> use HA; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------+ | Tables_in_HA | +--------------+ | u1 | +--------------+ 1 row in set (0.00 sec) mysql> select * from u1; #查看数据 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql>
可以看到数据库之间已经完成了同步。
3.5.11 读写分离配置
3.5.11.1 配置mycat机器，将balance设置为"1"
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="HA" checkSQLschema="true" sqlMaxLimit="100" dataNode='dn1'> </schema> <dataNode name="dn1" dataHost="host1" database="HA" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.1.102:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="192.168.1.103:3306" user="root" password="123456" /> </writeHost> <writeHost host="hostM2" url="192.168.1.104:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.1.105:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>
3.5.12 重启mycat
[root@mycat bin]# ./mycat restart Stopping Mycat-server... Stopped Mycat-server. Starting Mycat-server... [root@mycat bin]#
3.5.13 验证读写分离
1）在写主机（master1）的数据库的表（u1）中插入一条带系统变量数据，造成主从数据不一致，以便以测试
mysql> insert into u1 values(2,@@hostname)
在master1上查看
mysql> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | master | +------+----------+ 2 rows in set (0.00 sec) mysql>
在slave1上查看
mysql> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave | +------+----------+ 2 rows in set (0.00 sec)
在master2查看
mysql> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | master2 | +------+----------+ 2 rows in set (0.00 sec)
在slave2查看
mysql> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave2 | +------+----------+ 2 rows in set (0.00 sec)
可以看到4个数据库的数据均不一致
2）在mycat上进行测试
[root@mycat logs]# mysql -uroot -p123456 -h 192.168.1.101 -P8066 #登录Mycat Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6.7.6-release-20201124193646 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> use HA; #切换数据库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [HA]> select * from u1; #查询 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | master2 | +------+----------+ 2 rows in set (0.00 sec) MySQL [HA]> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave2 | +------+----------+ 2 rows in set (0.01 sec) MySQL [HA]> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave | +------+----------+ 2 rows in set (0.00 sec)
测试发现可以在3台机器之间进行切换。
注意：如果测试发现无法切到master2和slave2，则需要在这2台数据库中进行授权连接，命令如下：
mysql> grant all privileges on *.* to root@'%' identified by '123456';
3.5.14 测试故障切换
1）将master1主机的mysql服务停止掉，查看是否能切换到master2
[root@master mysql]# systemctl stop mysqld #停止数据库 [root@master mysql]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Sun 2022-03-27 20:14:23 CST; 4s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 13975 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 13951 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 13978 (code=exited, status=0/SUCCESS) Mar 27 19:02:51 master systemd[1]: Starting MySQL Server... Mar 27 19:02:58 master systemd[1]: Started MySQL Server. Mar 27 20:14:12 master systemd[1]: Stopping MySQL Server... Mar 27 20:14:23 master
2）在mycat插入数据测试
MySQL [HA]> insert into u1 values(3,@@hostname); Query OK, 1 row affected, 1 warning (0.01 sec)
可以看到任然可以写入数据
3）重新启动master1数据库
[root@master mysql]# systemctl start mysqld
4）mycat上查询
MySQL [HA]> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave2 | | 3 | slave2 | +------+----------+ 3 rows in set (0.01 sec) MySQL [HA]> select * from u1; ##可以发现写主机变成了master2，master1变成了读数据库 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | master | | 3 | master | +------+----------+ 3 rows in set (0.00 sec) MySQL [HA]> select * from u1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | slave | | 3 | slave | +------+----------+ 3 rows in set (0.00 sec) MySQL [HA]>
到此，双主双从的读写分离已经完成！
四、总结
Mycat中间件是一个开源的数据库中间件，旨在提供高性能、高可用性和可伸缩性的数据库访问解决方案。它主要用于分布式数据库架构中，为应用程序提供透明的数据库访问，同时具备负载均衡、故障转移和数据分片等功能。
Mycat中间件以其卓越的功能和灵活性在数据库领域广泛应用。首先，它提供了数据库访问的负载均衡机制，可以有效地分配用户请求到后端数据库服务器，从而提高整体系统的性能和可用性。通过动态路由和连接池管理，Mycat中间件能够根据实际负载情况智能地分配数据库资源，避免单个数据库节点过载或出现瓶颈。
其次，Mycat中间件支持数据的分片存储和水平扩展，这对于处理大规模数据和应对高并发访问非常重要。通过将数据水平划分为多个分片，每个分片可以独立存储在不同的数据库节点上，从而提高了数据读写的并发性能。同时，Mycat中间件还提供了数据迁移和扩容等管理工具，使得系统能够方便地进行扩展和维护。
此外，Mycat中间件还具备故障转移和容灾备份的功能。当数据库节点发生故障时，Mycat可以自动将请求切换到备用节点，从而保证系统的连续性和可用性。同时，它还支持数据的冗余备份和同步，以防止数据丢失和提高数据的可靠性。
总之，Mycat中间件是一个强大的数据库访问解决方案，为分布式数据库架构提供了重要的支持。它能够提供负载均衡、故障转移、数据分片等关键功能，同时还具备数据迁移、扩容和容灾备份等管理工具。通过使用Mycat中间件，开发人员可以轻松构建高性能、高可用性和可伸缩性的数据库系统，提升应用程序的性能和用户体验。
扫一扫
600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
