The Art of Moving Data Without Stopping the World

It’s 3:00 AM. You are staring at a terminal window, finger hovering over the Enter key. You are about to switch DNS pointers for a client's e-commerce platform handling 500 transactions per minute. If this migration fails, you aren't just losing sleep; you're losing revenue, reputation, and possibly your sanity.

Most "tutorials" tell you to run mysqldump, scp the file, and restore it. In the real world, where datasets exceed 500GB and SLAs demand 99.99% uptime, that is a recipe for disaster. The "Dump and Restore" method locks tables, spikes latency, and guarantees downtime proportional to your data size. That is unacceptable.

As systems architects operating in the Norwegian market, we face a dual challenge: technical integrity and strict legal compliance following the Schrems II ruling. You cannot just throw data into a random US-owned cloud bucket anymore. You need precise control, low latency within the Nordic region, and hardware that doesn't choke on I/O operations.

The Only Sane Strategy: Replication-Based Migration

Stop thinking about migration as a "move." Think of it as a "sync." The goal is to establish a real-time replica on your new infrastructure (CoolVDS), let it catch up to the master, and then promote it. This reduces downtime from hours to seconds.

Step 1: The Hardware Handshake

Before touching config files, look at your target storage. Database performance is I/O bound. If you migrate a write-heavy PostgreSQL instance to a VPS using shared HDD storage or capped SSDs, your migration will stall. We built CoolVDS on pure NVMe arrays precisely for this reason. When the catch-up phase begins, the disk thrashing is immense. You need high IOPS not just for the data landing, but for the concurrent binlog application.

Step 2: Preparing the Master (MySQL 8.0 Example)

First, ensure your current server (the Master) is configured to write binary logs. Without this, replication is impossible. Edit your my.cnf (usually in /etc/mysql/):

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

Pro Tip: Setting sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 is the safest ACID-compliant configuration. Yes, it impacts write speed slightly, but during a migration, data integrity is paramount. If the master crashes, you don't want a corrupted binlog.

Restart the service. Now, create a replication user. Do not use root. Restrict this user strictly to the IP of your new CoolVDS instance for security.

CREATE USER 'replicator'@'10.0.0.5' IDENTIFIED WITH mysql_native_password BY 'Str0ngP@ssw0rd!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.5';
FLUSH PRIVILEGES;

Step 3: The Initial Snapshot

We need a coordinate to start from. Use mysqldump with the --master-data=2 flag. This comments the binary log coordinates into the dump file itself, so the slave knows exactly where to start picking up changes.

mysqldump --all-databases \
  --single-transaction \
  --quick \
  --master-data=2 \
  --triggers \
  --routines \
  --events \
  -u root -p | gzip > /tmp/full_dump.sql.gz

Using --single-transaction is critical for InnoDB tables as it doesn't lock the database, allowing your live site to keep serving customers. Transfer this file to your new CoolVDS instance using rsync.

rsync -avz --progress /tmp/full_dump.sql.gz user@new-server-ip:/tmp/

The Ansible Automation Approach

If you are managing a fleet of servers, doing this manually is inefficient. Here is a snippet from an Ansible playbook we use to provision the database environment on the new host before the data arrives. This ensures the environment matches the source exactly.

---
- name: Provision Database Slave
  hosts: database_slaves
  become: true
  tasks:
    - name: Install MySQL Server
      apt:
        name: mysql-server
        state: present
        update_cache: yes

    - name: Configure mysqld.cnf for NVMe optimization
      lineinfile:
        path: /etc/mysql/mysql.conf.d/mysqld.cnf
        regexp: "^{{ item.key }}"
        line: "{{ item.key }} = {{ item.value }}"
      with_items:
        - { key: 'innodb_buffer_pool_size', value: '4G' } # Adjust based on RAM
        - { key: 'innodb_io_capacity', value: '2000' }   # NVMe speeds allow higher capacity
        - { key: 'innodb_log_file_size', value: '512M' }

    - name: Ensure MySQL service is running
      service:
        name: mysql
        state: started
        enabled: yes

Step 4: Configuring the Slave and Catching Up

On the new server, import the dump. This is the longest part of the process. Once imported, inspect the dump file head to find the log coordinates:

zcat /tmp/full_dump.sql.gz | head -n 30 | grep "CHANGE MASTER TO"

You will see something like MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1543. Now, configure the slave:

CHANGE MASTER TO
MASTER_HOST='192.168.1.10', -- Your old server IP
MASTER_USER='replicator',
MASTER_PASSWORD='Str0ngP@ssw0rd!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1543;

START SLAVE;

Check the status immediately:

SHOW SLAVE STATUS\G

You are looking for Seconds_Behind_Master to drop to 0. If you are on a CoolVDS NVMe plan, this usually happens incredibly fast because the disk write latency doesn't bottleneck the SQL thread applying the updates.

The Norwegian Context: Latency and Legality

Why does server location matter for migration? Latency. If your Master is in a datacenter in Oslo and your Slave is in Amsterdam, every single transaction packet has to travel a round trip. During the catch-up phase, high latency can cause the slave to lag perpetually behind the master, never reaching a clean sync state.

Furthermore, since the Schrems II ruling last year (July 2020), moving personal data of Norwegian citizens to US-controlled servers poses significant compliance risks. By keeping your database on CoolVDS infrastructure, located physically in Europe and operated by a European entity, you simplify your GDPR stance. You aren't just optimizing for milliseconds; you're optimizing for Datatilsynet audits.

Network Tuning for the Transfer

To speed up the replication stream over the WAN, you might need to tweak the TCP stack on both Linux servers if the distance is significant. Add this to /etc/sysctl.conf:

net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_congestion_control = cubic

Reload with sysctl -p. Standard TCP windows are often too small for high-throughput replication streams.

The Final Switchover

Once Seconds_Behind_Master is 0:

1. Put the Old Master into Read-Only mode: SET GLOBAL read_only = ON;.
2. Wait for the slave to process the final transaction (check position).
3. Promote the Slave: STOP SLAVE; RESET MASTER;.
4. Update your application config to point to the new CoolVDS IP.

This process ensures zero data loss. The only downtime is the time it takes to restart your application backend.

Why Infrastructure is the Hidden Variable

You can script the perfect migration, but if the underlying virtualization layer steals CPU cycles (common in OpenVZ or container-heavy shared hosting), your database will stutter. We use KVM virtualization at CoolVDS. This provides a hard hardware interrupt isolation. Your database kernel is your kernel. It doesn't fight for resources with the neighbor's Minecraft server.

When you are ready to test this, spin up a high-performance instance. Don't let IOPS bottlenecks dictate your migration window.

Ready to stabilize your backend? Deploy a KVM NVMe instance on CoolVDS today and see the difference raw I/O power makes.