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

It is 3:00 AM on a Sunday. You are staring at a terminal window, watching a cursor blink. The mysqldump import is taking three hours longer than predicted. Your maintenance window closed ten minutes ago. The CEO is texting you.

If you have been in operations long enough, you know this feeling. It is the smell of poor planning and slower disks.

With the General Data Protection Regulation (GDPR) officially enforceable as of last week (May 25, 2018), the stakes for data integrity and sovereignty here in Norway have never been higher. You cannot just throw a tarball onto an unencrypted FTP server anymore. You need a strategy that respects data privacy, minimizes downtime, and—crucially—doesn't rely on hope as a tactic.

I have migrated terabytes of data across the Norwegian Internet Exchange (NIX). Here is how you do it without melting your servers or your reputation.

The Myth of the "Maintenance Window"

The concept of a "maintenance window" is dying. Users expect 24/7 availability. If you are running an e-commerce shop on Magento 2 or a heavy SaaS backend, taking the site down for 6 hours to move from a legacy dedicated server to a modern VPS is unacceptable.

The only professional way to migrate a live database in 2018 is Replication-Based Migration.

This method involves setting up your new server as a slave (replica) of your current production master, letting it catch up, and then promoting it. It reduces downtime from hours to seconds.

Phase 1: The Infrastructure Audit

Before touching a config file, look at your hardware. Database performance is I/O bound. If you are migrating 50GB of InnoDB tables to a host using spinning rust (HDD) or shared SATA SSDs, you are going to hit high iowait times during the catch-up phase.

Pro Tip: Don't guess your I/O performance. Test it. On CoolVDS, we standardize on NVMe storage because the random Read/Write speeds are necessary for high-concurrency transaction logs (binlogs). A standard SSD often bottlenecks at 5,000 IOPS; NVMe pushes past 300,000.

Check Your Latency

If you are moving data from a legacy provider in Germany to a new instance in Oslo, network latency matters. Use mtr to verify the path stability.

Phase 2: The Initial Sync (MySQL 5.7 Focus)

For small databases (< 5GB), mysqldump is acceptable. For anything serious, it locks tables and ruins performance. We will use Percona XtraBackup. It allows for hot backups without locking your database.

First, install Percona tools on your source server (Ubuntu 16.04/18.04):

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
apt-get update
apt-get install percona-xtrabackup-24

Now, create the stream. We are going to stream the backup directly to the new CoolVDS instance over SSH. This avoids writing a massive file to the local disk (saving space) and encrypts the transfer (GDPR compliance).

innobackupex --stream=xbstream /var/lib/mysql | \
ssh user@new-coolvds-ip "xbstream -x -C /var/lib/mysql_temp/"

This pipe is efficient. However, ensure your new CoolVDS instance has the appropriate my.cnf tuning before you start the database. A common mistake is migrating to a machine with more RAM but leaving the innodb_buffer_pool_size at the default 128MB.

Optimizing `my.cnf` for the Target

On your new server, adjust /etc/mysql/my.cnf. If you have a 16GB RAM instance, allocate roughly 70-80% to the buffer pool.

[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 (Crucial for Performance)
innodb_buffer_pool_size = 12G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1 # ACID compliance
innodb_flush_method = O_DIRECT

# Replication Settings
server-id       = 2 # Must be different from Master
log_bin         = /var/log/mysql/mysql-bin.log
binlog_format   = ROW
gtid_mode       = ON
enforce_gtid_consistency = ON

Phase 3: The Catch-Up

Once the snapshot is restored on the new server, you need to configure replication to fetch the data that changed while the backup was transferring. XtraBackup creates a file called xtrabackup_binlog_info which contains the precise coordinates.

On the new server (Slave):

CHANGE MASTER TO
  MASTER_HOST='old-server-ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='SecurePassword123!',
  MASTER_LOG_FILE='mysql-bin.000452',
  MASTER_LOG_POS=982341;

START SLAVE;

Monitor the status. You want to see Seconds_Behind_Master drop to 0.

watch -n 1 "mysql -e 'SHOW SLAVE STATUS\G' | grep Seconds_Behind_Master"

Phase 4: The Switchover (Cut-Over)

This is the moment of truth. You have two servers in sync. The new CoolVDS instance is handling the write load replication perfectly.

1. Stop the App: Put your web application into maintenance mode. This stops new writes.
2. Verify Sync: Ensure the slave has processed all events.
3. Promote Slave: Run STOP SLAVE; RESET MASTER; on the new server. It is now independent.
4. Point DNS/Config: Update your application's `config.php` or `settings.py` to point to the new IP.
5. Start the App: Total downtime? Usually under 60 seconds.

Security & Compliance (The Norwegian Context)

Since we are dealing with Datatilsynet requirements, simply moving data isn't enough. You must ensure the data at rest is secure.

If you are using LUKS encryption on your partitions, ensure your keys are managed separately. Furthermore, avoid US-based cloud giants if you are worried about the CLOUD Act complications. Hosting on local Nordic infrastructure, like CoolVDS, simplifies the legal argument regarding data sovereignty.

Why KVM and NVMe Matter Here

I mentioned "noisy neighbors" earlier. In the shared hosting days, a database migration could fail because another user on the node decided to compile a kernel.

At CoolVDS, we use KVM (Kernel-based Virtual Machine). Unlike OpenVZ containers, KVM provides hardware virtualization. Your RAM is your RAM. Your CPU cycles are reserved. Combined with NVMe storage, this means your database import won't stall due to disk latency.

Final Thoughts

Migration is surgery. You don't perform surgery with a dull knife. Use Percona XtraBackup, use proper replication GTIDs, and host on infrastructure that respects I/O requirements.

Do not let slow I/O kill your SEO rankings or your patience. If you need a test environment to practice this replication topology, deploy a high-performance instance on CoolVDS. It takes about 55 seconds to spin up.