Don't Let a Migration Kill Your Uptime

I have seen grown men cry over corrupted InnoDB tablespaces. I have watched startups burn through their venture capital because they tried to migrate a 500GB database using a simple dump-and-restore script during peak hours. It’s not pretty.

If you are reading this, you are likely feeling the squeeze. Your current shared hosting is choking on I/O wait, your queries are piling up, and your boss is screaming about "scalability." You need to move. Now.

But here is the reality check: The "maintenance window" is a myth. In 2014, users expect 24/7 availability. If your service goes dark for four hours while rsync crawls over the wire, you lose revenue. If you are hosting here in Norway, you also have Datatilsynet (The Data Inspectorate) breathing down your neck regarding data integrity and location.

We are going to do this the hard way, which is the only right way: Live Master-Slave Replication over an SSH Tunnel.

The Architecture of a Safe Migration

Forget generic FTP transfers. We are dealing with raw block devices and database streams here. The goal is to set up a new CoolVDS instance as a replication slave, let it sync with your struggling master, and then promote it to master with less than a second of downtime.

Step 1: The Environment Check

Before touching a single config file, look at your hardware. Your new target host matters. If you are migrating to a CoolVDS KVM instance, you are already ahead of the curve because we use pure SSD storage. In database operations, IOPS (Input/Output Operations Per Second) are oxygen.

Traditional spinning HDDs (even SAS 15k) cannot handle the random write patterns of a heavy MySQL import without locking up. I recently benchmarked a CoolVDS SSD instance against a standard VPS provider in Oslo:

This difference is why we don't oversell resources. With OpenVZ containers (common elsewhere), a "noisy neighbor" can steal your I/O. With KVM on CoolVDS, your disk allocation is yours.

Step 2: Securing the Pipe

Never expose MySQL (port 3306) to the public internet. It is a security suicide mission. Since we don't have a private LAN between your old provider and CoolVDS, we tunnel.

On your Target (New CoolVDS Server), run this:

# Create a tunnel from the new server to the old server
# syntax: ssh -L local_port:127.0.0.1:remote_port user@old_server_ip

ssh -f -N -L 3307:127.0.0.1:3306 root@192.168.1.50 -p 22

Now, connecting to 127.0.0.1:3307 on your new box actually talks to the database on your old box, fully encrypted. No VPN overhead, no firewall headaches.

Step 3: Preparing the Master (Old Server)

You need a consistent snapshot. If you are running MyISAM tables, stop it. Convert them to InnoDB immediately. MyISAM locks the whole table for writes; InnoDB does row-level locking.

Create a replication user on the Old Server:

CREATE USER 'repl_user'@'127.0.0.1' IDENTIFIED BY 'StrongPassword!2014';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'127.0.0.1';
FLUSH PRIVILEGES;

The Dump

Use mysqldump with the --master-data flag. This appends the binary log coordinates to the dump file, so the slave knows exactly where to start.

mysqldump --single-transaction --quick --master-data=2 --routines --triggers --all-databases -u root -p > /root/full_dump.sql

Pro Tip: If your database is over 50GB, skip mysqldump and use Percona XtraBackup. It works at the filesystem level and is significantly faster for restores. But for most standard CMS installs (WordPress, Magento, Joomla), mysqldump on an SSD is sufficient.

Step 4: Configuring the Target (CoolVDS)

Before you import, you must tune the MySQL configuration file (/etc/my.cnf). The defaults in CentOS 6 are intended for servers with 512MB RAM. They are garbage for modern workloads.

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

[mysqld]
server-id = 2

# CRITICAL for performance
# Set to 70-80% of available RAM
innodb_buffer_pool_size = 4G 

# Improves write speed during import, but set back to 1 for safety later
innodb_flush_log_at_trx_commit = 2 

# Per-thread buffers - keep these conservative to avoid OOM
sort_buffer_size = 2M
read_buffer_size = 2M

max_connections = 200

Transfer the dump using scp (which runs over SSH):

scp -C root@192.168.1.50:/root/full_dump.sql /root/

Import it:

mysql -u root -p < /root/full_dump.sql

Step 5: Starting Replication

Open your dump file using head to find the coordinates:

head -n 50 /root/full_dump.sql | grep "MASTER_LOG_POS"

You will see something like: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

On your New CoolVDS Server (via the SSH tunnel port 3307):

CHANGE MASTER TO 
MASTER_HOST='127.0.0.1', 
MASTER_PORT=3307, 
MASTER_USER='repl_user', 
MASTER_PASSWORD='StrongPassword!2014', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=107;

START SLAVE;

Check the status with SHOW SLAVE STATUS \G. Look for Seconds_Behind_Master. It should drop to 0 quickly if your network between the old host and our NIX-connected datacenter is clean.

The Switchover

Once the slave is caught up, you are ready. This is the only time you need to pause writes.

1. Put your web application in "Maintenance Mode".
2. Verify Seconds_Behind_Master is 0.
3. Stop the slave: STOP SLAVE;
4. Update your application config (wp-config.php, local.xml, etc.) to point to the new CoolVDS IP.
5. Turn off Maintenance Mode.

Total downtime? Maybe 30 seconds. No data lost. No corrupt tables.

Why This Matters for Norwegian Business

Under the Personal Data Act (Personopplysningsloven), you are responsible for the security and integrity of user data. A failed migration that results in data loss isn't just an IT problem; it's a legal one. Furthermore, with the EU discussing stricter data regulations (the proposed General Data Protection Regulation), relying on US-based "Safe Harbor" hosting is becoming a gamble.

Hosting on CoolVDS keeps your data on Norwegian soil, protected by our laws, and running on hardware that doesn't choke when you hit the enter key.

Ready to stop fighting your server?

Don't let slow I/O kill your SEO. Deploy a test instance on CoolVDS in 55 seconds and see the difference pure SSD makes.