Surviving the Switch: Zero-Downtime Database Migration Strategies

There is a specific kind of silence that falls over an operations room when a database migration goes wrong. It’s not peaceful. It’s the sound of mysqldump hanging on a locked table while your CEO asks why the checkout page is throwing 503 errors. I’ve been there. I’ve seen a simple 50GB transfer turn into a 12-hour nightmare because someone underestimated the latency between Oslo and Frankfurt.

If you are still shutting down your web server to run a cold backup and restore, you are doing it wrong. In 2014, uptime isn't a luxury; it's the baseline. Whether you are running Magento, Drupal, or a custom stack, your users—and the search bots—won't wait.

This guide isn't about "digital transformation." It's about moving bits from Server A to Server B without losing data or sleep. We are going to look at the only strategy that matters for professional environments: Replication-Based Migration using Percona XtraBackup.

The Hardware Reality Check

Before touching the terminal, look at your destination. A database is only as fast as the disk it sits on. In the legacy hosting world, providers cram 50 tenants onto a single HDD RAID array. When neighbor A runs a backup, your I/O wait shoots through the roof.

This is why we standardized on KVM virtualization at CoolVDS. Unlike OpenVZ, where resources are shared in a muddy pool, KVM isolates your kernel and your I/O allocations. If you are migrating to us, ensure you have provisioned an instance with SSD storage. The random write speeds of SSDs are critical for the InnoDB engine during the "catch-up" phase of replication.

Step 1: The Non-Blocking Snapshot

Forget mysqldump for anything over 5GB. It locks tables. It’s slow. It generates massive SQL text files that take forever to parse.

We use Percona XtraBackup. It copies InnoDB data files physically while the server is running, without locking the database for the entire duration. It only locks briefly at the very end to sync the transaction log.

First, install the repository on your source server (assuming CentOS 6):

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

Now, create the full backup. This directory will be the foundation of your new database on CoolVDS.

# Create a directory for the backup
mkdir -p /data/backups/full

# Run the hot backup
innobackupex --user=root --password='YOUR_STRONG_PASSWORD' --no-timestamp /data/backups/full

While this runs, your site stays online. Users can still buy products. Comments still get posted.

Step 2: Prepare and Transport

Raw data files are useless until they are consistent. You need to apply the transaction logs (redo logs) that accumulated while the backup was running. Run this before you move the files to save CPU time on the destination:

innobackupex --apply-log /data/backups/full

Now, move the data. We use rsync. It’s resume-able, secure, and efficient.

Pro Tip: Moving data within Norway? Latency is negligible. Moving from a US provider to CoolVDS in Oslo? Use the -z flag for compression, but watch your CPU usage. If your CPU is the bottleneck, drop compression.

rsync -avzP /data/backups/full/ root@new.coolvds.server:/var/lib/mysql/

Step 3: Configuration & The "Slave" Setup

On your new CoolVDS instance, you need to configure MySQL (or MariaDB) to act as a replica. This ensures that any data written to the old server after your backup finished gets copied over.

Edit your /etc/my.cnf on the new server. You must have a unique server-id.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Replication Settings
server-id=2
log_bin=mysql-bin
relay_log=mysql-relay-bin
read_only=1

Make sure to adjust ownership after the rsync:

chown -R mysql:mysql /var/lib/mysql
service mysql start

Step 4: Synchronization

Check the xtrabackup_binlog_info file inside your backup directory. It contains the coordinates you need.

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

Log into MySQL on the new CoolVDS server and start the replication:

CHANGE MASTER TO
  MASTER_HOST='old.server.ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=4567;

START SLAVE;

Check the status with SHOW SLAVE STATUS\G. You want to see Seconds_Behind_Master: 0. Once that number hits zero, your two servers are identical.

The Norwegian Context: Data Sovereignty

Why go through this trouble? Why not just use a cloud bucket in the US? Because of the Personopplysningsloven (Personal Data Act). After the Snowden leaks last year, relying on Safe Harbor agreements is becoming a legal minefield for Norwegian businesses holding sensitive customer data.

By migrating to a provider physically located in Oslo, like CoolVDS, you reduce latency for your local users and simplify compliance with the Datatilsynet. You know exactly where your bits live.

Step 5: The Cutover

This is the only moment you need downtime, and it lasts seconds.

1. Put your web application in maintenance mode.
2. Verify the slave is fully caught up (Seconds_Behind_Master = 0).
3. Stop the slave: STOP SLAVE;
4. Make the new database writable: Run SET GLOBAL read_only = 0;
5. Point your application config (wp-config.php, local.xml, etc.) to the new CoolVDS IP.
6. bring the site back up.

Your database is the heart of your infrastructure. Don't treat it like a temporary file. By using replication, you validate the new environment before the old one is ever turned off. It takes more work, but it guarantees consistency.

Ready to get serious about your IOPS? Stop fighting with noisy neighbors and deploying on over-sold hardware. Spin up a KVM instance on CoolVDS today and see what dedicated resources actually feel like.