从零搭建MySQL主从复制、Mycat读写分离

最新推荐文章于 2024-05-19 21:13:48 发布

w4xj

最新推荐文章于 2024-05-19 21:13:48 发布

阅读量473

点赞数

分类专栏： MySQL 文章标签：数据库中间件 mysql 主从复制读写分离

本文链接：https://blog.csdn.net/sltylzx/article/details/103847187

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

参考：
https://blog.csdn.net/m0_37814112/article/details/78638359
https://cloud.tencent.com/developer/article/1508816

基本概念

1.数据库中间件

[1].中间件：是一类连接软件组件和应用的计算机软件，以便于软件各部件之间的沟通。

例子：Tomcat，web中间件。

[2].数据库中间件：连接java应用程序和数据库

2. 为什么要用Mycat ？

[1].Java与数据库紧耦合。
[2].高访问量高并发对数据库的压力。
[3].读写请求数据不一致

3.数据库中间件对比

在这里插入图片描述

[1].Cobar属于阿里B2B事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。
[2].Mycat是开源社区在阿里cobar基础上进行二次开发，解决了cobar存在的问题，并且加入了许多新的功能在其中。所以现在国内主流的数据库中间件就是Mycat。
[3].OneProxy基于MySQL官方的proxy思想利用c进行开发的，OneProxy是一款商业收费的中间件。舍弃了一些功能，专注在性能和稳定性上。
[4].kingshard由小团队用go语言开发，还需要发展，需要不断完善。
[5].Vitess是Youtube生产在使用，架构很复杂。不支持MySQL原生协议，使用需要大量改造成本。
[6].Atlas是360团队基于mysql proxy改写，功能还需完善，高并发下不稳定。
[7].MaxScale是mariadb（MySQL原作者维护的一个版本） 研发的中间件
[8].MySQLRoute是MySQL官方Oracle公司发布的中间件

4.主要功能

[1].读写分离

在这里插入图片描述

[2].数据分片：水平拆分和垂直拆分

在这里插入图片描述

[3].多数据源整合

在这里插入图片描述

5.应用场景

[1].单纯的读写分离，此时配置最为简单，支持读写分离，主从切换;
[2].分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片;
[3].多租户应用，每个应用-个库，但应用程序只连接Mycat,从而不改造程序本身，实现多租户化;
[4].报表系统，借助于Mycat的分表能力，处理大规模报表的统计;
[5].替代Hbase,分析大数据;
[6].作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，
[7].除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择。

6.Mycat原理

在这里插入图片描述

[1].sql解析
[2].分片分析
[3].路由分析
[4].读写分离
[5].分析缓存
[6].分析sql
[7].执行结果
[8].合并处理

7.架构与特性

在这里插入图片描述
[1].支持SQL92标准
[2].支持MySQL、Oracle、 DB2、 SQL Server、PostgreSQL等DB的常见SQL语法
[3].遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。
[4].基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。
[5].支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster。
[6].基于Nio实现，有效管理线程，解决高并发问题。
[7].支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数，支持跨库分页。
[8].支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join。
[9].支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。
[10].支持多租户方案
[11].支持分布式事务(弱xa)
[12].支持XA分布式事务(1.6.5)
[13].支持全局序列号，解决分布式下的主键生成问题。
[14].分片规则丰富，插件化开发，易于扩展。
[15].强大的web,命令行监控。
[16].支持前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、 SQL Server、mongodb、巨杉。
[17].支持密码加密
[18].支持服务降级
[19].支持IP白名单
[20].支持SQL黑名单、sql注入攻击拦截
[21].支持prepare预编译指令(1.6)
[22].支持非堆内存(Direct Memory)聚合计算(1.6)
[23].支持PostgreSQL的native协议(1.6)
[24].支持mysq|和oracle存储过程，out参数、多结果集返回(1.6)
[25].支持zookeeper协调主从切换、zk序列、配置zk化(1.6)
[26].支持库内分表(1.6)
[27].集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理(2.0开发版) 。

安装配置

1.安装前准备：两台mysql数据库，有则跳过

[1].准备一台干净的虚拟机，在mysql官网下载rpm包，上传到服务器上（或者直接在服务器上下载）

在这里插入图片描述

[2].依次安装服务端和客户端，做好相应的配置

[3].将安装好mysql的那台虚拟机克隆，并修改好配置，使其能分别运行mysql并能互通

在这里插入图片描述

2.在第三台机器上安装mycat（理论上用前两台也可以）

[1].在第三台机器上只安装mysql客户端即可

[2].mycat下载地址1.6.7.1

http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

[3].配置server.xml

将默认用户由root改为mycat，为了和mysql的root区分，schemas值为逻辑库名

        <user name="mycat" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

[4].修该schema.xml

①.将多余的配置删掉

②.为schema节点指定dataNode属性，注意N大写

③.为dataNode节点指定database属性，真正的物理库，在查询时需要真实存在，否则show tables和select 会报错：ERROR 1184 (HY000): Invalid DataSource:0）

④.dataHost节点中配置wirteHost节点（可多个），writeHost配置url（写主机的ip+port）、user、password。（注意：这里balance的值！！！之后会提到）

⑤.为writeHost节点配置readHost节点（可多个），readHost配置url（读主机的ip+port）、user、password

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          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.26.162:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.26.163:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

3.测试启动

[1].在mycat主机上测试能否通过mysql客户端连接读、写两台机器

mysql -uroot -p123456 -h 192.168.26.162 -P 3306
mysql -uroot -p123456 -h 192.168.26.163 -P 3306

在这里插入图片描述

[2].启动程序

①.控制台启动：去 mycat/bin 目录下执行 ./mycat console

②.后台启动：去 mycat/bin 目录下 ./mycat start

为了能第一时间看到启动日志，方便定位问题，我们选择①控制台启动。

在这里插入图片描述

如果域名解析出错，可修改/etc/hosts，添加127.0.0.1机器名称，然后重启网络即可

[3].登录mycat

mysql -umycat -p123456 -P 8066 -h 192.168.26.161

在这里插入图片描述

4.搭建一主一从

[1].一台处理所有读请求，一台处理所有写请求

在这里插入图片描述

[2].主从复制原理

在这里插入图片描述

[3].新增或修改写主机node162配置（/etc/my.cnf）

#主服务器唯一ID
server-id=1

# 启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
binlog-ignore-db=performance_schema

#设置需要复制的数据库
binlog-do-db=testdb

#设置logbin格式
binlog_format=STATEMENT

binlog的三种格式

Statement模式

每一条修改数据的sql都会记录到master的bin_log中，slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行的变化，较少bin-log日志量，节约IO，提高性能。因为它只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文信息。
缺点：由于它是记录执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，来保证所有语句在slave端能够得到和在master端相同的执行结果。由于mysql更新较快，使mysql的赋值遇到了不小的挑战，自然赋值的时候就会涉及到越复杂的内容，bug也就容易出现。在statement level下，目前就已经发现了不少情况会造成mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如：sleep（）函数在有些版本中就不能正确赋值，在存储过程中使用了last_insert_id（）函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行记录的裱花，所以不会出现类似的问题。
总结：
Statement level优点：
1、解决了row level的缺点，不需要记录每一行的变化。
2、日志量少，节约IO，从库应用日志块。
Statement level缺点：一些新功能同步可能会有障碍,比如函数、触发器等。

Row模式

日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改。
优点：在row level的模式下，bin_log中可以不记录执行的sql语句的上下文信息，仅仅只需要记录哪一条记录被修改，修改成什么样。所以row level的日志内容会非常清楚的记录每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或fuction，以及trigger的调用或处罚无法被正确复制的问题。
缺点：row level模式下，所有的执行语句都会记录到日志中，同时都会以每行记录修改的来记录，这样可能会产生大量的日志内容。
总结：
row level的优点：
1、记录详细
2、解决statement level模式无法解决的复制问题。
row level的缺点：日志量大，因为是按行来拆分。

Mixed模式（混合模式）

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。
新版本中的mysql中对row level模式也做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
缺点：还是会有一部分数据不一致的问题，比如对于@@xxx 会造成不一致，比如@@hostname则主机表示主机的主机名，从机表示从机的主机名

[3].新增或修改写主机node163配置（/etc/my.cnf）

#从服务器唯一ID
server-id= 2
#启用中继日志
relay-log=mysql-relay

[4].主机、从机重启 MySQL 服务

[5].主机从机都关闭防火墙

[6].在主机上建立帐户并授权 slave

#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
# 查询master的状态
show master status;

在这里插入图片描述

记录下File和Position的值

执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

[7].在从机上配置需要复制的主机

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

在这里插入图片描述

[8].启动从机复制功能

start slave;

在这里插入图片描述

[9].查看从服务器状态

show slave status\G;

在这里插入图片描述

[10].我们在主机中建库建表，在从机中就会同步该操作

在这里插入图片描述

[11].如何停止从服务复制功能

stop slave;

[12].如何重新配置主从

stop slave;
reset master;

[13].到这里我们配置好了主从复制，但是真正读写分离吗。

①.测试：在写主机中插入@@hostname，让主从两台机器的数据不一致（或者直接修改从机中的某条数据）

insert into test_user values(2,@@hostname);

在这里插入图片描述

②.登录mycat查询三次，能推测都是从读主机中查询

在这里插入图片描述

③.回到之前安装配置2.[4]中的schema.xml的配置中dataHost的balance属性，很容易就能猜到是负载策略，mycat的负载策略有四种：

（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2、S1、S2 都参与 select 语句的负载均衡。
（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

④.这里为了测试效果，改成2（注意：生产中读写分离配置为1或者3）

 <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

在这里插入图片描述

⑤.重启mycat，重连mycat再查询，至此真正实现读写分离，再次提醒：生产中读写分离配置为1或者3

在这里插入图片描述

4.搭建双主双从

在这里插入图片描述

[1].在做测试前把之前的的testdb数据库删除，把从机的主从也重置；

stop slave;
reset master;

[2].再克隆两台mysql机器，注意：因为是带mysql克隆的，所以克隆过来mysql的，所以mysql内的host信息也克隆过来了，所以克隆的机器除了改机器本身的配置外，还需要修改proxies_priv和user两个表的host字段为对应的主机，不然会导致远程连接不上

在这里插入图片描述

编号	角色	IP	主机名
1	Master1	192.168.26.162	node162
2	Slave1	192.168.26.163	node163
3	Master2	192.168.26.164	node164
4	Slave2	192.168.26.165	node165

[3].配置Master1（/etc/my.cnf）

#主服务器唯一ID
server-id=1

# 启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
binlog-ignore-db=performance_schema

#设置需要复制的数据库
binlog-do-db=testdb

#设置logbin格式
binlog_format=STATEMENT

#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

在这里插入图片描述

[4].配置Master2（/etc/my.cnf）,注意auto-increment-offset的值和M1不同

#主服务器唯一ID
server-id=2

# 启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
binlog-ignore-db=performance_schema

#设置需要复制的数据库
binlog-do-db=testdb

#设置logbin格式
binlog_format=STATEMENT

#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

[4].配置Slave1（/etc/my.cnf）,和一主一从一样

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

[5].配置Slave2（/etc/my.cnf）

#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay

注意四台机器的server-id不同

[6].重启mysql，确保重启成功

[7].登陆读主机，在两台主机上建立帐户并授权 slave（GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123456’;），当然M2是复制M1的机器，所以是已经授权了，可通过查询mysql库中的user表查看（select host,user from user;）

在这里插入图片描述

show master status;

在这里插入图片描述

分别记录下File和Position的值

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

[9].在从机上配置需要复制的主机;Slava1 复制 Master1，Slava2 复制 Master2；Master2 复制 Master1，Master1 复制 Master2

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

Slava1的复制命令

在这里插入图片描述

Slava2的复制命令

在这里插入图片描述

启动两台从服务器复制功能

start slave;
show slave status\G;

在这里插入图片描述

Master2的复制命令

在这里插入图片描述

Master1的复制命令

在这里插入图片描述

启动复制

在这里插入图片描述

[10].测试主从复制：在node162中新建testdb

在这里插入图片描述

[11].配置读写分离（mycat的schema.xml）

①.balance设置为1：全部的readHost与stand by writeHost参与select语句的负载均衡。

②.writeType设置为0：所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个

③.switchType设置为1: 1 默认值，自动切换。-1 表示不自动切换。2 基于 MySQL 主从同步的状态决定是否切换。

④.再添加一组读写节点

在这里插入图片描述

[12].重启mycat，登录mycat，新建user表，同样的方式插入一条测试数据

mysql> insert into user values(1,@@hostname);
Query OK, 1 row affected, 1 warning (0.03 sec)

在这里插入图片描述

[12].使用测试mycat查询，和预期一样，node162值作为写主机，standby写主机和读主机负责读

在这里插入图片描述

[13].测试抗风险：将node162关闭，看是否影响正常使用，经测试，当主写主机挂掉之后standby写主机自动切换为主写主机，原来主写主机的从机也不参与查询

在这里插入图片描述

[14].把挂掉的主机重启，我们发现：原来的主写主机变为standby-wirteHost，node163也能使用了

在这里插入图片描述

w4xj

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
从零搭建MySQL主从复制、Mycat读写分离

参考：https://blog.csdn.net/m0_37814112/article/details/78638359https://cloud.tencent.com/developer/article/1508816基本概念1.数据库中间件[1].中间件：是一类连接软件组件和应用的计算机软件，以便于软件各部件之间的沟通。例子：Tomcat，web中间件。[2].数据库中间件...
复制链接

扫一扫