七十、【Linux数据库】MySQL数据库读写分离 、 MySQL多实例

MySQL 读写分离与多实例功能概述

读写分离核心功能

1. 负载均衡：将读操作分发到多个从库
2. 高可用：主库故障时自动切换
3. 透明路由：应用层无需修改代码
4. 性能优化：写操作集中在主库，读操作分散到从库

多实例核心功能

1. 资源隔离：独立配置、端口、数据目录
2. 版本共存：同时运行不同MySQL版本
3. 环境隔离：开发、测试、生产环境分离
4. 成本优化：单机部署多个实例节省硬件

一、读写分离实现（ProxySQL）

1. 安装配置 ProxySQL

# 添加ProxySQL仓库
[root@localhost ~]# cat > /etc/yum.repos.d/proxysql.repo <<EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

# 安装ProxySQL
[root@localhost ~]# yum install proxysql -y

# 启动服务
[root@localhost ~]# systemctl start proxysql

2. 配置后端服务器

# 登录管理接口
[root@localhost ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 添加主库（写节点）
ProxySQL> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '192.168.1.100', 3306);

# 添加从库（读节点）
ProxySQL> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.101', 3306);
ProxySQL> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.102', 3306);

# 保存配置
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
ProxySQL> SAVE MYSQL SERVERS TO DISK;

3. 配置用户认证

# 添加应用用户
ProxySQL> INSERT INTO mysql_users(username, password, default_hostgroup) 
VALUES ('app_user', 'AppPass123!', 10);

# 保存配置
ProxySQL> LOAD MYSQL USERS TO RUNTIME;
ProxySQL> SAVE MYSQL USERS TO DISK;

4. 配置路由规则

# 写操作路由（主库）
ProxySQL> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^INSERT', 10, 1),
       (2, 1, '^UPDATE', 10, 1),
       (3, 1, '^DELETE', 10, 1);

# 读操作路由（从库）
ProxySQL> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (4, 1, '^SELECT', 20, 1);

# 保存配置
ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;
ProxySQL> SAVE MYSQL QUERY RULES TO DISK;

5. 测试读写分离

# 通过ProxySQL连接
[root@localhost ~]# mysql -u app_user -pAppPass123! -h 127.0.0.1 -P 6033

# 执行写操作
mysql> INSERT INTO test.tb1 VALUES (1);  -- 路由到主库

# 执行读操作
mysql> SELECT * FROM test.tb1;  -- 路由到从库

二、多实例部署

1. 创建实例目录

# 创建实例目录
[root@localhost ~]# mkdir -p /mysql/{3307,3308}/data
[root@localhost ~]# chown -R mysql:mysql /mysql

2. 初始化实例

# 初始化实例1 (3307)
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/mysql/3307/data

# 初始化实例2 (3308)
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/mysql/3308/data

3. 配置实例

# 实例1配置 (3307)
[root@localhost ~]# cat > /mysql/3307/my.cnf <<EOF
[mysqld]
port=3307
socket=/mysql/3307/mysql.sock
datadir=/mysql/3307/data
log-error=/mysql/3307/error.log
pid-file=/mysql/3307/mysql.pid
server-id=1
EOF

# 实例2配置 (3308)
[root@localhost ~]# cat > /mysql/3308/my.cnf <<EOF
[mysqld]
port=3308
socket=/mysql/3308/mysql.sock
datadir=/mysql/3308/data
log-error=/mysql/3308/error.log
pid-file=/mysql/3308/mysql.pid
server-id=2
EOF

4. 启动实例

# 启动实例1
[root@localhost ~]# mysqld_safe --defaults-file=/mysql/3307/my.cnf &

# 启动实例2
[root@localhost ~]# mysqld_safe --defaults-file=/mysql/3308/my.cnf &

5. 验证实例

# 连接实例1
[root@localhost ~]# mysql -S /mysql/3307/mysql.sock -e "SHOW VARIABLES LIKE 'port'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

# 连接实例2
[root@localhost ~]# mysql -S /mysql/3308/mysql.sock -e "SHOW VARIABLES LIKE 'port'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3308  |
+---------------+-------+

三、多实例读写分离整合

1. 配置主从复制

# 在3307实例执行
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# 在3308实例执行
mysql> CHANGE MASTER TO
    MASTER_HOST='127.0.0.1',
    MASTER_PORT=3307,
    MASTER_USER='repl',
    MASTER_PASSWORD='ReplPass123!';
mysql> START SLAVE;

2. 配置ProxySQL

# 添加多实例到ProxySQL
ProxySQL> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES
    (10, '127.0.0.1', 3307),  # 主库
    (20, '127.0.0.1', 3308);  # 从库

# 重载配置
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
ProxySQL> SAVE MYSQL SERVERS TO DISK;

3. 测试多实例读写分离

# 通过ProxySQL写入
mysql> INSERT INTO test.tb1 VALUES (2);  # 写入3307

# 通过ProxySQL读取
mysql> SELECT * FROM test.tb1;  # 从3308读取

命令总结表格