Zero-Downtime Database Migration: A Survival Guide for Norwegian DevOps

There are two types of sysadmins: those who check their backups, and those who haven't lost data yet. But database migration? That’s a different beast entirely. It is controlled chaos. You are essentially performing open-heart surgery on a runner while they are sprinting a marathon.

I recently oversaw a migration for a large logistics firm moving from bare metal in Frankfurt to a localized Norwegian setup. The challenge wasn't just the terabytes of data; it was the requirement for zero downtime. In the e-commerce and logistics sectors, 10 minutes of downtime is not an inconvenience; it's a measurable financial loss.

If you are still relying on mysqldump > scp > mysql for databases larger than 5GB, stop. You are locking tables and risking timeouts. In 2018, with the advent of accessible NVMe storage and mature replication tools, we have better ways to handle this. Here is the battle-tested architecture for migrating mission-critical data to high-performance infrastructure like CoolVDS, without waking up to a pager duty alert at 3 AM.

The Infrastructure Reality Check

Before you even touch a config file, look at the hardware. A database import is an I/O punisher. If your destination server is running on spinning rust (HDD) or shared SATA SSDs with noisy neighbors, your import could take days, causing replication lag to spiral out of control.

We strictly utilize KVM virtualization at CoolVDS for this reason. Unlike OpenVZ, where resources can be oversold, KVM guarantees that when we allocate 4 vCPUs and NVMe storage, you get that raw throughput. This is non-negotiable for the restore phase.

1. The Strategy: "Sync and Switch"

The only viable strategy for a live production environment is setting up a Master-Slave replication topology (or Master-Replica, as the terminology is shifting). You create a snapshot, move it, restore it, and let the new server catch up to the old one via the binary log.

Step 1: The Non-Blocking Snapshot

Do not use mysqldump. It is logical, slow, and expensive to restore. Use Percona XtraBackup. It performs a physical backup of InnoDB tables without locking them.

Here is how you grab the stream on the source server (Production):

This command creates a consistent snapshot while the database remains online. The --parallel=4 flag is crucial—it utilizes multiple threads to speed up the process, provided your CPU can handle the load.

Step 2: Secure Transport (GDPR Compliance)

Since May 25th of this year (2018), GDPR has changed the game. If you are moving customer data involving Norwegian citizens, you must ensure encryption in transit. Moving data via plain FTP or unencrypted netcat is negligent.

Use rsync over SSH. It’s standard, it’s resume-able (vital if the connection drops), and it’s encrypted.

rsync -avzP -e "ssh -p 22" /data/backups/ user@target-server-ip:/data/backups/

Pro Tip: If you are migrating across the NIX (Norwegian Internet Exchange) or within the Nordics, latency is generally low. However, enabling compression (-z) on rsync is only beneficial if your CPU speed is greater than your bandwidth bottleneck. On 10Gbps links between high-end CoolVDS instances, you might actually slow down transfer by compressing. Test without -z if you have a fat pipe.

Step 3: The Restoration & Optimization

On the destination CoolVDS server, you prepare the backup and restore it. This is where IOPS (Input/Output Operations Per Second) matter. A standard HDD delivers ~100 IOPS. Our NVMe arrays deliver tens of thousands. This is the difference between a 4-hour restore and a 15-minute restore.

Once restored, fix permissions:

Tuning `my.cnf` for 2018 Hardware

Don't just copy the old my.cnf. Your new environment likely has more RAM. Adjust the innodb_buffer_pool_size. A good rule of thumb is 70-80% of total RAM for a dedicated DB server.

[mysqld]
# Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql

# InnoDB Tuning for NVMe
innodb_buffer_pool_size = 12G  # Assuming 16GB RAM Instance
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1 # ACID compliance is mandatory
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000      # Increase this for NVMe!
innodb_io_capacity_max = 4000

Step 4: Replication Catch-up

Once the data is restored, the new server is outdated by the amount of time the transfer took. We need to sync the delta.

Check the xtrabackup_binlog_info file in your backup directory. It contains the binary log coordinates exactly where the snapshot finished.

CHANGE MASTER TO 
MASTER_HOST='10.0.0.5', 
MASTER_USER='repl_user', 
MASTER_PASSWORD='SecurePassword123!', 
MASTER_LOG_FILE='mysql-bin.000452', 
MASTER_LOG_POS=9852310;

Start the slave:

Watch Seconds_Behind_Master drop to 0. Once it hits zero, your databases are identical. You can now switch your application's connection string to the new CoolVDS IP address with virtually zero downtime.

The Legal & Latency Angle

Why bother moving to a Norwegian host? Aside from the obvious latency benefits—pinging Oslo from Oslo takes <2ms compared to ~35ms from Amsterdam—there is the Datatilsynet factor.

With GDPR in full effect, data sovereignty is a massive discussion point in boardrooms across Oslo. While the Privacy Shield currently allows data transfer to the US, many pragmatic CTOs I speak with are moving workloads back to Europe, and specifically Norway, to mitigate future legal risks. Storing your database on CoolVDS ensures your data rests physically within the jurisdiction you operate in.

Conclusion

Migration doesn't have to be a gamble. By using physical backups, secure tunnels, and leveraging high-performance NVMe storage, you mitigate the risks of corruption and timeout.

Don't let legacy hardware bottleneck your growth. If you are planning a migration, spin up a high-performance instance on CoolVDS today and test your restore times. You will likely find that our NVMe performance pays for itself in engineering hours saved.