Zero-Downtime Database Migration: A Battle Plan for Norwegian Systems

I still remember the first time I broke a production database. It was 2012, and I naively thought a mysqldump on a 200GB table during peak hours was a reasonable backup strategy. The I/O lock froze the entire ecommerce platform for 45 minutes. The CEO was not amused.

Three years later, the stakes are higher. In the current Nordic hosting market, latency is the new currency. If you are moving data from an old dedicated server to a modern VPS, you cannot afford maintenance windows that last all night. Your users expect 99.99% availability, and search engines punish slow Time To First Byte (TTFB).

This is not a theoretical guide. This is the exact workflow I use to migrate heavy MySQL and PostgreSQL workloads between data centers—specifically focusing on keeping data inside Norway for compliance with the Personopplysningsloven.

The "Dump and Restore" Trap

For any database larger than 5GB, the traditional export/import method is dead. It is too slow and requires too much downtime. If you are still running:

mysqldump -u root -p --all-databases > dump.sql

...you are asking for trouble. The restore process is single-threaded and disk-bound. On standard spinning rust (HDD), this can take hours. Even on the SSD-backed instances we provision at CoolVDS, reconstructing indexes takes time you don't have.

The Solution: Live Replication

The only professional way to migrate a live database in 2015 is setting up a Master-Slave replication topology. You treat your new server as a replica, let it sync, and then cut over. Here is the battle-tested procedure.

Step 1: The Initial Snapshot

Instead of locking the database, use Percona XtraBackup. It creates a hot backup without locking your InnoDB tables. If you are on CentOS 7, install the repo and the tool. Then, stream the backup directly to your new CoolVDS instance using xbstream and SSH. This saves disk I/O on the source.

innobackupex --stream=xbstream /tmp | ssh user@new-server-ip "xbstream -x -C /var/lib/mysql/data/"

Step 2: Configure the Replica

Once the data is on the new host (let's assume a CoolVDS KVM instance in Oslo), you need to configure it as a slave. In your my.cnf, ensure you have unique server IDs.

Pro Tip: Do not ignore the network latency. Even within Norway, routing can be tricky. We optimize our peering at NIX (Norwegian Internet Exchange) to ensure that sync latency between domestic data centers stays under 5ms. If your provider routes traffic via Frankfurt just to get from Bergen to Oslo, your replication lag will kill you.

Step 3: The Switchover

When Seconds_Behind_Master hits 0, you are ready.
1. Set your application to "Read Only" mode (downtime: ~10 seconds).
2. Verify the slave has processed the last transaction.
3. Promote the CoolVDS slave to Master.
4. Point your application config to the new IP.

Optimizing for the New Hardware

You cannot just copy the old config file. Moving from legacy hardware to a modern virtualized environment requires tuning. CoolVDS instances run on pure SSD arrays (and we are testing early NVMe tech in select zones). You must tell the database engine it has fast disks available.

In MySQL 5.6, adjust these settings in /etc/my.cnf:

[mysqld]
# 70-80% of RAM for dedicated DB servers
innodb_buffer_pool_size = 4G 

# Crucial for SSD performance
innodb_io_capacity = 2000
innodb_flush_neighbors = 0

# Safety for transaction integrity
innodb_flush_log_at_trx_commit = 1

Setting innodb_flush_neighbors = 0 is critical for SSDs. Old spinning disks needed neighbor flushing to sequentialize writes. SSDs handle random writes effortlessly; turning this off reduces latency.

The "Noisy Neighbor" Reality

Why do migrations fail after the move? Usually, it is CPU Steal. In a cheap VPS environment (often OpenVZ), providers oversell CPU cores. You might think you have 4 cores, but when a neighbor runs a heavy PHP script, your database stalls.

This is why we strictly use KVM virtualization at CoolVDS. It provides a hard hardware abstraction. Your RAM is yours. Your CPU cycles are reserved. For a database, consistency is more important than raw burst speed. You need to know that a query taking 50ms now will not take 500ms tomorrow because someone else is compiling a kernel on the same host.

Data Sovereignty and Compliance

We are operating in a post-Snowden world. With the Safe Harbor framework looking increasingly shaky, Norwegian businesses are prioritizing local data residency. Keeping your database on Norwegian soil isn't just about latency; it's about adhering to the Datatilsynet guidelines. When you migrate, ensure your provider isn't silently replicating backups to a US-based cloud bucket.

Final Thoughts

A migration is the perfect time to audit your infrastructure. Don't just lift and shift the mess you made three years ago. clean it up, optimize your config for SSDs, and ensure your host respects your resource guarantees.

If you need a staging environment to test your replication lag, you can spin up a high-performance instance on CoolVDS in less than a minute. Don't let slow I/O be the bottleneck that brings your application down.