MySQL主从复制与读写分离

最新推荐文章于 2025-06-07 17:40:08 发布

果子⌂

最新推荐文章于 2025-06-07 17:40:08 发布

阅读量875

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_81259717/article/details/148445860

版权

一、MySQL 主从复制原理

MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

MySQL 支持的复制类型
基于语句的复制	在主服务器上执行的 SQL语句，在从服务器上执行同样的语句，MySQL 默认采用基于语句的复制，效率比较高
基于行的复制	把改变的内容复制过去,而不是把命令在从服务器上执行一遍
混合类型的复制	默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

1、复制的工作过程

在每个事务更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master 通知存储引擎提交事务。

Slave 将 Master 的 Binary log 复制到其中继日志(Relay log)。首先，Slave 开始一个工作线程--I/0 线程,I/0 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process 从 Master 的进制日志中读取事件，如果已经跟上 Master，它会睡眠并等待 Master 产生新的事件。I/0 线程将这些事件写入中继日志。

SQL slave thread(SQl 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/0 线程保持一致，中继日志通常会位于0S 的缓存中，所以中继日志的开销很小。复制过程有一个很重要的限制，即复制在S1ave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。

二、MySQL读写分离原理

简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。

1、基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现，运维人员无从下手，

2、基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。

MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。

Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。经过上述简单的比较，通过程序代码实现 MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java 应用，如果在程序代码中实现读写分离对代码改动就较大。所以，像这种应用一般会考虑使用代理层来实现。本章后续案例通过 Amoeba 实现。

MyCAT 是一款开源的分布式关系型数据库中间件，主要用于解决大规模数据存储和高效查询的需求。它支持分布式 SQL 查询，兼容 MySQL通信协议，能够通过数据分片提高数据查询处理能力。MyCAT的前端用户可以将其视为一个数据库代理，使用 MySQL 客户端工具和命令行访问，而后端则可以通过 MySQL 原生协议与多个 MySQL 服务器通信，或者使用 JDBC协议与大多数主流数据库服务器通信

三、案例

1、网络拓扑

2、案例环境

主机	操作系统	IP 地址	应用
Master	openEuler 24.03	192.168.10.101	Mysql-server
Slave1	openEuler 24.03	192.168.10.102	Mysql-server
Slave2	openEuler 24.03	192.168.10.103	Mysql-server
Mycat	openEuler 24.03	192.168.10.104	Mycat2
客户端	openEuler 24.03	192.168.10.105	mysql

3、搭建主从复制

1.1、在所有节点配置时间同步

dnf -y install ntpdate

ntpdate ntp1.aliyun.com

1.2、关闭防火墙和内核保护

#关闭防火墙
systemctl stop firewalld
#禁用防火墙
systemctl disable firewalld

#关闭内核保护机制
setenforce 0
#永久关闭内核保护机制
vim /etc/selinux/config
###编辑内容###
SELINUX=disabled

1.3、编辑master配置文件

vim /etc/my.cnf

###编辑内容###

#启用二进制日志(Binary Log)并指定其存储路径
log-bin=/usr/local/mysql/data/mysql-bin

#定义二进制日志的记录格式为混合模式
binlog_format=MIXED

#为mysql 实例分配一个唯一的服务器标识符
server-id=1

#创建用户
create user 'myslave'@'%' identified by '123456';

#授权
grant replication slave on *.* to 'myslave'@'%';

alter user 'myslave'@'%' identified with mysql_native_password by '123456';

#刷新权限
flush privileges;

#查看主的状态
show master status;

其中 File 列显示日志名，Position 列显示偏移量,这两个值在后面配置从服务器的时候需要。Slave 应从该点上进行新的更新

1.4、配置从服务器

#配置同步
change master to master_host='192.168.10.101',master_user='myslave',master_pass
word='123456',master_log_file='/usr/local/mysql/data/mysql-bin.000001',master_log_pos=1149;

#启动同步
start slave;

#查看状态
show slave status\G

1.5、验证主从复制

1>、在master服务器上登录MySQL，创建数据库db111

2>、在slave服务器上登录MySQL，验证

4、基于主从复制搭建读写分离

        在代理服务器上操作

MyCAT是目前最流行的分布式数据库中间插件，是一个开源的分布式数据库系统，是一个实现了 MySQL协议的服务器。前端用户可以把它看作一个数据库代理，用 MySQL 客户端工具和命令行访问，其后端可以用 MySQL 原生协议与多个 MySQL 服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端 MySQL服务器里或者其他数据库里。
        MyCAT 发展到目前，已经不是一个单纯的 MySQL 代理了，它的后端可以支持MySQL、SQL Server、0racle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB这种新型 NoSQL,方式的存储。未来，它还会支持更多类型的存储。
        不过，无论是哪种存储方式，在最终用户看，MyCAT里都是一个传统的数据库表，支持标准的 SQL,语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度。

1.1、安装 Mycat2

Mycat 需要依赖于JAVA，因此需要在读写分离代理所在的系统预先安装,JAVA 环境
rpm -ivh jdk-8u171-linux-x64.rpm

dnf -y install jdk-8u171-linux-x64.rpm
1.2、安装并配置mycat 软件

1.2.1、unzip 解开后 mycat2 安装包，将其移动到目录“/usr/local”下
unzip mycat2-install-template-1.20.zip -d /usr/local/
1.2.2、创建软链接，并授权
ln -s /usr/local/mycat/bin/* /usr/local/bin/

cd /usr/local/mycat/bin && chmod +x ./* && ls
1.3、配置 Mycat 读写分离

Mycat2 读写分离配置可分为:创建数据库连接账号、启动Mycat2与读写分离配置等几个步骤，接下来一一进行介绍。

1.3.1、创建 Mycat2工作所必须的账号

启动 Mycat2服务，需要有真实的数据库服务器支撑才能运行，因此，需要在 MySQL 服务器(其它被 Mycat2 支持的数据库也如此)创建账号并给账号授权然后在 Mycat2 所在的宿主系统用 MySQL, 客户端用创建好的账号远程进行连接，验证账号的有效性和正确性
#创建用户
create user 'mycat'@'%' identified by 'pwd123';

#授权
grant all on *.* to 'mycat'@'%';

#指定加密方式
alter user 'mycat'@'%' identified with mysql_native_password by 'pwd123';

#刷新权限
flush privileges;
1.3.2、启动 Mycat2

与 Mycat1.x版本相比，Mycat2 的配置基本不需要手动去修改配置文件，而是可以在 Mycat2 启动之后，登录 Mycat 管理后台，用 SQL 指令或者客户端工具进行配置。在启动 Mycat2 之前，需要对原型库的数据源做相应的修改，修改的项主要是主数据库的连接信息，一个完整的修改过的原型数据源文件“/usr/1ocal/mycat/conf/datasources/prototypeDs.datasource.json”的内容如下:被修改过的内容，以红色字显示
cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/

cp mysql-connector-java-8.0.18.jar  /usr/local/mycat/lib/

cd - && cd .. && cd /usr/local/mycat/conf && ls

cd datasources/

#编辑配置文件
vim 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.10.101:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"mycat",
        "weight":0
}

#开启
mycat start

#查看监听
netstat -anpt | grep 8066

1.4、用客户端远程登录代理服务器

在客户端上操作
#用 MySQL, 客户端工具连接 Mycat 的服务端口 TCP 8066、用户名与密码在配置文件“/usr/local/mycat/conf/users/ root.user. json”
cat /usr/local/mycat/conf/users/ root.user. json


mysql -uroot -p123456 -h192.168.10.104 -P8066
1.5、Mycat2配置读写分离

两种配置 MySQL读写分离的方法，一种是直接在 Mycat 的配置目录“/usr/1ocal/mycat/conf”的子目录编辑相关的文本文件(Mycat1.x版本只用这种方法):另一种登录到 Mycat 交互界面，用特殊语法的 SQL 命令进行配置。本教程采用第二种方法，直接在 Mycat 的交互界面输入命令。

1.5.1、Mycat 增加数据源

需要正确输入的数据主要包括: MySQL, 主从数据库的 IP地址、数据库库名(schema)、数据库账号、数据库密码(生产数据库请使用复杂密码)、实例类型(READ、WRITE或 READ WRITE)。下边是添加一个主库源和两个从库源的具体指令：
#增加主库master：

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


#增加从库slave1和slave2：

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


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

#查看数据源信息

/*+ mycat:showDataSources{} */\G
如数据源配置有误可使用/*+ mycat:resetConfig{} */; 进行重置

正确执行完上面三条 SQL语句以后，在目录“/usr/local/mycat/conf/datasources”下自动生成三个文本文件，文件名以已经执行的 SQL 语句中“name的键值做前缀
ll /usr/local/mycat/conf/datasources
1.5.2、创建 Mycat 集群

在本案例中，集群成员包括一个主库与两个从库。根据业务场景，也可以创建多个集群，充分、有效的利用系统资源。创建 Mycat 集群的 SQL 语句如下:
/*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */;

上述 SQL语句执行完以后，将在目录“/usr/local/mycat/conf/clusters”自动生成 Mycat 集群配置文件“cls01.cluster. json”

ll /usr/local/mycat/conf/datasources

1.5.3、修改集群配置

在代理服务器上操作

vim /usr/local/mycat/conf/clusters/cls01.cluster.json

##编辑内容##
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "master"
        ],
        "maxCon":2000,
        "name":"cls01",

        "readBalanceType":"BALANCE_ALL_READ",   #请修改，所有显式读请求路由到从节点(事务内除外)

        "balance": 1,    #没有请添加，所有从节点按固定顺序依次接收读请求

        "replicas":[
                "slave1",    #从节点
                "slave2"
        ],
        "switchType":"SWITCH"
}

1.5.4、修改负载均衡的默认策略为轮询

vim /usr/local/mycat/conf/server.json 

##编辑内容##
{
  "loadBalance":{
    "defaultLoadBalance":"BalanceRoundRobin",
    "loadBalances":[]
  },
  "mode":"local",
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    "ip":"0.0.0.0",
    "mycatId":1,
    "port":8066,
    "reactorNumber":8,
    "tempDirectory":null,
    "timeWorkerPool":{
      "corePoolSize":0,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    },
    "workerPool":{
      "corePoolSize":1,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":1024,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    }
  }
}

1.6、验证Mycat的读写分离

1.6.1、登录 mycat 集群，创建测试库和测试表

在客户端上操作
#客户端远程登录代理服务器
mysql -uroot -p123456 -h 192.168.10.104 -P8066

#创建库db1，表t1,并插入三行数据
create database db1;
use db1;
create table t1(id int(2));
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
注意:
创建完库以后在 mycat 的 schemas 目录下会有一个以该库明明的文件，要修改这个文件标注上 mycat 的集群，然后重启 mycat

在代理服务器上操作

{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{
                "t1":{
                        "createTableSQL":"CREATE TABLE db1.t1 (\n\tid int(2)\n)",
                        "locality":{
                                "schemaName":"db1",
                                "tableName":"t1",
                                "targetName":"cls01"
                        }
                }
        },
        "schemaName":"db1",
        "shardingTables":{},
        "views":{}
}

1.6.2、停止salvel和slave2 的主从同步

master

slave

slave

1.6.3、测试读

1.6.4、测试写

在 slave1 和 slave2上查询不到,最终只有在 Master 上才能查看到这条语句内容，说明写操作在 Master 服务器上。由此验证，已经实现了 MySQL 读写分离。目前所有的写操作都全部在 Master 主服务器上，用来避免数据的不同步;所有的读操作都分摊给了Slave从服务器，用来分担数据库压力

五、搭建多主复制

1.1、在各节点上配置

vim /etc/my.cnf

###编辑内容###

log-bin=/usr/local/mysql/data/mysql-bin
binlog_format=MIXED
server-id=1
log-slave-updates=true

192.168.10.101

#创建用户
create user 'myslave1'@'%' identified by '123456';

#授权
grant replication slave on *.* to 'myslave1'@'%';

alter user 'myslave1'@'%' identified with mysql_native_password by '123456';

#刷新权限
flush privileges;

#查看主的状态
show master status;

配置同步并查看

#配置同步
change master to master_host='192.168.10.103',master_user='myslave3',master_password='123456',master_log_file='/usr/local/mysql/data/mysql-bin.000001',master_log_pos=1150;

#启动同步
start slave;

#查看状态
show slave status\G

192.168.10.102

#创建用户
create user 'myslave2'@'%' identified by '123456';

#授权
grant replication slave on *.* to 'myslave2'@'%';

alter user 'myslave2'@'%' identified with mysql_native_password by '123456';

#刷新权限
flush privileges;

#查看主的状态
show master status;

配置同步并查看

#配置同步
change master to master_host='192.168.10.101',master_user='myslave1',master_password='123456',master_log_file='/usr/local/mysql/data/mysql-bin.000001',master_log_pos=1151;

#启动同步
start slave;

#查看状态
show slave status\G

192.168.10.103

#创建用户
create user 'myslave3'@'%' identified by '123456';

#授权
grant replication slave on *.* to 'myslave3'@'%';

alter user 'myslave3'@'%' identified with mysql_native_password by '123456';

#刷新权限
flush privileges;

#查看主的状态
show master status;

配置同步并查看

#配置同步
change master to master_host='192.168.10.102',master_user='myslave2',master_password='123456',master_log_file='/usr/local/mysql/data/mysql-bin.000001',master_log_pos=1150;

#启动同步
start slave;

#查看状态
show slave status\G