Stop Praying for Downtime: Scientific Database Migration Strategies
There is nothing quite like the feeling of watching a progress bar stall at 99% during a maintenance window while your CEO breathes down your neck. If you are still migrating databases by shutting down the web server, tarring up /var/lib/mysql, and SCP-ing it across the continent, you are doing it wrong. In 2013, uptime is the only currency that matters.
Whether you are moving away from a congested US provider to improve latency for your Norwegian user base, or scaling up to better hardware, the physics remain the same: Latency kills conversions. Moving your infrastructure closer to the Norwegian Internet Exchange (NIX) in Oslo is smart, but the migration path is fraught with peril. Data corruption, inconsistent states, and the dreaded "split-brain" scenario are real threats.
At CoolVDS, we see developers making the same mistakes repeatedly. They trust the network too much and their disk I/O too little. Today, we are going to look at how to migrate a 50GB+ MySQL 5.5 database from a legacy provider to a high-performance KVM instance in Norway with practically zero downtime.
The Architecture of Trust: Why Norway?
Before we touch the terminal, let's talk about the destination. Why move to Oslo? Aside from the obvious ping reduction (often dropping from 40ms to <5ms for local users), there is the legal landscape. With the NSA revelations dominating the news cycle this summer, data sovereignty is no longer just a buzzword—it is a business requirement.
Operating under the Personopplysningsloven (Personal Data Act) and adhering to Datatilsynet's guidelines provides a layer of trust your customers are starting to demand. Furthermore, with the EU debating the proposed General Data Protection Regulation (GDPR), establishing your infrastructure within the EEA now is a prudent move for the pragmatic CTO.
The Strategy: Asynchronous Replication with XtraBackup
Forget mysqldump for anything larger than 1GB. It locks tables, it's slow, and the restore time is agonizing. For a live migration, we will use Percona XtraBackup to create a hot copy and then establish Master-Slave replication to sync the delta.
Step 1: Preparing the Source (The Master)
First, ensure your current server (Master) is ready for replication. Open your my.cnf (usually in /etc/my.cnf on CentOS 6) and ensure binary logging is enabled. You cannot replicate without the binlog.
[mysqld]
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = mixed
# vital for durability
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
If you change these, you need a restart. If you can't restart, you are stuck with a blocking dump. Assuming you are configured, create a replication user:
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'10.0.0.2' IDENTIFIED BY 'StrongPassword123';
mysql> FLUSH PRIVILEGES;Pro Tip: Never use the root user for replication. Also, if you are traversing the public internet, tunnel this traffic over SSH or a VPN. Replication traffic is unencrypted by default in MySQL 5.5!
Step 2: The Hot Copy
We use innobackupex because it allows us to back up InnoDB tables without locking the database. It captures the global transaction ID (GTID) or binary log coordinates exactly when the backup finishes.
# On the Source Server
innobackupex --user=root --password=YOURPASS /var/backups/mysql/Once finished, you will have a directory with a timestamp. Now, apply the logs to make the data consistent:
innobackupex --apply-log /var/backups/mysql/2013-07-12_14-00-00/
Step 3: Transfer to CoolVDS
This is where bandwidth matters. If you are moving to a CoolVDS instance, you are likely benefiting from our 1Gbps uplink. Use rsync to move the data. It is resumable and efficient.
rsync -avzP /var/backups/mysql/2013-07-12_14-00-00/ root@target-server:/var/lib/mysql_import/
Step 4: Configure the Target (The Slave)
On your new CoolVDS server (let's assume it's running a fresh CentOS 6.4 install), stop MySQL and move the data into place. Make sure to set the server-id to 2 in the new my.cnf.
service mysql stop
rm -rf /var/lib/mysql/*
innobackupex --copy-back /var/lib/mysql_import/2013-07-12_14-00-00/
chown -R mysql:mysql /var/lib/mysql
service mysql startNow, check the file xtrabackup_binlog_info inside the backup directory. It contains the coordinates you need.
cat /var/lib/mysql_import/2013-07-12_14-00-00/xtrabackup_binlog_info
# Output example: mysql-bin.000003 4567
Step 5: The Handover
Log into the new database and start the replication.
CHANGE MASTER TO
MASTER_HOST='source_server_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='StrongPassword123',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=4567;
START SLAVE;Check the status with SHOW SLAVE STATUS\G. Look for Seconds_Behind_Master. It should drop to 0 quickly.
| Metric | Standard HDD VPS | CoolVDS SSD VPS |
|---|---|---|
| Import Speed | 5-10 MB/s | 400+ MB/s |
| Replication Lag | High (I/O Bottleneck) | Near Zero |
| IOPS | ~100 | ~50,000+ |
Hardware Matters: The SSD Advantage
You can have the best DBA in the world, but if your disk I/O is saturated, your migration will fail. During the "catch-up" phase of replication, the disk is hammered with write operations. This is where standard spinning rust drives fail.
At CoolVDS, we have standardized on enterprise SSD arrays. While some providers are just starting to talk about PCIe flash or emerging NVMe storage technologies in their labs, we are delivering high-performance SSD storage today in production. This low latency is critical not just for the database, but for the OS overhead.
When you are running high-traffic sites (think Magento or heavy Drupal installations), CPU stealing on shared hosts is a silent killer. We use KVM (Kernel-based Virtual Machine) to ensure that the RAM and CPU cores you pay for are actually yours. No overselling, no noisy neighbors.
Final Cutover
Once the slave is in sync:
- Put your web application in maintenance mode (or Read-Only).
- Ensure
Seconds_Behind_Masteris 0. - Point your application config to the new CoolVDS IP.
- Stop the slave:
STOP SLAVE; - Promote the slave to master (ensure
read_only = 0). - Bring the app back online.
Total downtime? Usually less than 60 seconds.
Conclusion
Migration is not just about moving data; it is about mitigating risk. By leveraging tools like Percona XtraBackup and hosting on hardware that can actually handle the I/O pressure, you transform a terrifying night shift into a routine procedure.
Don't let slow I/O kill your SEO or your sanity. The Norwegian digital infrastructure is robust, but only if your server can keep up. Deploy a test instance on CoolVDS today and see what dedicated SSD performance does for your replication lag.