Stop Praying, Start Planning: A Real-World Database Migration Strategy

I have watched grown men cry over corrupted InnoDB tablespaces. I’ve seen a "simple" 50GB database import take 14 hours because the target server was throttled by a noisy neighbor on a cheap shared host. If you are reading this, you probably have a database that has outgrown its current home, and you are terrified of the downtime required to move it.

Good. Fear keeps you sharp.

With the GDPR enforcement date looming in May 2018, many of you are frantically moving data from US-based clouds back to European soil. Norway, with its EEA membership and strict adherence to Datatilsynet guidelines, is becoming the bunker of choice. But moving data isn't just about `scp`. It's about IOPS, latency, and consistency.

This guide isn't for hobbyists running WordPress blogs on shared hosting. This is for the DevOps engineer who needs to migrate a high-transaction MySQL 5.7 or MariaDB 10.2 cluster without killing the business.

The Bottleneck is Always Disk I/O

Let’s be honest about hardware. In 2018, if your hosting provider is still selling you spinning rust (HDD) or even cheap SATA SSDs for database workloads, they are robbing you. When you trigger a massive import or catch up on replication, your disk queues will explode.

This is why we benchmark. On a standard cloud instance, you might get 5,000 IOPS. On a CoolVDS instance backed by NVMe, we regularly see 20,000+ random write IOPS. That is the difference between an import taking 4 hours and it taking 20 minutes.

The Strategy: Replication, Not "Dump and Restore"

The traditional method of `mysqldump` > `gzip` > `scp` > `gunzip` > `mysql < dump.sql` is obsolete for anything larger than 1GB. It requires too much downtime. The only professional way to migrate in 2018 is setting up a Master-Slave replication topology, letting the new server catch up, and then promoting it to Master.

Phase 1: Tuning the Target (The CoolVDS Instance)

Before moving a single byte, you must configure the destination server to accept a write storm. The default `my.cnf` on most Linux distros is garbage.

Pro Tip: Temporarily relax ACID compliance during the initial load. It speeds up imports by 300%. Just remember to revert these settings before production traffic hits.

Edit your /etc/my.cnf on the new server:

[mysqld]
# MAX PERFORMANCE FOR IMPORT
innodb_buffer_pool_size = 6G  # Set to 70% of your RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0 # DANGEROUS: Only use during import phase!
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 8
innodb_read_io_threads = 8
# DISABLING BINLOGS LOCALLY SPEEDS UP IMPORT
skip-log-bin 

Phase 2: The Consistent Snapshot

Don't lock your live tables. We use Percona XtraBackup. It creates a physical backup of the data files while the server runs, without locking the database for reads or writes.

Install it (assuming CentOS 7):

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install percona-xtrabackup-24

Run the backup on the source server:

# Create the backup stream and pipe it directly to the new server via SSH
# This avoids writing to the local disk which might already be full
innobackupex --stream=xbstream --parallel=4 ./ | ssh user@new-coolvds-ip "xbstream -x -C /var/lib/mysql/backup/"

This command uses standard system tools available in 2018. Note the `--parallel=4`. If you are on a CoolVDS KVM slice, you have dedicated CPU cores. Use them.

Phase 3: Prepare and Sync

Once the data is on the new server, you need to prepare the backup (apply the transaction logs) so MySQL can start.

innobackupex --apply-log /var/lib/mysql/backup/

Now, change ownership and move it into place:

chown -R mysql:mysql /var/lib/mysql/backup/
rsync -avP /var/lib/mysql/backup/ /var/lib/mysql/

Phase 4: Establishing Replication

This is where the magic happens. XtraBackup creates a file called xtrabackup_binlog_info. It contains the exact binary log coordinates at the moment the snapshot finished.

cat /var/lib/mysql/xtrabackup_binlog_info
# Output: mysql-bin.000456  982374

On your new CoolVDS server (the Slave), execute:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.50', # IP of your old server
  MASTER_USER='replication_user',
  MASTER_PASSWORD='SecurePassword2018!',
  MASTER_LOG_FILE='mysql-bin.000456',
  MASTER_LOG_POS=982374;
START SLAVE;

Phase 5: The Cutover

Watch `SHOW SLAVE STATUS \G`. When `Seconds_Behind_Master` hits 0, your new server is a real-time mirror of the old one.

To switch over:

1. Set the old server to Read-Only (`SET GLOBAL read_only = 1;`).
2. Wait for the slave to catch the final transactions (usually takes 200ms on our network).
3. Point your application config (PHP/Python/Java) to the new IP.
4. Stop the slave and make it writable (`STOP SLAVE; SET GLOBAL read_only = 0;`).

The GDPR Angle (Why Location Matters)

With the General Data Protection Regulation coming into full effect in May, where your data sits physically is now a legal question. Hosting in Norway offers a unique advantage. We are EEA members, fully compliant with EU directives, but outside the direct jurisdiction of some more invasive surveillance apparatuses found elsewhere.

Furthermore, latency matters. If your dev team is in Oslo or Berlin, ping times to CoolVDS data centers are typically under 20ms via the NIX (Norwegian Internet Exchange). Moving your database here isn't just about compliance; it's about making your backend feel snappy.

Summary

Migrations fail because of poor IOPS planning and lack of testing. By using physical backups (XtraBackup) instead of logical dumps, and leveraging the high throughput of NVMe storage, you reduce the risk window significantly.

Don't let your infrastructure be the bottleneck. If you need a test environment to practice this replication strategy, spin up a high-performance instance today.

Ready to migrate? Deploy a CoolVDS NVMe instance in 55 seconds and see what real I/O speed looks like.