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

1. 一些概念

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

1.1 主从复制的过程:

  1. 主服务器将数据变更记录到二进制日志中。

  2. 从服务器通过 I/O 线程连接到主服务器,读取二进制日志中的内容,并将其记录到本地的中继日志(Relay Log)中。

  3. 从服务器的 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. 待补充

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值