Zero-Downtime Database Migration: A Battle-Hardened Guide for Norwegian Systems

I still wake up in a cold sweat thinking about the "Black Friday Migration of 2012." We were moving a 400GB Magento database from a sluggish dedicated server in Germany to a new facility in Oslo. The strategy? A simple mysqldump. The result? A locked table for 14 hours, a timed-out restore process, and a furious CEO screaming about lost revenue.

Never again.

If you are serious about hosting infrastructure, you know that mysqldump is a toy for datasets larger than 5GB. When you need to move critical data across the North Sea—or even just across the racks at the NIX (Norwegian Internet Exchange)—you need block-level consistency and raw speed. You need to understand the mechanics of I/O wait.

This guide is for the sysadmins who are tired of watching a blinking cursor. We are going to cover how to migrate a live MySQL 5.6 (or MariaDB 10) database from a legacy host to a high-performance KVM instance on CoolVDS with minimal downtime.

The Bottleneck is Always Disk I/O

Before we touch a single command, let’s talk hardware. The number one reason migrations fail or stall is IOPS (Input/Output Operations Per Second). When you run an import, your disk is thrashing. On legacy SATA drives, or even standard SSDs provided by budget hosts, you hit a ceiling.

In 2014, the standard for high-performance databases is shifting toward PCIe SSD storage. We rely on this architecture at CoolVDS because it eliminates the "noisy neighbor" effect found in OpenVZ containers. When you have dedicated PCIe throughput, your restore time isn't dependent on another user compressing log files on the same physical node.

Strategy: The Streaming Physical Backup

Forget logical backups (SQL text files). We are going to use Percona XtraBackup. It performs a hot, non-locking backup of InnoDB tables. We will stream this backup directly over SSH to the new CoolVDS server. This avoids writing a massive file to the disk of the old server (which is likely already full, because why else would you be migrating?).

Step 1: The Pre-Flight Check

On your Source Server (the old one) and Target Server (CoolVDS), ensure you have the Percona repository installed. Using CentOS 6:

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
yum install percona-xtrabackup-2.2 qpress -y

Pro Tip: Always run your migration inside a screen or tmux session. If your home internet drops in the middle of a 4-hour transfer, the pipe breaks. Protect your session.

Step 2: The SSH Stream (The "Magic" Command)

Instead of creating a file, we pipe the output of innobackupex into gzip, then into ssh, and finally extract it on the fly on the Target Server. This is efficiency.

Run this command from the Source Server:

innobackupex --stream=tar ./ | gzip - | ssh user@target-coolvds-ip "cat - | gunzip - | tar -xvf - -C /var/lib/mysql_import/"

The "Norwegian" Factor: While bandwidth in Norway is generally excellent, latency can fluctuate if you are routing through congested exchanges. If you are migrating from the US or Central Europe to Oslo, add the --compress flag to XtraBackup instead of standard gzip to reduce CPU overhead on the encryption layer.

Step 3: Preparing the Data

Once the data lands on the CoolVDS instance, it's not ready yet. InnoDB relies on transaction logs that might have been active during the backup. We must apply the logs to make the data consistent.

On the Target Server:

innobackupex --apply-log /var/lib/mysql_import/

Watch for the output: completed OK!. If you don't see that, do not proceed.

Optimizing the Target for Import

Here is where amateur admins fail. They start the database with default settings. When bringing up a fresh instance on CoolVDS, you have access to superior hardware resources. Configure your /etc/my.cnf to utilize them before you start the service.

Here is a battle-tested configuration for a server with 16GB RAM running purely MySQL:

[mysqld]
# Allocate 70-80% of RAM to the pool
innodb_buffer_pool_size = 12G

# Essential for write-heavy workloads
innodb_log_file_size = 512M

# If you are on CoolVDS PCIe SSDs, increase this. Default is too low.
innodb_io_capacity = 2000
innodb_flush_neighbors = 0

# Norway uses UTF-8. Don't get stuck with latin1.
character-set-server = utf8
collation-server = utf8_general_ci

The Final Switch: Minimizing Downtime

You have the data. It's consistent. Now, the cutover.

1. Source: Set the application to "Maintenance Mode" or Read-Only.
2. Source: Run a final incremental sync if the dataset is massive (using rsync for the changes, though XtraBackup incrementals are safer).
3. Target: chown -R mysql:mysql /var/lib/mysql_import
4. Target: Swap the data directories and start the service.

service mysql stop
mv /var/lib/mysql /var/lib/mysql_bak
mv /var/lib/mysql_import /var/lib/mysql
service mysql start

Data Sovereignty and the Personopplysningsloven

A quick note on compliance. We are seeing stricter enforcement from Datatilsynet regarding where personal data is stored. Following the Snowden revelations last year, many Norwegian CTOs are rightfully paranoid about US-based clouds.

Migrating to a provider physically located in Norway, like CoolVDS, simplifies your adherence to the Personal Data Act (Personopplysningsloven). You know exactly where the physical disks are spinning. There is no "cloud ambiguity."

Why KVM Matters for Databases

I mentioned earlier that we use KVM (Kernel-based Virtual Machine) at CoolVDS. This is deliberate. In container-based virtualization (like Virtuozzo or OpenVZ), the kernel is shared. This means the kernel's process scheduler decides if your database gets CPU time or if the neighbor's spam bot gets it.

With KVM, you have a dedicated kernel. Your innodb_buffer_pool is yours. Your CPU cycles are yours. For database workloads, this isolation is not a luxury; it is a requirement.

Don't let a slow migration script be the reason you lose a client. Use the right tools, pipe over SSH, and ensure your target hardware can handle the I/O write intensity.

Ready to test your I/O throughput? Deploy a high-performance KVM instance in Oslo on CoolVDS today and see the difference PCIe storage makes.