Ubuntu MariaDB读写分离实践

Ubuntu环境下MariaDB读写分离实践指南

一、前置准备

读写分离的核心基础是MariaDB主从复制，需先完成主从架构部署。建议准备2台及以上Ubuntu服务器（如master：192.168.1.100、slave：192.168.1.101），确保服务器间网络互通（关闭防火墙或放行3306端口）。

二、配置MariaDB主从复制

1. 配置主服务器（Master）

编辑主服务器MariaDB配置文件（/etc/my.cnf.d/server.cnf），添加以下参数：

[mysqld]
server-id=1                # 主服务器唯一ID（从服务器需不同）
log_bin=mysql-bin          # 开启二进制日志（记录数据变更）
binlog_do_db=test          # 指定同步的数据库（可选，留空则同步所有）

重启MariaDB服务使配置生效：
```
sudo systemctl restart mariadb
```

创建复制用户并授权：
登录MariaDB，执行以下SQL：

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

获取主服务器状态：
执行SHOW MASTER STATUS;，记录File（如mysql-bin.000001）和Position（如154）值，后续从服务器配置需用到。

2. 配置从服务器（Slave）

编辑从服务器MariaDB配置文件（/etc/my.cnf.d/server.cnf），添加以下参数：

[mysqld]
server-id=2                # 从服务器唯一ID
relay_log=mysql-relay-bin  # 开启中继日志（接收主服务器变更）
log_slave_updates=1        # 允许从服务器将更新写入中继日志
read_only=1                # 设置为只读（防止误操作破坏数据一致性）

重启MariaDB服务：
```
sudo systemctl restart mariadb
```

配置主从同步：
登录从服务器MariaDB，执行以下SQL（替换为主服务器的File、Position、用户名和密码）：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;                 # 启动复制线程

验证同步状态：
执行SHOW SLAVE STATUS\G，检查Slave_IO_Running和Slave_SQL_Running是否均为Yes（表示同步正常）。

三、选择读写分离中间件

常用的中间件有ProxySQL（高性能、易配置）、MySQL Router（官方轻量级）、HAProxy（TCP/HTTP负载均衡）。以下以ProxySQL为例，介绍具体配置步骤。

四、使用ProxySQL实现读写分离

1. 安装ProxySQL

在Ubuntu上通过以下命令安装：

wget https://github.com/sysown/proxysql/releases/download/v2.3.0/proxysql_2.3.0-1_amd64.deb
sudo dpkg -i proxysql_2.3.0-1_amd64.deb
sudo systemctl start proxysql
sudo systemctl enable proxysql

2. 登录ProxySQL管理接口

ProxySQL的管理端口为6032，使用默认账号admin登录：

mysql -u admin -padmin -P6032 -h127.0.0.1

3. 添加主从服务器信息

在mysql_servers表中添加主从服务器地址（hostgroup_id=10表示写组，hostgroup_id=20表示读组）：

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.1.100',3306); -- 主服务器（写组）
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'192.168.1.101',3306); -- 从服务器（读组）
LOAD MYSQL SERVERS TO RUNTIME;       -- 加载配置到内存
SAVE MYSQL SERVERS TO DISK;          -- 保存配置到磁盘

4. 配置主从监控

启用ProxySQL对主从状态的监控（需创建监控账号并授权）：

在主服务器上创建监控账号：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'monitor_password';
FLUSH PRIVILEGES;

在ProxySQL中设置监控参数：

SET mysql_monitor_username='monitor';
SET mysql_monitor_password='monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

5. 配置读写分组规则

创建读写分组（writer_hostgroup=10表示写组，reader_hostgroup=20表示读组）：

INSERT INTO mysql_replication_hostgroups VALUES (10,20,'test'); -- test为逻辑库名
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

6. 添加用户并关联规则

在主服务器上创建客户端连接账号（用于ProxySQL转发请求）：

GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'192.168.1.%' IDENTIFIED BY 'app_password';
FLUSH PRIVILEGES;

在ProxySQL中添加用户并设置默认组（default_hostgroup=10表示默认走写组）：

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app_user','app_password',10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

7. 测试读写分离

使用客户端连接ProxySQL（端口6033）：

mysql -u app_user -papp_password -h192.168.1.102 -P6033

执行写操作（如INSERT），检查主服务器数据是否同步；执行读操作（如SELECT），检查从服务器是否处理请求（可通过SHOW PROCESSLIST查看从服务器连接状态）。

五、验证与优化

验证数据一致性：在主服务器插入数据，检查从服务器是否同步。
监控性能：通过ProxySQL的stats表查看请求分布（如SELECT * FROM stats.stats_mysql_query_digest）。
调整规则：根据业务需求修改balance参数（如balance=3表示读请求仅发送到从服务器）。

注意事项

主从复制延迟：异步复制可能导致数据短暂不一致，需根据业务容忍度调整。
中间件高可用：ProxySQL需部署多实例，避免单点故障。
安全配置：限制中间件端口访问范围，使用强密码，定期备份配置。