Zero-Downtime Database Migration: Surviving the Move to Norway

Let’s be honest: moving a database is the closest most system administrators get to open-heart surgery. One wrong command, one corrupted index, or a sudden network drop between your old host in Germany and your new infrastructure in Norway, and you aren't just losing data—you're losing your job.

I’ve seen it happen. A perfectly good Magento store, generating thousands of kroner an hour, brought to its knees because the previous admin thought a simple mysqldump over the public internet was a valid migration strategy for a 50GB dataset. It wasn't. The restore took six hours. The site was down for eight.

If you are serious about uptime, you don't shut down the server to move it. You replicate it. Today, we are going to look at how to move a heavy MySQL 5.6 workload to a high-performance environment like CoolVDS without your users ever noticing a glitch.

The Bottleneck is Always Disk I/O

Before we touch a single config file, we need to address the hardware. In 2014, spinning rust (HDDs) has no place in a database server. I don't care if you have 15k RPM SAS drives in RAID 10; the random write penalties during a large import will choke your CPU.

When you migrate, you are essentially hammering the disk with writes. If your target VPS is running on a shared SAN with noisy neighbors, your import speed will fluctuate wildly. This is why we use KVM virtualization at CoolVDS. Unlike OpenVZ, where resources are often oversold, KVM gives us a dedicated kernel and strict isolation. Combined with local SSD storage, we can guarantee the IOPS needed to replay binary logs in real-time.

The Strategy: Asynchronous Master-Slave Replication

Forget offline migrations. The only professional way to move a live database is to turn your new server into a Read Replica (Slave) of your current server (Master), let them sync, and then promote the Slave.

Step 1: Preparing the Master (The Old Server)

First, ensure your current MySQL server has binary logging enabled. Without this, replication is impossible. Open /etc/mysql/my.cnf (or /etc/my.cnf on CentOS) and check for these lines:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = mixed
expire_logs_days = 7
max_binlog_size = 100M

If you change this, you must restart MySQL. Yes, this requires a momentary restart, but it's the only downtime we'll allow until the final cutover.

Step 2: The Initial Sync (Use XtraBackup, Not mysqldump)

If your database is under 500MB, mysqldump is fine. If it's 50GB, mysqldump locks tables and takes forever to restore because it has to rebuild indexes from scratch. Percona XtraBackup is the standard here. It copies the physical InnoDB data files, which makes restoration significantly faster.

On the Master, we stream the backup directly to the new CoolVDS server using innobackupex piped through SSH. This avoids writing a huge file to the local disk first.

innobackupex --user=root --password=YOURPASS --stream=xbstream /tmp | \
ssh root@new-coolvds-ip "xbstream -x -C /var/lib/mysql/data_temp"

This command grabs a consistent snapshot without locking your database. Your users can still buy products while this is running.

Step 3: Preparing the Slave (The CoolVDS Server)

Once the data is on the CoolVDS instance, we need to prepare it. This step applies the transaction logs to the data files to ensure consistency.

innobackupex --apply-log /var/lib/mysql/data_temp

Now, move the data into place and fix permissions:

service mysql stop
rm -rf /var/lib/mysql/*
mv /var/lib/mysql/data_temp/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql start

Pro Tip: Before starting MySQL on the new server, tune your innodb_buffer_pool_size. On a CoolVDS instance with 8GB RAM, set this to 5G or 6G. The default is often too low, and you want that data in RAM immediately.

Step 4: Configure Replication

XtraBackup creates a file called xtrabackup_binlog_info. This contains the exact binary log file and position at the moment the snapshot was finished. Read it:

cat /var/lib/mysql/xtrabackup_binlog_info
# Output example: mysql-bin.000003  4582

On your new CoolVDS server (the Slave), configure the replication link:

CHANGE MASTER TO
MASTER_HOST='old-server-ip',
MASTER_USER='replicator',
MASTER_PASSWORD='secure_password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=4582;

Start the slave:

START SLAVE;
SHOW SLAVE STATUS\G

Look for Seconds_Behind_Master. It will start high as it catches up on the changes that happened during the transfer. Watch it drop to zero. Once it hits zero, your two servers are identical.

The Cutover: Flipping the Switch

Now you have two identical databases. The old one is still taking writes. The new one on CoolVDS is copying them instantly. To switch over:

1. Put your web application in "Maintenance Mode" (stops new writes).
2. Verify Seconds_Behind_Master is 0.
3. Stop the slave: STOP SLAVE;
4. Point your application config to the new CoolVDS IP.
5. Disable Maintenance Mode.

Total downtime? About 15 to 30 seconds.

Why Location and Latency Matter

If you are serving customers in Norway, hosting your database in a budget data center in Kansas is negligent. The latency adds up on every single query. A Magento page load might execute 50+ queries. 150ms latency x 50 queries = a slow, unusable site.

CoolVDS infrastructure is peered directly at NIX (Norwegian Internet Exchange). We aren't hopping through three different transit providers in London to get packets to Oslo. Low latency isn't just a luxury; it's a technical requirement for high-performance database applications.

Security and Compliance (Datatilsynet)

Migrating data across borders isn't just technical; it's legal. Under the Norwegian Personal Data Act (*Personopplysningsloven*), you are responsible for the safety of your users' data. Transferring unencrypted SQL dumps over FTP is a violation of basic security principles.

Always use SSH tunnels or SSL replication when moving data over public networks. At CoolVDS, we can help you set up private networking (VLANs) if you have a multi-server cluster, keeping your database traffic completely off the public internet. This satisfies the strict security requirements of the Data Protection Authority.

Optimizing the Final Config

Once you are live on CoolVDS, ensure your configuration matches the SSD hardware. Standard my.cnf files assume spinning disks.

[mysqld]
# SSD Optimizations
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 0

# Durability vs Speed
# Set to 1 for max safety, 2 for higher speed (risk of 1 sec data loss on crash)
innodb_flush_log_at_trx_commit = 1

Don't let legacy hardware or sloppy migration scripts threaten your data integrity. The tools exist. XtraBackup, SSH tunnels, and KVM-based SSD VPS solutions like CoolVDS make zero-downtime migrations not just possible, but standard practice.

Ready to stop sweating during maintenance windows? Spin up a CoolVDS SSD instance today and test your replication lag. It takes less than 60 seconds.