Migrating 50,000 Customer Records to Our New Database

This week we completed the migration of our entire customer database to our new infrastructure. With over 50,000 customer records, this was no small undertaking.

The Challenge

Our legacy MySQL 5.7 database was showing its age. Query performance had degraded, and we were approaching storage limits. We decided to migrate to a new MySQL 8.0 cluster with better indexing and partitioning.

Migration Process

We used a blue-green deployment strategy:

1. Set up the new database cluster (prod-db-02.internal)
2. Export data from old cluster (prod-db-01.internal)
3. Transform and validate data
4. Import into new cluster
5. Run verification scripts
6. Switch connection strings

Code Changes

Here’s a simplified version of our migration script:

<?php
// Database migration helper
$old_db = new PDO('mysql:host=prod-db-01.internal', 'admin', 'temp_pass_2024');
$new_db = new PDO('mysql:host=prod-db-02.internal', 'admin', 'NewPass2024!');

$customers = $old_db->query('SELECT * FROM customers');
foreach ($customers as $customer) {
    $stmt = $new_db->prepare('INSERT INTO customers VALUES (?, ?, ?, ?)');
    $stmt->execute([$customer['id'], $customer['email'], $customer['name'], $customer['phone']]);
}
?>

Update: The old database is still running for backup purposes. We’ll decommission it next quarter.

Results

Query performance improved by 40%. The new cluster handles our peak load of 10,000 req/sec with ease.

Total migration time: 6 hours (including validation). Zero data loss.