【SQL】MySQL运维1：主从结构

南隅。

已于 2025-04-02 08:28:30 修改

阅读量805

点赞数 11

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： sql mysql 运维

于 2025-02-28 18:30:53 首次发布

本文链接：https://blog.youkuaiyun.com/backlash321/article/details/145935612

SQL 专栏收录该内容

7 篇文章

订阅专栏

文章目录

1. 一些概念
2. 一主一从
3. 待补充

1. 一些概念

在 MySQL 主从结构中，包含一个主服务器（Master）和一个或多个从服务器（Slave）。主服务器负责处理所有的写操作（如 INSERT、UPDATE、DELETE），并将这些操作记录到二进制日志（Binary Log）中。从服务器则从主服务器获取二进制日志，并将其中的操作在自己的数据副本上进行重演，从而保持与主服务器数据的一致性。从服务器通常用于处理读操作（如 SELECT），以分担主服务器的负载。

1.1 主从复制的过程：

主服务器将数据变更记录到二进制日志中。
从服务器通过 I/O 线程连接到主服务器，读取二进制日志中的内容，并将其记录到本地的中继日志（Relay Log）中。
从服务器的 SQL 线程读取中继日志中的内容，并在本地执行这些操作，实现数据的同步。

好处：读写分离以改善响应和提高并发（适合读多写少的应用，如论坛类，新闻类）；具备数据备份与恢复能力。

缺点：数据同步有延时；主服务器宕机从服务器顶上需要时间，这段时间的新数据可能丢失；配置复杂（涉及二进制日志，中继日志）。

1.2 主从复制的模式

一主一从：一个从机复制一个主机数据

一主多从：多个从机复制主机数据

主主复制：主机A复制主机B数据（A从B主），主机B复制主机A数据（B从A主）

多主一从：一个从机复制多个主机数据

级联复制：从机B同步主机A的操作，从机B还可作为主机，从机C来同步主机B的操作。

1.3 关于mysql配置文件

mysql有多个配置文件，它们按照一定顺序加载。后加载的文件中的相同配置项将覆盖前面加载的文件中的配置项。配置文件加载顺序为：/etc/my.cnf，/etc/mysql/my.cnf，/usr/etc/my.cnf，~/.my.cnf。

2. 一主一从

2.1 主机配置

2.1.1 配置文件

# /etc/mysql/conf.d/master.cnf

[mysqld]

server-id = 1         # 主服务器的唯一标识

log-bin = master-bin.log   # 二进制日志名，位置：Var/lib/mysql/

log-bin-binlog_format = ROW  # 二进制日志格式，建议使用ROW

binlog-do-db=test       # 指定复制的数据库

2.1.2 检查配置是否生效

-- 检查配置项

SHOW VARIABLES LIKE 'log_bin';

SHOW VARIABLES LIKE 'server_id';

SHOW VARIABLES LIKE 'binlog_format';

-- 查看主机二进制日志文件路径

SHOW MASTER STATUS;    -- 8.0 

SHOW BINARY LOG STATUS;  -- 8.4

-- 结果将用于从机配置

+---------------+----------+--------------+------------------+-------------------+

| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000004 |    158 |        |          |          |

+---------------+----------+--------------+------------------+-------------------+

2.1.3 创建用于主从复制的用户

CREATE USER'repl_user'@'%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO'repl_user'@'%';

FLUSH PRIVILEGES;

2.2 从机配置

2.2.1 从机配置文件

\# /etc/mysql/conf.d/slave01.cnf

[mysqld]

server-id = 2  # 从服务器的唯一标识，与主服务器不同

log-bin = master-bin.log   # 二进制日志名，位置：Var/lib/mysql/

log-bin-binlog_format = ROW  # 二进制日志格式，建议使用ROW

2.2.2 从机连接主机配置

-- 8.0

CHANGE MASTER 'master' TO

  MASTER_HOST='172.17.0.2',

  MASTER_USER='repl_user',

  MASTER_PASSWORD='password',

  MASTER_LOG_FILE='master-bin.000002',

  MASTER_LOG_POS=2137;

-- 8.4

CHANGE REPLICATION SOURCE TO 

  SOURCE_HOST='172.17.0.2',

  SOURCE_USER='repl_user',

  SOURCE_PASSWORD='password',

  SOURCE_LOG_FILE='master-bin.000002',

  SOURCE_LOG_POS=2137;

2.2.3 启动从机

-- 8.0

START SLAVE;

-- 8.4

START REPLICA;

-- 查看从机状态

SHOW REPLICA STATUS \G -- \G表示每行竖着排列显式

-- 结果有 Replica_IO_Running: Yes; Replica_SQL_Running: Yes两项就表示正确

2.3 创建主机和从机容器

# 创建主服务器

docker run -d --name master -p 3306:3306 \

-v /Users/mac/tmp/mysql/conf/master.cnf:/etc/mysql/my.cnf \

-v /Users/mac/tmp/mysql/data/master:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=123 mysql:latest

# 创建从服务器

docker run -d --name slave01 -p 3307:3306 \

-v /Users/mac/tmp/mysql/data/slave01:/var/lib/mysql \

-v /Users/mac/tmp/mysql/conf/slave01.cnf:/etc/mysql/my.cnf \

-e MYSQL_ROOT_PASSWORD=123 mysql:latest

2.4 错误排查

错误原因：在从机建通过CHANGE REPLICATION SOURCE TO语句立到主机的连接时，使用的是主机执行了一些操作（创建test数据库和test.Customers表）之前的SOURCE_LOG_POS=1053。事实上创建数据库test操作是的log_bin更新，log_bin的pos更新为2137。于是连接建立后，从机在同步主机上的最新操作时会因为log_pos值是旧值而失败（这样会覆盖主机上二进制日志文件从1053到2137之间的内容）。

错误处置：

/* 主机操作 */

-- 主机删除test数据库

DROP DATABASE test;

-- 主机查看log_pos最新的值

SHOW BINARY LOG STATUS;

+-------------------+----------+--------------+------------------+-------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000002 |   2137 | test     |          |          |

+-------------------+----------+--------------+------------------+-------------------+

/* 从机操作 */

-- 断开当前对主机的连接

STOP REPLICA;

-- 使用最新的log pos再连接

CHANGE REPLICATION SOURCE TO 

  SOURCE_HOST='172.17.0.2',

  SOURCE_USER='repl_user',

  SOURCE_PASSWORD='password',

  SOURCE_LOG_FILE='master-bin.000002',

  SOURCE_LOG_POS=2137;

-- 启动

START REPLICA;

3. 待补充

…