睿阳知识库
孙超
2023-03-30
目录

MySQL5.7 主从复制、热备

# 一. 概述

MySQL主从复制的原理是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个（I/O thread, SQL thread）运行在从节点。具体的流程如下：

  • 主节点将数据的改变记录到二进制日志（binlog）中，当主节点上的数据发生改变时，则将其改变写入二进制日志中；
  • 从节点发起连接，连接到主节点，并请求获取二进制日志的内容；
  • 主节点为每个从节点启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志（relay log）中；
  • 从节点启动一个I/O线程，读取主节点传过来的二进制事件并写入到本地的relay log文件中；
  • 从节点启动一个SQL线程，从relay log文件中读取二进制事件，并解析成SQL语句，在本地重放，使得其数据和主节点的保持一致。

# 二. 主从复制

MySQL主从复制，主库用于处理写请求，从库用于处理读请求。

提示

  • 操作系统：Centos 7.5，两台：192.168.1.100（主）、192.168.1.101（从）
  • 数据库：MySQL 5.7.37
  • 数据库端口：3306
  • 已安装好 MySQL
  • 两台服务器互相开通3306端口的访问权限

# 1. 主库服务器

  1. 配置my.cnf
[mysqld]
## 保持唯一性，不能和从库重复。推荐使用IP的四小节数字
server-id=100
log-bin=mysql-bin
## 设置不需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema
## 设置需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-do-db=要复制的数据库
  1. 创建用于复制的用户

  • 重新启动MySQL服务

  • 创建用户

use mysql;

create user 'slave1'@'192.168.1.101' identified BY '密码';
grant replication slave on *.* to 'slave1'@'192.168.1.101';
flush privileges;
  • 查看主库状态，记录日志文件名,日志位置
show master status;

-- 结果
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     514  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 2. 从库服务器

  1. 配置my.cnf
[mysqld]
server-id=101
read-only=ON

## 设置不需要复制的数据库，多个数据库则要多行分别设置 
replicate-ignore-db=mysql 
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

## 设置需要复制的数据库，多个数据库则要多行分别设置 
replicate-do-db=要复制的数据库
  1. 执行同步SQL

  • 重新启动MySQL服务

  • 执行同步SQL

use mysql;

change master to master_host='192.168.1.100', master_port=3306, master_user='slave1', master_password='密码', master_log_file='mysql-bin.000003', master_log_pos=514;
-- 开启从库模式 
start slave;
-- 查看从库状态（Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0 说明同步成功）
show slave status;

# 3. 验证

    1. 在主库上创建你要同步的数据库，例如：demo。然后在建个表，例如：t_demo (id, name)。最后插入数据。
    1. 到从库上查看数据是否已经同步过来。

# 三. 双机热备

双机热备，即MySQL主主复制，保证 MYSQL服务器数据的一致性。

提示

  • 操作系统：Centos 7.5，两台：192.168.1.110（主1）、192.168.1.111（主2）
  • 数据库：MySQL 5.7.37
  • 数据库端口：3306
  • 已安装好 MySQL
  • 两台服务器互相开通3306端口的访问权限

# 1. 配置my.cnf

  1. 主库1
[mysqld]
## 保持唯一性，不能和从库重复。推荐使用IP的四小节数字
server-id=110
log-bin=mysql-bin
log-slave-updates=ON
sync_binlog=1
## auto_increment，控制自增列AUTO_INCREMENT的行为，用于MASTER-MASTER之间的复制，防止出现重复值,
## n有多少台服务器，n就设置为多少, 步长设置为1,这样Master的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID
auto_increment_offset=1
auto_increment_increment=2
## 设置不需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema
## 设置需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-do-db=要复制的数据库

## 设置需要复制的数据库，多个数据库则要多行分别设置
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
## 设置需要复制的数据库，多个数据库则要多行分别设置 
replicate-do-db=要复制的数据库
  1. 主库2
[mysqld]
## 保持唯一性，不能和从库重复。推荐使用IP的四小节数字
server-id=111
log-bin=mysql-bin
log-slave-updates=ON
sync_binlog=1
## auto_increment，控制自增列AUTO_INCREMENT的行为，用于MASTER-MASTER之间的复制，防止出现重复值,
## n有多少台服务器，n就设置为多少, 步长设置为2,这样Master的auto_increment字段产生的数值是:2, 4, 6, 8, …等偶数ID
auto_increment_offset=2
auto_increment_increment=2
## 设置不需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema
## 设置需要写BinLog的数据库，多个数据库则要多行分别设置 
binlog-do-db=要复制的数据库

## 设置需要复制的数据库，多个数据库则要多行分别设置
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
## 设置需要复制的数据库，多个数据库则要多行分别设置 
replicate-do-db=要复制的数据库

# 2. 执行同步SQL

1. 查看 master 状态

  • 主库1
use mysql;

-- 查看 master 状态
show master status;
+------------------+----------+--------------+---------------------------------------------+---------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                          | Executed_Gtid_Set 
+------------------+----------+--------------+---------------------------------------------+---------------+
| mysql-bin.000001 |     1104 | demo         | mysql,information_schema,performance_schema |                   
+------------------+----------+--------------+---------------------------------------------+---------------+
  • 主库2
use mysql;

-- 查看 master 状态
show master status;
+------------------+----------+--------------+---------------------------------------------+---------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                          | Executed_Gtid_Set 
+------------------+----------+--------------+---------------------------------------------+---------------+
| mysql-bin.000002 |     1327 | demo         | mysql,information_schema,performance_schema |                   
+------------------+----------+--------------+---------------------------------------------+---------------+

2. 创建用于同步的用户

  • 主库1
create user 'backuper'@'192.168.1.110' identified BY '密码';
grant replication slave on *.* to 'backuper'@'192.168.1.110';
flush privileges;
  • 主库2
create user 'backuper'@'192.168.1.111' identified BY '密码';
grant replication slave on *.* to 'backuper'@'192.168.1.111';
flush privileges;

3. 同步设置SQL

  • 主库1
stop slave;
change master to master_host='192.168.1.111', master_port=3306, master_user='backuper', master_password='密码', master_log_file='mysql-bin.000002', master_log_pos=1327;
start slave;
show slave status;
  • 主库2
stop slave;
change master to master_host='192.168.1.110', master_port=3306, master_user='backuper', master_password='密码', master_log_file='mysql-bin.000001', master_log_pos=1104;
start slave;
show slave status;

# 3. 验证

    1. 在主库1上创建你要同步的数据库，例如：demo。然后在建个表，例如：t_demo (id, name)。最后插入数据。
    1. 到主库2查看数据是否已经同步过来。
    1. 在主库2上插入或更新数据。
    1. 到主库1查看数据是否已经同步过来。

# 三. 共享存储

通过共享存储的方式，可以实现MySQL数据备份和快速恢复数据库服务。这种方式可以提高数据恢复的速度，并且减少数据丢失的风险。

具体的操作步骤：

# 1. 配置共享存储

  • Linux：挂载共享存储设备（例如SAN、NAS等）
  • Windows：配置网络共享盘

# 2. 配置MySQL

在每个MySQL服务器上，将MySQL的数据和日志文件存储在共享存储设备。修改MySQL配置文件my.cnf或my.ini

datadir=<实际位置>

说明：若要开启binlog，还需要配置binlog的位置。

# 3.启动说明

如果不设置MySQL主主复制，那两台MySQL服务只能启动一个。

# 四. MySQL备份还原

分库分表
MySQL5升级到8

← MySQL5升级到8→

最近更新
01
vue问题记录
10-11
02
RSP进度
10-09
03
贡献度
09-28
更多文章>
Copyright © 2014-2025 甘肃睿阳科技有限公司 陇ICP备15001783-1号