如何在 MySQL 中实现读写分离？

发表于 更新于 分类于 阅读次数： 本文字数： 3.5k 阅读时长 ≈ 3 分钟

你好，我是猿java。

在 MySQL 中实现读写分离（Read-Write Separation）可以有效提升数据库的性能和扩展性。读写分离的基本思想是将数据库的读操作和写操作分离到不同的服务器上，通常使用主从复制（Master-Slave Replication）的架构来实现。以下是实现 MySQL 读写分离的详细步骤和方法：

1. 架构概览

  1. 主库（Master）：负责处理所有的写操作（INSERT、UPDATE、DELETE）和部分读操作。
  2. 从库（Slave）：负责处理所有的读操作（SELECT）。

通过这种架构，可以减轻主库的负载，提高整体系统的并发处理能力。

2. 实现步骤

1. 准备主库和从库

确保你有两台或多台服务器，一台作为主库，其他作为从库。安装相同版本的 MySQL，并确保网络连通。

2. 配置主库

编辑主库的 MySQL 配置文件（通常是 my.cnf 或 my.ini），添加或修改以下参数：

1
2
3
4
5
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
  • server-id：主库的唯一标识，通常设置为1。
  • log-bin：启用二进制日志，用于主从复制。
  • binlog-ignore-db：忽略特定数据库的日志。

重启 MySQL 服务使配置生效：

1
sudo service mysql restart

3. 创建复制用户

在主库上创建一个用于复制的用户，并赋予必要的权限：

1
2
3
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

4. 获取主库的二进制日志位置信息

在主库上执行以下命令，记录当前的二进制日志文件名和位置：

1
2
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录 File 和 Position 的值，例如 mysql-bin.000001 和 154。

注意：执行 FLUSH TABLES WITH READ LOCK 后，主库会锁定表以确保数据一致性。完成后，需保持会话开启或使用其他方法解锁。

5. 导出主库数据

在主库上导出数据库数据，可以使用 mysqldump：

1
mysqldump -u root -p --all-databases --master-data > all_databases.sql

--master-data 参数会在导出的 SQL 文件中包含二进制日志位置信息，便于在从库上设置。

6. 导入数据到从库

将导出的 all_databases.sql 文件传输到从库服务器，并导入：

1
mysql -u root -p < all_databases.sql

7. 配置从库

编辑从库的 MySQL 配置文件，设置唯一的 server-id 和其它必要参数：

1
2
3
4
5
[mysqld]
server-id=2
relay-log=relay-log
log-bin=mysql-bin
read_only=1
  • server-id：从库的唯一标识，确保与主库不同。
  • relay-log：启用中继日志。
  • log-bin：如果从库还作为其他从库的主库，需要启用二进制日志。
  • read_only：设置从库为只读，防止直接写入。

重启从库的 MySQL 服务：

1
sudo service mysql restart

8. 启动复制进程

在从库上执行以下命令，配置主库信息并启动复制：

1
2
3
4
5
6
7
8
CHANGE MASTER TO
    MASTER_HOST='主库IP或主机名',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
  • MASTER_HOST：主库的IP地址或主机名。
  • MASTER_USER 和 MASTER_PASSWORD：之前创建的复制用户。
  • MASTER_LOG_FILE 和 MASTER_LOG_POS：主库的二进制日志文件名和位置。

验证复制是否正常运行：

1
SHOW SLAVE STATUS\G

检查 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes，且没有错误信息。

9. 配置应用程序

在应用程序中，将写操作路由到主库，读操作路由到从库。这通常可以通过以下几种方式实现：

  • 应用层控制：在应用代码中明确指定读写分离的数据库连接。
  • 数据库中间件：使用中间件如 ProxySQL、MaxScale 等，自动将读写请求分离。
  • 负载均衡器：配置负载均衡器，将写请求定向到主库，读请求分发到从库。

使用 ProxySQL 实现读写分离

ProxySQL 是一个功能强大的 MySQL 中间件，支持复杂的路由和负载均衡策略。以下是简单的配置步骤：

  1. 安装 ProxySQL
1
2
3
# 以 Ubuntu 为例
sudo apt-get update
sudo apt-get install proxysql
  1. 配置 ProxySQL

登录 ProxySQL 管理接口：

1
mysql -u admin -padmin -h 127.0.0.1 -P 6032

添加主库和从库到配置中：

1
2
3
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '主库IP', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '从库IP1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '从库IP2', 3306);

添加复制用户：

1
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('repl', 'password', 0);

配置查询路由规则，将读语句路由到从库：

1
2
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply)
VALUES (1, '^SELECT .*', 1, 1);

保存并加载配置：

1
2
3
4
5
6
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
  1. 修改应用程序的数据库连接

将应用程序的数据库连接地址指向 ProxySQL 的地址（通常是 127.0.0.1:6033），ProxySQL 将根据配置将读写请求分别转发到主库和从库。

三、注意事项

  1. 数据一致性：由于主从复制是异步的，可能会存在主库写入后从库还未同步完成的短暂不一致。因此，业务逻辑需要考虑这种情况，避免读到脏数据。
  2. 监控和故障转移：部署监控工具，实时监控主从库的状态。一旦主库出现故障，需要及时进行故障转移，提升从库为新的主库。
  3. 主从库的配置管理：确保主从库的配置一致性，特别是字符集、时间设置等，避免因配置差异引发的问题。
  4. 安全性：确保复制用户的权限最小化，并通过防火墙或 VPN 等措施保护主从库之间的通信安全。

四、扩展

  1. 多从库架构：可以根据读负载的需求，增加多个从库，进一步提升读性能。
  2. 主主复制（Master-Master Replication）：在不同的主机上部署双向复制，实现双主架构，增强系统的高可用性和负载均衡能力。但配置和管理复杂度较高。
  3. 使用 Galera Cluster：类似于 Percona XtraDB Cluster 或 MariaDB Galera Cluster，提供同步复制和多主架构，适用于需要高可用性和数据一致性的场景。

通过上述方法和步骤，您可以在 MySQL 中成功实现读写分离，提升数据库的性能和可扩展性。

6. 学习交流

如果你觉得文章有帮助，请帮忙转发给更多的好友，或关注公众号：猿java，持续输出硬核文章。

drawing