Zero-Downtime Database Migration: A Battle-Tested Guide for Norwegian Systems

Migration day. It’s the only day on the calendar where I genuinely consider switching careers to goat farming in Vestland. If you are reading this, you are likely staring at a legacy dedicated server that is aging poorly, spinning rust hard drives that sound like a jet engine, and a MySQL version that belongs in a museum.

You need to move. You need better I/O. And critically, you cannot afford the four hours of downtime required to `scp` a 500GB dump file across the Atlantic.

I have managed migrations for high-traffic e-commerce sites in Oslo and heavy SaaS platforms in Trondheim. The strategy is always the same: Don't move the database. Clone it, sync it, then kill the original.

This is the technical reality of moving to a modern NVMe-based VPS in 2019 without losing data or your sanity.

The Infrastructure Reality Check: IOPs or Bust

Before we touch `my.cnf`, we need to talk about where the data is going. In 2019, moving a database from an HDD (or even a cheap SATA SSD) to an NVMe interface is the single biggest performance upgrade you can make. We aren't just talking about throughput; we are talking about latency.

When your database executes a complex `JOIN` or a table scan, it hits the disk hard. On a standard SATA SSD, you might get 500 MB/s read. On the NVMe drives we use as standard at CoolVDS, you are looking at 3,000+ MB/s. That isn't just a number; it is the difference between a query taking 2 seconds or 200 milliseconds.

Pro Tip: If you are migrating to a VPS, ensure your provider isn't over-selling CPU steal time. Run `iostat -c 1 5` immediately after provisioning. If `%steal` is above 0.5 consistently, terminate the instance and move. CoolVDS guarantees dedicated KVM resources specifically to prevent this "noisy neighbor" effect on database workloads.

The Strategy: Master-Slave Replication

Forget the "maintenance window" where you shut down the site for the night. That doesn't work for modern businesses. We are going to set up your new CoolVDS instance as a replication slave, let it catch up to real-time, and then promote it to master.

Step 1: Configure the Legacy Master

You need to enable binary logging on your current server. This is the journal of changes the slave will read. Edit your `my.cnf` (usually in `/etc/mysql/` on Ubuntu/Debian or `/etc/` on CentOS 7).

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

A restart is required here (`systemctl restart mysql`). Yes, this is your only moment of downtime, usually less than 30 seconds.

Step 2: Create the Replication User

The new server needs permission to read the logs. Don't use root. Create a specific user with limited scope.

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

Step 3: The Initial Dump

This is where most people fail. They take a dump that is inconsistent. You must use `--master-data=2` (or `--source-data` if you are on the bleeding edge of MySQL 8 tools) to record the exact log position at the moment of the dump.

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

Note: If your database is over 100GB, `mysqldump` is too slow. Look into Percona XtraBackup. It performs physical backups rather than logical ones, which restores much faster on the target.

Step 4: Prepare the Target (CoolVDS)

On your new NVMe VPS, install the exact same version of MySQL/MariaDB. Do not try to upgrade versions during a migration unless you have a death wish. Upgrade after the data is stable.

Tune the new configuration before importing. Since you are likely moving to a VPS with more RAM, adjust the buffer pool:

[mysqld]
server-id = 2
# Set to 70-80% of available RAM
innodb_buffer_pool_size = 8G 
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # Compliance is key, don't lose transactions

Step 5: Import and Link

Transfer the dump using `scp` or `rsync`. Import it:

zcat /tmp/full_db_dump.sql.gz | mysql -u root -p

Once imported, inspect the dump file (use `zless` or `head`) to find the coordinates recorded by `--master-data`. You are looking for a line like:

On the new server, execute the linkage:

CHANGE MASTER TO
MASTER_HOST='192.0.2.10',  -- Your legacy server IP
MASTER_USER='replicator',
MASTER_PASSWORD='YourStrongPassword_2019!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=987654;

START SLAVE;

Check the status with `SHOW SLAVE STATUS \G`. You want to see `Seconds_Behind_Master` drop to 0.

The Local Angle: Latency and Datatilsynet

Why bother moving servers geographically? Two reasons: Speed and Law.

1. The Speed of Light: If your customers are in Oslo or Bergen, hosting in a German or US datacenter adds 30-100ms of latency per round trip. For a PHP application doing 20 SQL queries per page load, that adds up to seconds of delay. CoolVDS infrastructure is peered directly at NIX (Norwegian Internet Exchange), meaning latency to local ISPs is often sub-5ms.

2. GDPR & Data Sovereignty: We are a year into GDPR now. While the Privacy Shield framework currently allows data transfer to the US, the legal ground is shifting. Many Norwegian entities are preferring to keep data within EEA borders (or specifically inside Norway) to satisfy Datatilsynet requirements. Moving your DB to a local provider like CoolVDS isn't just technical optimization; it's risk management.

The Cutover

Once your CoolVDS instance is synced (0 seconds behind master):

1. Put your web application into maintenance mode.
2. Set the legacy database to Read-Only (`SET GLOBAL read_only = ON;`).
3. Ensure the slave has processed the final transaction.
4. Update your application config to point to the new CoolVDS IP.
5. Promote the slave (`STOP SLAVE; RESET MASTER;`).
6. Bring the application back up.

Total downtime? Roughly 2 minutes.

The difference in performance on the other side, thanks to NVMe storage and KVM isolation, will be immediately noticeable. Your disk I/O wait times will vanish, and your CPU load will likely drop by half.

Don't let legacy hardware hold your schema hostage. Spin up a test instance on CoolVDS today and benchmark the I/O yourself.