详解MySQL实现主从复制(多主多从)

最新推荐文章于 2024-05-21 16:04:46 发布
最新推荐文章于 2024-05-21 16:04:46 发布
阅读量808 收藏 1
点赞数 2
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhangzhikai1/article/details/110522181
版权

MySQL实现主从复制

文章目录

前言

windows 下的mysql主从复制

一、什么是主从复制？

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

二、主从复制的作用

1、主数据库出现问题，可以切换到从数据库。
2、可以进行数据库层面的读写分离，
3、可以在从数据库上进行日常备份

三、复制过程

Binary log：主数据库的二进制日志Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

四、主机一号配置

首先按照常规方式按照一个mysql实例：

  1. 将下载后的mysql压缩包解压如下图：
    在这里插入图片描述
  2. 在根目录下创建my_3306.ini配置文件，配置内容如下：
#设置3306端口
port=3306
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3306
#允许最大连接数
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
#服务器 id
server-id=3306 
#二进制文件存放路径log_3306文件夹需要手动创建
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3306\\mysql-bin
#待同步的数据库（根据需要修改）
binlog-do-db=testdb
#不需要备份的数据库 （根据需要修改）
#binlog-ignore-db=mysql
binlog-ignore-db = information_schema
#二进制文件记录格式 
binlog_format = STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
  1. 在根目录下创建install_3306.bat自动安装执行脚本，配置内容如下
cd /d %~dp0\bin
mysqld --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini" --initialize-insecure --user=mysql --console
mysqld install mysql_3306 --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini"
net start mysql_3306
pause
  1. 在根目录下创建remove_3306.bat自动卸载执行脚本，配置内容如下：
cd /d %~dp0\bin
net stop mysql_3306
mysqld -remove mysql_3306
pause
  1. 执行安装操作：
    配置mysql环境变量，右键以管理员身份运行install_3306.bat，如需卸载请执行remove_3306.bat
    在这里插入图片描述如上图显示安装成功并启动成功，默认密码为空
    执行以下操作
-- 切换到mysql数据库 
use mysql;
-- 修改root用户的可连接的host   %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 在主机创建一个用户
create user 'slave'@'%' identified by 'slave'; 
-- 给创建的而用户赋予复制的权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 展示主机状态
show master status;

执行完查看主机状态之后不要再操作以免数据变动

五、从机一号具体操作

  1. 安装第二个mysql实例：
    复制install_3306.bat,my_3306.ini,remove_3306.bat三个文件在同一目录，并分别改名为install_3307.bat,my_3307.ini,remove_3307.bat，并将三个3307文件中的所有3306改为3307,修改完成后按照上面的执行安装操作

    在这里插入图片描述my_3307.ini配置

[mysqld]
#设置3307端口
port=3307
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3307
#允许最大连接数
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
#服务器 id
server-id=3307 
#开启二进制日志（从服务器不是必须要开启二进制日志log_3307文件夹需手动创建）
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3307\\mysql-bin
#执行同步操作的数据库（根据需要修改）需要和master数据库要同步的数据库名称保持一致
replicate-do-db=testdb
#复制的数据库
binlog-ignore-db=information_schema
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8mb4
  1. 执行安装.bat
    安装成功并启动成功，默认密码为空
    执行以下操作
-- 切换到mysql数据库 
use mysql;
-- 修改root用户的可连接的host   %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 与主机1号连接
change master to master_host='主机ip地址',
master_port=主机数据库端口号,
master_user='主机分配的复制用户',
master_password='主机分配的复制用户的密码',
master_log_file='主机执行show master status 之后得到的值',
master_log_pos='主机执行show master status 之后得到的值';
-- 开启复制
start slave;
-- 查看状态
 show slave status\G;
-- 当结果中的这两个值为yes则主一从一复制搭建完成
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

六、主机二号具体操作

  1. 将下载后的mysql压缩包解压如下图：
    在这里插入图片描述
  2. 在根目录下创建my_3308.ini配置文件，配置内容如下：
#设置3308端口
port=3308
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3308
#允许最大连接数
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
#服务器 id
server-id=3308 
#二进制文件存放路径log_3308文件夹需要手动创建
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3308\\mysql-bin
#待同步的数据库（根据需要修改）
binlog-do-db=testdb
#不需要备份的数据库 （根据需要修改）
#binlog-ignore-db=mysql
binlog-ignore-db = information_schema
#二进制文件记录格式 
binlog_format = STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3308
default-character-set=utf8mb4
  1. 在根目录下创建install_3308.bat自动安装执行脚本，配置内容如下
cd /d %~dp0\bin
mysqld --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3308.ini" --initialize-insecure --user=mysql --console
mysqld install mysql_3308 --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3308.ini"
net start mysql_3308
pause
  1. 在根目录下创建remove_3308.bat自动卸载执行脚本，配置内容如下：
cd /d %~dp0\bin
net stop mysql_3308
mysqld -remove mysql_3308
pause
  1. 执行安装操作：
    配置mysql环境变量，右键以管理员身份运行install_3308.bat，如需卸载请执行remove_3308.bat
    在这里插入图片描述如上图显示安装成功并启动成功，默认密码为空
    执行以下操作
-- 切换到mysql数据库 
use mysql;
-- 修改root用户的可连接的host   %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 在主机创建一个用户
create user 'slave'@'%' identified by 'slave'; 
-- 给创建的而用户赋予复制的权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 展示主机状态
show master status;

执行完查看主机状态之后不要再操作以免数据变动

七、从机二号具体操作

  1. 安装第二个mysql实例：
    复制install_3306.bat,my_3306.ini,remove_3306.bat三个文件在同一目录，并分别改名为install_3309.bat,my_3309.ini,remove_3309.bat，并将三个3309文件中的所有3306改为3309,修改完成后按照上面的执行安装操作

    在这里插入图片描述my_3309.ini配置

[mysqld]
#设置3309端口
port=3309
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3309
#允许最大连接数
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
#服务器 id
server-id=3309 
#开启二进制日志（从服务器不是必须要开启二进制日志log_3309文件夹需手动创建）
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3309\\mysql-bin
#执行同步操作的数据库（根据需要修改）需要和master数据库要同步的数据库名称保持一致
replicate-do-db=testdb
#复制的数据库
binlog-ignore-db=information_schema
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3309
default-character-set=utf8mb4
  1. 执行安装.bat
    安装成功并启动成功，默认密码为空
    执行以下操作
-- 切换到mysql数据库 
use mysql;
-- 修改root用户的可连接的host   %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 与主机2号连接
change master to master_host='主机ip地址',
master_port=主机数据库端口号,
master_user='主机分配的复制用户',
master_password='主机分配的复制用户的密码',
master_log_file='主机执行show master status 之后得到的值',
master_log_pos='主机执行show master status 之后得到的值';
-- 开启复制
start slave;
-- 查看状态
 show slave status\G;
-- 当结果中的这两个值为yes则主二从二复制搭建完成
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

八、两台主机互相备份

  1. 主机1号执行
-- 主机1号与主机2号连接
change master to master_host='主机2号ip地址',
master_port=主机数据库端口号,
master_user='主机2号分配的复制用户',
master_password='主机2号分配的复制用户的密码',
master_log_file='主机2号执行show master status 之后得到的值',
master_log_pos='主机2号执行show master status 之后得到的值';
-- 开启复制
start slave;
-- 查看状态
 show slave status\G;
-- 当结果中的这两个值为yes则主一复制主二搭建完成
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
  1. 主机2号执行
-- 主机2号与主机1号连接
change master to master_host='主机1号ip地址',
master_port=主机数据库端口号,
master_user='主机1号分配的复制用户',
master_password='主机1号分配的复制用户的密码',
master_log_file='主机1号执行show master status 之后得到的值',
master_log_pos='主机1号执行show master status 之后得到的值';
-- 开启复制
start slave;
-- 查看状态
 show slave status\G;
-- 当结果中的这两个值为yes则主二复制主一搭建完成
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

九、测试

在主一创建数据库看其余3个数据是否同步
在主二创建数据库看其余3个数据是否同步

zhangzhikai1
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL主从复制之多主多从部署流程—2023.04
我是小bā吖的博客
04-03 2227
介绍了MySQL2主2从的搭建方式。
MYSQL多主多从
桀骜浮沉的博客
06-04 3999
MYSQL两主两从 1.准备4台mysql服务端口号分别为3307,3308,3309,3310 2.修改四台mysql的my.cnf文件 我选择3307，3309两个数据库为主服务器，3308是3307的从节点，3310是3309的从节点 3307的my.cnf添加二进制日志和服务id log-bin=mysql-bin server-id=3307 auto_increment_increment=2 auto_increment_offset=1 log-slave-updates sync_binl
数据库多主多从
最新发布
WWNY666的博客
05-21 638
如遇到错误检查 日志文件 /var/log/mariadb/mariadb.log。mariadb版本:10.5.16。
MySQL主从复制详解
huangjhai的博客
09-29 6263
随着业务流量的增长，一台MySQL数据库服务器已经满足不了需求了，会负载过重，容易出现宕机的情况，导致数据的丢失。这个时候就需要实现数据库的负载均衡和读写分离，来减少单台MySQL数据库服务器的压力。我们可以通过使用MySQL内置的复制功能来搭建MySQL一主一从或一主多从的集群环境。主服务器只负责写，而从服务器只负责读，从而减少单台MySQL数据库服务器的压力。
mysql 8.0集群搭建(一主多从和多主多从)
wn_96的博客
04-19 4478
MYSQL8.0集群搭建 一主多从基础环境**1. 下载 mysql rpm包****2.主节点添加主从同步账户****3.开启二进制日志文件和添加server-id**4.登入主节点mysql重置偏移量5.注册从节点6.启动 启动所有从节点的slave 基础环境 ip hostname 192.168.30.201 mc1 192.168.30.202 mc2 192.168.30.203 mc3 192.168.30.204 mc4 内存 1G Cpu 1C 1. 下载
MySQL的主从复制详解
wh柒八九的博客
04-23 606
主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。 文章目录主从复制的作用 主从复制的作用 主数据库出现问题，可以切换到从数据库。 可以进行数据库层面的读写分离。 可以在从数据库上进行日常备份。 ...
详解MySQL实现主从复制过程
12-15
一、什么是主从复制 将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。 二、主从复制的作用 1、主数据库出现...
详解Docker方式实现MySql 主从复制（实践篇）
09-09
本篇文章主要介绍了详解Docker方式实现MySql 主从复制（实践篇），小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧
MySQL5.7.18主从复制搭建(一主一从)教程详解
09-09
MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。这篇文章主要介绍了MySQL5.7.18主从复制搭建(一主一从)教程详解,需要的朋友可以参考下
基于Docker如何实现MySQL主从复制详解
01-10
MySQL的主从复制是实现应用的高性能，高可用的基础。对于数据库读操作较密集的应用，通过使数据库请求负载均衡分配到不同MySQL服务器，可有效减轻数据库压力。当遇到MySQL单点故障中，也能在短时间内实现故障切换。...
MySQL主从复制（一主一从、双主双从）
qq_759035366的博客
12-05 1394
主从数据库是什么意思呢，主是主库的意思，从是从库的意思。数据库主库对外提供读写的操作，从库对外提供读的操作。数据库为什么需要主从架构呢？高可用，实时灾备，用于故障切换。比如主库挂了，可以切从库。读写分离，提供查询服务，减少主库压力，提升性能备份数据，避免影响业务。
mysql 多主一从复制全过程
09-05
mysql5.5 实现多主一从复制 数据库
mysql主主同步的配置
热门推荐
cc_梅
07-30 2万+
主主同步事实上就是在主从的基础上，将原先的从机当主机，主机当从机再配置一遍主从同步，具体操作如下：   注意：配置过程中个人使用的指令所需要的权限比较高，主要是因为个人习惯了用root账户操作，如果使用自己创建的账户（输入指令时是‘$’ 而非‘#’，‘#’表示系统账户）可能会说权限不足，请自行切换到root用户或使用可执行的其他等效指令   1.在阿里云数据库1的my.cnf的mysqld...
MySQL5.7 多主一从（多源复制）同步配置
eagle89的专栏
12-28 3674
多主一从
mysql 多主多从_Mysql集群讲解(五) 多主多从环境搭建
weixin_39722692的博客
01-18 234
Mysql集群讲解(五) 多主多从环境搭建A:概述:一主多从，可以缓解读的压力，但是一旦主宕机了，就不能写了；所以我们可以采用双主双从架构架构规划：主master 3307 ---> 从slave 3309主master 3308 ---> 从slave 33103307 3308 互为主从2个写节点，每个写节点下又是2个读节点；B:文件配置:1、配置每一台MySQL服务器的配置文件...
mysql 双主多从_MySql 双主多从配置指导
weixin_39939661的博客
01-19 271
MySql 双主多从配置指导一、背景互联网项目为了数据的可靠性和架构的可拓展性经常会用到双主多从的数据库，来实现数据的备份、负载均衡和突发状况时数据库切换。二、思路配置两台数据库A、B互为主从，然后配置以A为主的从数据库C，和以B为主的从数据库D在A和B的配置中添加：log-slave-updates=1将所有的操作写入到binary log。使得A的更改不止同步到B和C，还会同步到D。同理B也会...
Mysql-主从配置&多主多从配置
Feverasa的博客
05-05 2654
​ 我们现在模拟的是主从(1台主机、一台从机)，其主从同步的原理，就是对bin-log二进制文件的同步，将这个文件的内容从主机同步到从机。 一、配置文件的修改 1、主机配置文件修改配置 ​ 我们首先需要mysql主机(192.168.254.130)的/etc/my.cnf配置文件，添加如下配置: #主机唯一ID server-id=1 #二进制日志 log-bin=mysql-bin #不需要同步的数据库 binlog-ignore-db=mysql binlog-ignore-db=informatio
MySQL一主一从架构的实现
yuki5233的博客
08-21 1921
目录 主从原理 对于每一个连上来的从库，主库都有一个client线程与之对应。现在先看一下主从的基本数据流： 1、客户端sql更新命令 2、主库执行sql语句 3、主库写binlog 4、主库client线程读binlog发送给从库的io线程 5、从库io线程写盘（relay-log） 6、从库sql线程读（relay-log） 7、从库执行更新（relay-info）。 这里有涉及到两个...
MYSQL主从复制的相关操作(详解)
07-21
MYSQL主从复制的相关操作(详解)；为什么要主从复制？ MySQL主从复制是指将一个MySQL数据库的数据和日志复制到另一个MySQL数据库的过程。在主从复制中，一个数据库作为主数据库，负责处理写操作和更新操作；而另一个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值