mysql的读写分离怎么实现（十分钟带你了解SQL）
mysql的读写分离怎么实现（十分钟带你了解SQL）发布服务器分发数据库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 =
目录
- 一、SQL Server的主从复制搭建1.1、Sql Server主从复制结构图1.2、基于SQL Server2016实现主从
- 二、mysql的主从复制搭建2.1、MySQL主从复制结构图2.2、基于Docker搭建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]
搭建发布服务器
复制》配置分发
这里创建一个自己的路径，共享文件夹
分发数据库
发布服务器
然后下一步完成
启用代理
服务确认一下登陆权限
到这里发布服务器就建好了。
发布
发布就是把主库的数据或操作发布到发布服务器
现在主库里录入了两条数据
新建发布
选择发布的数据库
发布类型
这里有几种不同发布方式，根据自己业务场景选择，互联网一般是事务发布，有操作就同步。
选择同步的表
一直下一步到这里，勾选初始化订阅
代理安全性
下一步
发布名称
完成
这时候在上面设的发布服务器的共享文件夹中能看到有发布文件了
创建订阅
新建一个从库“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秒的延迟，这个可以调优，这里就不说多内容了，有兴趣的可以去查资料操作一下。
到这里全部就完成了！
