Importing Big Databases with MAMP & phpMyAdmin on OSX

When you are using phpMyadmin on MAMP or MAMP Pro out of the box, you might run into trouble trying to import databases.

There are a couple of problems:

  • MAMP’s PHP has a conservative limit to amount of data you can POST in a request (32Mb).
  • MAMP’s PHP has a conservative limit on the size of uploads in general (32Mb).
  • MAMP’s PHP has a conservative memory allowance by default (8Mb)
  • MAMP’s PHP has a short timeout value for scripts and pages (30 seconds)

These will manifest themselves in errors such as:

“ You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit. ”

or

“Script timeout passed, if you want to finish import, please resubmit same file and import will resume.”

I was recently encountering these problems when trying to import a 170mb .sql file. It is easily fixed. First you have to find out what version of PHP your MAMP installation is using. You can do this by opening MAMP, going to “Preferences” and clicking “PHP” along the top. This will indicate your PHP version (mine is 5.5.10). Alternatively, when MAMP is open you can click “Open Start Page” and click “PHPInfo” along the top navigation bar. This will also tell you the version.

Once you have your version, open Finder, press [Shift][Command][G] and enter /Applications/MAMP/bin/php/php5.5.10/conf/. You will see a php.ini file. Open it in your favourite text editor. You are going to change the values of the following variables:

; Maximum size of POST data that PHP will accept.
post_max_size = 256M

; Maximum allowed size for uploaded files.
upload_max_filesize = 256M

; Maximum execution time of each script, in seconds
max_execution_time = 600     

; Maximum amount of time each script may spend parsing request data
max_input_time = 600

; Maximum amount of memory a script may consume (8MB)
memory_limit = 512M      

It is important to note that for the variables related to size - post_max_size, upload_max_size and memory_limit - you must change the variable to a size larger than that of the .sql file that you are trying to import. My .sql file was 170mb but if you are importing a larger .sql file you need to set the memory-related variables to an appropriately large value.

Now restart MAMP and you should no longer be experiencing the problems.


MAMP Not Starting With Yosemite?

As @gavintyte has kindly pointed out, if you are running Yosemite, you may also have to rename /Applications/MAMP/Library/bin/envvars to /Applications/MAMP/Library/bin/_envvars.

This Stackoverflow question & answer has more information about this issue.


Still Not Working?

@gavintyte pointed out that to be able to import his .sql file, he had to edit /Applications/MAMP/bin/startMysql.sh and add --max_allowed_packet=500M to the end of the command:

/Applications/MAMP/Library/bin/mysqld_safe
	--port=8889
	--socket=/Applications/MAMP/tmp/mysql/mysql.sock
	--pid-file=/Applications/MAMP/tmp/mysql/mysql.pid
	--log-error=/Applications/MAMP/logs/mysql_error_log
	--max_allowed_packet=500M &

Again where 500M is a value larger than your database file. There is some more information about this on Stackoverflow


Still STILL Not Working?

Luke Kowald emailed me to mention that he still couldn’t get larger uploads working with any of above fixes. I haven’t experienced this but the following answer from Stackoverflow worked for him:

Why have editing mamps php.ini no effect

It seems that MAMP uses templates php.ini files from /Applications/MAMP PRO/MAMP PRO.app/Contents/Resources/ that can overwrite your updated values in /Applications/MAMP/bin/php/phpX.X.XX/conf/php.ini