Migrate Database (MySQL to RDS)

Overview

This article walks the installer through migrating the LILT application database from an in-cluster MySQL deployment to an Amazon RDS for MySQL instance. It applies when moving an existing self-managed installation onto a fully managed cluster, such as the environment described in Install System (AWS EKS), where RDS replaces the in-cluster database. The database is exported through a proxied connection to a staging host and restored into RDS from there. Nothing is written inside the database pod at any point, so the procedure works even when the existing storage volume has no free space for a dump file. The export stage is read-only against the source cluster, leaving the original environment available as a fallback until the migration is accepted. Object storage migration is a separate procedure; see Migrate Object Storage (MinIO to S3).

Tools you will need

AWS user with permissions to describe the RDS instance, modify its parameter group, and create snapshots.
Required utilities for the staging host:
- AWS CLI
- kubectl, with access to the source cluster
- MySQL client tools matching the source server version
A staging host with:
- Network access to the RDS endpoint, or the ability to transfer the dump file to a host that has it
- Free disk space for the compressed dump

Structure of this Article

The first section explains where the database migration belongs in the overall install sequence, because restoring at the wrong point produces a schema that cannot be repaired automatically. The remaining sections cover assessing the source database, preparing the RDS instance, exporting and restoring the data, reconciling the schema migration metadata, applying pending schema migrations, rebuilding the search index, and verifying the result.

Installer privileges

All commands are run from a staging host by a user with the permissions stated in the “Tools” section above. Commands that target the source cluster assume the LILT namespace is lilt and the database pod is mysql-0; adjust if your installation differs.

Migration Order

Restore before installing LILT

The application’s schema migrations are the mechanism that brings an older database up to the current schema. They must run against the restored data, which means the restore has to complete before LILT is installed on the new cluster. Installing first and restoring afterwards does not work, and fails in a way that is difficult to detect. On an empty database the installer applies every migration and produces a complete current-version schema. Restoring an older dump on top of that does not reset it: a dump drops and recreates only the tables it contains, so every table introduced after the dump was taken survives untouched, while the migration metadata table is replaced wholesale with the older list. The result is a mixture of old and new tables whose metadata claims the newer migrations never ran. Nothing reports an error at that point; the inconsistency surfaces later as failures during a subsequent upgrade. The correct sequence is:

Provision the RDS instance and prepare the parameter group
Create the application database user and grants
Restore the dump
Reconcile the schema migration metadata
Take an RDS snapshot
Install LILT, which applies the pending schema migrations
Reset the search index cursor and restart the indexer
Verify, then return the environment to service

Migrate first, upgrade separately

If the new environment also runs a newer LILT release than the source, treat the storage move and the version upgrade as two operations. Restore the data and confirm it is intact at the original version first, then perform the version upgrade against RDS. Combined, a failure partway through several hundred schema migrations cannot be attributed to either cause.

Assess the Source Database

Set the user environment variables

export SOURCE_DB="lilt_dev"
export STAGE_DIR="/export"

lilt_dev is the default application database name. Confirm it against your installation:

kubectl get configmap -n lilt lilt-configmap -o yaml | grep -i dbname

Read the database credentials

export SRC_PASS=$(kubectl -n lilt get secret mysql-svcbind-root \
  -o jsonpath='{.data.password}' 2>/dev/null | base64 -d)

If that secret does not exist in an older installation, use the root password configured for the database chart.

Record the server configuration

These values determine the export options and the RDS parameter group. Record all of them before continuing:

kubectl -n lilt exec -i mysql-0 -- mysql -uroot -p"$SRC_PASS" -N -e "
SELECT @@VERSION, @@GLOBAL.GTID_MODE, @@GLOBAL.ENFORCE_GTID_CONSISTENCY,
       @@GLOBAL.max_allowed_packet, @@GLOBAL.character_set_server,
       @@GLOBAL.collation_server;"

Record the database-level character set and collation separately. These must be reproduced on RDS:

kubectl -n lilt exec -i mysql-0 -- mysql -uroot -p"$SRC_PASS" -N -e "
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata WHERE schema_name='$SOURCE_DB';"

Measure the database

This sizes the disk requirement on the staging host. A logical dump is smaller than the reported total because it contains no index data, and compresses further:

kubectl -n lilt exec -i mysql-0 -- mysql -uroot -p"$SRC_PASS" -N -e "
SELECT ROUND(SUM(data_length+index_length)/1024/1024/1024,2) AS gb, COUNT(*) AS tables
FROM information_schema.tables WHERE table_schema='$SOURCE_DB';"

kubectl -n lilt exec mysql-0 -- df -h /var/lib/mysql

Reclaim space on the storage volume

When binary logging is enabled, the log files are written into the data directory and can occupy a significant share of a small volume. Reclaiming that space reduces the risk of the volume filling during the export:

kubectl -n lilt exec -i mysql-0 -- mysql -uroot -p"$SRC_PASS" -e "SHOW BINARY LOGS;"
kubectl -n lilt exec -i mysql-0 -- mysql -uroot -p"$SRC_PASS" -e \
  "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 HOUR;"

Prepare the RDS Instance

Engine version

Create the instance with an engine version matching the source server recorded above. Restoring a dump taken from a newer server into an older engine version can fail on syntax or features the older version does not support.

Parameter group

Set the following in the instance’s parameter group:

log_bin_trust_function_creators = 1
max_allowed_packet              = <value recorded from the source>

log_bin_trust_function_creators is required because the restoring user does not hold the SUPER privilege on RDS, and stored routines or triggers in the dump fail without it. If the application’s database client cannot negotiate the caching_sha2_password authentication plugin, also enable mysql_native_password in the parameter group. Recent MySQL versions disable that plugin by default, and the failure only appears when the application connects — testing as the administrative user succeeds regardless.

Correct the database character set and collation

This step is required when the database was created for you, either by the instance’s initial database name setting or by an infrastructure-as-code definition. A dump taken with the --databases option includes a CREATE DATABASE ... IF NOT EXISTS statement carrying the source character set and collation. When the database already exists, that statement does nothing and its collation clause is ignored, leaving the database on the engine’s default collation instead of the source’s. The restored tables are unaffected, because every CREATE TABLE statement in the dump carries its own character set and collation. The problem appears later: tables created by subsequent schema migrations without an explicit collation inherit the database default, and queries joining them against restored tables fail with Illegal mix of collations. That surfaces days afterwards as intermittent errors rather than as a restore failure. Create a credentials file for the administrative user and correct the collation to match the source:

export RDS_ENDPOINT="example-instance.abcdefghijkl.us-east-1.rds.amazonaws.com"

umask 077
cat > /tmp/rds-admin.cnf <<EOF
[client]
host=$RDS_ENDPOINT
user=<rds-master-user>
password=<rds-master-password>
ssl-mode=REQUIRED
EOF

mysql --defaults-extra-file=/tmp/rds-admin.cnf -e \
  "ALTER DATABASE \`$SOURCE_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql --defaults-extra-file=/tmp/rds-admin.cnf -e "
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata WHERE schema_name='$SOURCE_DB';"

Substitute the character set and collation recorded from the source. ALTER DATABASE changes only the default applied to newly created objects; it does not rewrite existing data.

Create the application database user

User accounts and grants are not carried by the dump and must be created directly. Skip this step if your infrastructure definition already provisions the user:

mysql --defaults-extra-file=/tmp/rds-admin.cnf <<'SQL'
CREATE USER IF NOT EXISTS 'lilt'@'%' IDENTIFIED WITH mysql_native_password BY '<app-password>';
ALTER USER 'lilt'@'%' IDENTIFIED WITH mysql_native_password BY '<app-password>';
GRANT ALL PRIVILEGES ON `lilt_dev`.* TO 'lilt'@'%';
FLUSH PRIVILEGES;
SQL

A database-level grant is valid even before the database exists, and the CREATE privilege it includes is what allows the dump to create the database if it is not already present. Do not run any database bootstrap or schema initialization job at this stage. Creating the database and user is required; applying schema migrations before the restore produces the inconsistent schema described in “Migration Order”.

Export the Database

Stop application writes

A dump is only consistent if nothing is writing to the database. This also protects the storage volume: a consistent dump requires the server to retain previous row versions for the duration of the export, and on a volume with little free space a long export against a live database can exhaust it. Record the current replica counts first, so the environment can be restored if the migration is abandoned:

kubectl -n lilt get deploy,statefulset \
  -o custom-columns='KIND:.kind,NAME:.metadata.name,REPLICAS:.spec.replicas' \
  --no-headers > /tmp/replicas-before.txt

Scale down the application, excluding the data services:

DATA_SVCS='mysql|mongodb|rabbitmq|clickhouse|elasticsearch|redis|dragonfly|qdrant|minio'

kubectl -n lilt get deploy -o name \
  | grep -vE "$DATA_SVCS" \
  | xargs -r -I{} kubectl -n lilt scale {} --replicas=0

kubectl -n lilt get rollout -o name 2>/dev/null | xargs -r -I{} \
  kubectl -n lilt patch {} --type=merge -p '{"spec":{"replicas":0}}'

kubectl -n lilt get cronjob -o name 2>/dev/null | xargs -r -I{} \
  kubectl -n lilt patch {} --type=merge -p '{"spec":{"suspend":true}}'

kubectl -n lilt get cronworkflow -o name 2>/dev/null | xargs -r -I{} \
  kubectl -n lilt patch {} --type=merge -p '{"spec":{"suspend":true}}'

Do not scale down every deployment in the namespace with a single command. Doing so stops the database, which the export reads from, and discards the replica counts needed to restore the environment.

Open a connection to the database

Forward the database service port to the staging host. Port 13306 is used locally to avoid conflicting with any MySQL instance already running there:

kubectl -n lilt port-forward svc/mysql 13306:3306 &
PF_PID=$!
sleep 2

mysql -h 127.0.0.1 -P 13306 -u root -p"$SRC_PASS" -N -e "SELECT 'connection ok', @@VERSION;"

Write the credentials to a protected file rather than passing them on the command line, so the password does not appear in the process list:

umask 077
cat > /tmp/src.cnf <<EOF
[client]
host=127.0.0.1
port=13306
user=root
password=$SRC_PASS
EOF

A default installation runs the in-cluster database without TLS, which is what the connection above assumes. If custom certificates have been applied to the database outside of the service mesh, add an explicit mode to the credentials file. REQUIRED encrypts the connection without validating the server certificate, which is normally what is wanted for an export running through a local tunnel:

[client]
host=127.0.0.1
port=13306
user=root
password=<source-password>
ssl-mode=REQUIRED

Certificate validation fails even with a valid certificate when connecting through a forwarded port, because the certificate is issued for the in-cluster service name and does not match 127.0.0.1. Use ssl-mode=VERIFY_CA with ssl-ca pointing at the issuing authority if validation is required, and connect by a name the certificate covers rather than by address:

ssl-mode=VERIFY_CA
ssl-ca=/path/to/ca.pem

If the source server does not accept encrypted connections at all, set ssl-mode=DISABLED instead. Do not carry any of these settings over to the RDS credentials file: RDS requires encrypted connections and presents certificates from a public authority, so it keeps ssl-mode=REQUIRED as shown later in this article. Port forwarding tunnels through the Kubernetes API server and can drop during a long transfer. If the connection proves unstable, capture the service definition, expose it as a NodePort for the duration of the migration, connect directly to a node, and restore the original definition afterwards:

kubectl -n lilt get svc mysql -o yaml > /tmp/svc-mysql-before.yaml
kubectl -n lilt patch svc mysql -p \
  '{"spec":{"type":"NodePort","ports":[{"name":"mysql","port":3306,"targetPort":"mysql","nodePort":32306}]}}'

# transfer, then restore
kubectl -n lilt apply -f /tmp/svc-mysql-before.yaml

Export to the staging host

The export runs on the staging host and writes only there. No dump file is created inside the database pod:

mysqldump --defaults-extra-file=/tmp/src.cnf \
  --databases $SOURCE_DB \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF --no-tablespaces --hex-blob \
  --default-character-set=utf8mb4 --max-allowed-packet=512M \
  --compression-algorithms=zlib \
| sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \
| gzip -1 > $STAGE_DIR/$SOURCE_DB.sql.gz

Each option addresses a specific requirement:

--defaults-extra-file must be the first argument on the command line, or it is ignored without warning.
--set-gtid-purged=OFF is required when the source has GTID mode enabled. Without it the dump contains a statement that sets a global GTID variable, which RDS rejects because the restoring user does not hold SUPER.
--no-tablespaces avoids a statement requiring the PROCESS privilege, which the restoring user does not hold.
--single-transaction produces a consistent snapshot without locking tables.
--databases includes the CREATE DATABASE and USE statements, making the dump self-contained.
The sed filter removes DEFINER clauses, which reference user accounts that do not exist on the new instance. Applying it in the same pipeline avoids writing the data to disk twice.

Validate the export

A truncated dump that appears to restore successfully is the worst outcome, so validate before restoring:

gunzip -t $STAGE_DIR/$SOURCE_DB.sql.gz && echo "archive intact"
gunzip -c $STAGE_DIR/$SOURCE_DB.sql.gz | tail -5 | grep -c "Dump completed"
gunzip -c $STAGE_DIR/$SOURCE_DB.sql.gz | grep -c "GTID_PURGED"
gunzip -c $STAGE_DIR/$SOURCE_DB.sql.gz | grep -c "DEFINER="
ls -lh $STAGE_DIR/$SOURCE_DB.sql.gz

The first count must be 1 and the other two must be 0. The Dump completed marker is written only when the export finishes cleanly, so its absence means the connection dropped partway and the export must be repeated.

Record the row-count baseline

Capture this while writes are still stopped. It is the reference for verification after the restore:

mysql --defaults-extra-file=/tmp/src.cnf -N -e "
SELECT table_name, table_rows FROM information_schema.tables
WHERE table_schema='$SOURCE_DB' ORDER BY table_name;" | sort > $STAGE_DIR/src-rows.txt
wc -l $STAGE_DIR/src-rows.txt

Close the connection

kill $PF_PID

Restore into RDS

If the staging host cannot reach the RDS endpoint, transfer the dump to a host that can before continuing. Create a credentials file for the application user and restore:

umask 077
cat > /tmp/rds.cnf <<EOF
[client]
host=$RDS_ENDPOINT
user=lilt
password=<app-password>
ssl-mode=REQUIRED
EOF

time gunzip -c $STAGE_DIR/$SOURCE_DB.sql.gz \
  | mysql --defaults-extra-file=/tmp/rds.cnf --max-allowed-packet=512M \
  2>&1 | tee /tmp/restore.log

RDS requires encrypted connections, hence ssl-mode=REQUIRED. Expect the restore to take considerably longer than the export, because it rebuilds every index. If the restore fails partway, do not retry over the partial result. Recreate the database with the correct collation and start again:

mysql --defaults-extra-file=/tmp/rds-admin.cnf -e "
DROP DATABASE IF EXISTS \`$SOURCE_DB\`;
CREATE DATABASE \`$SOURCE_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON \`$SOURCE_DB\`.* TO 'lilt'@'%';"

Reconcile the Schema Migration Metadata

The application records each applied schema migration by filename in a metadata table named SequelizeMeta, and matches them by exact filename. Some migrations are authored with a .ts extension and shipped in the application image compiled to .js. If a database recorded them under their .ts names, the installed application sees those migrations as never applied, attempts to run them again, and fails on objects that already exist — typically reported as a duplicate key or duplicate column error. Check whether the restored database is affected:

mysql --defaults-extra-file=/tmp/rds.cnf -D $SOURCE_DB -e "
SELECT SUM(name LIKE '%.ts') AS ts, SUM(name LIKE '%.js') AS js, COUNT(*) AS total
FROM \`SequelizeMeta\`;"

If ts is 0, no action is needed. Otherwise normalize the recorded names to the compiled extension. This affects only the metadata table — no schema and no application data are modified — and running it a second time has no effect:

CREATE TABLE IF NOT EXISTS `SequelizeMeta_backup` AS SELECT * FROM `SequelizeMeta`;

START TRANSACTION;

DELETE t FROM `SequelizeMeta` t
JOIN `SequelizeMeta` j
  ON j.name = CONCAT(SUBSTRING(t.name, 1, CHAR_LENGTH(t.name) - 3), '.js')
WHERE t.name LIKE '%.ts';

UPDATE `SequelizeMeta`
SET name = CONCAT(SUBSTRING(name, 1, CHAR_LENGTH(name) - 3), '.js')
WHERE name LIKE '%.ts';

SELECT SUM(name LIKE '%.ts') AS must_be_zero FROM `SequelizeMeta`;

COMMIT;

The DELETE handles the case where both extensions are recorded for the same migration, which would otherwise collide on the table’s primary key during the rename.

Take a Snapshot

Create a snapshot before any schema migration runs. This is the only inexpensive way to recover from a migration that applies partially:

aws rds create-db-snapshot \
  --db-instance-identifier <rds-instance-identifier> \
  --db-snapshot-identifier pre-migration-$(date +%Y%m%d%H%M)

Apply Schema Migrations

Install LILT on the new cluster as described in Install System (AWS EKS), with the database connection pointing at the RDS endpoint. During installation the application applies every schema migration recorded as unapplied, bringing the restored database up to the current schema. When migrating from an older release, expect a large number of migrations to run. This is normal and represents the accumulated schema changes between the two versions.

Confirm the migrations succeeded

Do not infer success from pod status. A migration failure does not necessarily cause the pod to report a failure, so the application can start against a partially migrated schema. Inspect the initialization container’s logs directly:

kubectl -n lilt get pods | grep front
kubectl -n lilt logs <front-pod> -c <init-container> | grep -iE "error|failed|duplicate"

Confirm the metadata table grew as expected. Record the count before installation and compare it afterwards; the difference is the number of migrations applied:

mysql --defaults-extra-file=/tmp/rds.cnf -D $SOURCE_DB -e "
SELECT COUNT(*) AS applied, SUM(name LIKE '%.ts') AS unreconciled
FROM \`SequelizeMeta\`;"

unreconciled must be 0. If it is not, the reconcile step was skipped or new entries were recorded under the wrong extension, and the next upgrade will attempt to reapply them. Installation is idempotent with respect to migrations: any that did not run are applied on the next install or upgrade. If the logs show a failure, resolve the cause and run the installer again rather than editing the metadata table by hand.

Rebuild the Search Index

The search index on the new cluster is empty, and restored content is not indexed automatically. The indexer tracks its position in a table named IndexerCursor, which holds a timestamp; only records modified after that timestamp are indexed. The restored row carries the position reached on the original cluster, and every restored record predates it, so without this step search returns no results for migrated content. Perform this after the schema migrations have completed. The cursor is read once when the indexer starts, so it must be paired with a restart. CURSOR is a reserved word in MySQL and must be quoted with backticks:

mysql --defaults-extra-file=/tmp/rds.cnf -D $SOURCE_DB -e \
  "SELECT id, \`cursor\` FROM \`IndexerCursor\`;"

mysql --defaults-extra-file=/tmp/rds.cnf -D $SOURCE_DB -e \
  "UPDATE \`IndexerCursor\` SET \`cursor\` = '1970-01-01 00:00:00' WHERE id = 1;"

mysql --defaults-extra-file=/tmp/rds.cnf -D $SOURCE_DB -e \
  "SELECT id, \`cursor\` FROM \`IndexerCursor\`;"

Restart the indexer so it reads the new position:

kubectl -n lilt rollout restart deploy/indexer-core
kubectl -n lilt logs deploy/indexer-core | grep -i "cursor"

The column is a DATETIME, so a date before 1970 is also accepted; the value above is simply a conventional starting point. The search indices themselves are created automatically when the indexer starts and require no manual step. Monitor progress by watching the index document counts grow:

kubectl -n lilt exec deploy/indexer-core -- \
  curl -s 'http://elasticsearch.lilt.svc.cluster.local:9200/_cat/indices?v'

Verify the Migration

Compare row counts

mysql --defaults-extra-file=/tmp/rds.cnf -N -e "
SELECT table_name, table_rows FROM information_schema.tables
WHERE table_schema='$SOURCE_DB' ORDER BY table_name;" | sort > /tmp/dst-rows.txt

diff $STAGE_DIR/src-rows.txt /tmp/dst-rows.txt

Row counts reported by information_schema are estimates for InnoDB tables, so use this comparison to identify substantial differences and confirm those with a direct COUNT(*) on the largest tables. Counts will legitimately differ for tables that the schema migrations altered.

Confirm the character set and collation

mysql --defaults-extra-file=/tmp/rds.cnf -e "
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata WHERE schema_name='$SOURCE_DB';"

Application checks

Perform these in order, because each one exercises a different part of the migration:

Sign in, which confirms the application can authenticate against the restored user records.
Confirm the jobs list is populated, which confirms general read access to restored data.
Open a previously existing document, which confirms the restored records resolve correctly.
Search for a phrase from restored content, which confirms the index was rebuilt.
Create a new job and run a translation, which confirms writes succeed against the new instance.

Post-Migration Configuration

Connection settings. The database host, port, user and password must point at the RDS endpoint. Confirm every location where the deployment configures them.
Application user. Confirm the application authenticates as the dedicated database user rather than the instance’s administrative user.
In-cluster database. Once the migration is accepted, the in-cluster MySQL deployment is no longer used. Leave it in place until then, since it is the fallback.
Encrypted connections. RDS requires TLS. Any component configured for an unencrypted in-cluster connection must be updated.

Cleanup

Restore the application to its previous replica counts using the file recorded before the export, then remove the credentials files and the staged dump:

rm -f /tmp/src.cnf /tmp/rds.cnf /tmp/rds-admin.cnf
rm -f $STAGE_DIR/$SOURCE_DB.sql.gz

Restore the database service definition if it was exposed as a NodePort. Retain the row-count baseline and the restore and migration logs as a record of the migration. Keep the in-cluster database volume intact until the migration has been accepted. No step in the export modifies it, so the original environment remains a complete fallback.

Debugging

Access denied when the application connects, while administrative access works: the application’s client cannot negotiate the instance’s default authentication plugin. Enable mysql_native_password in the parameter group and recreate the application user with that plugin. Restore fails on a stored routine or trigger: log_bin_trust_function_creators is not set to 1 in the parameter group. Restore fails on a statement setting a global GTID variable: the export was taken without --set-gtid-purged=OFF. Repeat the export with that option. Illegal mix of collations after an upgrade: the database default collation does not match the restored tables, because the database was created before the restore and its collation was not corrected. Compare the database default against a restored table’s collation and correct it, then recreate the affected tables. Duplicate key or duplicate column errors during schema migration: migration metadata records entries under a different file extension than the installed image uses. See “Reconcile the Schema Migration Metadata”. Search returns no results for migrated content: the index cursor was not reset, or the indexer was not restarted after resetting it. The export stops partway with a connection error: the port-forward tunnel dropped. The dump cannot be resumed; repeat the export, and use the NodePort approach if the tunnel proves unreliable.

​Overview

​Tools you will need

​Structure of this Article

​Installer privileges

​Migration Order

​Restore before installing LILT

​Migrate first, upgrade separately

​Assess the Source Database

​Set the user environment variables

​Read the database credentials

​Record the server configuration

​Measure the database

​Reclaim space on the storage volume

​Prepare the RDS Instance

​Engine version

​Parameter group

​Correct the database character set and collation

​Create the application database user

​Export the Database

​Stop application writes

​Open a connection to the database

​Export to the staging host

​Validate the export

​Record the row-count baseline

​Close the connection

​Restore into RDS

​Reconcile the Schema Migration Metadata

​Take a Snapshot

​Apply Schema Migrations

​Confirm the migrations succeeded

​Rebuild the Search Index

​Verify the Migration

​Compare row counts

​Confirm the character set and collation

​Application checks

​Post-Migration Configuration

​Cleanup

​Debugging