2025年2月24日 星期一 甲辰(龙)年 腊月廿四 设为首页 加入收藏
rss
您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

Windows环境下搭建MySQL主从同步实现读写分离(保姆级教程,超详细!!!)

时间:09-30来源:作者:点击数:22
文章目录

前言

面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改操作,从库负责处理查询操作,能够有效的避免由数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。

MySQL的读写分离是一种常见的数据库架构模式,用于提高数据库的可用性和扩展性。通过将读取请求分散到多个从服务器(slave servers),可以减轻主服务器(master server)的负载,从而提高读取性能。同时,写入请求仍然集中在一个主服务器上执行,保证了数据的一致性和完整性。

在这里插入图片描述

读写分离的基本原理

主服务器(Master):负责所有写操作(如插入、更新、删除)。

从服务器(Slave):通过复制主服务器的数据来保持与主服务器的数据一致性,负责读取请求(如查询)。

环境介绍及说明

mysql版本:5.7.22

主库端口:3306

从库端口:3307

mysql官网下载:https://downloads.mysql.com/archives/community/

注意:如果主从库两者版本不一致,一般主库的版本需要比从库的版本低,避免由于版本问题导致有些sql不能执行的问题。

主库my.ini初始配置

  • [mysql]
  • # 设置mysql客户端默认字符集
  • default-character-set=utf8
  • [mysqld]
  • # 设置3306端口
  • port = 3306
  • # 设置mysql的安装目录
  • basedir=D:\\tools\\mysql-5.7.22-winx64
  • # 设置mysql数据库的数据的存放目录(自动生成,不然可能报错)
  • datadir=D:\\tools\\mysql-5.7.22-winx64\\data
  • # 允许最大连接数
  • max_connections=10000
  • # 允许最大连接人数
  • max_user_connections=1000
  • # 服务端使用的字符集默认为8比特编码的latin1字符集
  • character-set-server=utf8
  • # 创建新表时将使用的默认存储引擎
  • default-storage-engine=INNODB
  • # 连接时间一年
  • wait_timeout=31536000
  • interactive_timeout=31536000

创建用于同步的数据库和表

创建user_db库用于同步数据

在这里插入图片描述

创建user表

  • CREATE TABLE `user` (
  • `id` int(11) NOT NULL,
  • `name` varchar(50) DEFAULT NULL,
  • PRIMARY KEY (`id`)
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一、新增mysql从库

1.复制mysql文件夹

打开原有的mysql文件夹,复制一份改为从库

如:D:\tools\mysql-5.7.22-winx64(作为主库) --> D:\tools\mysql-5.7.22-winx64-s1(作为从库)

在这里插入图片描述

2.修改从库的my.ini

  • [mysql]
  • # 设置mysql客户端默认字符集
  • default-character-set=utf8
  • [mysqld]
  • # 设置3307端口
  • port = 3307
  • # 设置mysql的安装目录
  • basedir=D:\\tools\\mysql-5.7.22-winx64-s1
  • # 设置mysql数据库的数据的存放目录(自动生成,不然可能报错)
  • datadir=D:\\tools\\mysql-5.7.22-winx64-s1\\data
  • # 允许最大连接数
  • max_connections=10000
  • # 允许最大连接人数
  • max_user_connections=1000
  • # 服务端使用的字符集默认为8比特编码的latin1字符集
  • character-set-server=utf8
  • # 创建新表时将使用的默认存储引擎
  • default-storage-engine=INNODB
  • # 连接时间一年
  • wait_timeout=31536000
  • interactive_timeout=31536000

3.安装到windows服务

然后将从库安装为windows服务,注意配置文件位置:

在cmd窗口(命令行窗口)中输入以下命令

  • mysqld install MYSQL5s1
在这里插入图片描述

打开服务可以看到已经安装到服务中

在这里插入图片描述

注意:以管理员身份运行命令

由于从库是从主库复制过来的,因此里面的数据完全一致,可使用原来的账号、密码登录。

点击启动此服务,启动成功后即完成第一步。

在这里插入图片描述

二、在my.ini中配置主、从库相关参数

1.主库新增配置参数

  • # 主从配置
  • server_id = 1 # 主库和从库需要不一致,分别配一个唯一的ID编号
  • log_bin=master-bin # 二进制文件存放路径,存放在根目录data文件夹下
  • #设置需要同步的数据库
  • binlog_do_db=user_db
  • #屏蔽系统库同步
  • binlog_ignore_db=mysql
  • binlog_ignore_db=information_schema
  • binlog_ignore_db=performance_schema

注意:要添加在[mysql]下

完整my.ini配置

  • [mysql]
  • # 设置mysql客户端默认字符集
  • default-character-set=utf8
  • [mysqld]
  • # 设置3306端口
  • port = 3306
  • # 主从配置
  • server_id = 1 # 主库和从库需要不一致,分别配一个唯一的ID编号
  • log_bin=master-bin # 二进制文件存放路径,存放在根目录data文件夹下
  • #设置需要同步的数据库
  • binlog_do_db=user_db
  • #屏蔽系统库同步
  • binlog_ignore_db=mysql
  • binlog_ignore_db=information_schema
  • binlog_ignore_db=performance_schema
  • # 设置mysql的安装目录
  • basedir=D:\\tools\\mysql-5.7.22-winx64
  • # 设置mysql数据库的数据的存放目录(自动生成,不然可能报错)
  • datadir=D:\\tools\\mysql-5.7.22-winx64\\data
  • # 允许最大连接数
  • max_connections=10000
  • # 允许最大连接人数
  • max_user_connections=1000
  • # 服务端使用的字符集默认为8比特编码的latin1字符集
  • character-set-server=utf8
  • # 创建新表时将使用的默认存储引擎
  • default-storage-engine=INNODB
  • # 连接时间一年
  • wait_timeout=31536000
  • interactive_timeout=31536000
不同版本参数不同问题

有些mysql版本可能参数不一样,导致my.ini配置完重启mysql失败,可以尝试使用以下配置

  • [mysqld]
  • #开启二进制日志,记录了所有更改数据库数据的SQL语句
  • log‐bin = mysql‐bin
  • #设置服务id,主从不能一致
  • server‐id = 1
  • #设置需要同步的数据库
  • binlog‐do‐db=sharding_user_db
  • #屏蔽系统库同步
  • binlog‐ignore‐db=mysql
  • binlog‐ignore‐db=information_schema
  • binlog‐ignore‐db=performance_schema

详情可以看这篇文章:https://www.cdsy.xyz/computer/soft/database/mysql/240930/cd63735.html

2.从库新增配置参数

  • server_id = 2 # 主库和从库需要不一致,分别配一个唯一的ID编号
  • log_bin=master-bin # 二进制文件存放路径,存放在根目录data文件夹下
  • #设置需要同步的数据库
  • replicate_wild_do_table=user_db.%
  • #屏蔽系统库同步
  • replicate_wild_ignore_table=mysql.%
  • replicate_wild_ignore_table=information_schema.%
  • replicate_wild_ignore_table=performance_schema.%

注意:要添加在[mysql]下

完整my.ini配置

  • [mysql]
  • # 设置mysql客户端默认字符集
  • default-character-set=utf8
  • [mysqld]
  • # 设置3307端口
  • port = 3307
  • server_id = 2 # 主库和从库需要不一致,分别配一个唯一的ID编号
  • log_bin=master-bin # 二进制文件存放路径,存放在根目录data文件夹下
  • #设置需要同步的数据库
  • replicate_wild_do_table=user_db.%
  • #屏蔽系统库同步
  • replicate_wild_ignore_table=mysql.%
  • replicate_wild_ignore_table=information_schema.%
  • replicate_wild_ignore_table=performance_schema.%
  • # 设置mysql的安装目录
  • basedir=D:\\tools\\mysql-5.7.22-winx64-s1
  • # 设置mysql数据库的数据的存放目录(自动生成,不然可能报错)
  • datadir=D:\\tools\\mysql-5.7.22-winx64-s1\\data
  • # 允许最大连接数
  • max_connections=10000
  • # 允许最大连接人数
  • max_user_connections=1000
  • # 服务端使用的字符集默认为8比特编码的latin1字符集
  • character-set-server=utf8
  • # 创建新表时将使用的默认存储引擎
  • default-storage-engine=INNODB
  • # 连接时间一年
  • wait_timeout=31536000
  • interactive_timeout=31536000

3.删除auto.cnf文件

主从MySQL下的数据(data)目录下有个文件auto.cnf,文件中定义了uuid,要保证主从数据库实例的uuid不一样,建议直接删除掉,重启服务后将会重新生成。

在这里插入图片描述

4.重启主库和从库

  • net start [主库服务名]
  • net start [从库服务名]

或在服务中重启服务

在这里插入图片描述

三、关联主从库

1.主库授权主从复制专用账号

进入主库,我这里使用navicat

  • #授权主库复制专用账号
  • GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync123';
  • #刷新权限
  • FLUSH PRIVILEGES;
  • #确认位点 记录下文件名以及位点
  • show master status;
在这里插入图片描述

2.设置从库向主库同步数据、并检查链路

先停止同步

  • STOP SLAVE;

修改从库指向到主库,使用上一步记录的文件名以及位点

  • CHANGE MASTER TO
  • master_host = 'localhost',
  • MASTER_PORT=3306,
  • master_user = 'db_sync',
  • master_password = 'db_sync123',
  • master_log_file = 'master-bin.000002',
  • master_log_pos = 1090;

字段说明:

master_host :主库的ip

MASTER_PORT:主库的端口号,不写默认3306

master_user 和 master_password: 为上面主库操作中授权主备授权主库复制专用账号时填写的字符串

master_log_file和master_log_pos:填写在主库中通过show master status查询到的File和Position字段

  • #启动同步
  • START SLAVE;
  • #查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功,如果不为Yes,请检查error_log,然后排查相关异常。
  • show slave status;

当Slave_IO_Running和Slave_SQL_Running字段为Yes时表示配置成功。

在这里插入图片描述

3.测试

在主库user表中添加数据

在这里插入图片描述

刷新从库user表

在这里插入图片描述

发现自动同步数据成功。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐