Load Large Database into MAMP

Recently I’ve needed to create a development version of our Magento website so I can work on / break things and not break the live site. The phpMyAdmin in MAMP does not like large database imports though, as they will usually fail. This seems to happen especially with Magento databases for me. The simple way to get around this is to not use phpMyAdmin at all for loading the database. Instead, I’ve found loading it directly through mysql works and it’s a LOT faster.

One other tip. Magento DB’s will often give you errors on import due to foreign key collisions. In order to fix these errors open your database export.sql file (be patient Magento DB’s are always hefty) and put

SET FOREIGN_KEY_CHECKS = 0;

at the top and then at the very end of the file

SET FOREIGN_KEY_CHECKS = 1;

After that is done you’re ready to import. Simply adjust the following with the correct path to MAMP (if you’ve customized things) and change the name of your database export as appropriate.

/Applications/MAMP/Library/bin/mysql -u root -p DATABASENAME < ~/Desktop/DBEXPORT.sql

By default MAMP uses “root” as the default username for mysql. If you’ve changed that then change root to the correct user of course. The last part of that is the path to your database export that you’ve saved locally. Adjust that as needed to the correct path.