SQL Server、MySQL主從搭建，EF Core讀寫分離程式碼實現
2022-07-28 09:02:33
一、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]
搭建釋出伺服器
複製》設定分發
這裡建立一個自己的路徑，共用資料夾
分發資料庫
釋出伺服器
然後下一步完成
啟用代理
服務確認一下登陸許可權
到這裡釋出伺服器就建好了。
釋出
釋出就是把主庫的資料或操作釋出到釋出伺服器
現在主庫裡錄入了兩條資料
新建釋出
選擇釋出的資料庫
釋出型別
這裡有幾種不同釋出方式，根據自己業務場景選擇，網際網路一般是事務釋出，有操作就同步。
選擇同步的表
一直下一步到這裡，勾選初始化訂閱
代理安全性
下一步
釋出名稱
完成
這時候在上面設的釋出伺服器的共用資料夾中能看到有釋出檔案了
建立訂閱
新建一個從庫「MyDb_Copy」，為一個沒建立表的空庫
新建訂閱
選擇訂閱的釋出
。
選擇推播方式（釋出伺服器主動推播），還是拉取方式（從庫伺服器拉取方式），一個從庫選推播，多個從庫選擇拉取方式
選擇訂閱資料庫
分發代理安全性
一直下一步，直到完成！
驗證
看從庫資料同步過來了
主庫增加一條資料
從庫看到也同步了
到這裡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 dbContext = _dbContext.Read();
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