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

There is nothing quite like the cold sweat of a database migration going sideways at 03:00 CET. I've been there. You run a mysqldump on a 50GB dataset, transfer it over a shaky FTP connection, and pray the restore finishes before the CEO wakes up. If you are still doing "Dump and Restore" for production workloads in 2015, you are playing Russian Roulette with your uptime.

It is time to grow up. The era of the "Under Maintenance" page is ending. Users expect 24/7 availability, whether they are accessing a Magento store from Oslo or a SaaS platform in Trondheim.

In this guide, I'm going to walk you through how we handle high-stakes migrations. We aren't just moving files; we are moving state. And we are going to do it with almost zero downtime, leveraging the raw I/O power of KVM-based virtualization.

The Architecture of a Live Migration

The strategy is simple in theory but strict in execution. We don't shut down the old server to move to the new one. We make the new server a replica of the old one. Once they are in sync, we flip the switch.

Step 1: The Initial Sync (Without Locking)

If you use mysqldump without the right flags, you lock your tables. Your site hangs. The phone rings. Don't do that. For MySQL (or Percona/MariaDB), XtraBackup is the industry standard for hot backups. But if you must use standard tools, use the --single-transaction flag for InnoDB tables.

mysqldump -u root -p --opt --single-transaction --master-data=2 --default-character-set=utf8 database_name | gzip > /tmp/db_dump.sql.gz

The --master-data=2 flag is critical. It writes the binary log coordinates to the dump file, so you know exactly where to start replication on the new CoolVDS instance.

Step 2: Transferring the Asset

Bandwidth matters, but latency kills. Moving data across the Atlantic is painful. If your target audience is in Norway, your server should be in Norway. Hosting in Oslo reduces latency to the Norwegian Internet Exchange (NIX) to under 5ms.

Use rsync for the transfer. It’s resume-able and efficient.

rsync -avz --progress -e "ssh -p 22" /tmp/db_dump.sql.gz user@192.0.2.10:/tmp/

Pro Tip: If you are migrating to a CoolVDS instance, enable the private network interface if you are moving between our nodes. Unmetered gigabit transfers save you hours on terabyte-scale datasets.

Configuring the Beast: Optimization is Not Optional

You cannot just dump data into a default MySQL 5.6 installation and expect performance. Most default configs are tuned for 512MB RAM virtual machines from 2010. On a modern CoolVDS slice with high-speed SSDs, you need to push the hardware.

Here is a snippet from my battle-tested my.cnf for a 16GB RAM instance:

[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 Optimization
# Set this to 70-80% of available RAM
innodb_buffer_pool_size = 12G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1 # ACID compliance. Set to 2 for speed if you accept 1 sec data loss.
innodb_file_per_table = 1

# Connection Handling
max_connections = 500
thread_cache_size = 50

# SSD Optimization
innodb_flush_neighbors = 0
innodb_io_capacity = 2000 # Crank this up for CoolVDS SSDs

Note the innodb_io_capacity. Default is 200. On spinning rust (HDDs), 200 is fine. On the enterprise-grade SSDs we use at CoolVDS, leaving this at 200 is like driving a Ferrari in first gear. Crank it up.

The Switchover: Catching Up

Once the dump is imported on the new server, the data is already stale. This is where replication saves us.

1. Configure the New Server as a Slave: Read the log coordinates from the head of your dump file.
2. Start Slave: Let MySQL pull the changes that happened during the transfer.
3. Verify: Check Seconds_Behind_Master. It should drop to 0.

CHANGE MASTER TO
MASTER_HOST='198.51.100.1',
MASTER_USER='replication_user',
MASTER_PASSWORD='SecurePassword123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=10732;

When you are ready to cut over, you simply put the old site in read-only mode, wait for the slave to process the final few queries (milliseconds), and update your DNS or load balancer to point to CoolVDS.

Why Infrastructure Choice Dictates Success

I have seen migrations fail not because of bad SQL, but because of "Steal Time" (CPU Ready). This happens on oversold OpenVZ hosts where noisy neighbors eat your CPU cycles during the import process.

This is why we strictly use KVM (Kernel-based Virtual Machine) at CoolVDS. KVM provides hardware virtualization. Your RAM is your RAM. Your CPU cores are reserved. When you are importing 10 million rows, you need sustained CPU power, not burstable credits that run out halfway through.

Compliance and The "Datatilsynet" Factor

We operate under Norwegian law. The Personal Data Act (Personopplysningsloven) places strict requirements on how personal data is handled. With the Safe Harbor framework looking increasingly shaky (especially after the Snowden revelations), keeping your data within Norwegian borders is the safest bet for legal compliance.

Final Thoughts

Migrations don't have to be scary. They are a matter of physics and planning. You calculate the transfer size, you gauge the I/O throughput, and you minimize the delta.

Don't let slow I/O kill your SEO or your patience. If you are planning a migration in 2015, you need hardware that keeps up with your ambition.

Ready to stop waiting on iowait? Deploy a high-performance KVM instance on CoolVDS today and experience what dedicated resources actually feel like.