Database Migration: performing open-heart surgery while the patient runs a marathon

I still remember the first time I dropped a production database during a migration. It was 2014. The backups were cold. The client was furious. And the restore took six hours because we were running on spinning rust (HDDs) instead of proper solid-state storage. That silence on the conference call? It stays with you.

In 2022, there is absolutely no excuse for hard downtime during a migration. Whether you are moving from a legacy on-prem server in Bergen to a cloud instance, or switching providers to escape the data sovereignty nightmares brought on by Schrems II, the goal is the same: zero data loss, near-zero downtime.

This guide is for the system administrators who know that rsync isn't enough when the database engine is writing 5,000 transactions per second. We are going to look at how to migrate MySQL (8.0) and PostgreSQL (14) while keeping the application live.

The Legal Elephant: GDPR and Data Location

Before we touch a single config file, look at your map. If you are serving Norwegian customers, your data needs to stay close. Latency is physics. Light takes time to travel. But more importantly, Datatilsynet (The Norwegian Data Protection Authority) is watching. Since the Schrems II ruling, moving personal data outside the EEA—or even to US-owned providers within Europe—is a legal minefield.

Hosting on CoolVDS solves two problems here:

1. Compliance: Your data sits on hardware in Oslo. No US cloud act exposure.
2. Latency: We peer directly at NIX (Norwegian Internet Exchange). Your ping to local ISPs is practically a rounding error.

Scenario A: The MySQL/MariaDB Replication Swap

The only way to move a high-traffic MySQL database without stopping the world is Master-Slave Replication. We set up the new CoolVDS instance as a replica, let it catch up, and then promote it to Master.

1. Prepare the Source (Old Server)

You need binary logging enabled. Without this, replication is impossible. Open your /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf:

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

Restart MySQL. Then, create a user specifically for replication. Do not use root. Security hygiene matters.

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

2. The Initial Dump

We need a snapshot of the data. If your dataset is under 50GB, mysqldump is fine. If it's larger, look at Percona XtraBackup. We will stick to mysqldump for clarity, but notice the flags. We aren't locking tables for hours.

mysqldump -u root -p --all-databases --master-data=2 --single-transaction --quick > full_dump.sql

Pro Tip: The --master-data=2 flag is critical. It writes the binary log coordinates (filename and position) into the dump file as a comment. You will need these to tell the slave where to start reading.

3. Restore on CoolVDS (The Destination)

Transfer the file using scp or rsync. This is where disk I/O becomes your bottleneck. If you chose a provider with shared storage or spinning disks, go grab a coffee. Maybe three.

On CoolVDS, we use local NVMe storage. The import speed is limited mostly by CPU parsing, not disk write speeds. I've seen 100GB imports finish in the time it takes to stand up and stretch.

Once imported, configure the CoolVDS instance as a slave:

CHANGE MASTER TO
MASTER_HOST='192.0.2.10',  -- Your OLD server IP
MASTER_USER='repl_user',
MASTER_PASSWORD='Complex_Password_2022!',
MASTER_LOG_FILE='mysql-bin.000001', -- Get this from the dump file head
MASTER_LOG_POS= 8945; -- Get this from the dump file head

START SLAVE;

Check the status with:

SHOW SLAVE STATUS\ G

You want to see Seconds_Behind_Master: 0. Once that hits zero, your data is synced in real-time.

Scenario B: PostgreSQL High-Performance Migration

Postgres handles things differently. For version 14, pg_basebackup is your best friend for streaming replication setup.

On the source (old server), edit pg_hba.conf to allow the CoolVDS IP to stream replication:

# /etc/postgresql/14/main/pg_hba.conf
# Allow replication connections from CoolVDS instance
host    replication     all             185.xxx.xxx.xxx/32          md5

Reload the config: systemctl reload postgresql.

Now, on the CoolVDS instance (make sure the data directory is empty), run the base backup command. This streams the entire data directory over the wire, effectively cloning the server.

pg_basebackup -h old_server_ip -D /var/lib/postgresql/14/main -U repl_user -P -v -R -X stream -C -S coolvds_slot

The -R flag automatically writes the standby.signal file and connection settings. Start the service, and you have a read-only replica running on high-performance NVMe infrastructure.

The Cutover: Kill It With Fire

Your new server is running. It's synced. It's fast. Now for the scary part.

1. Lower TTLs: 24 hours before migration, drop your DNS TTL to 300 seconds.
2. Maintenance Mode: Put the app in maintenance mode. This stops writes.
3. Verify Sync: Check Master and Slave one last time.
4. Promote Slave:
   • MySQL: STOP SLAVE; RESET MASTER;
   • Postgres: pg_ctl promote -D /var/lib/postgresql/14/main
5. Switch DNS: Point your domain to the CoolVDS IP.

Why Infrastructure Dictates Success

You can script everything perfectly, but if the underlying hardware chokes, you fail. I've debugged migrations where the sheer volume of binlogs generated during a sync saturated the network link of a cheap budget VPS.

Database performance is rarely about raw CPU GHz; it is about IOPS (Input/Output Operations Per Second) and latency stability. When a database commits a transaction, it forces a disk sync (fsync). If that sync takes 10ms on a crowded SAN, your application feels slow. If it takes 0.1ms on local NVMe (standard on CoolVDS), your application feels instant.

Don't let IO wait times kill your migration. Test your strategy, validate your backups, and ensure your destination server can handle the heat.

Ready to upgrade your database performance? Deploy a high-IOPS NVMe instance on CoolVDS today and see the difference raw speed makes.