# 部署架构
– 主库：192.168.1.100
– 备库1：192.168.1.101
– 备库2：192.168.1.102
– PgBouncer：192.168.1.200

# 配置步骤

## 1. 配置PgBouncer
$ cat > /etc/pgbouncer/pgbouncer.ini << EOF [databases] fgedudb = host=192.168.1.100 port=5432 dbname=fgedudb fgedudb_ro1 = host=192.168.1.101 port=5432 dbname=fgedudb fgedudb_ro2 = host=192.168.1.102 port=5432 dbname=fgedudb [pgbouncer] listen_addr = * listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 5000 default_pool_size = 50 min_pool_size = 10 reserve_pool_size = 20 reserve_pool_timeout = 5.0 max_db_connections = 200 max_user_connections = 100 EOF ## 2. 配置路由规则 $ cat > /etc/pgbouncer/routing_rules.ini << EOF # 路由规则 [writes] query = ^(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP|TRUNCATE|REINDEX|VACUUM|ANALYZE) pool = fgedudb [reads] query = ^SELECT pool = fgedudb_ro1,fgedudb_ro2 EOF ## 3. 启动PgBouncer $ systemctl start pgbouncer ## 4. 应用连接配置 # 应用连接字符串 jdbc:postgresql://192.168.1.200:6432/fgedudb ## 5. 验证读写分离 # 查看PgBouncer连接状态 $ pgbouncer -C "SHOW POOLS;" # 测试写操作 $ psql -h 192.168.1.200 -p 6432 -U fgedu -d fgedudb -c "INSERT INTO fgedu_test (name) VALUES ('test');" # 测试读操作 $ psql -h 192.168.1.200 -p 6432 -U fgedu -d fgedudb -c "SELECT * FROM fgedu_test;" # 查看备库状态 $ psql -h 192.168.1.101 -U fgedu -d fgedudb -c "SELECT * FROM fgedu_test;"

# 部署架构
– 主库：192.168.1.100
– 备库1：192.168.1.101
– 备库2：192.168.1.102
– 备库3：192.168.1.103
– ProxySQL：192.168.1.200

# 配置步骤

## 1. 安装ProxySQL
$ dnf install -y proxysql

## 2. 配置ProxySQL
$ mysql -u admin -padmin -h 127.0.0.1 -P 6032

ProxySQLAdmin> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
(10, ‘192.168.1.100’, 5432, 100, 1000),
(20, ‘192.168.1.101’, 5432, 100, 1000),
(20, ‘192.168.1.102’, 5432, 100, 1000),
(20, ‘192.168.1.103’, 5432, 100, 1000);

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup, active, max_connections) VALUES
(‘fgedu’, ‘fgedu123’, 10, 1, 5000);

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, ‘^SELECT.*FOR UPDATE’, 10, 1),
(2, 1, ‘^SELECT’, 20, 1);

ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;
ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL USERS TO DISK;
ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL QUERY RULES TO DISK;

## 3. 启动ProxySQL
$ systemctl start proxysql
$ systemctl enable proxysql

## 4. 应用连接配置
# 应用连接字符串
jdbc:postgresql://192.168.1.200:6033/fgedudb

## 5. 验证读写分离
# 查看ProxySQL状态
$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e “SELECT * FROM mysql_servers;”

# 测试写操作
$ psql -h 192.168.1.200 -p 6033 -U fgedu -d fgedudb -c “INSERT INTO fgedu_test (name) VALUES (‘test’);”

# 测试读操作
$ psql -h 192.168.1.200 -p 6033 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_test;”

# 查看备库状态
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_test;”

# 部署架构
– 主库：192.168.1.100
– 备库：192.168.1.101

# 应用层实现

## 1. Java应用实现
// 数据源配置
@Configuration
public class DataSourceConfig {

@Bean(name = “masterDataSource”)
@ConfigurationProperties(prefix = “spring.datasource.master”)
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = “slaveDataSource”)
@ConfigurationProperties(prefix = “spring.datasource.slave”)
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = “routingDataSource”)
public DataSource routingDataSource(@Qualifier(“masterDataSource”) DataSource masterDataSource,
@Qualifier(“slaveDataSource”) DataSource slaveDataSource) {
Map targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER, masterDataSource);
targetDataSources.put(DataSourceType.SLAVE, slaveDataSource);

RoutingDataSource routingDataSource = new RoutingDataSource();
routingDataSource.setTargetDataSources(targetDataSources);
routingDataSource.setDefaultTargetDataSource(masterDataSource);

return routingDataSource;
}

@Bean
public PlatformTransactionManager transactionManager(@Qualifier(“routingDataSource”) DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}

// 数据源路由
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}

// 数据源上下文
public class DataSourceContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal<>();

public static void setDataSourceType(DataSourceType type) {
contextHolder.set(type);
}

public static DataSourceType getDataSourceType() {
return contextHolder.get() != null ? contextHolder.get() : DataSourceType.MASTER;
}

public static void clearDataSourceType() {
contextHolder.remove();
}
}

// 读写分离注解
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
DataSourceType value() default DataSourceType.MASTER;
}

// 读写分离切面
@Aspect
@Component
public class DataSourceAspect {
@Before(“@annotation(dataSource)”)
public void before(JoinPoint point, DataSource dataSource) {
DataSourceType type = dataSource.value();
DataSourceContextHolder.setDataSourceType(type);
}

@After(“@annotation(dataSource)”)
public void after(JoinPoint point, DataSource dataSource) {
DataSourceContextHolder.clearDataSourceType();
}
}

// 服务层使用
@Service
public class UserService {
@Autowired
private UserMapper userMapper;

@DataSource(DataSourceType.MASTER)
public void createUser(User user) {
userMapper.insert(user);
}

@DataSource(DataSourceType.SLAVE)
public User getUserById(Long id) {
return userMapper.selectByPrimaryKey(id);
}
}

## 2. 验证读写分离
# 测试写操作
$ curl -X POST http://localhost:8080/api/users -H “Content-Type: application/json” -d ‘{“name”: “test”, “email”: “test@example.com”}’

# 测试读操作
$ curl http://localhost:8080/api/users/1

# 查看主库数据
$ psql -h 192.168.1.100 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_user;”

# 查看备库数据
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_user;”