MySQL 双向同步配置

发布于：2025-03-19 ⋅ 阅读:(17) ⋅ 点赞:(0)

分别配置mysql1和mysql2互为主从，实现双主部署

参考：MySQL数据库实现主主同步_mysql主主同步-CSDN博客

MySQL 高可用性—keepalived+mysql双主（有详细步骤和全部配置项解释）-腾讯云开发者社区-腾讯云

Mysql主从复制原理

在这里插入图片描述

从上图看大致分成三个步骤

  1. master将增删改操作记录到二进制日志(binlog)中
  2. slave通过IOthread线程将master的二进制日志拷贝到它的中继日志(Relay log)；
  3. slave重做中继日志中的事件，将改变反映它自己的数据。
    该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，在事件写入二进制日志完成后，master通知存储引擎提交事务。
    下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
    SQLthread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。

一.准备工作

1.准备两台虚拟机

  • 名称分别为mysql1和mysql2
  • mysql1的ip是192.168.154.136和mysql2的ip是192.168.154.138
  • 两台虚拟机的防火墙都关掉
  • 两台虚拟机中下载mysql8.0免安装版
    在这里插入图片描述

2.下载免安装版的mysql

mysql8.0免安装下载网址：https://dev.mysql.com/downloads/mysql/8.0.html

参考：MySQL 8.0.20安装教程（Win10）—— 免安装版_安装mysql8.0.20-CSDN博客

参考：MySQL完全卸载以及安装8.0版本（Windows11版本一步到位）_mysql8.0完全卸载教程-CSDN博客

2.1卸载之前的mysql8.0

如果之前电脑上安装了mysql，需要删除否则可能导致免安装版安装的时候报错

2.2下载mysql8.0免安装版

复制网址到浏览器中，下载mysql8.0免安装版，下载完是个zip压缩包

下载完成后解压到本地，刚下完没有my.ini配置文件，需要手动创建

my.ini的基本配置，basedir和datadir需要根据路径做调整

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录，一定要与上面的安装路径保持一致
basedir=C:\\Program Files\\MySQL\\mysql-8.0.40-winx64
# 设置mysql数据库的数据的存放目录，自动生成，无需手动创建，当然也可以放在其他地方
datadir=C:\\Program Files\\MySQL\\mysql-8.0.40-winx64\\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口，不建议修改，这是公认端口号
port=3306
default-character-set=utf8mb4

设置完my.ini后进入bin目录，打开cmd，开启服务

mysqld --initialize --console
mysqld --install
net stop mysql
net start mysql

  • 第一个红框是进入mysql的bin路径
  • 第二个红框是初始化一个新的 MySQL 数据库实例
  • 第三个红框是mysql的初始密码，后续登录后可以修改成别的

3.修改虚拟机mysql my.ini读取的位置(免安装版的不用管这个)

遇到的问题：之前一直卡在这一步，刚下载完之后看到没有my.ini就自己创建了一个my.ini，在里面写了server-id=2进去，想测一下mysql服务是否能读取到my.ini，结果一直读取不到，查了资料后发现mysql服务读取的my.ini路径并不是Program Files而是在programData下的MySQL

下面是解决的方法

参考：Windows11下Mysql8.0修改My.ini配置文件未生效的问题_mysql 配置文件修改无效-CSDN博客

下载完默认读取的是C:\ProgramData\MySQL\MySQL Server 8.0目录下的my.ini文件，修改为C:\Program Files\MySQL\MySQL Server 8.0路径下的my.ini文件

win+r输入regedit进入注册表

按照第一个红框的路径找到imagePath后，修改imagePath中读取的my.ini位置

“C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe” --defaults-file=“C:\Program Files\MySQL\MySQL Server 8.0\my.ini” MySQL

修改成上面这样的；指定了MySQL服务的可执行文件路径和配置文件路径

把programdata路径下的my.ini文件复制到programFile下；之后需要修改my.ini的配置

两台虚拟机都进行这样的配置

4.修改配置文件

[mysqld]
server-id=11     
log-bin=mysql-bin  
auto_increment_increment=2  
auto_increment_offset=1
replicate-do-db=demo_db

  • server-id=11 #数据库的唯一ID，另一台设置成了12
  • log-bin=mysql-bin #存放日志文件位置
  • auto_increment_increment=2 #控制主键自增的步长，几台服务器就设置几
  • auto_increment_offset=1 #设置自增起始值。这个是第1台，那么为1，下一台则为2。
  • replicate-do-db=demo_db #选择要同步的数据库。
[mysqld]
server-id=12
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=demo_db

下面的是第二台服务器，因此id和auto_increment_offset需要调整

主机和从机修改完配置文件后都要重启一下mysql服务

登录mysql后，用SHOW GLOBAL VARIABLES LIKE ‘server_id’;查看服务id是否被更改

表明my.ini读取成功

二.第三台计算机用户连接权限问题

在没有给第三胎计算机的用户设置权限的时候，连接虚拟机的mysql会被拒绝，需要创建用户并添加权限

CREATE USER 'root'@'YUJIANAN' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'YUJIANAN';

用户YUJIANAN可以用root和123456连接数据库；为了方便授予了all peivileges，包括修改数据库的权限

在两个虚拟机的mysql中都要给第三台计算机的用户设置权限

修改之后就可以连接上了，并且能够对数据库进行修改；

三. mysql1为主mysql2为从配置

1.mysql1创建master11用户

mysql -uroot -p123456
create user 'master11'@'%' identified with mysql_native_password by '#Master11';
GRANT REPLICATION SLAVE ON *.* TO 'master11'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'master11'@'%';
FLUSH PRIVILEGES;
show grants for 'master11'@'%';
show master status;

master11用户是在mysql2绑定mysql1数据库时用到的

2.mysql2绑定mysql1数据库

mysql -uroot -p123456
change master to master_host='192.168.154.136',master_user='master11',master_password='#Master11',master_log_file='mysql-bin.000003',master_log_pos=2328;
start slave;
show slave status \G

红框内的两个值都是Yes表示已经同步成功，如果不全是Yes会有相应的报错可以看到

这一步后，实现了mysql1为主mysql2为从的配置，接下来再配置mysql2作为主机mysql1作为从机，实现双主的效果

四.mysql2为主，mysql1为从机配置

1.mysql2创建master12用户

mysql -uroot -p123456
create user 'master12'@'%' identified with mysql_native_password by '#Master12';
GRANT REPLICATION SLAVE ON *.* TO 'master12'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'master12'@'%';
FLUSH PRIVILEGES;
show grants for 'master12'@'%';
show master status;

2.mysql1绑定mysql2数据库

mysql -uroot -p123456
change master to master_host='192.168.154.138',master_user='master12',master_password='#Master12',master_log_file='mysql-bin.000002',master_log_pos=1080;
start slave;
show slave status \G

红框内的两个值都是Yes表示已经同步成功

五.测试结果

1.黑框测试

create database demo_db;
use demo_db
create table demo_tb(id int not null,name varchar(20) default 'username');
insert into demo_tb values(1,'zhangsan'), (2,'lisi'), (3,'kunkun');

在虚拟机mysql1中创建数据库和数据表添加数据

show databases;
select * from demo_db.demo_tb;
use demo_db
insert into demo_tb values(4,'liao'),(5,'chengpi'),(6,'mahua');

在虚拟机mysql1添加了三条数据后在虚拟机mysql2中也可以查询到

在虚拟机mysql2中添加了三条数据

select * from demo_tb;

虚拟机mysql1也能查询到

2.navicat测试

mysql1添加一条数据，mysql2中刷新后也会有

mysql2中新建表，mysql1中也会有

3.重启测试

3.1 关闭mysql2

关闭虚拟机mysql2后重启，重启后mysql1和mysql2的双主模式仍在

3.2 关闭mysql1

关闭虚拟机mysql1后重启，重启后mysql1和mysql2的双主模式仍在

3.3 关闭mysql2，并在mysql1中写数据

关闭虚拟机mysql2

关闭了虚拟机mysql2，连不上虚拟机mysql2的数据库，在mysql1的demo_tb表中新增了一条数据

重启mysql2后，双主模式仍在

重启后连接虚拟机mysql2的数据库，数据同步成功

3.4 关闭mysql1，并在mysql2中写数据

关闭虚拟机mysql1

关闭了虚拟机mysql1，连不上虚拟机mysql1的数据库，在mysql2的demo_tb表中新增了一条数据

重启mysql1后，双主模式仍在

重启后连接虚拟机mysql1的数据库，数据同步成功

3.5 mysql1和mysql2都关闭

同时把mysql1和mysql2关闭

重启后连接虚拟机mysql1和虚拟机mysql2的数据库，数据没有丢失

重启mysql1和mysql2后，双主模式仍在

4.关闭主从复制，修改数据库看启动后数据是否同步

可以看到已经把两台虚拟机的主从关掉了

在虚拟机mysql1的数据库demo_db下创建新的表

启动虚拟机mysql1和mysql2的主从模式

同步效果仍在

虚拟机mysql2数据库demo_tb数据库中的test表自动创建了

5.测试时遇到的问题

5.1重启两台虚拟机后主从同步失效

这个是12.4遇到的，当时重启了两个虚拟机后就出现了这个问题；12.5把原来的mysql8.0卸载掉了，用了免安装版重新跑了一下这个流程，也测试了一下两台虚拟机都重启，结果写在 五.测试结果 3.5mysql1和mysql2都关闭中 ，在12.5没有复现这个问题。

推测可能是因为之前在开启了主从之后又去给第三台计算机的用户添加了权限导致偏移值的变化，后续如果又发现了这个问题再找找原因

5.2 navicat连接失败

本地用户连接不上navicat是因为权限不够，需要在虚拟机的mysql中授予相应的权限，详情见二.第三台计算机用户连接权限问题

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布