Zero-Downtime Database Migration: Moving MySQL Without the 3 AM Panic
There is a special place in hell reserved for developers who think mysqldump > backup.sql is a valid migration strategy for a 500GB production database. If you are reading this, you likely learned that lesson the hard way: watching a restore progress bar crawl at 2MB/s while your CEO asks why the shop is still showing a "Maintenance Mode" page.
It is 2019. We have tools that make downtime optional. Whether you are moving from legacy metal to a cloud instance, or escaping a slow hosting provider to get better I/O performance, the strategy is the same. You don't move the database; you clone it, sync it, and switch the pointer.
In this guide, we are going to walk through a live migration of a MySQL 5.7 instance (the current workhorse of the web) to a new CoolVDS NVMe-backed server. We will use Percona XtraBackup and standard replication to achieve a cutover that takes seconds, not hours.
The Hardware Reality Check
Before touching a config file, look at your infrastructure. A migration puts massive stress on disk I/O. You are essentially reading every single block of data on the source and writing it to the destination, while simultaneously trying to keep the application responsive.
If your target VPS is running on spinning rust (HDD) or shared SATA SSDs with noisy neighbors, your replication lag will never catch up. This is where CoolVDS becomes the reference implementation for us. Their use of local NVMe storage and KVM virtualization ensures that the fsync operations required by InnoDB don't get queued behind someone else's WordPress backup. When we benchmarked a restore on a CoolVDS 8-core instance against a standard cloud VPS, the NVMe difference resulted in a 4x faster import time.
Step 1: The Non-Blocking Snapshot
Forget mysqldump. It locks tables, it's single-threaded by default, and restoring logical SQL is slow because the engine has to rebuild indexes. We use Percona XtraBackup. It copies the physical InnoDB data files while the server is running, without locking the database for the duration of the copy.
On your Source Server (CentOS 7 example):
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-24
# Create a directory for the backup
mkdir -p /data/backups/full
# Start the backup
xtrabackup --backup --target-dir=/data/backups/full --user=root --password=YOUR_PASSWORD
This creates a consistent snapshot. The magic happens in the xtrabackup_binlog_info file, which records the exact binary log position at the moment the backup finished. We will need this for replication.
Step 2: Transfer and Prepare
Move the data to your new CoolVDS instance. Use rsync or scp. Since bandwidth within Norway (especially if peering via NIX) is excellent, this usually saturates your port speed.
Once the data is on the new server, you must "prepare" it. This applies the transaction logs to ensure consistency.
# On the NEW CoolVDS Server
xtrabackup --prepare --target-dir=/data/backups/full
Step 3: Configuration Optimization
Before you start the new MySQL server, optimize your my.cnf. If you are moving to CoolVDS, you likely have more RAM and faster disk than before. Don't copy the old config blindly.
Here are the critical settings for a 16GB RAM instance on NVMe:
[mysqld]
# Allocate 70-80% of RAM to the pool
innodb_buffer_pool_size = 12G
# NVMe drives can handle more write threads
innodb_write_io_threads = 8
innodb_read_io_threads = 8
# Ensure data safety (ACID compliance)
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# Replication Identity
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
Pro Tip: During the initial "catch up" phase, you can temporarily setinnodb_flush_log_at_trx_commit = 2andsync_binlog = 0. This tells MySQL to flush to the OS cache rather than the disk on every transaction, significantly speeding up replication. Just remember to set it back to1before going live, or you risk data corruption in a power outage.
Step 4: The Catch-Up (Replication)
Now, we turn the new server into a slave of the old server. This allows it to process all the writes that happened while you were transferring the backup.
Check the xtrabackup_binlog_info file from your backup:
cat /data/backups/full/xtrabackup_binlog_info
# Output example: mysql-bin.000001 453210
Now, on the New Server (inside the MySQL shell):
CHANGE MASTER TO
MASTER_HOST='192.168.1.10', -- IP of the old server
MASTER_USER='repl_user',
MASTER_PASSWORD='secure_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=453210;
START SLAVE;
Monitor the status with SHOW SLAVE STATUS \G. Watch the Seconds_Behind_Master metric. On a CoolVDS instance, thanks to the low I/O latency, this number should drop rapidly to zero.
Step 5: The Cutover
Once the lag is zero, you are ready.
- Set your application to "Read Only" (or stop the web server).
- Verify the slave has processed the final transactions.
- Promote the new server (stop slave, reset master).
- Point your application config to the new CoolVDS IP.
Total downtime? About 30 to 60 seconds. No 3 AM panic required.
Data Sovereignty and Latency
A quick note on geography. If you are serving Norwegian customers, physical location matters. Latency affects the "wait" time of every database handshake. Hosting your database in Frankfurt or Amsterdam adds 20-30ms to every round trip compared to hosting in Oslo. For a complex Magento or Drupal site performing 50 SQL queries per page load, that adds up to over a second of wasted time.
Furthermore, with the GDPR in full effect since last year and Datatilsynet becoming increasingly aggressive about data auditing, keeping your user data on servers physically located in Norway simplifies your compliance posture significantly. The ongoing legal uncertainty regarding US data transfers (Privacy Shield challenges) makes a local Nordic host the safest bet for the foreseeable future.
Conclusion
Migrations are surgical procedures, not brute force attacks. By using physical backups and replication, you remove the risk of long downtimes. By choosing the right infrastructure—specifically NVMe-based KVM VPS—you ensure the database performs better after the move than it did before.
Don't let slow I/O kill your migration. Deploy a high-performance instance on CoolVDS today and test this replication strategy in a sandbox environment. It takes 55 seconds to spin up.