Database Migration Without the Heart Attack: A Guide for the Paranoiac

Let’s be honest: "Maintenance Mode" is a failure of imagination. If you are telling your client that their e-commerce store needs to go dark for four hours while you SCP a 50GB SQL dump across the Atlantic, you aren't just losing money—you're admitting defeat. In the high-stakes world of systems administration, downtime is the enemy. It is the thing we wake up in a cold sweat about.

I've seen too many competent sysadmins melt down because they underestimated I/O wait times on a shared hosting environment during a migration. They start the import, the disk queue spikes to 100%, and suddenly the "quick 30-minute swap" turns into an all-nighter fueled by stale coffee and panic.

Today, we are going to look at how to execute a migration for a high-traffic MySQL backend (5.5 or 5.6) with near-zero downtime. We aren't just moving files; we are setting up a replication stream, syncing the delta, and promoting the slave. And we are going to do it keeping the specific constraints of the Norwegian infrastructure landscape in mind—because latency to NIX (Norwegian Internet Exchange) matters more than you think.

The Architecture of Trust: KVM vs. The World

Before we touch a single config file, we need to talk about where you are landing. In 2013, the hosting market is flooded with cheap OpenVZ containers. They promise the moon, but when you try to run a heavy innodb_buffer_pool or perform a massive write operation (like a restore), you hit the "noisy neighbor" wall. The host kernel chokes, and your import stalls.

For a database, you need guaranteed resources. This is why I exclusively deploy on KVM (Kernel-based Virtual Machine) instances, like the ones provided by CoolVDS. With KVM, your RAM is your RAM. Your disk scheduler is yours to break. When we are dealing with the kind of I/O throughput required for a live migration, anything less than full virtualization is a liability.

The Strategy: Replication, Not Just Restoration

The naive approach is: mysqldump > Transfer > Restore. This works for a blog with ten visitors. It does not work for a Magento store pushing transactions every second. The professional approach utilizes Percona XtraBackup and MySQL's native replication.

Step 1: The Non-Blocking Snapshot

First, we need a consistent snapshot of the master database without locking the tables for hours. mysqldump locks tables. innobackupex does not.

On your source server (Master):

While this runs, check your I/O status. If you are on a legacy HDD VPS, watch your iostat -x 1. You'll likely see %util hitting 90%+. This is where having the Pure SSD storage arrays found on CoolVDS instances changes the game. The random read/write speeds of SSDs mean your backup finishes in minutes, not hours, without starving the web server of I/O cycles.

Step 2: Transfer and Restore

Move the data to your new CoolVDS instance. We use rsync or scp, but ensure you are running this inside a screen session. Connections drop. Don't let your transfer die with your SSH session.

On the destination server (Slave), prepare the backup:

Once applied, move it back to the data directory:

Step 3: Configuration Optimization

Before you start the MySQL service on the new node, optimize your my.cnf. Most defaults in RHEL/CentOS 6 are garbage for modern hardware.

Pro Tip: Do not blindly copy your old my.cnf. Your new CoolVDS server likely has more RAM. Adjust your innodb_buffer_pool_size to 70-80% of available RAM. If you have 8GB RAM, give InnoDB 6GB.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Optimization for ACID compliance and Speed
innodb_buffer_pool_size=6G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=1 # Keep this 1 for safety, 2 for speed if you trust your UPS
innodb_file_per_table=1

# Replication Settings
server-id=2
log_bin=mysql-bin
relay-log=mysql-relay-bin
read_only=1

The