Home » Web Tutorials » WordPress » How to Import Large MySQL Database in MAMP Using Terminal?

How to Import Large MySQL Database in MAMP Using Terminal?

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” option if it is not opened automatically.

Open WebStart Page in MAMP
Open WebStart Page in MAMP

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

Open phpinfo in MAMP
Open phpinfo in MAMP

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

Post Max Size Limit in phpMyAdmin
Post Max Size Limit in phpMyAdmin

On the WebStart page, click on phpMyAdmin link under “MySQL” tab 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” tab. 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.

Max File Upload Size in MAMP
Max File Upload Size in MAMP

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”.

Upload Max Filesize in MAMP
Upload Max Filesize in MAMP

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 folder you are using in MAMP. You can view the PHP version by opening MAMP app and check against “PHP version” option. For example – if you use 8.3.14 version, then go to “php8.3.14 > conf” folder.

Check MAMP PHP Version
Check MAMP PHP Version

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.

Update php.ini File
Update php.ini File

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 be 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 app. 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 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, click “Start” button and ensure that the MySQL server is running. You should see “Stop” button showing as green power button.

Start MAMP Server
Start MAMP Server

Step 3 – Using MySQL in Terminal

Press “Command + Spacebar” to launch Spotlight Search on your MacBook. Type “terminal” and open Terminal application. If you are using MAMP version 6 or lower, enter the below command and hit enter.

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

MAMP version 7 added MySQL server 8 and you have options to use either MySQL 5.7 or 8.0. You can check or change the MySQL server under “MAMP > Preferences > Server” and select the version against “Use MySQL server” option.

Check MySQL Server in MAMP
Check MySQL Server in MAMP

So, if you are using MySQL 5.7 with MAMP version 7 or higher, then use the following command:

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

If you are using MySQL 8.0 with MAMP version 7 or higher, then use the following command:

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

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

Entering MySQL in Terminal
Entering MySQL in Terminal

The localhost database uses both username and password as “root”. So, the uroot and proot in the Terminal command indicates the database username is root and the password is root. You will see a 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 that you should create the same database name that you download in step 1 from live site. So, make sure to change test_db to your live site database’s name. Now, enter the below command to check the new database is created successfully.

show databases;

This command will show all 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”.

Using Database Created
Using Database Created

Step 5 – Importing Database

Enter the below command to import the database you have downloaded 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 on 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.

Database Dumping Progress
Database Dumping Progress

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 the “test_db” database successfully uploaded in phpMyadmin with all 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. You can login to the local site using http://localhost/wp-admin/ (ports 80 & 3306) or http://localhost:8888/wp-admin/ (ports MAMP & default). You can set the ports under “MAMP > Preferences > Ports” and clicking the required button against “Set Web & MySQL ports to” option.

Note: You can use similar commands in Windows Command Prompt and on Adminer setup using correct path.

12 thoughts on “How to Import Large MySQL Database in MAMP Using Terminal?”

  1. Thanks a lot for this tutorial, I could able to upload the database. It seems MAMP is going for Pro version nowadays, do you have any suggestions for alternative for MAMP on MacBook?

    1. Local app from WP Engine is the best option. However, it will also have similar DB import issues when you have larger database.

  2. I am a complete newbie and was on the verge of giving up after having read, re-read and made several failed attempts to move a database to a new installation of mamp on a new computer! Thank you a thousand times over – simple, clear instruction….that works!!

  3. Very well done. I am a real newbie and had a little bit of a hard time following some parts like do i put test_db where you said to or do i put the name of the db that i downloaded. I went with the later. Seems very clear after I got through it but at the time I wasn’t sure.

Leave a Comment

Your email address will not be published. Required fields are marked *