MySQL 主备部署与主库读写分离

概述

MySQL 是一个广泛使用的开源关系型数据库管理系统。为了提高系统的可用性和数据的安全性，通常会采用主备（Master-Slave）架构来部署 MySQL 服务。在主备架构中，主服务器负责处理所有的写操作（如插入、更新和删除），而备服务器则可以用来分担一部分的读操作，以此来实现负载均衡并减少主服务器的压力。

环境准备

在开始配置之前，请确保你已经安装了 MySQL 数据库，并且有两个或更多的服务器实例可以用于主备部署。此外，所有服务器的操作系统和 MySQL 版本应当尽可能一致，以避免兼容性问题。

必要条件

两台或更多运行相同版本 MySQL 的服务器。
服务器之间网络连通。
MySQL 用户具有相应的权限进行复制配置。
所有服务器的时间保持同步（推荐使用 NTP 服务）。

主备同步配置

主服务器（Master）配置

编辑 MySQL 配置文件：通常为 my.cnf 或 my.ini，添加以下内容到 [mysqld] 下面：
```
server-id=1
log-bin=mysql-bin
binlog-format=row
```

创建用于复制的用户：登录 MySQL 并执行以下 SQL 命令创建一个具有复制权限的用户。

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

锁定表：在开始复制前，需要锁定所有表以防止数据变化。
```
FLUSH TABLES WITH READ LOCK;
```
获取二进制日志位置：记录下当前的二进制日志文件名和位置，这将在配置从服务器时用到。
```
SHOW MASTER STATUS;
```
导出主库数据：使用 mysqldump 工具导出数据，然后将其导入到备库。
```
mysqldump -u root -p --all-databases --master-data > backup.sql
```
解锁表：完成备份后，记得解锁表。
```
UNLOCK TABLES;
```

备服务器（Slave）配置

编辑 MySQL 配置文件：同样在 [mysqld] 下添加 server-id，但其值应不同于主服务器，例如 server-id=2。
导入主库数据：将之前从主库导出的数据导入到备库中。
```
mysql -u root -p < backup.sql
```

启动复制：使用 CHANGE MASTER TO 命令配置从服务器连接到主服务器，并指定之前记录的二进制日志文件名和位置。

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;

启动从线程：最后启动从服务器的 I/O 和 SQL 线程，开始数据同步。
```
START SLAVE;
```
检查同步状态：可以通过 SHOW SLAVE STATUS\G; 查看复制的状态，确保没有错误发生。

主库读写分离

一旦主备同步设置完毕，我们就可以考虑实现读写分离。读写分离是指让应用程序将写操作发送到主服务器，而将读操作重定向到一个或多个备服务器上。这样不仅可以减轻主服务器的负担，还可以提升查询性能。

实现方式

应用层：直接在应用程序代码中实现逻辑判断，根据操作类型选择不同的数据库连接。
代理层：使用数据库代理软件（如 ProxySQL, MaxScale）自动分配读写请求。这类工具能够智能地管理连接池、负载均衡以及故障转移等功能。
中间件：某些框架或平台提供了内置的支持，允许开发者通过简单的配置来启用读写分离。

注意事项

在设计读写分离时，要注意事务的一致性。对于需要强一致性的查询，应该直接在主库上执行。
定期监控主备之间的延迟，确保备库的数据不会落后太多。
对于高并发场景，可能需要额外配置更多的备库来分散读取压力。

常见问题与解决方法

在进行 MySQL 主备部署的过程中，可能会遇到各种问题。以下是几个常见的“坑”以及对应的闭坑策略：

1. 时间不同步

问题描述：如果主备服务器之间的时间不同步，可能会导致复制失败或产生不一致的数据。

闭坑策略：

使用 NTP (Network Time Protocol) 服务来同步所有服务器的时间。
确认所有服务器的时区设置一致。

2. 主备延迟

问题描述：备服务器的数据更新总是慢于主服务器，尤其是在高负载情况下。

闭坑策略：

调整 MySQL 的配置参数，比如 innodb_flush_log_at_trx_commit 和 sync_binlog，以优化性能。
如果延迟过大，考虑增加更多的备服务器来分担读取压力。
检查是否有长时间运行的查询或者锁争用影响了备服务器的性能。

3. 复制中断

问题描述：由于网络问题或者其他原因，复制过程可能会突然中断。

闭坑策略：

设置 MASTER_CONNECT_RETRY 和 MASTER_RETRY_COUNT 参数，让备服务器在连接失败后自动尝试重新连接。
使用 GTID（全局事务标识符）代替传统的基于位置的复制，它能更可靠地处理复制中断后的恢复。
定期检查 SHOW SLAVE STATUS 的输出，及时发现并解决问题。

4. 权限不足

问题描述：用于复制的 MySQL 用户权限不足，导致无法正常复制数据。

闭坑策略：

确保复制用户拥有 REPLICATION SLAVE 权限。
如果需要，还应该授予 REPLICATION CLIENT 权限，以便查看主服务器的状态信息。
对于特定数据库或表的复制，可以考虑限制用户的权限范围。

5. 字符集不匹配

问题描述：主备服务器之间的字符集设置不一致，可能导致乱码或其他数据损坏的问题。

闭坑策略：

在所有服务器的 MySQL 配置文件中明确指定默认字符集（如 utf8mb4），并在数据库、表和列级别也做相应设置。
确保应用程序连接字符串中包含正确的字符集参数。

6. 数据一致性问题

问题描述：即使主备复制成功，但由于某些非事务性存储引擎的存在，可能会导致数据不一致。

闭坑策略：

尽量使用支持事务的存储引擎（如 InnoDB），并且确保所有表都使用相同的存储引擎。
对于已有的 MyISAM 表，考虑转换为 InnoDB 以增强数据一致性。

总结

通过上述步骤，你可以成功搭建一个 MySQL 的主备集群，并实施读写分离策略。不过，实际生产环境中还需要考虑更多因素，比如安全性、容错能力等。因此，在部署前务必做好充分的测试，并根据具体需求调整配置。

标签：主库,读写,MySQL,复制,主备,MASTER,服务器
From： https://blog.csdn.net/songchaoyang123/article/details/144578256

MySQL 主备部署与主库读写分离