SQL Server、MySQL主从搭建，EF Core读写分离代码实现

一、SQL Server的主从复制搭建

1.1、SQL Server主从复制结构图.

SQL Server的主从通过发布订阅来实现主库把增删改操作发布到发布服务器，从库通过订阅发布服务器，发布服务器把操作推送到从库进行同步。

1.2、基于SQL Server2016实现主从

新建一个主库“MyDB”

建一个表"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读写分离代码实现

完成 SQL Server、MySQL主从搭建，EF Core读写分离代码实现

这时候在上面设的发布服务器的共享文件夹中能看到有发布文件了 SQL Server、MySQL主从搭建，EF Core读写分离代码实现

创建订阅

新建一个从库“MyDb_Copy”，为一个没创建表的空库

新建订阅

选择订阅的发布

选择推送方式（发布服务器主动推送），还是拉取方式（从库服务器拉取方式），一个从库选推送，多个从库选择拉取方式

选择订阅数据库

分发代理安全性

一直下一步，直到完成！

验证

看从库数据同步过来了

主库增加一条数据

从库看到也同步了 SQL Server、MySQL主从搭建，EF Core读写分离代码实现

到这里SQL Server2016的主从复制就完成了！

二、MySQL的主从复制搭建

2.1、MySQL主从复制结构图

主库把增删查改的操作写入到binlog日志。

从库开启两个线程，一个IO线程，负责读取binlog日志到relay日志。一个SQL线程从relay日志读取数据写入从库DB

2.2、基于Docker搭建MySQL的主从

拉取镜像

docker pull mysql:5.7

准备两个文件,主库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

配置主服务

首先，进入容器：

[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 -u root -p123456

查看数据库状态：

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

mysql -u root -p123456

修改 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服务

mysql>start slave;

查看slave状态

show slave status \G;

验证主从库搭建结果

主库创建数据库

刷新从库，也把数据库同步过来了

主库创建一张表

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;

从库也同步了

主库插入数据，从库也能同步。

到这里，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>();

验证

在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();
}

执行结果:

查看数据库，新增的数据也查入成功了。

这里读程序读写分离也完成了！

有没有细心的朋友发现读的时候日志只显示读到了3条记录，而上面一共有4条记录。

原因是主从同步会有延迟，从库没那么快同步到数据，一般都有个0.几到1秒的延迟，这个可以调优，这里就不说多内容了，有兴趣的可以去查资料操作一下。

延时是没办法解决的，只能让延时时间变得更小，也会有个毫秒级的延时，只能根据业务去选择，实时的数据还是读主库（例如刚插入就要刷新列表），而平时的查询则用从库就可以了。

到这里全部就完成了！

源码地址：https://github.com/weixiaolong325/EFCoreReadWriteSeparate

1.1、SQL Server主从复制结构图.

1.2、基于SQL Server2016实现主从

董川民