Stop Praying, Start Replicating: The Realist's Guide to Database Migration

It’s 3:00 AM. You have a terminal window open, a cold cup of coffee, and a progress bar that hasn't moved in ten minutes. If you’ve been in operations long enough, you know this feeling. Migrating a database isn't just about moving bytes; it's about preserving state in a hostile environment.

Most "tutorials" tell you to run mysqldump, SCP the file, and restore it. That works for a WordPress blog with ten visitors. But if you are running a high-transaction e-commerce site or a SaaS platform in the Nordic market, taking your database offline for four hours to transfer 500GB is not a strategy. It's negligence.

In 2019, with the GDPR fully enforceable and the Datatilsynet (Norwegian Data Protection Authority) watching, losing data integrity during a move is a legal liability. Here is how we execute zero-downtime migrations using replication, not hope.

The Hardware Bottleneck: Why Your Target Matters

Before we touch a config file, let's talk about IOPS. If you are migrating a write-heavy database to a host using spinning rust (HDD) or shared SATA SSDs, you are setting yourself up for replication lag that you will never catch up on.

When you import a dump, you are essentially hammering the disk with sequential writes followed by random index builds. We benchmarked this. Importing a 50GB dataset on standard SSD VPS vs. CoolVDS NVMe instances showed a 4x difference in restore time.

Pro Tip: Always check the target disk scheduler. For NVMe inside a KVM environment (which is what we run), you want none or noop inside the guest to let the host handle scheduling.

# Check your scheduler
cat /sys/block/vda/queue/scheduler
# [mq-deadline] kyber bfq none

# Switch to none for NVMe passthrough efficiency
echo none > /sys/block/vda/queue/scheduler

Step 1: The Master Configuration

We are going to use the Master-Slave replication method. Your current server is the Master. Your new CoolVDS instance is the Slave.

First, ensure your Master is actually writing binary logs. Without this, replication is impossible. Edit your my.cnf (usually in /etc/mysql/ or /etc/my.cnf). We need to ensure GTID (Global Transaction ID) is on. In 2019, using classic file-position replication is risky; GTID is much more robust against network blips.

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

# GTID Settings for MySQL 5.7 / 8.0
gtid_mode                = ON
enforce_gtid_consistency = ON

If you change these, you need a restart. If you cannot restart, you are stuck with a blocking migration. Plan your maintenance window accordingly.

Step 2: The Consistent Snapshot

We need a base dataset. Do not just copy the /var/lib/mysql folder unless you shut down the database (cold backup). For a hot backup, we use mysqldump with specific flags to ensure consistency without locking tables for the duration of the dump.

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

The critical flags:

• --single-transaction: Uses InnoDB MVCC to give you a consistent view of the data at one point in time without locking.
• --master-data=2: This writes the binary log coordinates (file and position) into the dump file as a comment. We need this to tell the Slave where to start.

Step 3: Transfer and Restore

Move the data. Since we operate in Norway, and likely your data falls under GDPR jurisdiction, do not use FTP. Use SCP or rsync over SSH. Latency matters here. If your current host is in Frankfurt and you are moving to Oslo (CoolVDS), the peering path via NIX (Norwegian Internet Exchange) is usually clean, but packet loss kills TCP throughput.

# On the new CoolVDS instance
scp user@old-server:/tmp/dump.sql.gz .

# Restore (Use pv to monitor progress, otherwise you go insane)
zcat dump.sql.gz | pv | mysql -u root -p

While this runs, tweak your new server's innodb_buffer_pool_size to 70% of available RAM. You can dial it back later, but for the import, you need memory.

Step 4: Enabling Replication

Once the import finishes, check the head of the dump file to find the coordinates.

zcat dump.sql.gz | head -n 50 | grep "CHANGE MASTER"

You will see something like:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=5421;

Now, log into your new CoolVDS database (the Slave) and configure the link. Note: Create a replication user on the Master first with REPLICATION SLAVE privileges.

CHANGE MASTER TO
  MASTER_HOST='192.0.2.10', -- IP of your old server
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=5421;

START SLAVE;

Check status with SHOW SLAVE STATUS \G. You want to see:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0 (Eventually)

The "Cutover"

Now you have two identical databases. The old one is live; the new one on CoolVDS is syncing in near real-time. To switch:

1. Lower the TTL of your DNS records to 300 seconds a day before.
2. Put your application in "Maintenance Mode" (read-only).
3. Wait for Seconds_Behind_Master to hit 0.
4. Stop the slave: STOP SLAVE;.
5. Promote the Slave to Master (stop replication configuration).
6. Point your application config to the new CoolVDS IP.

Total write-downtime? About 30 seconds. Much better than the 4 hours you would spend waiting for a file transfer.

Why Infrastructure Matters

You can execute this procedure perfectly, but if the underlying host has "noisy neighbors" stealing CPU cycles, your replication SQL thread will lag. In a shared hosting environment, `iowait` is the silent killer of database performance.

At CoolVDS, we use KVM virtualization to ensure strict resource isolation. We don't oversell CPU cores, and our storage backend is pure NVMe. When you run START SLAVE, the catch-up process is limited only by your network bandwidth, not by the disk struggling to write IOPS.

If you are handling sensitive Norwegian user data, you also need to know exactly where that data sits. Our servers are physically located in Oslo, ensuring compliance with local data residency requirements.

Database migration is surgery. Don't use dull tools.

Ready to stabilize your database performance? Spin up a high-performance NVMe instance on CoolVDS today and test the IOPS for yourself.