-- 存儲過程無法通過語法區(qū)分讀寫，只能路由至主庫
DELIMITER //
CREATE PROCEDURE GetOrderDetails(IN orderId INT)
BEGIN 
  SELECT * FROM orders WHERE id = orderId; -- 讀操作，但中間件無法識別
  UPDATE orders SET status = 'processed' WHERE id = orderId; -- 寫操作
END //
DELIMITER ;

# MaxScale 配置文件 (maxscale.cnf)  
# 定義主庫
[server1]
type=server
address=192.168.1.101 
port=3306
protocol=MySQLBackend 
# 定義從庫 
[server2]
type=server 
address=192.168.1.102 
port=3306
protocol=MySQLBackend
# 定義從庫
[server3]
type=server
address=192.168.1.103
port=3306 
protocol=MySQLBackend 
# 讀寫分離路由 
[ReadWriteSplit]
type=service 
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=maxscale_pass 
# 負(fù)載均衡策略 
[ReadOnlyService]
type=service 
router=readconnroute 
router_options=slave 
servers=server2,server3
# 監(jiān)控主從狀態(tài)  
[ReplicationMonitor]  
type=monitor  
module=mariadbmon  
servers=server1, server2, server3  
user=monitor_user  
password=monitor_password

-- 主庫配置 (my.cnf)  
[mysqld]  
server-id=1  
log-bin=mysql-bin  
binlog-format=ROW
binlog-do-db=your_database  
-- 從庫配置 (my.cnf)  
[mysqld]  
server-id=2  
relay-log=mysql-relay-bin  
read-only=1  

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

// database.providers.ts  
import { DataSource } from 'typeorm';  
export const databaseProviders = [  
  {  
    provide: 'MASTER_DATABASE',  
    useFactory: () => new DataSource({  
      type: 'mysql',  
      host: 'master_host',  
      port: 3306,  
      username: 'master_user',  
      password: 'master_password',  
      database: 'your_db',  
      entities: [__dirname + '/..//*.entity{.ts,.js}'],  
      synchronize: false,  
    }).initialize(),  
  },  
  {  
    provide: 'SLAVE_DATABASE',  
    useFactory: () => new DataSource({  
      type: 'mysql',  
      host: 'slave_host',  
      port: 3306,  
      username: 'slave_user',  
      password: 'slave_password',  
      database: 'your_db',  
      entities: [__dirname + '/..//*.entity{.ts,.js}'],  
      synchronize: false,  
    }).initialize(),  
  },  
];  
// user.service.ts  
import { Injectable, Inject } from '@nestjs/common';  
import { InjectDataSource } from '@nestjs/typeorm';  
import { DataSource } from 'typeorm';  
@Injectable()  
export class UserService {  
  constructor(  
    @Inject('MASTER_DATABASE') private masterDataSource: DataSource,  
    @Inject('SLAVE_DATABASE') private slaveDataSource: DataSource,  
  ) {}  
  // 寫操作強(qiáng)制使用主庫  
  async createUser(userData: any) {  
    return this.masterDataSource.query(  
      `INSERT INTO users (name, email) VALUES (?, ?)`,  
      [userData.name, userData.email],  
    );  
  }  
  // 讀操作使用從庫（非實(shí)時敏感查詢）  
  async getUsers() {  
    return this.slaveDataSource.query(`SELECT * FROM users`);  
  }  
  // 實(shí)時敏感查詢使用主庫  
  async getRealTimeData() {  
    return this.masterDataSource.query(  
      `/* FORCE_MASTER */ SELECT * FROM transactions WHERE id = ?`,  
      [transactionId],  
    );  
  }  
}  

// database.providers.ts - 定義主從庫連接池  
import { createPool, Pool } from 'mysql2/promise';  
const masterPool: Pool = createPool({  
  host: 'master_db_host',  
  user: 'write_user',  
  password: 'write_password',  
  database: 'app_db',  
  connectionLimit: 10,  
});  
const slavePool: Pool = createPool({  
  host: 'slave_db_host', // 可擴(kuò)展為數(shù)組實(shí)現(xiàn)負(fù)載均衡  
  user: 'read_user',  
  password: 'read_password',  
  database: 'app_db',  
  connectionLimit: 20,  
});  
// query.service.ts - 根據(jù)操作類型路由查詢  
import { Injectable } from '@nestjs/common';  
@Injectable()  
export class QueryService {  
  async executeQuery(sql: string, isWrite: boolean = false) {  
    const pool = isWrite ? masterPool : slavePool;  
    const [rows] = await pool.query(sql);  
    return rows;  
  }  
  // 實(shí)時性敏感查詢顯式指定主庫  
  async criticalQuery(sql: string) {  
    return this.executeQuery(`/*MASTER*/ ${sql}`, true);  
  }  
}  
// user.controller.ts - 業(yè)務(wù)層調(diào)用示例  
@Controller('users')  
export class UserController {  
  constructor(private queryService: QueryService) {}  
  @Get(':id')  
  async getUser(@Param('id') id: string) {  
    // 讀操作路由到從庫  
    return this.queryService.executeQuery(  
      `SELECT * FROM users WHERE id = ${id}`  
    );  
  }  
  @Post()  
  async createUser(@Body() userData) {  
    // 寫操作路由到主庫  
    return this.queryService.executeQuery(  
      `INSERT INTO users (name) VALUES ('${userData.name}')`,  
      true  
    );  
  }  
}

// 配置主庫和從庫連接 
const masterDbConfig = { host: 'master-db', user: 'admin', password: 'pass' };
const slaveDbConfigs = [
  { host: 'slave-db1', user: 'readonly', password: 'pass' },
  { host: 'slave-db2', user: 'readonly', password: 'pass' }
];
// 手動路由讀寫操作 
import { DataSource } from 'typeorm';
const masterDataSource = new DataSource({ ...masterDbConfig, type: 'mysql' });
const slaveDataSources = slaveDbConfigs.map(config => new DataSource({ ...config, type: 'mysql' }));
// 寫操作路由至主庫
async function executeWrite(query: string) {
  await masterDataSource.query(query);
}
// 讀操作輪詢路由至從庫（簡單負(fù)載均衡）
let currentSlaveIndex = 0;
async function executeRead(query: string) {
  const dataSource = slaveDataSources[currentSlaveIndex];
  currentSlaveIndex = (currentSlaveIndex + 1) % slaveDataSources.length;
  return dataSource.query(query);
}

-- ProxySQL 讀分組配置示例  
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES  
(10, 'slave1', 3306),  
(10, 'slave2', 3306);  
UPDATE mysql_query_rules SET destination_hostgroup=10 WHERE match_pattern='^SELECT';  
LOAD MYSQL SERVERS TO RUNTIME;  

-- 啟用GTID與半同步  
SET GLOBAL gtid_mode=ON;  
SET GLOBAL enforce_gtid_consistency=ON;  
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  

-- 主庫配置
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
-- 從庫配置
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

// MHA狀態(tài)監(jiān)控端點(diǎn) (NestJS)  
@Get('/mha-status')  
async checkMHA() {  
 const ssh = await exec('masterha_check_status --conf=/etc/mha.cnf');  
 return { status: ssh.includes('alive') ? 'OK' : 'DOWN' };  
}