Don't Let a Migration Kill Your Uptime

I have seen grown men cry over corrupted InnoDB tablespaces. It usually happens at 3:00 AM on a Tuesday. The client wanted to save money on hosting, picked a budget provider with spinning rust (HDD), and then wondered why their Magento store locked up during a traffic spike.

Now you are the one tasked with moving that mess to a proper environment. Migrating a static website is trivial. Rsync and done. Migrating a live, transactional database with gigabytes of data while keeping the service running? That is open-heart surgery.

In this guide, we are going to look at how to move a MySQL 5.7 (or MariaDB 10.1) database from a legacy host to a high-performance CoolVDS instance. We are prioritizing data integrity and near-zero downtime. We will focus on the Norwegian context—latency to NIX (Norwegian Internet Exchange) and compliance with Datatilsynet guidelines.

The Bottleneck is Always I/O

Before we touch a single command, you need to understand why most migrations fail. It is not usually network bandwidth; it is Disk I/O. When you dump a database, you are hammering the disk. When you import it, you are hammering it harder.

If you are migrating to a VPS that shares resources with noisy neighbors, your import might stall. This is why we use KVM virtualization at CoolVDS. Unlike OpenVZ, where RAM is often oversold, KVM gives you dedicated kernel resources. More importantly, we run on NVMe storage. In 2017, if you are running a database on standard SSDs or, god forbid, SAS drives, you are voluntarily bottlenecking your throughput. NVMe changes the game for iowait.

Step 1: The Pre-Flight Check

Do not start a migration without checking your variables. Login to your current server and check the my.cnf.

# Check current buffer pool size
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

If your dataset is 10GB and your buffer pool is 128MB, your performance is garbage. On your destination CoolVDS server, ensure your configuration matches your available RAM. A good rule of thumb is setting innodb_buffer_pool_size to 60-70% of total system RAM if it's a dedicated database server.

Latency Matters

If your target audience is in Oslo or Bergen, hosting in Frankfurt or London adds unnecessary milliseconds. Physics is undefeated. Ping your target IP.

ping -c 5 185.x.x.x # Your CoolVDS IP

From within Norway, you should see single-digit latency. This matters for replication lag if you are doing a master-slave migration.

Step 2: The Strategy (Master-Slave Replication)

For a small database (under 500MB), mysqldump is fine. Take the site offline, dump, move, import, bring online. Total downtime: 10 minutes.

For anything serious (10GB+), we use Replication. This allows us to sync data while the old site is still live.

1. Configure the Master (Old Server)

Edit /etc/mysql/my.cnf. You need a unique server ID and binary logging enabled.

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name

Restart MySQL. Create a replication user. Do not use the root user.

CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Security Warning: Opening port 3306 to the world is reckless. We will use an SSH tunnel later so we don't have to expose this port publicly.

2. Dump the Data

We need a consistent snapshot. If you use mysqldump, use the --single-transaction flag to avoid locking InnoDB tables.

mysqldump --single-transaction --master-data=2 -u root -p your_database_name | gzip > dump.sql.gz

The --master-data=2 flag is critical. It writes the binary log coordinates into the dump file, so the slave knows exactly where to start replicating.

3. Transfer to CoolVDS

Use scp or rsync. Since CoolVDS provides high bandwidth, this will be fast.

scp dump.sql.gz root@your-coolvds-ip:/root/

Step 3: The Import and Sync

On your new CoolVDS instance (the Slave), configure my.cnf with server-id = 2 and restart.

Import the data:

zcat dump.sql.gz | mysql -u root -p your_database_name

Now, set up the secure tunnel. We don't want replication traffic going over the public internet in cleartext. This is crucial for GDPR compliance preparations.

ssh -N -L 3307:127.0.0.1:3306 root@old-server-ip

This forwards the local port 3307 on your CoolVDS box to port 3306 on the old server. Now configure the slave:

CHANGE MASTER TO 
MASTER_HOST='127.0.0.1', 
MASTER_PORT=3307, 
MASTER_USER='replicator', 
MASTER_PASSWORD='StrongPassword123!', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=12345; -- Get these values from the head of your dump file

Start the slave:

START SLAVE;
SHOW SLAVE STATUS \G

Check for Seconds_Behind_Master. It should drop to zero. Once it does, your two databases are identical in real-time.

Step 4: The Switchover

You now have a live copy on CoolVDS. The cutover is simple:

1. Put your application in "Maintenance Mode" (stops writes).
2. Wait for the slave to process remaining events (usually takes less than 1 second on our NVMe infrastructure).
3. Stop the slave: STOP SLAVE;
4. Update your application's config to point to localhost (the new CoolVDS server).
5. Turn off Maintenance Mode.

Total downtime: roughly 30 seconds.

Why Infrastructure Breaks Projects

You can execute these commands perfectly, but if the underlying hardware waits on I/O, you will fail. I have debugged servers where high I/O wait caused the replication thread to lag hours behind the master.

This is why we built CoolVDS with a focus on raw performance. We don't over-provision. We use enterprise NVMe drives that handle the random read/write patterns of heavy databases effortlessly. When you are dealing with Norwegian client data, you also have to think about data sovereignty. Hosting on a VPS physically located in Norway (or closely peered in Europe) simplifies your life with Datatilsynet.

Don't risk your reputation on cheap hosting. Database consistency is not a place to cut corners.

Next Steps

Ready to test your migration plan? Spin up a CoolVDS instance today. We offer hourly billing, so you can benchmark your import speeds for a few kroner before committing to a monthly plan. If you need help tuning your my.cnf for our hardware, our support team speaks fluent Linux.