MySQL 双向同步配置
分别配置mysql1和mysql2互为主从，实现双主摆设参考：MySQL数据库实现主主同步_mysql主主同步-CSDN博客
MySQL 高可用性—keepalived+mysql双主（有详细步骤和全部配置项解释）-腾讯云开发者社区-腾讯云
Mysql主从复制原理
https://i-blog.csdnimg.cn/direct/821f203c5df94b909c160bd6bc9b16d3.png
从上图看大致分成三个步骤
[*]master将增删改操作记录到二进制日记(binlog)中
[*]slave通过IOthread线程将master的二进制日记拷贝到它的中继日记(Relay log)；
[*]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免安装版
https://i-blog.csdnimg.cn/direct/e353c5704d664b74ab8d1ed235cde068.png
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，需要删除否则大概导致免安装版安装的时候报错
https://i-blog.csdnimg.cn/img_convert/399c1d8ececac755b2a6066b600c58f2.png
https://i-blog.csdnimg.cn/img_convert/3f7607552d31234e508a24067ad7cdba.png
2.2下载mysql8.0免安装版
复制网址到浏览器中，下载mysql8.0免安装版，下载完是个zip压缩包
https://i-blog.csdnimg.cn/img_convert/708d415402bdb7c61b5c1428301b5806.png
下载完成后解压到本地，刚下完没有my.ini配置文件，需要手动创建
https://i-blog.csdnimg.cn/img_convert/edd2821f6618369d3396295ae7a1c505.png
my.ini的根本配置，basedir和datadir需要根据路径做调整
# 设置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客户端默认字符集
default-character-set=utf8mb4
# 设置mysql客户端连接服务端时默认使用的端口，不建议修改，这是公认端口号
port=3306
default-character-set=utf8mb4
设置完my.ini后进入bin目录，打开cmd，开启服务
mysqld --initialize --console
mysqld --install
net stop mysql
net start mysql
https://i-blog.csdnimg.cn/img_convert/cb6d4a9786921436475012de7b315d39.png
[*]第一个红框是进入mysql的bin路径
[*]第二个红框是初始化一个新的 MySQL 数据库实例
[*]第三个红框是mysql的初始密码，后续登录后可以修改成别的
https://i-blog.csdnimg.cn/img_convert/b260e440d175dd6b982e4092e5c99366.png
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进入注册表
https://i-blog.csdnimg.cn/img_convert/07266a8675d0412650a8765dffa074ba.png
按照第一个红框的路径找到imagePath后，修改imagePath中读取的my.ini位置
https://i-blog.csdnimg.cn/img_convert/8ad6656b9a91b7541e254047bd8269c7.png
“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服务的可执行文件路径和配置文件路径
https://i-blog.csdnimg.cn/img_convert/494e130a8b7dd6e593b91cf104225a97.png
把programdata路径下的my.ini文件复制到programFile下；之后需要修改my.ini的配置
https://i-blog.csdnimg.cn/img_convert/2d02d7c7e7ea0b7c7a3307b8fb3d6fec.png
两台假造机都举行如许的配置
4.修改配置文件
server-id=11
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
replicate-do-db=demo_db
https://i-blog.csdnimg.cn/img_convert/1779ed4e20397c3ec136fa67d5dc9dc0.png
[*]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 #选择要同步的数据库。
server-id=12
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=demo_db
下面的是第二台服务器，因此id和auto_increment_offset需要调整
https://i-blog.csdnimg.cn/img_convert/e6b2124efb22a44cfd7c772281a3b770.png
主机和从机修改完配置文件后都要重启一下mysql服务
登录mysql后，用SHOW GLOBAL VARIABLES LIKE ‘server_id’;检察服务id是否被更改
https://i-blog.csdnimg.cn/img_convert/97949ee54d2e24b97848fc6d4156e4c4.png
表明my.ini读取成功
二.第三台计算机用户连接权限题目
在没有给第三胎计算机的用户设置权限的时候，连接假造机的mysql会被拒绝，需要创建用户并添加权限
https://i-blog.csdnimg.cn/img_convert/cd5b5eb30d0b0e4066bb862216165f64.png
CREATE USER 'root'@'YUJIANAN' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'YUJIANAN';
用户YUJIANAN可以用root和123456连接数据库；为了方便授予了all peivileges，包括修改数据库的权限
https://i-blog.csdnimg.cn/img_convert/9feaeae2b835e83c240dd0b5d8f35d0c.png
在两个假造机的mysql中都要给第三台计算机的用户设置权限
https://i-blog.csdnimg.cn/img_convert/fd0cbe494c29fd9fb4ce553595f6c10d.png
修改之后就可以连接上了，并且可以或许对数据库举行修改；
三. 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数据库时用到的
https://i-blog.csdnimg.cn/img_convert/49bcd1ca87e3796ef7e729962d6af1ce.png
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
https://i-blog.csdnimg.cn/img_convert/4eaba885339445c4cb4348ba33cde46a.png
红框内的两个值都是Yes表示已经同步成功，假如不满是Yes会有相应的报错可以看到
https://i-blog.csdnimg.cn/img_convert/47267c1363ee420eb372b5e70b79e6ce.png
这一步后，实现了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;
https://i-blog.csdnimg.cn/img_convert/a87e1954a5d11c403eb5e6f8e61a089f.png
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
https://i-blog.csdnimg.cn/img_convert/18441626910453c3e5ba85b3b323b649.png
红框内的两个值都是Yes表示已经同步成功
https://i-blog.csdnimg.cn/img_convert/0b368f44ec002e3ef752de48a35225af.png
五.测试结果
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中创建数据库和数据表添加数据
https://i-blog.csdnimg.cn/img_convert/9f0dac07d196d997211b050f76780f64.png
show databases;
select * from demo_db.demo_tb;
use demo_db
insert into demo_tb values(4,'liao'),(5,'chengpi'),(6,'mahua');
在假造机mysql1添加了三条数据后在假造机mysql2中也可以查询到
https://i-blog.csdnimg.cn/img_convert/5d3537e685586ecbd39b4008c027cd8f.png
在假造机mysql2中添加了三条数据
https://i-blog.csdnimg.cn/img_convert/985e9c9c72b8ac74198c81ba35a3eaeb.png
select * from demo_tb;
假造机mysql1也能查询到
https://i-blog.csdnimg.cn/img_convert/b79df8ff139dc085cc926b0ea86eff1a.png
2.navicat测试
mysql1添加一条数据，mysql2中刷新后也会有
https://i-blog.csdnimg.cn/img_convert/df19a17c7b136cc5aef552ef337c58fa.png
https://i-blog.csdnimg.cn/img_convert/289fff48d046935f323d72d13397d219.png
mysql2中新建表，mysql1中也会有
https://i-blog.csdnimg.cn/img_convert/68b2a188980007dfc03fb621e86289c8.png
https://i-blog.csdnimg.cn/img_convert/08e27219660ee22c398861003890f22d.png
3.重启测试
3.1 关闭mysql2
关闭假造机mysql2后重启，重启后mysql1和mysql2的双主模式仍在
https://i-blog.csdnimg.cn/img_convert/422f486e438e142b28488fe461974420.png
https://i-blog.csdnimg.cn/img_convert/184a15447da607cbc9bba278e897ee2d.png
https://i-blog.csdnimg.cn/img_convert/64093268323d484aa962898c2e4f49d2.png
3.2 关闭mysql1
关闭假造机mysql1后重启，重启后mysql1和mysql2的双主模式仍在
https://i-blog.csdnimg.cn/img_convert/a9e584513f48471f31111272dee09f27.png
https://i-blog.csdnimg.cn/img_convert/919257261b659a0a91cebd8160e3a5c6.png
https://i-blog.csdnimg.cn/img_convert/9160994ea68b3306a84d3faeecbb849d.png
3.3 关闭mysql2，并在mysql1中写数据
关闭假造机mysql2
https://i-blog.csdnimg.cn/img_convert/4b393f0d6e3018bd3200cdc812290907.png
关闭了假造机mysql2，连不上假造机mysql2的数据库，在mysql1的demo_tb表中新增了一条数据
https://i-blog.csdnimg.cn/img_convert/58ca7f3f3a8314768efb18d7bbb16d1a.png
重启mysql2后，双主模式仍在
https://i-blog.csdnimg.cn/img_convert/e22d1165f43fac694216ce81727650a0.png
https://i-blog.csdnimg.cn/img_convert/0c82cacac33dd86cb2237123bc8a2ee1.png
重启后连接假造机mysql2的数据库，数据同步成功
https://i-blog.csdnimg.cn/img_convert/3d090af6071c9276c6f96c04a6473bcb.png
3.4 关闭mysql1，并在mysql2中写数据
关闭假造机mysql1
https://i-blog.csdnimg.cn/img_convert/9aff86cdbcd8f17aa2664a20452df026.png
关闭了假造机mysql1，连不上假造机mysql1的数据库，在mysql2的demo_tb表中新增了一条数据
https://i-blog.csdnimg.cn/img_convert/10d63edbb1116abcd53987ad5cfc7997.png
重启mysql1后，双主模式仍在
https://i-blog.csdnimg.cn/img_convert/ec64f5cf4d45f159f837fb3ff2cbf927.png
https://i-blog.csdnimg.cn/img_convert/a9d08a5021f4d9a1ae823c516239d96f.png
重启后连接假造机mysql1的数据库，数据同步成功
https://i-blog.csdnimg.cn/img_convert/5bbbc1b4ab6e73f1cc862e0f1eaa9bd8.png
3.5 mysql1和mysql2都关闭
同时把mysql1和mysql2关闭
https://i-blog.csdnimg.cn/img_convert/172e3072790ec64055748e1d7992ed3a.png
重启后连接假造机mysql1和假造机mysql2的数据库，数据没有丢失
https://i-blog.csdnimg.cn/img_convert/199952c1743980dd956a6ac8ebf82843.png
https://i-blog.csdnimg.cn/img_convert/2e35eed005305fda06474a660b62ee7f.png
重启mysql1和mysql2后，双主模式仍在
https://i-blog.csdnimg.cn/img_convert/1a8a3b1ee27ed55d43514564755c5775.png
https://i-blog.csdnimg.cn/img_convert/c0c881b14b7f9b2226cf61f5e16bdca5.png
4.关闭主从复制，修改数据库看启动后数据是否同步
可以看到已经把两台假造机的主从关掉了
https://i-blog.csdnimg.cn/img_convert/9f808216a9ba175daef22388a8985af7.png
https://i-blog.csdnimg.cn/img_convert/496e355d338aa832fdba9aba15202988.png
在假造机mysql1的数据库demo_db下创建新的表
https://i-blog.csdnimg.cn/img_convert/7dd02c92959a8f66e708ec108c5acba3.png
https://i-blog.csdnimg.cn/img_convert/2beb496fccf62ead54f217a4b9e288c7.png
启动假造机mysql1和mysql2的主从模式
https://i-blog.csdnimg.cn/img_convert/1b8786d9420c73546927c1e5f44c9e4a.png
同步效果仍在
https://i-blog.csdnimg.cn/img_convert/1aa26a4143432be7bef63bbfb2dc47e2.png
假造机mysql2数据库demo_tb数据库中的test表自动创建了
https://i-blog.csdnimg.cn/img_convert/930ed1f884cc317d9cb38baddccfc208.png
5.测试时遇到的题目
5.1重启两台假造机后主从同步失效
https://i-blog.csdnimg.cn/img_convert/5f6a9afad8b396647342badd2c56fcf2.png
这个是12.4遇到的，当时重启了两个假造机后就出现了这个题目；12.5把原来的mysql8.0卸载掉了，用了免安装版重新跑了一下这个流程，也测试了一下两台假造机都重启，结果写在 五.测试结果 3.5mysql1和mysql2都关闭中 ，在12.5没有复现这个题目。
推测大概是因为之前在开启了主从之后又去给第三台计算机的用户添加了权限导致偏移值的变化，后续假如又发现了这个题目再找找原因
5.2 navicat连接失败
本地用户连接不上navicat是因为权限不够，需要在假造机的mysql中授予相应的权限，详情见二.第三台计算机用户连接权限题目
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]