Overview

A common database architecture optimization involves a single master and multiple slaves, with read-write separation and active synchronization. When an application has more read operations than write operations and the database struggles with read load, read-write separation can linearly improve read performance by adding more slave databases. The master handles write operations, while slaves handle read operations. Data synchronization between master and slaves occurs via binary logs (binlog). This approach primarily addresses "database read performance issues."

Project Case: Read-Write Separation

1. Import Sharding-JDBC Dependency

<dependency>
    <groupId>io.shardingjdbc</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>2.0.3</version>
</dependency>

2. Configure Master-Slave Data Sources and Read-Write Rules

Sharding-JDBC automatically routes connections to the master or slave based on whether the SQL statement is a Data Manipulation Language (DML) or Data Query Language (DQL) operation.

# Sharding-JDBC configuration
sharding:
  jdbc:
    data-sources:
      # Master database configuration
      main_ds:
        jdbc-url: jdbc:mysql://127.0.0.1:3306/master_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
      # First slave database configuration
      slave_ds_1:
        jdbc-url: jdbc:mysql://127.0.0.1:3306/slave_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
    # Read-write separation configuration
    master-slave-rule:
      # Load balancing algorithm: round-robin (alternatives: random)
      load-balance-algorithm-type: round_robin
      # Specify master database
      master-data-source-name: main_ds
      # Specify slave databases
      slave-data-source-names: slave_ds_1
      # Read-write separation group name (arbitrary)
      name: main_slave_group

3. Bind Configuration Properties to a Class

@Data
@ConfigurationProperties(prefix = "sharding.jdbc")
public class MasterSlaveShardingConfig {
    // Bind master-slave data sources
    private Map<String, HikariDataSource> dataSources = new HashMap<>();
    // Bind read-write separation rules
    private MasterSlaveRuleConfiguration masterSlaveRuleConfig;
}

4. Configure DataSource with Master-Slave and Separation Rules

@Configuration
@EnableConfigurationProperties(MasterSlaveShardingConfig.class)
@Slf4j
@ConditionalOnProperty({
    "sharding.jdbc.data-sources.main_ds.jdbc-url",
    "sharding.jdbc.master-slave-rule.master-data-source-name"
})
public class ShardingDataSourceSetup {

    @Autowired
    private MasterSlaveShardingConfig shardingConfig;

    @Bean
    public DataSource masterSlaveDataSource() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.putAll(shardingConfig.getDataSources());
        MasterSlaveRuleConfiguration rule = shardingConfig.getMasterSlaveRuleConfig();
        Map<String, Object> props = new HashMap<>();
        // Configure master-slave data sources and separation rules into DataSource
        DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, rule, props);
        log.info("Master-slave data source configuration completed");
        return dataSource;
    }
}

Master-Slave Synchronization

Sharding-JDBC does not directly provide master-slave synchronization. This is typically managed at the database level (e.g., MySQL’s master-slave replication). When using Sharding-JDBC for read-write separation, ensure data synchronization between the master and slaves.

For MySQL, the general master-slave synchronization process is:

1. Configure the Master: Enable binary logging on the master. All changes (e.g., INSERT, UPDATE, DELETE) are logged.
2. Configure the Slave: Set up the slave to connect to the master and specify the binary log position to start replication. The slave requests and applies the binary log content.
3. Data Replication: The master sends binary log content to the slave, which parses and executtes the SQL to stay synchronized.
4. Continuous Synchronization: The slave continuously monitors the master’s binary log for changes and applies them in real time.

When using Sharding-JDBC for read-write separation:

• Ensure database master-slave replication is properly configured.
• In Sharding-JDBC configuration, specify which data sources are master and slave.
• Ensure the application routes write operations (INSERT, UPDATE, DELETE) to the master and read operations (SELECT) to slaves.

Note: While master-slave replication provides data redundancy and backup, it is not a high-availability solution. If the master fails, slaves can still serve reads but not writes. For high availability, consider solutions like MySQL Group Replication or other database-specific high-availability mechanisms.