Zero-Downtime Database Migrations: A Survival Guide for Norwegian Systems
It’s 03:00 AM. You’re staring at a terminal window, watching a mysqldump restoration crawl at 4MB/s. The client expects the site back up at 06:00. The estimated completion time is 09:30. We have all been there. It is the specific nightmare that defines our career path.
Database migration is the single most dangerous operation in systems administration. Get it right, and nobody notices. Get it wrong, and you lose data, customers, and sleep. In the Norwegian market, where latency to the NIX (Norwegian Internet Exchange) and compliance with Personopplysningsloven (Personal Data Act) are non-negotiable, you cannot simply throw data onto a generic cloud instance and hope for the best.
I have spent the last decade fixing failed migrations. Here is how we do it properly, without the downtime and without the panic.
The "Dump and Pray" Method is Dead
If your strategy involves mysqldump > data.sql and mysql < data.sql, you are operating with a mindset from 2005. This works for a 50MB blog. It fails catastrophically for a 50GB e-commerce store.
The problem isn't just speed; it's consistency. During the dump, you lock tables. The site goes dark. If the transfer fails halfway, you start over. You need a stream, not a snapshot.
The Solution: Asynchronous Master-Slave Replication
The only robust way to migrate a live production database in 2015 is to turn your new server into a slave of the old one. This allows you to sync data in the background while the live site keeps serving requests. Once the slave catches up (0 seconds behind master), you simply switch the connection string.
Step 1: The Initial Sync (Without Locking)
Do not use mysqldump. Use Percona XtraBackup. It allows you to take a hot backup of your InnoDB tables without locking the database. Your users can keep buying while you pack up the data.
# On the source server (Master)
innobackupex --user=root --password=SECRET /var/backups/mysql/
innobackupex --apply-log /var/backups/mysql/2015-07-09_12-00-00/Transfer this directory to your new CoolVDS instance using rsync. It handles network interruptions better than SCP.
Step 2: Configuration Flags That Matter
Before starting the new server, you must optimize the my.cnf. Most VPS providers give you a default config that assumes you are running on a calculator. If you are on a CoolVDS instance with 8GB RAM, do not leave the defaults alone.
[mysqld]
server-id = 2
# Crucial for data integrity on crash
innodb_flush_log_at_trx_commit = 1
# Set to 70-80% of total RAM for dedicated DB servers
innodb_buffer_pool_size = 6G
# Keep binary logs for replication safety
log_bin = /var/log/mysql/mysql-bin.logHardware Bottlenecks: The IOPS Trap
You can have the best DBA skills in Oslo, but if your disk I/O is garbage, your migration will stall. This is where most generic hosting fails. They put you on shared spindle drives (HDDs) with "noisy neighbors." If another customer decides to compile a kernel, your database latency spikes.
In 2015, SSDs are becoming standard, but there is a tier above that: NVMe. The protocol reduces overhead significantly compared to SATA. When we built the CoolVDS platform, we mandated KVM virtualization on NVMe storage. Why?
| Feature | Standard SATA SSD | CoolVDS NVMe |
|---|---|---|
| Latency | ~150 microseconds | ~20 microseconds |
| Queue Depth | 32 commands | 64,000 commands |
| Throughput | ~550 MB/s | ~3,000 MB/s |
When you are replaying binary logs to catch up a slave database, high IOPS (Input/Output Operations Per Second) is the difference between catching up in minutes or lagging behind forever.
The Norwegian Context: Data Sovereignty
We are seeing increased scrutiny from Datatilsynet regarding where data physically lives. With the ongoing discussions around the EU Data Protection Directive (and the Safe Harbor framework looking shaky), keeping your customer data on Norwegian soil is not just about patriotism; it is risk mitigation.
Latency is also a feature. If your primary customer base is in Norway, hosting in a datacenter in Texas or even Frankfurt introduces unavoidable physics. A ping from Oslo to Frankfurt is ~20-30ms. From Oslo to a local datacenter? <5ms. For a database performing 50 sequential queries to render a page, that difference aggregates to a full second of load time.
Pro Tip: Check your `resolv.conf`. Ensure your DNS lookups aren't adding latency to your database connections. Use `skip-name-resolve` in MySQL to force IP-based connections and shave off precious milliseconds.
Executing the Switch
Once your CoolVDS slave is running and `Seconds_Behind_Master` is 0:
- Lower the TTL on your DNS records to 300 seconds.
- Put the application in "Maintenance Mode" (Show a polite HTML page).
- Stop writes to the old Master.
- Verify the Slave has executed the final transaction.
- Promote the CoolVDS Slave to Master.
- Point your application config to the new IP.
Total downtime? Less than 60 seconds. No data loss. No corruption.
Migration doesn't have to be a gamble. It requires the right strategy, valid backups, and hardware that doesn't choke under load. Don't let slow I/O kill your SEO or your sanity.
Ready to test real performance? Spin up a KVM-based NVMe instance on CoolVDS today and benchmark it yourself.