MySQL主从复制（读写分离前置工作）

关注

原创 2025-08-18 19:48:17 951 阅读 AI 写同款· GEO 优化 ›

MySQL主从复制和读写分离 前言在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。主MySQL服阅读详情

八、MySQL主从复制

原理：

两个日志：主bin-log 从relay-log

三个线程：主dump 从io、sql

作用：

·实时备份：主服务器实时备份数据到从服务器，主服务器中数据丢失时，可以从从服务器中恢复数据。
·读写分离：主服务器负责写操作（insert\update\delete），从服务器负责读操作（select）
·高可用集群：当主服务器突然下线，从服务器可以很快地接替主服务器的功能。
·负载均衡：将原本一台服务器处理的请求分发给多台服务器去处理，适用于高并发场景，防止单台服务器负载过高而崩溃。

主从服务器准备工作：

# 主从服务器准备工作

# 配置yum源

mount /dev/cdrom /media

cd /etc/yum.repos.d

# 关闭防火墙，沙盒

systemctl stop firewalld

systemctl disable firewalld

setenforce 0

# mysql 安装

wget 192.168.56.200/Software/mysql_install.sh

# 一键安装

bash mysql_install.sh

# 重新加载

source /etc/profile

bash

# 登录mysql

mysql -uroot -p

再输入密码

show databases;

实战：

一、主库配置，开启主服务器的master的log_bin

vim /etc/my.cnf

+++++++++++++++++++++++++++++++++++++

[mysqld]

...

# 在后面直接设置主服务器唯一ID

server-id=1

# 启用二进制日志

log-bin= /usr/local/mysql/data/mysql-bin

# 设置不要复制的数据库(可设置多个)

binlog-ignore-db=sys

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

# 设置需要复制的数据库(可设置多个，也可以不写)

# binlog-do-db=test

# 设置logbin格式

binlog_format=STATEMENT

+++++++++++++++++++++++++++++++++++++

二、从库配置开启从服务器的realy log

vim /etc/my.cnf

+++++++++++++++++++++++++++++++++++++

[mysqld]

...

# 在之前配置下方编写从服务器唯一ID

server-id=2

# 启用中继日志

relay-log=/usr/local/mysql/data/mysql-relay

+++++++++++++++++++++++++++++++++++++

# 重启两台服务器上的mysql

/etc/init.d/mysqld restart

三、关闭主从数据库服务器防火墙

# 查看防火墙状态（一般已经默认关闭了）

systemctl status firewalld

# 关闭防火墙（可省略）

# systemctl stop firewalld

四、主数据库创建用户slave并授权

# 在主数据库端(192.168.221.20)

# 登录mysql

mysql -uroot -proot123

# 创建用户slave

create user 'slave'@'%' identified with mysql_native_password by 'Root@123';

# 授权

grant replication slave on *.* to 'slave'@'%';

# 刷新权限

flush privileges;

五、从数据库端验证主数据库slave用户是否可用

# 在从数据库端（192.168.221.30）

# 验证主数据库slave用户是否可用

mysql -uslave -p -h192.168.221.20

输入密码Root@123

六、配置主从节点信息

# 在主数据库端(192.168.221.20)查询服务ID及Master状态

# 登录

mysql -uroot -proot123

# 查询server_id是否可配置文件中一致

show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效），若一致则跳过这步

set global server_id = 1;

# 查询Master状态，并记录 File（对应下一步中的master_log_file）并查询Position （对应下一步中的master_log_pos）的值

show master status;

记录下File和Position的值

# 注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

七、在从数据库设置同步

# 在从数据库（192.168.221.30）

# 退出slave登录的mysql，登录root

mysql -uroot -proot123

# 查询server_id是否可配置文件中一致

show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效，若一致则跳过这步）

set global server_id = 2;

# 设置主数据库参数（用上一步创建的slave用户及密码）

change master to

master_host='192.168.221.20',

master_port=3306,

master_user='slave',

master_password='Root@123',

master_log_file='mysql-bin.000001',

master_log_pos=828;

# 开始同步

start slave;

# 查询Slave状态

show slave status\G;

# 查看是否配置成功

Slave_IO_Running ：yes

Slave_SQL_Running ：yes，则证明配置成功。（后续可不看）

若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值

# 若出现错误，则停止同步，重置后再次启动

stop slave;

reset slave;

start slave;

八、测试主从复制

# 主服务上执行（192.168.221.20）

mysql -uroot -proot123

# 创建test库，t1表，添加测试数据

create database test;

use test;

create table t1(id int,name varchar(30));

insert into t1(id,name) values(1,"aaa");

# 从服务器上执行（192.168.221.30）

mysql -uroot -proot123

# 查看是否同步数据

show databases;

use test;

show tables;

select * from t1;

GTID方案

1、GTID 是什么？

GTID 的全称是 Global Transaction Identifier，全局事务 ID，当一个事务提交时，就会生成一个 GTID，相当于事务的唯一标识。GTID 是 MySQL 数据库中用于唯一标识每个事务的机制，主要应用于主从复制场景，能简化复制配置、提高故障转移效率。

·结构：GTID=server_uuid:gno

- source_id：通常是生成事务的主库的服务器UUID，用于区分不同的数据库实例。- transaction_id：是在该主库上生成的事务的序列号，从1开始递增。

例如，8a8b8c8d-8e8f-8a8b-8c8d-8e8f8a8b8c8d:100 就是一个GTID，其中8a8b8c8d-8e8f-8a8b-8c8d-8e8f8a8b8c8d是source_id，100是transaction_id。

2、GTID的工作原理

GTID 方案：主库计算主库 GTID 集合和从库 GTID 的集合的差集，主库推送差集 binlog 给从库。

·事务生成：主库上执行的每个事务都会被分配一个唯一的GTID，该GTID会随着事务一起记录到二进制日志（binlog）中。

·复制传输：从库通过I/O线程将主库的binlog传输到本地的中继日志（relay log），同时也会获取到事务对应的GTID。

·事务执行与记录：从库的SQL线程读取中继日志中的事务并执行，执行完成后，会将该GTID记录到gtid_executed系统变量中，用于标识已经执行过的事务。

·冲突避免：当主从切换或有新的从库加入时，新的主库会根据从库的gtid_executed来判断哪些事务需要被发送，确保从库不会重复执行已经执行过的事务。

3、GTID的优势

·简化复制配置：在配置主从复制时，不再需要指定二进制日志文件名和位置，只需启用GTID并正确配置相关参数，从库就能自动找到需要同步的起点。例如，传统方式配置从库需要执行change master to master_log_file='xxx', master_log_pos=xxx，而使用GTID后，只需change master to master_auto_position=1。

·便于故障转移：当主库发生故障需要切换到从库时，通过查看各从库的gtid_executed，可以快速确定哪个从库拥有最新的事务，从而选择最合适的从库作为新的主库，减少数据丢失的风险。

·提高事务追踪能力：每个事务都有唯一的GTID，通过GTID可以方便地在主从库之间追踪事务的执行情况，便于排查复制过程中出现的问题。

4、如何启用GTID

# 修改主库和从库的配置文件：

vim /etc/my.cnf

+++++++++++++++++++++++++++++++++

[mysqld]

...

## 在配置文件中添加GTID配置

gtid_mode=on       #启用GTID后，每个事务都会被分配一个唯一的GTID

enforce_gtid_consistency=on  

#从库只能复制具有与主库一致的 GTID 链的事务，以确保数据一致性

+++++++++++++++++++++++++++++++++

# 修改从库配置同步的参数

## 格式

change master to

master_host=’$host_name’,           #主机IP

master_port=$port,                   #端口号

master_user=’$user_name’,           #用户名

master_password=’$password’,

master_auto_position=1;             #主从关系使用的GTID协议

5、在线将传统复制切换到GTID复制

## 在主从库执行

set global enforce_gtid_consistency=WARN;

set global enforce_gtid_consistency=on;

set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

## 先在从库上执行

set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

## 然后在主库执行

set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

# 查看是否为0,等于0是表示所有连接都转为gtid复制

show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

# 主从库执行

set global gtid_mode=on;

# 从库执行

stop slave;

change master to master_auto_position=1;

start slave;

## 永久生效,记得更改配置文件

6、在线将GTID的环境切换到非GTID

# 从库执行

stop slave;

change master to master_auto_position=0;

start slave;

# 主从库执行

set @@GLOBAL.GTID_MODE = ON_PERMISSIVE

# 主库执行

set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE

# 这个时候已经从gtid转到传统复制了,不过还是中间状态

# 在从库执行

set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE

# 主从库执行

select @@GLOBAL.GTID_OWNED;

# 是不是为空

# 主库执行

set global gtid_mode=0;

# 主库执行

set global enforce_gtid_consistency=off;

## 永久生效,记得配置文件修改

收录于