Database Migrations Without the Heart Attack

I still remember the silence in the Slack channel three years ago. We were migrating a 400GB Magento database from a legacy dedicated server in Frankfurt to a new cloud instance. We thought a simple dump and restore would take two hours. It took nine. The disk I/O on the target server was choked, the site was down during peak hours, and the client was—rightfully—furious.

Database migration is the single most stressful task in systems administration. It is open-heart surgery on a runner who is still sprinting. If you are reading this in 2021, the landscape has shifted. We aren't just worried about downtime anymore; we are worried about data sovereignty. With the Schrems II ruling shaking up how we handle data transfers outside the EEA, many of you are moving workloads back home to Norway or EU-based infrastructure.

This is not a theoretical guide. This is how you move data without losing your sanity, specifically tailored for the high-bandwidth, low-latency environment we enjoy here in the Nordics.

The I/O Bottleneck: Why Your Restore is Slow

The number one reason migrations fail or exceed their maintenance window is storage performance. You can have 64 CPU cores, but if your disk cannot handle the write operations (IOPS) of a massive import, you are dead in the water.

When you pipe a mysqldump into a new server, the database engine has to write pages to disk, update indexes, and verify constraints. This crushes standard SSDs and completely stalls mechanical HDDs.

Pro Tip: Always disable the Double Write Buffer in MySQL during the import phase to double your write throughput. Just remember to re-enable it immediately after for data safety.

This is where infrastructure choice dictates success. At CoolVDS, we standardized on NVMe storage for this exact reason. In our benchmarks, restoring a 50GB PostgreSQL dump on NVMe is approximately 4-6x faster than on standard SATA SSDs. If you are migrating to a provider that still uses shared mechanical storage, you are setting yourself up for a very long night.

Strategy 1: The Tunnel Replication (Minimal Downtime)

For datasets larger than 20GB, the "dump and restore" method involves too much downtime. The professional approach is Master-Slave Replication.

The concept is simple: you make the new server a slave (replica) of the old server. They sync up. When they are identical, you pause the application, promote the slave to master, and switch the connection string. Downtime is reduced from hours to seconds.

Step 1: Secure the Transport

Never replicate over the public internet without encryption. If you don't have a private VLAN between your providers, use an SSH tunnel. This creates a secure pipe between your legacy host and your new CoolVDS instance.

# Run this on your NEW server (CoolVDS instance)
# This forwards port 3307 on localhost to port 3306 on the old server
ssh -N -f -L 3307:127.0.0.1:3306 user@old-server-ip -i /root/.ssh/id_rsa_migration

Step 2: The Initial Sync

You need a snapshot of the master to start replication. For MySQL/MariaDB, XtraBackup (by Percona) is superior to mysqldump because it is non-blocking.

# On the OLD server
xtrabackup --backup --target-dir=/data/backups/ --datadir=/var/lib/mysql

# Prepare the backup (apply logs)
xtrabackup --prepare --target-dir=/data/backups/

Transfer this backup to the new server using rsync. We use the -a flag to preserve permissions and -z for compression, which is vital if you are migrating across data centers (e.g., AWS usually charges heavy egress fees).

rsync -avz --progress /data/backups/ user@new-server-ip:/data/backups/

Step 3: Configure the Replica

Once the data is restored on the new server, configure it to follow the old server via the SSH tunnel we created earlier.

-- Log into MySQL on the NEW server
CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_PORT=3307,
  MASTER_USER='replication_user',
  MASTER_PASSWORD='ComplexPassword2021!',
  MASTER_AUTO_POSITION=1;
START SLAVE;

Optimizing the Target: Configuration is Key

A fresh installation of MySQL or PostgreSQL usually comes with conservative defaults. Before you start your import or replication, you must tune the configuration file (`my.cnf` or `postgresql.conf`).

Here is a battle-tested configuration block for a MySQL 8.0 server with 16GB RAM running on CoolVDS NVMe infrastructure. Note the specific attention to log file sizes and buffer pools.

[mysqld]
# Allocating 70-80% of RAM to buffer pool is standard for dedicated DB servers
innodb_buffer_pool_size = 12G

# Vital for write-heavy migrations. 
# Larger log files mean fewer checkpoints and better disk I/O performance.
innodb_log_file_size = 2G

# If you have NVMe, you can increase I/O capacity significantly
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Ensure data integrity (ACID compliance)
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# Networking tweaks for latency
max_allowed_packet = 64M
skip-name-resolve

The Norwegian Context: Latency and Legality

Technically, you can host your database anywhere. Legally and practically, location matters.

Latency: If your application server is in Oslo but your database is in a data center in Frankfurt, you are adding 15-25ms of latency to every single query. For an application that does 50 queries to render a dashboard, that is over a second of wasted time waiting for light to travel through fiber. Hosting your database on a VPS Norway instance ensures your latency to local users and the NIX (Norwegian Internet Exchange) is negligible, often under 2ms.

Compliance: Since the Schrems II judgment in 2020, moving personal data of Norwegian citizens to US-controlled clouds has become a legal minefield. Datatilsynet (The Norwegian Data Protection Authority) has been clear about the risks. Migrating your database to a Norwegian provider like CoolVDS isn't just about speed; it's about simplifying your GDPR compliance posture. Your data stays here, under Norwegian jurisdiction.

The Final Cutover

When the replication lag is zero (`Seconds_Behind_Master: 0`), you are ready.

1. Put your web application into maintenance mode.
2. Stop the write traffic to the old database.
3. Verify the new database has caught up completely.
4. Promote the new database (Stop Slave, Reset Master).
5. Update your application config to point to the new CoolVDS IP.
6. Start the application.

If you have planned correctly, this window is less than 60 seconds.

Post-Migration Checklist

Database migration separates the amateurs from the pros. It requires understanding the full stack: from the physics of NVMe storage to the logic of TCP/IP tunneling. At CoolVDS, we build our infrastructure to handle the stress of these operations. Our ddos protection keeps the bad actors out while you focus on the data, and our pure NVMe backend ensures your rsync finishes before your coffee gets cold.

Don't risk your data on budget spindles. Architect for performance.