The Anatomy of a Panic-Free Database Migration

There is no keystroke in the life of a sysadmin more terrifying than hitting Enter on a command that stops a production database. It doesn't matter how many backups you have; that split second of silence is deafening. If you are reading this, you are likely planning to move a critical workload—perhaps a Magento store or a SaaS backend—away from legacy spinning rust or a cloud provider that just hiked their prices. In late 2020, the stakes are higher. With the recent Schrems II ruling by the CJEU invalidating the Privacy Shield, moving data isn't just a technical challenge; it's a legal minefield. If you handle Norwegian user data, you need to know exactly where those bytes are landing.

I've managed migrations where a simple mysqldump took 14 hours because the target disk IOPS were capped. Never again. This guide covers how to execute a migration with minimal downtime, focusing on the realities of the Nordic infrastructure landscape.

1. The Hardware Reality Check: NVMe or Bust

Before touching a config file, look at your target infrastructure. Database writes are I/O heavy. If you are migrating to a standard VPS with shared SATA storage, you have already failed. In 2020, NVMe storage is the baseline requirement for databases larger than 5GB.

Why? Latency. During an import or replication catch-up, your disk queue length will skyrocket. On CoolVDS KVM instances, we expose the underlying NVMe throughput directly to the guest OS. This prevents the dreaded "iowait" lockup that happens when cloud providers throttle your IOPS after a burst credit exhaustion.

Pro Tip: Before scheduling the migration, run a quick fio test on the new server. If random write IOPS are under 10k, abort.

2. The Strategy: Replication, Not Dumps

For any database over 10GB, a dump-and-restore strategy requires too much maintenance window time. The only professional path is Master-Slave Replication. You set up the new server as a slave, let it sync, and then promote it to master.

Scenario: Migrating MySQL 8.0

Let's assume you are moving from a legacy provider in Frankfurt to a CoolVDS instance in Oslo to lower latency for Norwegian customers. First, configure the target (slave) server for optimal write speeds during the initial sync. We will relax ACID compliance temporarily to speed up the import.

Edit /etc/mysql/my.cnf on the new server:

[mysqld]
# TEMPORARY SETTINGS FOR IMPORT SPEED
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_doublewrite = 0

# STANDARD PERFORMANCE SETTINGS
innodb_buffer_pool_size = 4G # Adjust to 70% of RAM
innodb_log_file_size = 1G
innodb_io_capacity = 2000 # Only set this high on NVMe

Do not forget to revert the temporary settings after the sync is complete, or a power loss will corrupt your data.

3. Data Synchronization

Instead of locking the master tables for hours, we use Percona XtraBackup. It allows us to take a hot backup without locking the database (for InnoDB tables). If you are strictly using standard tools, you can use mysqldump with the --single-transaction flag, but XtraBackup is faster for physical copying.

Step A: Create the Snapshot (Source Server)

xtrabackup --backup --target-dir=/data/backups/ --user=root --password=YOURPASS

Step B: Transfer to CoolVDS (Target)

We use rsync here. It's robust and resumable. Since we are moving data across borders (Germany to Norway), encryption is mandatory.

rsync -avzP -e ssh /data/backups/ user@new-server-ip:/data/backups/

Step C: Prepare and Restore (Target)

xtrabackup --prepare --target-dir=/data/backups/
cp -r /data/backups/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql

4. Configuring Replication

Once the data is restored, you need the binary log coordinates from the backup file (check xtrabackup_binlog_info). Configure the new server to follow the old one.

CHANGE MASTER TO
  MASTER_HOST='old-server-ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='secure_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123456;

START SLAVE;

Monitor the status with SHOW SLAVE STATUS \G. Watch the Seconds_Behind_Master value. On our network, latency between major European hubs and Oslo is minimal, so this should drop to 0 quickly.

5. The PostgreSQL Approach (pg_basebackup)

If you are running PostgreSQL (current stable 13.0 or 12.4), the process is even smoother using replication slots.

On the Source (pg_hba.conf):

host    replication     replicator      new_server_ip/32        md5

On the Target (CoolVDS):

pg_basebackup -h old_server_ip -D /var/lib/postgresql/12/main -U replicator -P -v -R --slot=migration_slot -C

The -R flag automatically writes the standby.signal and connection info. The -C flag creates the replication slot on the source, ensuring the master holds onto WAL files until the slave receives them. This is crucial if your network connection drops.

6. The Cutover: Handling DNS and TTL

Technical synchronization is only half the battle. Traffic redirection is the other.

1. Lower TTL: 24 hours before migration, lower your DNS TTL to 300 seconds (5 minutes).
2. Maintenance Mode: When Seconds_Behind_Master is 0, put your application in maintenance mode. This stops new writes.
3. Stop Slave: Run STOP SLAVE; on the new server.
4. Promote: Reset the slave configuration (RESET MASTER) to make it a standalone read/write server.
5. Switch DNS: Update the A record to the new CoolVDS IP.

Why Location and Compliance Matter in 2020

Let's address the elephant in the server room: Schrems II. Since July, relying on US-owned cloud providers for hosting European personal data has become legally risky. The Privacy Shield is no longer a valid transfer mechanism.

By migrating your database to a Norwegian provider like CoolVDS, you are physically locating your data within the EEA/EFTA framework, adding a layer of sovereignty that abstract cloud regions cannot match. Furthermore, Datatilsynet (The Norwegian Data Protection Authority) is notoriously strict; having your infrastructure aligned with local standards is a competitive advantage.

Performance Metrics

Final Thoughts

Database migrations expose every weakness in your stack. Poor network routing becomes connection timeouts. Slow disks become replication lag. By using robust replication tools like XtraBackup or pg_basebackup, and hosting on hardware that doesn't choke on I/O, you turn a potential disaster into a routine maintenance task.

Your data is the lifeblood of your business. Don't strangle it with slow storage. If you are ready to test these speeds yourself, spin up a CoolVDS instance. We don't oversell our cores, and our NVMe arrays are ready for your heavy writes.