Importing and Exporting Databases in MAMP

When using MAMP and the phpMyAdmin interface provided, there may be times when you struggle to import and export large databases, perhaps due to timeout issues. Importing and Exporting Databases in MAMP using a command line interface will eradicate any problems with large database files.

MAMP provides mysql and mysqldump executable files within the Applications/MAMP/Library/bin directory. This allows the use of the mysql and mysqldump commands that can be used to import and export databases.

To import a database, you can use the mysql commands with the arguments specified below.

$ /Applications/MAMP/Library/bin/mysql -u[username] -p [database_name] < [database_file].sql

Where [username] is replaced by the database username, [database_name] replaced with the name of the database you are attempting to import the database too, and [database_file] replaced with the file name of the SQL file you are attempting to import.

After running the command, you’ll be prompted to enter the password for your database username on the next line. If you’ve not changed the database information that MAMP sets up for you when first installing the application, the username and password should both be root.

You could also add in the password on the original mysql command line.

$ /Applications/MAMP/Library/bin/mysql -u[username] -p[password] [database_name] < [database_file].sql

However, the general rule of thumb is not to type the password out when using the mysql command for security reasons because it displays the password in plain text.

Every time you want to import a database, you have to use /Applications/MAMP/Library/bin/mysql rather than mysql.

However, you can shorten this path by creating an alias. Simply open up your .bash_profile file, and paste in the following contents.

alias mysql='/Applications/MAMP/Library/bin/mysql'

This will allow you to just use the mysql command rather than have to type out the absolute path to the executable file.

$ mysql -u[username] -p [database_name] < [database_file].sql

To export a database, use the mysqldump command.

$ /Applications/MAMP/Library/bin/mysqldump -u[username] -p [database_name] > [database_file].sql

The arguments used in mysqldump are similar to those when using mysql, except a greater than sign is used rather than a less than sign.

The [database_file] option will be the name of the file that gets created when you export the database data.

Similarly, you can add an alias for mysqldump so that you don’t have to type out the path each time.

alias mysqldump='/Applications/MAMP/Library/bin/mysqldump'

You can also export the database as a gzip file by adding a pipe, |, followed by the name of the compressed file.

$ /Applications/MAMP/Library/bin/mysqldump -u[username] -p [database_name] | gzip > [database_file].sql.gz

Using commands for importing and exporting databases in MAMP prevents any PHP-related errors that may occur when trying to do so using phpMyAdmin. It is generally seen as a quicker method as well.

Note: This article is based on MAMP version 4.