Import Large MySQL Database in phpMyAdmin in MAMP

MAMP is free software you can use to install localhost environment for testing and developing WordPress sites. In our earlier article, we have explained how to move live WordPress site to your MacBook using MAMP. One of the important steps in moving the live site is to upload your database into MAMP server. It will work seamlessly when you have smaller database. However, uploading large database of few hundreds MB in size will not work through phpMyAdmin. In this article, let us explain how to upload large MySQL database in MAMP using Terminal app in macOS.

Uploading Limitations in phpMyAdmin

The database upload through phpMyAdmin has limitation of maximum file size. Launch your MAMP application and click on the “WebStart page” if it is not opened automatically.

The webstart page will show all the details of your local server installation. Navigate to the PHP section and click on the phpinfo link.

Search for “post_max_size” parameter. As you can see in the below example, MAMP set this parameter by default as 8MB.

On the webstart page click on phpMyAdmin link or go to “localhost/phpmyadmin” URL in the address bar. Here you can see all the databases used in MAMP server and click on the “Import” button. You will see the maximum size of the file to import is 8,192KiB which is nothing but 8MB limit set in “post_max_size” parameter.

Note that the “upload_max_filesize” parameter is also used to limit the upload file size; phpMyAdmin limit is determined from “post_max_size” parameter as it is an upload through HTML form. MAMP will limit the “upload_max_filesize” size to 32MB but phpMyAdmin will still use the lower limit of 8MB as defined in “post_max_size”.

Increasing Limit Using php.ini File

Increasing the PHP parameters is very easy. Create a plain text file, paste the below values and save it with the name as “php.ini”.

upload_max_filesize = 64M
post_max_size = 64M
max_execution_time = 3000
memorylimit = 512M

Go to “/Applications/MAMP/bin/php/“ folder and open the PHP version you are using in MAMP. You can view the PHP version by going to “MAMP > Preferences…>PHP”. If you use 7.2.7 version then open the folder “php7.2.7” and open “conf” folder.

Upload the “php.ini” file here to increase the values in the phpinfo and phpMyAdmin sections. But in our experience, this will not help in uploading larger database files of 100+ MB.

Importing Large MySQL Database File

If your live site database size is 100+ MB, then most probably the file import in phpMyAdmin will simply fail. Also there will problems when you have security and caching plugins that have tables in the database. These plugins will not allow uploading the database due to missing dependencies or verifications.

So the simple way to upload the database in MAMP server is to use Terminal command prompt. Follow the below instructions to upload the bigger database files in MAMP server.

Step 1 – Download SQL File

First step is to download the database file from your live site. You can ask your hosting company to help you or use cPanel backup options or use phpMyAdmin to export the database into SQL dump. You can also use plugins to download MySQL database from your WordPress installation. Remember the dumped database file will be in compressed format like .gzip or .zip. Extract the file and keep it in .sql format.

Step 2 – Open MAMP

Open MAMP application and ensure that the MySQL server is running. You should see “MySQL Server” and “Apache Server” statuses are showing as green in the app.

Step 3 – Using MySQL in Terminal

Press “Command + Spacebar” to launch Spotlight Search on your MacBook. Type “terminal” and open Terminal application. Enter the below command and hit enter.

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot

You will a welcome message to “MySQL Monitor” along with password usage warning and help. The command prompt will change from your username to “mysql>”.

The localhost database uses both username and password as “root”. So the uroot and proot in the Terminal commands are indicating the database username is root and the password is root. You will see the warning message for using the password in command line as insecure.

Step 4 – Handling Databases in Terminal

Before uploading the database, you need to create the same database name and use it in mysql Terminal prompt. Enter the following command to create a database with the name “test_db”.

CREATE DATABASE test_db;

Remember you should create the same database name that you download in step 1 from live site. Otherwise you should change the database. Now enter the below command to check the new database is created successfully.

show databases;

This command will show all the databases of the current MySQL Server running with MAMP.

Finally enter the below command to use the database for importing.

use test_db;

You will see a messages as “Database changed”.

Step 5 – Importing Database

Enter the below command to import the database you have download from live server in step 1.

SET autocommit=0 ; source /Applications/MAMP/htdocs/test_db.sql ; COMMIT ;

In this example, we use database name as “test_db.sql” which is saved under “/Applications/MAMP/htdocs/“ folder. If you don’t know the path of the database then just enter the following command in Terminal prompt and press spacebar to have a space.

SET autocommit=0 ; source

Open Finder and locate the database file, simply drag and drop no the Terminal to add the location of the file. Then you can enter the remaining command ; COMMIT ; to complete it.

SET autocommit=0 ; source SQL_database_file_path/database_name.sql ; COMMIT ;

Finish typing the command and hit enter. Terminal will start dumping the database and show query running status.

Step 6 – Check the Database

After the Terminal dumping is finished, open phpMyAdmin and check the database is created successfully. In our example, we have “test_db” successfully uploaded in phpMyadmin will the tables.

Remember after uploading the database you need to change the URLs from live server to localhost as explained in this article. After completing all the migration steps, you can check the localhost site having the entire content from the live site.