mysql读写库分离实现

基础概念

MySQL读写库分离是一种数据库架构设计模式，旨在提高数据库的性能和可扩展性。在这种模式下，读操作和写操作被分离到不同的数据库实例上。通常，主库（Master）负责处理写操作，而从库（Slave）负责处理读操作。

优势

提高性能：读写分离可以将读操作和写操作分散到不同的数据库实例上，从而减轻主库的压力，提高整体性能。
提高可用性：如果主库发生故障，可以从从库中选择一个升级为主库，保证系统的可用性。
扩展性：通过增加从库的数量，可以轻松扩展系统的读取能力。

类型

基于SQL语句的读写分离：通过解析SQL语句，将读操作和写操作分别路由到不同的数据库实例。
基于连接的读写分离：通过配置连接池，将读操作和写操作分别连接到不同的数据库实例。

应用场景

高并发读取场景：适用于需要处理大量读取请求的应用，如电商网站、社交媒体等。
数据备份和恢复：通过从库进行数据备份，可以提高数据的安全性和恢复速度。
负载均衡：通过读写分离，可以将负载分散到多个数据库实例上，提高系统的整体性能。

实现方法

以下是一个基于MySQL的读写分离实现示例：

1. 配置主从复制

首先，需要在MySQL中配置主从复制。假设主库的IP地址为192.168.1.1，从库的IP地址为192.168.1.2。

主库配置（my.cnf）：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=mydatabase

从库配置（my.cnf）：

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

然后重启MySQL服务，并在主库上创建一个复制用户：

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

在从库上设置主库信息：

CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
START SLAVE;

2. 实现读写分离

可以使用代理层（如MySQL Proxy、MaxScale）或应用程序层面来实现读写分离。

示例：使用应用程序层面实现读写分离

假设我们有一个简单的PHP应用程序，可以使用以下代码实现读写分离：

<?php
$host = '192.168.1.1'; // 主库地址
$slaveHost = '192.168.1.2'; // 从库地址
$user = 'username';
$password = 'password';
$dbname = 'mydatabase';

function connect($host, $user, $password, $dbname) {
    $conn = new mysqli($host, $user, $password, $dbname);
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    }
    return $conn;
}

function execute($conn, $sql) {
    return $conn->query($query);
}

// 写操作
$writeConn = connect($host, $user, $password, $dbname);
$writeResult = execute($writeConn, "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')");

// 读操作
$readConn = connect($slaveHost, $user, $password, $dbname);
$readResult = execute($readConn, "SELECT * FROM users");
?>