PHP数据库读写分离配置_PHP主从复制数据库连接设置

class DbConnectionManager
{
    private $masterConnection = null;
    private $slaveConnections = [];
    private $currentSlaveIndex = 0;

    public function __construct(array $masterConfig, array $slaveConfigs)
    {
        // 假设这里用PDO
        $this->masterConnection = new PDO(
            $masterConfig['dsn'],
            $masterConfig['user'],
            $masterConfig['pass'],
            $masterConfig['options'] ?? []
        );
        $this->masterConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        foreach ($slaveConfigs as $config) {
            $slavePdo = new PDO(
                $config['dsn'],
                $config['user'],
                $config['pass'],
                $config['options'] ?? []
            );
            $slavePdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->slaveConnections[] = $slavePdo;
        }
    }

    public function getWriteConnection(): PDO
    {
        return $this->masterConnection;
    }

    public function getReadConnection(): PDO
    {
        if (empty($this->slaveConnections)) {
            // 如果没有从库，或者从库都挂了，为了保证可用性，可以降级使用主库进行读操作
            // 但这可能会增加主库压力，需要权衡
            error_log("No slave connections available, falling back to master for read.");
            return $this->masterConnection;
        }

        // 简单的轮询负载均衡
        $connection = $this->slaveConnections[$this->currentSlaveIndex];
        $this->currentSlaveIndex = ($this->currentSlaveIndex + 1) % count($this->slaveConnections);

        // 实际生产中，这里可能需要检查连接是否有效，无效则尝试下一个或重连
        // 比如可以尝试执行一个简单的 SELECT 1; 检查连接活性
        return $connection;
    }

    // 可以在这里加入事务管理，确保事务都在主库上执行
    public function beginTransaction() {
        return $this->masterConnection->beginTransaction();
    }

    public function commit() {
        return $this->masterConnection->commit();
    }

    public function rollBack() {
        return $this->masterConnection->rollBack();
    }
}

// 示例用法
// $manager = new DbConnectionManager(
//     ['dsn' => 'mysql:host=master_ip;dbname=test', 'user' => 'root', 'pass' => ''],
//     [
//         ['dsn' => 'mysql:host=slave1_ip;dbname=test', 'user' => 'root', 'pass' => ''],
//         ['dsn' => 'mysql:host=slave2_ip;dbname=test', 'user' => 'root', 'pass' => ''],
//     ]
// );

// $writePdo = $manager->getWriteConnection();
// $writePdo->exec("INSERT INTO users (name) VALUES ('John Doe')");

// $readPdo = $manager->getReadConnection();
// $stmt = $readPdo->query("SELECT * FROM users");
// $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

getWriteConnection()

getReadConnection()

UserRepository

save()

update()

delete()

DbConnectionManager->getWriteConnection()

findById()

findAll()

DbConnectionManager->getReadConnection()

$userRepository->save($user)

$userRepository->findById(1)

// 伪代码示例
class UserRepository
{
    private $dbManager;

    public function __construct(DbConnectionManager $manager)
    {
        $this->dbManager = $manager;
    }

    public function save(User $user): bool
    {
        $pdo = $this->dbManager->getWriteConnection();
        // 执行INSERT或UPDATE操作
        // ...
        return true;
    }

    public function findById(int $id): ?User
    {
        $pdo = $this->dbManager->getReadConnection();
        // 执行SELECT操作
        // ...
        return $user;
    }
}

config/database.php

mysql

read

write

beginTransaction()

commit()

rollBack()