The Art of the Zero-Downtime MySQL Migration: A DevOps Survival Guide
There is nothing quite as soul-crushing as watching a progress bar stuck at 99% while your CEO screams that the checkout page is returning a 503 Service Unavailable. If you are still putting up a static HTML "Under Maintenance" page in 2013, you are doing it wrong. In the high-velocity world of Norwegian e-commerce, downtime isn't just an inconvenience; it's a breach of trust.
Whether you are moving from a legacy bare-metal box to a scalable VPS or escaping a hosting provider that overloads their OpenVZ nodes, migration is inevitable. I have spent the last three weeks migrating a high-traffic media portal from an old rack in Nydalen to a proper cloud setup. Here is what I learned, what broke, and how to do it without losing a single row of data.
The Bottleneck is Always I/O
Before we touch a single config file, let’s talk about physics. Database migration is fundamentally an I/O operation. You are reading from disk A and writing to disk B. If your target host is running standard SATA drives in a RAID 10, you are going to suffer.
When we test database imports on CoolVDS, we see a massive difference because of the underlying SSD storage. In 2013, SSDs are no longer a luxury; for databases, they are a requirement. If your disk queue length spikes during the import, your transaction log syncs will hang, and your "quick" migration becomes an all-nighter.
Pro Tip: Always runiostat -x 1on your target server before starting the migration. If%utilis hovering above 5% on an idle system, abort. You have a noisy neighbor, and your database will crawl. This is why we prefer KVM virtualization at CoolVDS—hardware isolation matters.
Strategy 1: The "Poor Man's" Dump (Small Datasets)
If your database is under 2GB, you don't need complex replication. You need mysqldump, but you probably aren't using the right flags. The default flags lock tables, which kills your live site.
Use this command to dump InnoDB tables without locking the world:
mysqldump -u root -p --opt --single-transaction --quick --max_allowed_packet=128M db_name | gzip > db_backup_2013.sql.gz
Why these flags?
--single-transaction: Creates a consistent snapshot without locking the entire database (InnoDB only).--quick: Forcesmysqldumpto retrieve rows one by one rather than retrieving the whole result set and buffering it in memory. Essential for limited RAM VPS environments.
Strategy 2: The Master-Slave Flip (The Professional Choice)
For anything larger than 10GB, or if you cannot afford downtime, you must use replication. The idea is simple: turn your new CoolVDS server into a "Slave" of your current "Master," let them sync up, and then flip the switch.
Step 1: Configure the Master (Current Server)
Open your /etc/my.cnf. You need to enable binary logging and set a unique server ID. If you haven't done this yet, you will need a restart (yes, the only required downtime).
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = target_database
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
Step 2: Snapshot and Position
We need to know the exact point in the binary log where our snapshot is taken. Enter the MySQL console:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Record the File and Position values. Do not close this terminal window, or the lock will release. Open a new terminal to perform the actual data dump using rsync or mysqldump.
Step 3: Transfer to the New Host
Transfer the data securely. Since we are dealing with data governed by the Norwegian Personopplysningsloven (Personal Data Act), do not use FTP. Use SCP or rsync over SSH.
rsync -avzP /var/lib/mysql/ root@new-server-ip:/var/lib/mysql/
Step 4: Configure the Slave (CoolVDS)
On your new server, update my.cnf:
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
innodb_buffer_pool_size = 2G # Adjust to 70% of your VPS RAM
Step 5: Start Replication
Once the data is restored on the new server, log into MySQL and tell it where to look for updates:
CHANGE MASTER TO
MASTER_HOST='192.168.1.50',
MASTER_USER='replication_user',
MASTER_PASSWORD='SecurePassword123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS= 107;
START SLAVE;
Check the status with SHOW SLAVE STATUSG. You want to see Slave_IO_Running: Yes and Slave_SQL_Running: Yes. If you see Seconds_Behind_Master dropping to 0, you are in sync.
The "Switchover" Moment
Once the slave is caught up, the switch is painless:
- Update your application code (PHP/Java/Python) to point to the new IP.
- Lock the old Master (optional but recommended to prevent split-brain):
FLUSH TABLES WITH READ LOCK; - Wait for the Slave to process the final events.
- Promote the Slave to Master:
STOP SLAVE; RESET MASTER;
Your users won't even notice a blip. We recently executed this for a client migrating to our Oslo node, ensuring their data stayed within Norwegian borders to satisfy local compliance requirements.
A Note on Kernel Tuning
A fresh CentOS 6 install is rarely optimized for high-throughput databases. Before you go live, tweak your sysctl.conf to handle more connections.
# /etc/sysctl.conf
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 1200
net.core.somaxconn = 65535
vm.swappiness = 0
Setting swappiness to 0 tells the kernel to avoid swapping out RAM to disk at all costs. On a database server, swapping is death.
Why Infrastructure Matters
You can script the perfect migration, but if the destination hardware is weak, you are just migrating to a new problem. In 2013, the difference between a cheap VPS provideroverselling their RAM and a provider like CoolVDS using dedicated KVM resources is night and day.
When you are running a database, you need guaranteed CPU cycles and fast random read/write speeds. Our SSD-backed instances are designed specifically for this workload. Don't risk your data integrity on spinning rust.
Ready to upgrade your infrastructure? Stop fighting with slow I/O. Deploy a high-performance SSD VPS on CoolVDS today and experience what raw speed feels like.