MySQL 主从复制(实时热备)原理与配置

Wesley13
• 阅读 757

MySQL是现在普遍使用的数据库,但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性,就要会一些提高可靠性的技术。MySQL主从复制可以做到实时热备数据。本文介绍MySQL主从复制原理及其配置过程。

术语:主从复制——master slave replication(M-S模式)。

用途

  • 备份
  • 读写分离
  • 高可用和故障切换(需要做HA)
  • MySQL升级测试

原理

实际上,它的原理思路很简单。MySQL 中有一种日志叫做 bin 日志(二进制日志),这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是"从"服务器向"主"服务器请求这个日志文件,"主"服务器会把这个 bin 日志复制到"从"服务器上执行一遍,这样"从"服务器上的数据就和"主"服务器上的数据相同了。

流程图:

MySQL 主从复制(实时热备)原理与配置

图解:

  1. 主服务器必须启用二进制日志(log-bin),记录任何修改了数据库数据的事件;
  2. 从服务器开启一个线程(I/O Thread)把自己扮演成 MySQL 的客户端,通过 MySQL 协议,请求主服务器的二进制日志文件中的事件;
  3. 主服务器启动一个线程(Dump Thread),检查自己二进制日志中的事件,跟对方请求的位置对比,如果不带请求位置参数,则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器;
  4. 从服务器接收到主服务器发送过来的数据把它放置到中继日志(relay log)文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置(主服务器中的二进制文件会有多个,其名结尾以6位数递增);
  5. 从服务器启动另外一个线程(SQL Thread ),把 relay log 中的事件读取出来,并在本地再执行一次。

配置过程

主服务器(Master)

  • 启用二进制日志 log-bin;
  • 设置一个全局唯一的 server_id;
  • 提前准备好一个有复制权限(replication slave,replication client)的用户。

从服务器(Slave)

  • 启动中继日志 relay-log;
  • 设置一个全局唯一的 server_id;
  • 使用主服务器提供的有复制权限的用户连接至 Master;
  • 启动复制线程。

配置演示

测试环境

主服务器:192.168.43.75
从服务器:192.168.43.111
mysql版本:mysql-8.0
系统:主 Windows10,从 Windows7

主服务器配置

1.配置 my.ini

log-bin=master-log-bin
server-id=1
innodb-file-per-table=ON

2.重启服务,并进入MySQL

MySQL 主从复制(实时热备)原理与配置

查看主服务器状态信息:

MySQL 主从复制(实时热备)原理与配置

注:这里的文件名和位置值会在后面的配置中用到。

3.给复制用户授权

grant replication slave, replication client on *.* to repl_user@192.168.43.111;或者对现有的超级用户修改可访问主机IP:update user set host='%' where user='root';    # '%' 代表所有局域IP

一定要刷新权限:

flush privileges;

主服务器配置完成。

从服务器配置

1.配置 my.ini

log-bin=slave-log-bin
relay-log=relay-log
relay-log-index=relay-log.indexserver-id=2
innodb_file_per_table=ON

2.重启并进入MySQL服务(略)

3.用主服务器提供的复制用户连接主服务器

change master to master_host='192.168.43.75',master_port=3306,master_user='repl_user',master_password='123456',master_log_file='master-log-bin.000005',master_log_pos=155;  # 这里的 master 日志文件和位置必须与主服务器当前状态一致!

4.启动复制线程

MySQL 主从复制(实时热备)原理与配置

此时会启动 IO Thread 和 SQL Thread 这两个线程。 

(停止复制线程:stop slave)

查看从服务器状态信息:

MySQL 主从复制(实时热备)原理与配置

MySQL主从配置到此完成。

测试

在主服务器上新建一个数据库test:

MySQL 主从复制(实时热备)原理与配置

MySQL 主从复制(实时热备)原理与配置

MySQL 主从复制(实时热备)原理与配置

在从服务器上查看:

MySQL 主从复制(实时热备)原理与配置

MySQL 主从复制(实时热备)原理与配置

测试通过,完美。

常见问题处理

当我们配置好MySQL主从同步时,原本是可以实现主从同步的,但是重启机器后就发现无法同步了。

MySQL Replication 中 slave 机器上有两个关键的线程,死一个都不行,一个是 Slave_IO_Running,一个是 Slave_SQL_Running。一个负责与主机的 IO 通信,一个负责自己的 slave mysql 语句执行。

1)如果是Slave_SQL_Running:No,如图。

MySQL 主从复制(实时热备)原理与配置

解决办法:

> stop slave;
> set global sql_slave_skip_counter=1;
> start slave;

MySQL 主从复制(实时热备)原理与配置

2)如果是Slave_IO_Running:No,如图。

MySQL 主从复制(实时热备)原理与配置

解决办法:
查看主服务器状态:

MySQL 主从复制(实时热备)原理与配置

查看从服务器状态:

MySQL 主从复制(实时热备)原理与配置

找到问题所在:Master_Log_File没有对应。

> stop slave;
> change master to master_log_file='mysql-bin.000026',master_log_pos=0;
> slave start;
> show slave status\G;

MySQL 主从复制(实时热备)原理与配置

得解。

点赞
收藏
评论区
推荐文章
Jacquelyn38 Jacquelyn38
1年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
1年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
1年前
PHP中的NOW()函数
是否有一个PHP函数以与MySQL函数NOW()相同的格式返回日期和时间?我知道如何使用date()做到这一点,但是我问是否有一个仅用于此的函数。例如,返回:2009120100:00:001楼使用此功能:functiongetDatetimeNow(){
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
helloworld_34035044 helloworld_34035044
5个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
3A网络 3A网络
2个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
3A网络 3A网络
2个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t