How We Migrated MySQL from Docker Swarm to Alibaba RDS and Fixed a CPU Crisis

Background and Motivation

The author, a senior operations engineer, joined a fast‑growing blockchain finance company that ran all services—including MySQL—in Docker Swarm on Alibaba Cloud ECS instances. Anticipating rapid user growth, the single‑container MySQL deployment was deemed unsustainable, prompting a move to a managed RDS database.

Original Architecture

Traffic flowed from CDN → WAF → ECS cluster. Each ECS host ran Docker Engine with containers for Nginx, PHP, Redis, and MySQL. MySQL data persisted via host directory mounts, and PHP applications connected to the MySQL container.

Docker‑Compose Example

version: "3"
services:
  ussbao:
    # replace username/repo:tag with your name and image details
    image: <em>hidden</em>
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
    environment:
      MYSQL_ROOT_PASSWORD: <em>hidden</em>
    volumes:
      - "/data//mysql/db1/:/var/lib/mysql/"
      - "/etc/localtime:/etc/localtime"
      - "/etc/timezone:/etc/timezone"
    networks:
      default:
        external:
          name: <em>hidden</em>

The compose file shows each database running as a single MySQL container without replication or any performance tuning.

Migration Plan

The team decided to provision an RDS instance and follow these steps:

Plan RDS instance size and create databases/users.

Add the application server IPs to the RDS whitelist.

Dump each MySQL container using mysqldump.

Import the dump files into RDS.

Update PHP configuration to point to the new RDS endpoint.

Clear PHP caches.

Test the application.

Enable RDS automatic backups.

Backup Script Used Before Migration

#!/bin/bash
# Database IP
dbserver='*******'
# Database user
dbuser='ganbing'
# Database password
dbpasswd='************'
# Databases to back up (space‑separated)
dbname='db1 db2 db3'
backtime=`date +%Y%m%d%H%M`
out_time=`date +%Y%m%d%H%M%S`
backpath='/data/backup/mysql/'
logpath='/data/backup/logs/'

echo "################## ${backtime} #############################"

echo "开始备份" >> ${logpath}/${dbname}_back.log

for DB in $dbname; do
  source=`/usr/bin/mysqldump -h ${dbserver} -u ${dbuser} -p${dbpasswd} ${DB} > ${backpath}/${DB}-${out_time}.sql` 2>> ${backpath}/mysqlback.log
  if [ "$?" == 0 ]; then
    cd $backpath
    tar zcf ${DB}-${backtime}.tar.gz ${DB}-${backtime}.sql > /dev/null
    rm -f ${DB}-${backtime}.sql
    find $backpath -name "*.tar.gz" -type f -mtime +15 -exec rm -rf {} \; > /dev/null 2>&1
    echo "数据库 ${dbname} 备份成功!!" >> ${logpath}/${dbname}_back.log
  else
    echo "数据库 ${dbname} 备份失败!!" >> ${logpath}/${dbname}_back.log
  fi
done

echo "完成备份"
echo "################## ${backtime} #############################"

Post‑Migration Issue

After moving several databases to RDS, the system experienced a sudden surge of request timeouts at the start of the business’s peak hour. Monitoring showed the RDS CPU spiking and hundreds of active sessions, all blocked on a single table ub_user_calculate that lacked any index.

Investigation revealed the table had over two million rows and no indexes, causing full‑table scans, connection exhaustion, and the observed outage.

Resolution Steps

Identify the problematic table and confirm missing indexes.

Coordinate with developers to decide which columns need indexing.

Attempt to create indexes; initial attempts hung due to massive session count.

Kill lingering sessions (partial success) and temporarily disable the domain entry to stop new traffic.

Add the required indexes.

Restart the RDS instance to clear CPU and session buildup.

Re‑enable the domain and verify normal traffic and CPU levels.

After the restart, CPU usage dropped, sessions cleared, and the service recovered.

Index Creation Guidelines

Index fields that are frequently queried but rarely updated.

Ensure ORDER BY and GROUP BY columns are indexed.

Limit the number of indexes per table to six.

Keep indexed column lengths short and fixed.

A primary‑key column does not need a separate index.

Index high‑selectivity columns.

Index Usage Pitfalls

Leading % in LIKE disables index usage.

Columns containing NULL are often excluded from indexes.

If any side of an OR condition lacks an index, the whole query may bypass indexes.

Using != forces a full scan.

Avoid functions or calculations on indexed columns.

Follow the left‑most prefix rule for composite indexes and keep field order consistent.

Prevent implicit type conversion by matching data types.

Conclusion

The outage highlighted the critical importance of proper index design before migration. Regular schema reviews, proactive monitoring of container‑based MySQL performance, and a well‑tested migration checklist are essential for high‑availability services.