mysql读写库分离实现

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

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

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;

<?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");
?>