一、SQL Server的主从复制搭建
1.1、SQL Server主从复制结构图
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql](//dev-img.mos.moduyun.com/20231020/0e807924-8bc6-44a5-a9be-2eeb149ecfbb.png)
SQL Server的主从通过发布订阅来实现
主库把增删改操作发布到发布服务器,从库通过订阅发布服务器,发布服务器把操作推送到从库进行同步。
1.2、基于SQL Server2016实现主从
新建一个主库“MyDB”
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_02](//dev-img.mos.moduyun.com/20231020/6d0bd350-74c7-45e4-9e38-072e38a54e01.png)
建一个表"SysUser"测试
CREATE TABLE [dbo].[SysUser](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Account] [varchar](20) NOT NULL,
[Password] [varchar](100) NOT NULL,
[Phone] [varchar](50) NOT NULL,
[CreateTime] [datetime] NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
搭建发布服务器
复制》配置分发
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_03](//dev-img.mos.moduyun.com/20231020/bbd56d0e-32f8-42f9-b59d-d32c391eceab.png)
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_04](//dev-img.mos.moduyun.com/20231020/42b5e567-f40d-46a1-b4ad-25de15246002.png)
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_05](//dev-img.mos.moduyun.com/20231020/a171af42-41e7-4171-88f2-f1262b2bd4b6.png)
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_06](//dev-img.mos.moduyun.com/20231020/d935d049-ea15-4b02-b9bc-f34351dd5031.png)
这里创建一个自己的路径,共享文件夹
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_07](//dev-img.mos.moduyun.com/20231020/0084cde4-1c0f-42f1-b277-5f608e1ccdae.png)
分发数据库
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_08](//dev-img.mos.moduyun.com/20231020/d2b8cd2b-19fa-4019-84f0-00997e3e5fac.png)
发布服务器
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_09](//dev-img.mos.moduyun.com/20231020/9306cd45-64f0-4cd4-9312-2badfacd8487.png)
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_10](//dev-img.mos.moduyun.com/20231020/b6de9241-4ec2-498e-bd75-dc8d0ad40c7e.png)
然后下一步完成
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_11](//dev-img.mos.moduyun.com/20231020/af7c62fe-e82c-44c2-92fe-a94204676c43.png)
启用代理
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_12](//dev-img.mos.moduyun.com/20231020/1ae61241-8087-423e-9df7-8a54b2bcaaba.png)
服务确认一下登陆权限
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_13](//dev-img.mos.moduyun.com/20231020/8a29affe-70bb-40f1-8d67-3ab388702980.png)
到这里发布服务器就建好了。
发布
发布就是把主库的数据或操作发布到发布服务器
现在主库里录入了两条数据
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_14](//dev-img.mos.moduyun.com/20231020/b40eed09-803e-4766-9d8a-a2e899bf31cb.png)
新建发布
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_15](//dev-img.mos.moduyun.com/20231020/05e0ce24-bcfb-43ea-9ef9-ca6c7886e8bc.png)
选择发布的数据库
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_16](//dev-img.mos.moduyun.com/20231020/caa70177-8a3d-469f-99be-faba555d632a.png)
发布类型
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_17](//dev-img.mos.moduyun.com/20231020/ff317384-3f88-49db-86bd-da48805c619e.png)
这里有几种不同发布方式,根据自己业务场景选择,互联网一般是事务发布,有操作就同步。选择同步的表
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_18](//dev-img.mos.moduyun.com/20231020/be084b98-bb70-4522-a0bf-eaf3e281cb41.png)
一直下一步到这里,勾选初始化订阅
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_19](//dev-img.mos.moduyun.com/20231020/f039b02b-e461-4c63-b1de-2d67112db4e1.png)
代理安全性
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_20](//dev-img.mos.moduyun.com/20231020/7f4391bf-4ccf-47d7-964b-31bff283be7d.png)
下一步
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_21](//dev-img.mos.moduyun.com/20231020/7b166377-fcd4-4696-b11e-e9266920ecf9.png)
发布名称
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_22](//dev-img.mos.moduyun.com/20231020/b532a1d5-1a2d-4a2c-ae1b-fb9d48e5a297.png)
完成
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_23](//dev-img.mos.moduyun.com/20231020/b6fca2da-656a-4e52-b510-f30ef7143138.png)
这时候在上面设的发布服务器的共享文件夹中能看到有发布文件了
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_24](//dev-img.mos.moduyun.com/20231020/b7d812a6-d9a1-448e-87dd-fe9191fe80df.png)
创建订阅
新建一个从库“MyDb_Copy”,为一个没创建表的空库
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_25](//dev-img.mos.moduyun.com/20231020/3a1fb957-b575-46bd-9143-8a6064c255b6.png)
新建订阅
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_26](//dev-img.mos.moduyun.com/20231020/0ba4870c-0ca9-4d84-95a9-1b26890fa8d5.png)
选择订阅的发布
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_27](//dev-img.mos.moduyun.com/20231020/c4d303d4-4f54-4edc-8ef3-77a09e7b9f82.png)
。选择推送方式(发布服务器主动推送),还是拉取方式(从库服务器拉取方式),一个从库选推送,多个从库选择拉取方式
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_28](//dev-img.mos.moduyun.com/20231020/d5fb097f-9af0-4d87-95b3-ce246b4bbd6d.png)
选择订阅数据库
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_29](//dev-img.mos.moduyun.com/20231020/8dee1378-f52f-4fd9-b317-fa202aff4995.png)
分发代理安全性
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_30](//dev-img.mos.moduyun.com/20231020/1ac4421c-9cd9-4a2f-9a75-9864e72f1823.png)
一直下一步,直到完成!
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_mysql_31](//dev-img.mos.moduyun.com/20231020/08574a0d-ecdd-442b-9aae-f2277e916630.png)
验证
看从库数据同步过来了
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_32](//dev-img.mos.moduyun.com/20231020/62d005e7-c50f-4c43-a17f-22d0732de039.png)
主库增加一条数据
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_33](//dev-img.mos.moduyun.com/20231020/9389eb8e-e29f-4c45-a137-7d526e568964.png)
从库看到也同步了
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_SQL_34](//dev-img.mos.moduyun.com/20231020/bf959a57-9f2a-4f06-86fc-f550d392210c.png)
到这里SQL Server2016的主从复制就完成了!
二、MySQL的主从复制搭建
2.1、MySQL主从复制结构图
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_35](//dev-img.mos.moduyun.com/20231020/cfe154b9-1ebd-48e9-a07d-bff13eee3194.png)
主库把增删查改的操作写入到binlog日志。
从库开启两个线程,一个IO线程,负责读取binlog日志到relay日志。一个SQL线程从relay日志读取数据写入从库DB
2.2、基于Docker搭建MySQL的主从
拉取镜像
准备两个文件,主库mysqld.cnf,上传到目录 /home/mysql/master
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
#id不要重复
server-id=11
从库mysald.cnf,上传到目录 /home/mysql/slave
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#id不重复
server-id=22
#从库不需要事务,改MyISAM快些
default-storage-engine=MyISAM
创建主库容器
docker run --name mysql-master -p 3307:3306 -v /home/mysql/master:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
创建从库容器
docker run --name mysql-slave -p 3308:3306 -v /home/mysql/slave:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
用连接工具连接上数据库,这里用DBeaver
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_36](//dev-img.mos.moduyun.com/20231020/8d5cb623-2596-45f8-bce4-20bafb154d47.png)
配置主服务
首先,进入容器:
[root@localhost ~]# docker exec -it mysql-master /bin/bash
bash-4.2#
链接MySQL
bash-4.2# mysql -u root -p123456
mysql>
修改 root 可以通过任何客户端连接
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql>
重启Master服务器
mysql> exit
Bye
bash-4.2# exit
exit
[root@localhost ~]# docker restart mysql-master
mysql-master
[root@localhost ~]#
再次进入master容器
docker exec -it mysql-master /bin/bash
连接 MySQL
查看数据库状态:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
把File的值“mysql-bin.000005”和 Position的值154记录下来
配置从服务器
首先,进入容器:
docker exec -it mysql-slave1 /bin/bash
连接 MySQL
修改 root 可以通过任何客户端连接(默认root用户可以对从数据库进行编辑的)
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
配置从同步主服务数据,执行如下SQL
change master to
master_host='192.168.101.20',
master_user='root',
master_log_file='mysql-bin.000005',
master_log_pos=154,
master_port=3307,
master_password='123456';
- master_log_file='mysql-bin.000005' 上面主库记录下来的值
- master_log_pos=154 上面主库记录下来的值
启动slave服务
查看slave状态
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_37](//dev-img.mos.moduyun.com/20231020/99cf19a9-e7e5-4904-96c8-b1be52922436.png)
验证主从库搭建结果
主库创建数据库
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_38](//dev-img.mos.moduyun.com/20231020/b7c1bd1d-6443-43e5-9288-7dd6ad1ad226.png)
刷新从库,也把数据库同步过来了
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_39](//dev-img.mos.moduyun.com/20231020/e8fc99b1-9d17-40d8-afd2-35a9a72d3095.png)
主库创建一张表
CREATE TABLE MyDB.sys_user (
id int auto_increment NOT NULL,
user_name varchar(150) NOT NULL,
account varchar(20) NOT NULL,
password varchar(100) NOT NULL,
phone varchar(50) NOT NULL,
create_time DATETIME NOT NULL,
CONSTRAINT sys_user_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci
AUTO_INCREMENT=1;
从库也同步了
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_40](//dev-img.mos.moduyun.com/20231020/9351ed7a-6e7c-45f2-922e-9af92bc4dfeb.png)
主库插入数据,从库也能同步。
到这里,MySQL的主从搭建就完成了!
三、EF Core代码读写分离实现
这里用.NET6 +EF Core6.0 +SQLServer演示。
建一个.NET6的web程序
安装NuGet包
Microsoft.EntityFrameworkCore(6.0.7)
Microsoft.EntityFrameworkCore.SqlServer(6.0.7)
appsetting.json增加 ConnectinStrings节点
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"WriteConnection": "Data Source=.;Database=MyDB;User ID=sa;Password=123456",
"ReadConnection": "Data Source=.;Database=MyDB_Copy;User ID=sa;Password=123456"
}
}
增加一个类DBConnectionOption.cs来接收连接配置
public class DBConnectionOption
{
public string WriteConnection { get; set; }
public string ReadConnection { get; set; }
}
增加一个类SysUser.cs来对应数据库表SysUser实体
public class SysUser
{
public int Id { get; set; }
public string UserName { get; set; }
public string Account { get; set; }
public string Password { get; set; }
public string Phone { get; set; }
public DateTime CreateTime { get; set; }
}
增加一个类MyDBContext.cs来访问数库上下文
public class MyDBContext : DbContext
{
private DBConnectionOption _readWriteOption;
public MyDBContext(IOptionsMonitor<DBConnectionOption> options)
{
_readWriteOption = options.CurrentValue;
}
public DbContext ReadWrite()
{
//把链接字符串设为读写(主库)
this.Database.GetDbConnection().ConnectionString = this._readWriteOption.WriteConnection;
return this;
}
public DbContext Read()
{
//把链接字符串设为之读(从库)
this.Database.GetDbConnection().ConnectionString = this._readWriteOption.ReadConnection;
return this;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(this._readWriteOption.WriteConnection); //默认主库
}
public DbSet<SysUser> SysUser { get; set; }
}
增加一个类DbContextExtend.cs来扩展上下文修改连接字符串
/// <summary>
/// 拓展方法
/// </summary>
public static class DbContextExtend
{
/// <summary>
/// 只读
/// </summary>
/// <param name="dbContext"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static DbContext Read(this DbContext dbContext)
{
if (dbContext is MyDBContext)
{
return ((MyDBContext)dbContext).Read();
}
else
throw new Exception();
}
/// <summary>
/// 读写
/// </summary>
/// <param name="dbContext"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static DbContext ReadWrite(this DbContext dbContext)
{
if (dbContext is MyDBContext)
{
return ((MyDBContext)dbContext).ReadWrite();
}
else
throw new Exception();
}
}
修改Program.cs,增加
builder.Services.Configure<DBConnectionOption>(builder.Configuration.GetSection("ConnectionStrings"));//注入多个链接
builder.Services.AddTransient<DbContext, MyDBContext>();
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_.NET Core_41](//dev-img.mos.moduyun.com/20231020/8f14228e-2a73-41cf-8b34-7d8993f2fa80.png)
验证
在HomeController的Index方法里实现读写分离操作
public IActionResult Index()
{
//新增-------------------
SysUser user = new SysUser()
{
UserName="李二狗",
Account="liergou",
Password=Guid.NewGuid().ToString(),
Phone="13345435554",
CreateTime=DateTime.Now
};
Console.WriteLine($"新增,当前链接字符串为:{_dbContext.Database.GetDbConnection().ConnectionString}");
_dbContext.ReadWrite().Add(user);
_dbContext.SaveChanges();
//只读--------------------------------
var users= _dbContext.Read().Set<SysUser>().ToList();
Console.WriteLine($"读取SysUser,数量为:{users.Count},当前链接字符串为:{_dbContext.Database.GetDbConnection().ConnectionString}");
return View();
}
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_42](//dev-img.mos.moduyun.com/20231020/2fba21b7-040c-4fae-b878-952f54e0a6aa.png)
执行结果:
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_sql_43](//dev-img.mos.moduyun.com/20231020/36c69a79-c421-4c45-b0f7-ceb2fe000c6b.png)
查看数据库,新增的数据也查入成功了。
![SQL Server、MySQL主从搭建,EF Core读写分离代码实现_服务器_44](//dev-img.mos.moduyun.com/20231020/b34643c2-7021-42d5-9676-9fb62cd2e45a.png)
这里读程序读写分离也完成了!
有没有细心的朋友发现读的时候日志只显示读到了3条记录,而上面一共有4条记录。
原因是主从同步会有延迟,从库没那么快同步到数据,一般都有个0.几到1秒的延迟,这个可以调优,这里就不说多内容了,有兴趣的可以去查资料操作一下。
延时是没办法解决的,只能让延时时间变得更小,也会有个毫秒级的延时,只能根据业务去选择,实时的数据还是读主库(例如刚插入就要刷新列表),而平时的查询则用从库就可以了。
到这里全部就完成了!
源码地址:https://github.com/weixiaolong325/EFCoreReadWriteSeparate