Posted on 01/30/2010 in Coding
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.