mysql设置主从数据库的同步

最新推荐文章于 2025-10-17 11:04:50 发布

原创最新推荐文章于 2025-10-17 11:04:50 发布 · 2.6k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #主从数据 #主从复制 #主从同步

mysql 专栏收录该内容

20 篇文章

订阅专栏

本文详细介绍了如何通过MysqlReplication实现服务器负载均衡，包括读写分离和数据备份，以及如何配置一主多备、双主互备和一从多主架构。涵盖了从环境准备到实际操作的全过程，重点讲解了主从复制原理和常见配置方法。

1.业务场景

场景1：实现服务器负载均衡
若我们所有的业务代码存于一个服务器上，而这个服务器有的时候回宕机，导致业务停顿，造成影响。这个时候就需要做高可用，在数据库的处理上如果两个mysql实现高可用，避免单点问题。
场景2：读写分离；master写，其他slave读，这种架构最大问题I/O压力集中在Master上;
场景3：通过复制实现数据的异地备份
可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业带来比较大的损失。

2.实现的目标

一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。
一句话表示就是，主数据库做什么，从数据库就跟着做什么。

3.原理

我们的主从复制基于Mysql Replication来进行实现

3.1 什么是Mysql Replication

Replication可以实现将数据从一台数据库服务器（master）复制到一台或多台数据库服务器（slave）

默认情况下属于异步复制，无需维持长连接
通过配置，可以复制所有的库或者几个库，甚至库中的一些表
是MySQL内建的，本身自带的

3.2 Mysql Replication的原理

简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作。
在这里插入图片描述

注意：

DML表示SQL操作语句如：update, insert，delete
Relay log :中继日志

整体上来说，复制有3个步骤：
（1） master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）
（2） slave将master的binary log events拷贝到它的中继日志(relay log)
（3） slave重做中继日志中的事件，修改salve上的数据。

3.3 My Replication的作用

Fail Over 故障切换
Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
High Performance 高性能，可以多台slave,实现读写分离

3.4 MySQL主从复制

第一步：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线程将这些事件写入中继日志。

第三步：SQL slave thread（SQL从线程）处理该过程的最后一步

SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

4 My Replication常见方案

One master and Muti salve 一主多备（MS）

在这里插入图片描述

一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中在Master上<多台同步影响IO>

M-S-S

使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates
在这里插入图片描述

Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。

M-M 双主互备（互为主从）

每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。
在这里插入图片描述

M-M-M

监控三台机器互相作对方的master
在这里插入图片描述

天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重。
mysql在5.6已经自身可以实现fail over故障切换

One slave Muti master 一从对多主

好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

5.实现

5.1 环境准备

本地安装两个mysql，或者使用虚拟机，或者使用docker安装，需要准备两个mysql，本文使用docker安装
在docker中先创建两个mysql服务器
主服务器：3308
从服务器：3309
在这里插入图片描述

不会创建？点击此处

5.2 修改 my.cnf配置文件

master的my.cnf配置文件


#mysql master1 config 
[mysqld]
server-id = 1        # 节点ID，确保唯一

# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

slave1的my.cnf配置文件

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

6.进入master数据库，创建复制用户并授权

依次执行

CREATE USER repl_user IDENTIFIED BY 'root';
CREATE USER 'repl_user'@'172.17.0.1' IDENTIFIED BY 'root';
CREATE USER 'repl_user'@'192.168.1.2' IDENTIFIED BY 'root';

root表示该用户的用户密码
在这里插入图片描述

你只需要修改的是将 192.168.1.2改为你自己主机的ip地址即可
查看自己创建用户是否成功

 SELECT User, Host FROM mysql.user;

在这里插入图片描述

6.1 赋予该用户复制的权利

依次执行

grant replication slave on *.* to 'repl_user'@'%';
grant replication slave on *.* to 'repl_user'@'172.17.0.1';
grant replication slave on *.* to 'repl_user'@'192.168.1.2';

FLUSH PRIVILEGES;

在这里插入图片描述
注意：我们接下来进行一个很重要的步骤，就是修改主库repl_user的plugin
依次执行以操作：

alter user 'repl_user'@'%' identified with mysql_native_password by 'root';
alter user 'repl_user'@'172.17.0.1' identified with mysql_native_password by 'root';
alter user 'repl_user'@'192.168.1.2' identified with mysql_native_password by 'root';

在这里插入图片描述

6.2 查看master的状态

show master status;

在这里插入图片描述

7.配置从库

登录进入从库的mysql服务内执行以下命令

CHANGE MASTER TO 
MASTER_HOST = 'localhost',  
MASTER_USER = 'repl_user', 
MASTER_PASSWORD = 'root',
MASTER_PORT = 3308,
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=1374,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

在这里插入图片描述