Move a MySQL app, uploads and keys to a new VPS

Moving an application is not just copying a database dump. A record may refer to an uploaded file, an encrypted value may require an existing key, and a scheduled worker may keep changing data after the website is put into maintenance mode.

This command sheet generalises the separate-app migration workflow used in my project notes. It assumes a small MySQL 8.x application, an agreed maintenance window and a rehearsed restore. For large or high-availability databases, use an experienced operator and a migration design suited to replication and acceptable downtime.

Inventory before stopping anything

Write down the source/destination MySQL versions, schema names, character sets, collations, storage engines, data volume, upload directory and maximum packet requirements. List every writer: API, background jobs, scheduled tasks, imports and MySQL events. Include views, routines, triggers and their DEFINER accounts.

SELECT VERSION(), @@max_allowed_packet;
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'appdb'
ORDER BY TABLE_NAME;
SHOW EVENTS FROM appdb;

This is metadata inspection, not proof of backup completeness. Check compatibility before moving between MySQL versions; do not assume a newer dump can be restored into an older server. If SQL objects reference the original schema explicitly, a simple rename is insufficient.

Keep the installed release and dependency lockfile with the migration record. Changing application version, database engine and host simultaneously makes failures harder to isolate.

Prepare private credentials and an empty target

Use a migration account with only the privileges required for the export/import objects. Runtime accounts should be separate. MySQL privileges for routines, views and events are more extensive than ordinary CRUD permissions; confirm the mysqldump requirements for your version rather than repeatedly granting global privileges until an error disappears.

Configure a login path under the same operating-system account that will run the client:

mysql_config_editor set --login-path=source-migration --host=127.0.0.1 --user=migration_reader --password
mysql --login-path=source-migration --execute='SELECT VERSION();'

The password is entered at a prompt, not included in the command. The login-path file is obfuscated, not a high-security vault; protect the account and file permissions. See the MySQL configuration-editor documentation. Running the dump with sudo changes which account's login path is used.

Have an administrator create an empty destination schema with a compatible collation. Provision its narrowly scoped application account separately. Never restore over a live schema just because the names match.

Quiesce every writer, then export

Put the application into a maintenance mode that actually rejects writes. Drain requests, pause named workers and timers, and handle database events that write data. Stop only the intended application's process. Choose the supervisor you really use:

sudo systemctl stop demo-api.service
sudo systemctl is-active demo-api.service

An inactive result is expected. For a PM2 app, run the equivalent named stop as its PM2 owner; do not use a broad stop-all command. Neither supervisor command alone proves there are no other writers.

Create a new private backup directory:

umask 077
migration_dir=$(mktemp -d /var/tmp/app-migration.XXXXXX)
mysqldump --login-path=source-migration --single-transaction --quick --hex-blob --no-tablespaces --set-gtid-purged=OFF --routines --triggers --events appdb > "$migration_dir/appdb.sql"
dump_status=$?
test "$dump_status" -eq 0 && test -s "$migration_dir/appdb.sql"

If the final check fails, do not continue with that dump. Inspect the error privately and repeat into a new backup. A nonempty file alone does not prove success.

This command deliberately omits --databases so it does not add the usual CREATE DATABASE/USE wrapper. Review routines, events and schema-qualified references before importing under another name. --single-transaction provides an InnoDB transaction snapshot, not transactional consistency for every storage engine, and concurrent DDL can invalidate assumptions. Keep all writers and migrations paused during this small-app procedure.

Include uploads and encryption keys

Archive the private upload directory while it is stable. Verify the real path and ensure you have permission to read all required files. A generic example is:

tar -czf "$migration_dir/uploads.tar.gz" -C /srv/demo/shared uploads
cd "$migration_dir"
sha256sum appdb.sql uploads.tar.gz > SHA256SUMS

Do not place the dump, archive or key material in a public web directory. Transfer through authenticated SSH into a restricted destination directory. Verify the destination host key first and compare checksums there.

Move required encryption keys through your approved secret-management process, preserving their exact values and key identifiers. Generating replacement keys is not a substitute: it can make existing ciphertext unreadable. Transfer related encrypted tables and attachment files together. Decide separately whether session-signing secrets should remain stable or intentionally invalidate sessions.

Do not paste credentials or keys into tutorial downloads, chat logs or a release manifest. A manifest should describe which secrets must exist, not contain their values.

Restore once, into the verified empty schema

On the destination, check transfer checksums and verify the selected database before importing. Configure a separate destination login path first.

sha256sum -c SHA256SUMS
mysql --login-path=destination-migration appdb_new --execute='SELECT DATABASE(); SHOW TABLES;'
mysql --login-path=destination-migration appdb_new < appdb.sql

Proceed only when the database is the intended empty target and the preceding command succeeds. Do not add --force to ignore SQL errors. Review the restore's exit status and object definitions, especially DEFINER accounts and event activation. Keep destination writers paused during verification.

Restore the upload archive into an inspected destination directory; do not blindly unpack an untrusted archive as root. Assign ownership to the destination runtime account rather than assuming numeric user IDs match across servers. Set private configuration to the new schema and credentials, and confirm the application reads that file rather than a stale release copy.

Verify data and make a one-writer cutover

Compare table inventories, important exact row counts and known records. information_schema TABLE_ROWS is approximate for InnoDB: it is not an exact reconciliation. Check routine/trigger/event presence and inspect any schema-qualified references.

Start the destination privately. With synthetic accounts, test sign-in, an old record, decryption, an existing attachment and one new create/update/delete cycle. A successful database ping cannot replace these tests. Use an HTTPS test route or the curl routing checks before moving the public domain.

During cutover, keep the old application unable to write, even while DNS caches still point at it. Do not allow two independent writable data copies. After routing is changed, check both IPv4/IPv6, application logs and job behaviour. Resume each intended worker deliberately.

Rollback is a data decision

Before any destination writes, reverting traffic to the unchanged source can be straightforward. After new writes, switching back can lose data. Stop destination writes, preserve its state and decide how to reconcile the changes before reactivating the source.

Retain restricted source/destination backups according to your retention plan and verify access to the key backups. A cloud snapshot can assist recovery, but is not automatically an application-consistent backup.

These commands were syntax-reviewed as a migration procedure; this article does not claim a live production database was migrated to test it. Rehearse the complete workflow on a disposable copy first. Continue with staged application rollback or return to the VPS learning path.