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.