Garrett Bjerkhoel

This past week I migrated a production application over from a shared server, over to a dedicated server. I had to move over quite a bit of stuff, the database, thousands of images, permissions, etc. When you're used to moving small databases around, you usually don't run into too many problems other than a incomplete file or just something that takes 5 minutes to fix.

This database wasn't like small databases, it was gigantic, just one of it's tables had roughly 12,000,000 rows. Initially I wrote up a mysqldump like so

mysqldump -u root -p app_production > ~/app_production.sql

In the end it was ~800mb, so I went ahead and used scp to get that file onto my computer, so I could again use scp to move it through the VPN to the dedicated server. After all was copied, I tried to import it into the new production database with

mysql -u root -p app_production < ~/app_production.sql

When I tried to evaluate the query after it failed, it turned out to be a 1,000,000+ row insert that made it fail every time I tried to import it. After looking around a bit, a co-worker sent me this, which had a list of all the flags the mysqldump command has (you can also check it's man page).

So in the end, this is the command that exported a complete insert for insert database:

// -e: Allows utilization of the new, much faster INSERT syntax.
// -c: Use complete insert statements. 
mysqldump -e -c -u root -p app_production > ~/app_production.sql
gzip app_production.sql

In the end it was 1.9GB, twice as large, it then needed to be compressed so I used gzip. Here is the before and after:

// Before
INSERT INTO `addresses` ('kPJgZbpHyRd1'), ('yMVqfRuRkp0h'), [..];

// After
INSERT INTO `addresses` ('key') VALUES ('kPJgZbpHyRd1');
INSERT INTO `addresses` ('key') VALUES ('yMVqfRuRkp0h');
INSERT INTO `addresses` ('key') VALUES ('GfAuffA1fyy0');
INSERT INTO `addresses` ('key') VALUES ('yd5UJ5MlXG4g');

So lesson learned, it's easier for MySQL to take bits and pieces at a time, rather than a million row insert. Also to note, rather than going from, Origin Server → You → Dest. Server, log into the Dest. Server and pull the file straight from the Origin Server. Server between server speeds were insanely fast, it was moving at 7.1MB/s compared to the 200KB/s at first.