Moving Heavy Iron: Strategies for Zero-Downtime Database Migration

It is December 2015. The European Court of Justice has just invalidated the Safe Harbor agreement (Schrems I), sending shockwaves through every CTO’s office from Oslo to Berlin. If you are running a serious workload, you aren't just thinking about performance anymore; you are thinking about data sovereignty. You need your data on Norwegian soil, protected by the Datatilsynet, and you need it there yesterday.

But moving a 200GB+ transactional database isn't like FTPing a WordPress theme. If you run a standard mysqldump on a live Magento store or a high-traffic forum, you lock your tables. The site halts. Customers leave. Your phone explodes.

I have seen seasoned sysadmins weep over corrupted InnoDB tablespaces during a botched transfer. Today, we are going to do it the right way. No downtime. No data loss. Just pure, clean replication.

The "Dump and Pray" Method vs. The Professional Way

Most VPS providers will tell you to just stop the service, dump the data, and scp it over. That works if you run a hobby blog. It does not work if you process payments.

For mission-critical migrations to a VPS in Norway, we use a Replication-Based Strategy. The concept is simple: we turn your new CoolVDS server into a slave of your current live server, let them sync up, and then promote the slave to master.

Step 1: The Hardware Foundation

Before you even touch a config file, look at your destination IOPS. Database imports are I/O heavy. If you migrate to a provider using standard mechanical SAS drives or shared storage, your import will take days.

Pro Tip: Always verify the underlying storage technology. At CoolVDS, we use KVM virtualization to ensure isolation. Unlike OpenVZ, where a "noisy neighbor" can steal your disk I/O, KVM guarantees your resources. Plus, we are rolling out NVMe storage, which offers random read/write speeds that make SATA SSDs look like spinning rust.

The Execution: Migrating MySQL 5.6

Let's assume you are moving from a legacy host to a high-performance CoolVDS instance. We will use Percona XtraBackup. It allows us to take a hot backup without locking the database.

1. Prepare the Source (Master)

Edit your /etc/my.cnf on the source server to enable binary logging if you haven't already. You cannot replicate without this.

[mysqld]
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

You will need a replication user. Run this inside the MySQL shell:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Security Note: Replace '%' with the specific IP of your CoolVDS instance to tighten security.

2. The Hot Backup

Install Percona XtraBackup. If you are on CentOS 6 or 7:

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install percona-xtrabackup-23

Now, stream the backup directly to your new server using xtrabackup and ssh. This saves disk space on the source.

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

This command is beautiful. It streams the binary data across the wire, utilizing the stable latency between major European hubs and the NIX (Norwegian Internet Exchange). If you are piping this into a CoolVDS instance in Oslo, you'll see the throughput saturate the link.

3. Prepare and Restore on Target

Once the stream finishes, you need to prepare the backup (apply the transaction logs) on the new server.

innobackupex --apply-log /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

4. Configure the Slave

Check the xtrabackup_binlog_info file on the new server. It contains the coordinates you need.

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

Now, start MySQL on the CoolVDS instance and configure the replication:

CHANGE MASTER TO
  MASTER_HOST='old-server-ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4567;

START SLAVE;

Type SHOW SLAVE STATUS \G. Look for Seconds_Behind_Master. It should rapidly drop to 0.

Optimization: Tuning for the New Metal

You have moved to better hardware. Don't run your old config. If you have upgraded from 4GB RAM to a 16GB CoolVDS plan, adjust your buffer pool.

[mysqld]
# Set to 70-80% of total RAM
innodb_buffer_pool_size = 12G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # ACID compliance is mandatory
innodb_flush_method = O_DIRECT

The Cutover (Zero Downtime)

When you are ready to switch:

1. Put your application in "Maintenance Mode" (or Read-Only) for 30 seconds.
2. Ensure Seconds_Behind_Master is 0.
3. Stop the slave: STOP SLAVE;
4. Point your application's DNS or configuration to the new CoolVDS IP.

Because the data was pre-synced, the downtime is literally just the time it takes to edit a config file or propagate a DNS change (lower your TTL beforehand!).

Why Local Matters

Latency is the silent killer of database performance. If your app server is in Frankfurt but your database is in the US, physics is working against you. By keeping your stack in Norway, you aren't just complying with the upcoming stricter EU data regulations; you are ensuring single-digit millisecond latency for your Norwegian user base.

CoolVDS infrastructure is built directly on this principle. We don't oversell, and we don't route your traffic through cheap, congested transit providers. We prioritize the shortest path to NIX.

Database migration is surgery. You need steady hands and sharp tools. Don't let slow I/O or bad network routing botch the operation.

Ready to test the difference? Spin up a high-performance CoolVDS instance today and benchmark your import speeds.