Horizontal Database Scaling Strategies and Practical Implementation with MariaDB, Keepalived, and ShardingJDBC

Horizontal Database Scaling Overview

Initially three databases (A, B, C) were used with a uid%3 sharding rule to evenly distribute data; as user volume grew, adding a fourth database (D) required changing the rule to uid%4, causing massive data migration and service instability.

Five Migration Schemes

1. Stop‑Service Migration

Publish downtime announcement.

Stop the Service.

Perform offline data migration according to the new sharding rule.

Validate data consistency with a custom verification program.

Update Service configuration from uid%3 to uid%4.

Restart the Service.

Prepare rollback plans for each step.

Pros: Guarantees migration correctness; Cons: Service downtime hurts user experience.

2. Stop‑Write Migration

Enable read‑write separation; set all databases to read‑only before migration.

Announce the upgrade.

Intercept write requests in the Service layer and return a maintenance message.

Copy data to the new node according to the new sharding rule.

Validate data using backup data.

Change configuration without restarting the Service.

Restore write capability and clean up redundant data.

Rollback plans for each step.

Cons: Large data copy time and prolonged write suspension.

3. Log‑Based Migration

Core idea: use logs to synchronize data during migration.

During upgrade, record modification logs (table, primary key, operation type) without full data.

Develop a migration tool to move existing data to the new node while the old node continues serving.

After migration, use a log‑incremental tool to replay logs and keep the new node in sync.

Perform data verification and finally switch traffic to the new node.

Pros: Minimal impact on online service; Cons: Complex tooling and longer overall cycle.

4. Dual‑Write Scheme (Small‑to‑Medium Data)

Implement dual writes via Canal or MQ.

Add the new node.

Perform an initial data dump and import.

Enable Canal to capture changes from the old node and replicate them to the new node.

Switch traffic using Nginx.

Handle any synchronization anomalies with custom scripts.

Validate data completeness.

5. Smooth 2N Expansion (Large Data)

Use a master‑master pair for each shard and add a replica to each, then adjust the sharding rule without moving existing data.

Upgrade existing replicas to master role, expanding from 2 to 4 shards.

Update sharding configuration via a configuration center.

Clean redundant data gradually; no service interruption.

Practical Implementation

MariaDB Service Installation

yum -y install wget
## Backup CentOS-Base.repo
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -P /etc/yum.repos.d/ http://mirrors.aliyun.com/repo/epel-7.repo

yum clean all

yum makecache

vi /etc/yum.repos.d/mariadb-10.2.repo

[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum -y install mariadb mariadb-server MariaDB-client MariaDB-common

MariaDB Master‑Master Synchronization

Server1 (ID=1) configuration snippet:

[mysqld]
server-id = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=1
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

Server2 (ID=2) configuration snippet (offset=2):

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

Create replication user:

grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
flush privileges;

Configure master info on each server with change master to ... and start slave.

Keepalived High‑Availability Setup

Server1 /etc/keepalived/keepalived.conf (VIP 192.168.116.150):

global_defs {
  router_id vip1
}

vrrp_instance VI_1 {
  state BACKUP
  interface ens33
  virtual_router_id 111
  priority 100
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass 6666
  }
  virtual_ipaddress {
    192.168.116.150
  }
}

virtual_server 192.168.116.150 3306 {
  delay_loop 6
  lb_algo rr
  lb_kind DR
  persistence_timeout 0
  protocol TCP
  real_server 192.168.116.140 3306 {
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
      connect_timeout 10
      retry 3
      delay_before_retry 3
      connect_port 3306
    }
  }
}

Server2 configuration is analogous with a lower priority and its own VIP.

ShardingJDBC Integration

Key steps:

Add Maven dependency for ShardingSphere‑JDBC.

Configure sharding rules in application.yml .

Create DataSource objects for each node.

Use OrchestrationShardingDataSource to dynamically add a new DataSource at runtime.

// Dynamic data source configuration for expansion
Properties properties = loadProperties("datasource1.properties");
DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
// set connection pool options
OrchestrationShardingDataSource dataSource = SpringContextUtil.getBean("tradeSystemDataSource", OrchestrationShardingDataSource.class);
Map
dataSourceMap = dataSource.getDataSource().getDataSourceMap();
dataSourceMap.put(DatasourceEnum.DATASOURCE_2.getValue(), druidDataSource);
// renew configuration
dataSource.renew(new DataSourceChangedEvent("/" + DruidSystemDataSourceConfiguration.DYNAMIC_SHARDING + "/config/schema/logic_db/datasource", dataSourceConfigMap));

Final Remarks

The author encourages readers to like, share, and follow the public account for more technical content and offers a knowledge‑planet subscription with discounted pricing.