MAMP is a great tool for running servers locally, however, when you must export that site you have been developing locally and your database is huge, then you could have some problems with PHPMyAdmin.
One solution is to use terminal. The reason that I am writing this post is so that I can reference it in the future. It always takes me more that 10 minutes to figure this out since I never use terminal, so I thought someone else may find this of some use.
I understand there are probably many ways to do this, but this works for me, so… try out the code below.
EXPORT DATABASE FROM MAMP
Step One:
- Open a new terminal window
Step Two:
- Navigate to the MAMP install by entering the following line in terminal
cd /applications/MAMP/library/bin
- Hit the enter key
Step Three:
- Write the dump command
./mysqldump -u [USERNAME] -p [DATA_BASENAME] > [PATH_TO_FILE]
- Hit the enter key
- Example:
./mysqldump -u root -p wp_database > /Applications/MAMP/htdocs/symposium10_wp/wp_db_onezero.sql
- Quick tip: to navigate to a folder quickly you can drag the folder into the terminal window and it will write the location of the folder. It was a great day when someone showed me this.
Step Four:
- This line of text should appear after you hit enter
Enter password:
- So guess what, type your password, keep in mind that the letters will not appear, but they are there
- Hit the enter key
Step Five:
- Check the location of where you stored your file, if it is there, SUCCESS
- Now you can import the database, which will be outlined next.
IMPORT DATABASE INTO MAMP
Step One:
- Open a new terminal window
- CAREFUL: This will replace all tables in the database you specify!
Step Two:
/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]
- Hit the Enter Key
- Example:
/applications/MAMP/library/bin/mysql -u root -p wordpress_db < /Applications/MAMP/htdocs/backupDB.sql
- Quick Tip: Don’t forget that you can simply drag the file into the terminal window and it will enter the location of the file for you.
Step Three:
- You should be prompted with the following line:
Enter password:
- Type your password, keep in mind that the letters will not appear, but they are there
- Hit the enter key
Step Four:
- Check if you database was successfully imported
- Navigate to phpMyAdmin in a browser
http://localhost:8888/MAMP/
I hope this helps, please let me know if anyone has any errors, questions or comments.
March 1, 2010 at 11:38 am
thanks!!f finally a quick and easy way to do this. what a timesaver! much appreciated
August 26, 2010 at 1:30 pm
This worked great but…
Maybe you could help with a small error, I’m having a weird issue when its importing half way though it says: ERROR 1062 (23000) at line 3065: Duplicate entry ‘itรs-151-node’ for key ‘word_sid_type’
Then stops importing the database.
August 26, 2010 at 2:24 pm
@Curtis Have you tried deleting all the entries in the database first? Look like it’s finding a duplicate entry.
September 24, 2010 at 4:51 am
Thanks for giving the way to do this
This worked great..
October 1, 2010 at 4:21 am
Worked like a treat. Had been struggling with this for hours… Thanks very much Nick!
May 13, 2011 at 7:45 am
All A little over my head when it comes to the programming but I will try it out for myself this weekend when I have some free time and motivation… summer is coming!
June 16, 2011 at 5:06 pm
Im getting no such file or directory when i drag my sql file into terminal (for the file location), I tried it from a couple of different locations, but still “No such file or directory”. Any Ideas?
August 23, 2011 at 6:05 pm
Thanks Nick, you saved my skin with this!
October 3, 2011 at 8:11 am
Wow! I can’t tell you how long I have been ggogling trying to figure out how to do this! Thank you so much! I am finally one step closer to having my development area working! Thank you so much for sharing your knowledge!
October 3, 2011 at 8:43 am
Yikes! I may have spoken too soon. After I entered the password, I had the solid grey rectangle sitting on the next line without any text so I assumed that SSH was busy working on my huge sql file. However, it’s been about an hour and a half and nothing has happened. ๐ I still have that rectangle with no text and when I use phpMyAdmin to look at the database, no tables have been imported. Any thoughts?
November 8, 2011 at 12:28 pm
We kneel in the long shadow of your excellence. Thanks from H & P
February 9, 2012 at 3:22 pm
I always read this but I’m happy to quote it now “you made my day”.
Not only for the terminal trick, importing like this is wonderful I hope you’ll never delete this page I’m going to take it as reference too from today
March 25, 2012 at 8:01 pm
You. are. an. absolute. champion ๐
I’ve been doing my head in for 3 days looking for a solution and yours worked ๐
May 7, 2012 at 8:32 am
Thanks for the post, Works fine.
May 8, 2012 at 6:38 pm
Nick. you just saved my day. thanks!
August 10, 2012 at 12:02 pm
This worked great – thanks! Just one note: I had to unzip the .sql.tar.gz for it to work with no errors. Maybe obvious to experienced users but figured I’d throw it in just in case.
August 14, 2012 at 4:15 pm
Worked great with 1.5 gb db (not zipped .sql ๐
Thank you very much Nick!
November 7, 2012 at 8:20 am
You are my last hope – followed your instructions but get ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) which is weird. Tried all possible passwords: my system password, then the one of the local MAMP db that I am trying to replace. Help!
November 8, 2012 at 10:32 am
HI Kat,
Sounds like you don’t have the right privileges to access mysql. Take a look at this post http://stackoverflow.com/questions/3033551/mamp-mysql-in-terminal . You might not have the user created, see here. http://stackoverflow.com/questions/10423198/mysql-error-1045-access-denied
Let me know if you are still having trouble. ๐
November 9, 2012 at 7:59 am
Thanks, Nick – in the end I opted for plan b which suddenly worked after restarting my Mac (always thought that was a PC thing, but no!): simply manipuating the php.ini file to extend the min_upload data I managed to import my 46Mb database in my MAMP phpMyAdmin (http://www.cyberciti.biz/faq/linux-unix-apache-increase-php-upload-limit/). My database knowledge is so basic that I honestly didn’t understand WHAT they were talking about in the links you sent – must do my homework that end. Many thanks for answering my post. Kind regards!
November 9, 2012 at 12:26 pm
Hi
Very clear tutorial, thank you.
I followed the steps, but after Import Step 3 I got:
ERROR 2006 (HY000) at line 469: MySQL server has gone away
Do you know how I can fix this? Looking at the db with phpmyadmin the first 15 tables were imported successfully but then it stopped… (about another 30 tables to go…).
November 14, 2012 at 6:01 am
Brilliant! You’ve saved my life. Been looking for this info all day. Thank you!
December 10, 2012 at 5:15 am
Thanks for this, a major timesaver. Worked perfectly for me!
March 4, 2013 at 6:53 am
FANTASTIC!!!!! You saved me hours ๐
April 6, 2013 at 9:25 am
Hi nick, thank you so much ! ๐
June 15, 2013 at 6:13 pm
Dude, thanks a lot. This tutorial was very helpfull for me!
Thank you!
April 27, 2015 at 3:40 pm
Muchas gracias, funciono bien.!
April 28, 2015 at 6:27 am
Thanks alot, this is great. Much better than using phpMyAdmin when handling huge databases!
May 6, 2015 at 6:29 am
Hi,
Really good tutorial, saved my time :))
July 14, 2015 at 11:00 pm
Where has this been my whole life!? ๐
Thank you! Thank you! Thank you!
October 14, 2015 at 10:26 am
Any idea why it would be creating a blank sql file? My database is NOT empty!
February 22, 2016 at 11:07 pm
Thanks! This was a really big help!
February 25, 2016 at 3:45 pm
wow, thanks for this dude, simple and easy!
January 3, 2018 at 2:18 pm
This isn’t working for me. I’m getting into the directory OK but when I get to the dump command I get -bash: ./mysqldump: No such file or directory. I am using symlinks and storing my websites in Dropbox. I don’t know that would change the file path or if I’m just not entering the file path correctly. I’m a little confused with this example: ./mysqldump -u root -p wp_database > and this /Applications/MAMP/htdocs/symposium10_wp/wp_db_onezero.sql
Are these separate commands? I entered ./mysqldump -u [username] [database name]
I couldn’t get past that but if I did I wouldn’t have known the name or where the .sql file was located in the second line.
January 16, 2018 at 8:56 am
Like Curtis above, I get a ERROR 1062 (23000) at line n: Duplicate entry ‘34001’ for key ‘PRIMARY’
I think there is a validation across multiple tables (there are indeed several records with id 34001, but in different tables.
May 3, 2018 at 9:51 am
Very old post, but still works like a charm. Thank you, saved my day!
July 23, 2018 at 12:06 pm
Worked like a charm and saved me so much time! Thank you!
March 12, 2019 at 11:20 am
Worked like a charm. Thanks for sharing!
April 4, 2019 at 5:41 pm
Thanks for the guide! Been using MAMP recently on my new project!
June 20, 2019 at 7:00 am
Thanks for this great help – yet
strange that some things work for some and not for others.
In my case I had to split step 2 into 2:
* /applications/MAMP/library/bin/mysql -u [USERNAME] -p
* (after login prompt)
* source [DATABASE_NAME]
July 11, 2019 at 10:19 am
Worked like charm for a crazy db I had, the final line number was 591601!
November 7, 2019 at 9:39 am
after upgrade to mysql5.7, show tables in my db returns 0 rows but phisical files are present in /var/lib/mysql/db/db. there i find .frm,.MYI,.MYD files. mysqldump does not backup that db as it thinks there are no tables…but it is there. I did not do a backup.mysqldump before it happened. Can I recover from it?
February 28, 2020 at 8:15 am
Still working after 10 years!!!! Thanxs!!!!
August 19, 2020 at 3:20 pm
This worked perfectly on my 1.7 Gb SQL file. Thank you for this fantastic time saver!
October 6, 2020 at 12:15 pm
This is so useful. I spent hours of searching on SO, etc… Thank you!
November 19, 2020 at 12:46 pm
Thank you.