The Art of Moving Data Without Losing Sleep (or Packets)

There are two types of system administrators: those who obsessively verify their backups, and those who have never truly felt the icy grip of panic when a DROP TABLE runs on production. I’ve been in the trenches for over a decade, and if there is one thing that keeps me awake at night, it’s database migration.

It sounds simple on paper. Export. Transfer. Import. But when you are dealing with a 50GB Magento database serving customers from Oslo to Tromsø, "simple" turns into a nightmare of table locks, timeout errors, and angry CEOs asking why the checkout is spinning. In 2018, with the GDPR axe hanging over our heads, we cannot afford sloppy data handling.

This guide isn't for the hobbyist running a Wordpress blog on a shared host. This is for the professionals who need to move gigabytes of transactional data with near-zero downtime. We are going to look at how to do this properly using replication, why hardware latency is your biggest enemy, and how to leverage the CoolVDS infrastructure to make this painless.

The Hardware Bottleneck: Why Your Old VPS Is Choking

Before we touch a single configuration file, let's talk about I/O. The most common failure point in migration isn't the network; it's the disk.

When you import a large SQL dump, your disk I/O hits 100%. If you are on a standard spinning HDD or a crowded SATA SSD node, your IOPS (Input/Output Operations Per Second) will hit a wall. I recently audited a migration for a client moving from a legacy host in Germany. Their import was taking 14 hours because of "noisy neighbors" stealing I/O cycles.

We moved the target to a CoolVDS NVMe instance. The import time dropped to 45 minutes. Why? Because NVMe (Non-Volatile Memory Express) talks directly to the CPU via the PCIe bus, bypassing the SATA bottleneck. In 2018, deploying a database on anything less than NVMe is professional negligence.

The Strategy: Replication, Not Just Dump-and-Restore

The `mysqldump` utility is fine for backups, but for migration, it requires downtime. The moment you start the dump, you have to lock writes, or you get data inconsistency. For a high-traffic site, shutting down for hours is not an option.

The solution is Master-Slave Replication. We set up the new CoolVDS server as a slave to your current live server. They sync up, and when they are identical, we flip the switch. That reduces downtime from hours to seconds.

Step 1: Preparing the Master (Current Server)

First, ensure your current server has binary logging enabled. Open your /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu) and check these lines:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server_id = 1
expire_logs_days = 7
binlog_format = ROW
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Note: If you change this, you need a restart. If you can't restart, you are stuck with the dump method. Plan your maintenance window accordingly.

Step 2: The Non-Blocking Snapshot

Instead of locking everything, we use Percona XtraBackup. It’s open-source and allows us to take a hot backup of InnoDB tables without locking the database. It is the gold standard for MySQL backups in 2018.

# Install Percona XtraBackup (CentOS 7 example)
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-24

# Create the backup stream and pipe it directly to the new CoolVDS server
innobackupex --stream=xbstream /tmp | ssh user@new-coolvds-ip "xbstream -x -C /var/lib/mysql_temp/"

This command streams the backup directly to your new server over SSH. No need to save a massive file locally first. Efficient and fast.

Step 3: Configuring the Slave (CoolVDS)

On your new CoolVDS instance, you need to prepare the data. Ensure your my.cnf is tuned for the specific resources you purchased. If you have 8GB of RAM, don't leave the defaults.

[mysqld]
server_id = 2
# Allocate 70-80% of RAM to the pool on a dedicated DB server
innodb_buffer_pool_size = 6G 
innodb_log_file_size = 512M
innodb_io_capacity = 2000 # Increase this for NVMe! Default is too low.

Pro Tip: On CoolVDS NVMe instances, you can safely crank up innodb_io_capacity to 2000 or higher. The drives can handle it. Standard cloud VPS limits usually choke around 500-800.

Step 4: Starting Replication

Once the data is restored on the new server, check the xtrabackup_binlog_info file created by Percona. It contains the coordinates you need.

CHANGE MASTER TO
MASTER_HOST='192.0.2.10', 
MASTER_USER='repl_user', 
MASTER_PASSWORD='SecurePassword123!', 
MASTER_LOG_FILE='mysql-bin.000004', 
MASTER_LOG_POS=45678;

START SLAVE;

Run SHOW SLAVE STATUS \G and watch the Seconds_Behind_Master drop to zero. Once it hits zero, your new server is a perfect real-time mirror of your old one.

The Norwegian Context: GDPR and Latency

Why bother moving to a Norwegian provider like CoolVDS? Two reasons: Physics and Law.

1. Physics (Latency): If your customers are in Oslo, serving them from a datacenter in Frankfurt adds 20-30ms of round-trip time (RTT). For a database-heavy application making 50 queries per page load, that latency compounds. Hosting at a local facility with direct peering to NIX (Norwegian Internet Exchange) keeps that latency under 5ms.

2. Law (GDPR): Since May 25th of this year, GDPR has changed the landscape. Datatilsynet (The Norwegian Data Protection Authority) is watching. Storing user data within Norwegian borders simplifies your compliance posture significantly. You know exactly where the physical drives are. With some global cloud giants, "EU Region" can sometimes be a nebulous concept.

The Switchover

When you are ready to cut over:

1. Put your application in "Maintenance Mode" (stops new writes).
2. Wait for the Slave to process the last few events (Seconds_Behind_Master = 0).
3. Promote the Slave: Run STOP SLAVE; RESET MASTER; on the CoolVDS box.
4. Update your application's `config.php` or connection string to point to the new IP.
5. Open the gates.

This process usually takes less than 60 seconds. Your users might notice a momentary blip, but they won't see a "Under Maintenance" page for hours.

Final Thoughts

Database migration is high-stakes, but with the right architecture, it’s manageable. Don't let slow hardware dictate your maintenance windows. Use replication, respect the data locality, and leverage modern NVMe storage.

If you are planning a migration and need a target that can handle high write loads without sweating, spin up a high-performance instance on CoolVDS. The network stability and disk speed speak for themselves.