Coming from a Linux background, one of the things I didn’t like about MAMP was the way it hid away the mysql command line interface. Once you are comfortable using it, the CLI is a powerful and quick method of interacting with a mysql server. I just wanted to share a quick and easy way to access the MAMP mysql command line client on Mac OSX.
Firstly you can run the Mac OSX terminal easily by just typing:
ctrl + space bar (to access spotlight)
Then start typing ‘Terminal’ before you have even finished the little Apple gnomes will have guessed you want to open the Terminal (which of course you do). Start the Terminal by hitting enter when spotlight highlights the Terminal app.
Now that you are in a Terminal you are able to do all sorts of excellent things to your Mac. For now though, we’ll stick to running the mysql client. The client is located in /Applications/MAMP/Library/bin so to run it execute this command:
/Applications/MAMP/Library/bin/mysql -uroot -p |
The -uroot tells the client you want to log in as user ‘root’ if you have other users you could equally log in as those. The -p tells the client to ask for a password, you can pass the password to the client so it won’t ask, but this will leave your password dangling in places like your bash history file. If you don’t care about that – then by all means change the -p to -psecret where your password replaces ‘secret’.
If you leave the -p as it is, you’ll be prompted for your password, which you can see on the MAMP ‘start page’ in case you have forgotten. If you have done it right you should now see a command line prompt like this:
mysql> |
This is where the magic happens. I won’t go in to too much detail because there are roughly 2 million mysql books in existence, testament to the fact there is a lot to cover, far more than I could get through in a single blog post. However, to give you a few commands to try out, here are a handful of useful mysql commands.
USE database_name |
Where database_name is the name of the database you are interested in inspecting/changing. This sets the client context to given database. You can pass a database name as an argument to the client, to pre-set the context. For example:
/Applications/MAMP/Library/bin/mysql -uroot -p my_db |
Will implicitly set the client to use ‘my_db’ when it starts, so that you do not have to type the USE command.
SHOW TABLES; |
This command will print a list of tables in the current database. This is useful if you are trying to figure out what tables exist in a new database, or to find out if there is some sort of logging table (e.g. the Google Checkout logging table in Magento) that might be useful.
To get an idea for how big a table is (in terms of row count) you can run a quick:
SELECT COUNT(*) FROM table_name; |
This will report how many rows are in the table named table_name. This can be useful if you are about to select * from the table, because it might save you spamming your terminal full of query output.
One last handy little trick is the formatting for query output. By default it will come out in row format which, for queries with lots of result rows or lots of selected columns, can be hard to read. By replacing the semi-colon ; at the end of a query with a \G you can cause the output to be made vertical in the format field:value.
For example:
SELECT * FROM table_name\G |
Will show all the rows from table_name in a more easily readable vertical format.
Extra for Experts: adding a local bin dir to make running MySQL easier
Based on comment feedback from daffy I’m adding a little guide to make running mysql easier.
Firstly make a directory for your local bin:
mkdir ~/bin |
Now link the mysql binary (and as many others as you want) in to this directory.
cd ~/bin # The syntax is ln -s TARGET LINK_NAME ln -s /Applications/MAMP/Library/bin/mysql mysql |
Lastly edit your .bash_profile file (it’s in your home directory) and add the following line in order to add your new local bin directory to your path:
# If you use vi, you'll win friends and influence people # i puts you into insert mode, ESC takes you out of it. # :x saves and exits # shift+g moves the cursor to the end vi ~/.bash_profile |
Then add this line – but if you already have a PATH declaration, you can just add it to the end of that:
# For example mine is: /Users/ashley/bin/ export PATH=$PATH:/Users/_your_username_here/bin/ |
Now you can run MySQL by just typing mysql on the terminal command line.
Troubleshooting:
1) Check your PATH contains your local bin:
echo $PATH |
2) Check your bin directory has the link correctly:
ls -la ~/bin |
3) Check the mysql being run is the one from your local bin:
which mysql |
Well, that concludes the little 10 minute introduction to running and using the mysql command line client for MAMP in Mac OSX. You can find out much much more about the wonders of mysql by reading the mysql manual.
Wow, short and sweet and very helpful. Nice run-through! Thanks!! 🙂
Yay! You made my day buddy! Was looking everywhere trying to make it work. It will be a challenge working on terminal given my background with GUI applications to do the same.
I like your blog, simple, nice to the point….
Cheers!
Kiran
Outstanding tutorial, saved me loads of time and effort after many, many dead ends. thanks!
I was getting nowhere with various overpriced books until I followed this tutorial. God bless!
Alternatively you can create a link so all you will have to do is call mysql like this:
mysql -uroot -p
By entering these commands into terminal then you wont have to type the long command anymore.
//Fix mysql command line link for mamp
sudo ln -s /Applications/MAMP/Library/bin/mysql /usr/local/bin/mysql
sudo ln -s /Applications/MAMP/Library/bin/mysqldump /usr/local/bin/mysqldump
sudo ln -s /Applications/MAMP/Library/bin/mysqladmin /usr/local/bin/mysqladmin
very helpful, thanks a lot!
Wow, looks like this will be very useful. Just going to start an “Intro to SQL” course using mySQL at eclasses.org, and wasn’t sure how to get SQL commands working in MAMP. Thanks very much in advance.
Definitely took me out of a jam. I had a huge database to Import that was too big for phpMyAdmin, so I just went in to MySQL using Terminal as described and used the source statement to import the database. It took about 30 seconds.
Thanks.
wow thanks!! I knew how to do this on windows, but didn’t know how to do this on my mac. saved me a lot of time!
thanks again for this post
Sweet!!… thanks..
And also thanks to Scott for the tip for the short command.
Very helpful, I ran into the same issue as Michael, having a database too large to import with phpMyAdmin. After getting a handle on the CLI, phpMyAdmin is a pain to use, so I’m glad to have come across this workaround for MAMP.
As for getting to the mySQL command line as lazily as possible, I went this route:
cd ~
vi .bash_profile
Use the vi editor to add this to your profile:
# mysql command line access for mamp
alias db=”/Applications/MAMP/Library/bin/mysql -uroot -p”
Save your change, quit vi editor, and reload your profile (. ~/.bash_profile). Now you can just type “db” and you’ll be prompted for your MAMP root password.
Awesome. Just what I needed. Was using myPHPadmin and trying to import a database that was larger than 32mb. Much thanks!
Thanks.
Assuming that a database is already created in phpmyadmin,
and that the sql file you ant to load is somewhere your mac.
Here is a 1 line command I use to quickly setup a local environment from a web downloaded sqlfile.
/Applications/MAMP/Library/bin/mysql -uroot -proot tan -e “drop database tan;” && /Applications/MAMP/Library/bin/mysql -uroot -proot -e “create database tan;” && /Applications/MAMP/Library/bin/mysql -uroot -proot -e “source /anyFolder/…/mySqlBackup.sql” tan
Hope this helps someone…
Great post. Very useful.
Thanks.
if you want to get mysql to work without typing in the full PATH to the mysql executable in the bin directory..
look at the install docs for mysql..
you add a symbolic link to the directory where the mysql installation resides.. ie in MAMP
check out this guys website for configuring mysql for the command line..
then you can just type
mysql
at the command prompt and it works..
http://www.tonyamoyal.com/2010/04/13/install-mysql-on-mac-os-x-10-6-and-add-startupitem/
D
Thanks, I’ve added a similar step above based on your feedback – I actually have a local bin dir that I symlink things into on Mac, and then add my local bin to the path. I’ve included instructions for that, if anyone is interested. See the extra for experts section for more info.
Thanks! Very useful!
Thank you kindly for the article! Don’t forget to restart apache afterwards so the path setting gets read. I didn’t notice this in the tutorial, but maybe could be added to the troubleshooting or steps?
Thanks again!
Hi Ashley,
Thanks so much for the step-by-instructions.
MySQL is new to me …added to that recently… switched to Mac…was breaking my head on how to use Terminal to access MySQL….to create database and upload about 6000 records. Was gathering info from net in bits-n-pieces….Thanks again.
Hi Ashley, this proved quite useful for me to get started with CLI, thank you for posting 🙂
2 days ago on my MacPro OS 10.6.8 I uploaded the latest Adobe updates. My Dreamweaver CS4 was also starting to crash. I had to get rid of a Configuration-1 that had formed. No more repeated crashes.
But I could not get the Dreamweaver connections actions to see databases any more. I reinstalled MAMP after saving the db folder and reinstalling it. Mamp’s PHP Admin sees databases just fine.
Navicat Lite which I just installed today sees my mysql databases perfectly and immediately.
Dreamweaver CS4 fails in every possible way to see the tables. I have spent two days going from “solution” to solution. I have changed PHP and Mysql ports back and forth between 8888 & 8889 to 80 & 3306, I have done terminal sudo commands to create a “symbolic” link between the mysql socket in MAMP in the Application folder etc. etc.
But every single connection that I create or try to re-use from days ago fails. No tables seen.
I have removed connections and started over. I have all the Dreamweaver site files in the Mamp htdocs folder. The Dreamweaver site shows that my local and testing server urls are working.
PHP works fine. either localhost or localhost.8888 works, depending on whether I have started MAMP in 80 & 3306 or 8888 & 8889 for apache & mysql.
I see in the connection files in Dreamweaver that in the MAC folder it says that it is going to use HTTP as the connection method if it detects that it is on a Mac computer.
In the Navicat Lite download it included a folder with connection scripts for setting up HTTP Tunnel. I put that folder into the MAMP installation to see if I could figure out how to use them.
But I can’t find information on how to set up HTTP tunnel.
At any rate, PHPMyAdmin and Navicat Lite can connect to my mysql databases created inside MAMP.
I now need ONE solution that FORCES Dreamweaver to use the proper connection string, to go looking for the same socket or data connection method that works by default.
Only Dreamweaver is too stupid to find it.
I say this because I have worked with Dreamweaver since the 90’s on Windows machines. Version after version of Dreamweaver NEVER gets this issue permanently resolved.
Have you any special knowledge of how to MODIFY Dreamweaver connectivity to see what every other mysql product sees instantly?
Thank you for something really creative. I have spent two days reading and trying all the standard resolutions.
Very useful, thank you, Ashley!
This was very helpful. Thanks.
Thanks a lot, very useful.
2 years later and still so relevant. Thanks!!
Great, relevant content. Thanks Ashley!
Thanks! Very helpful! 🙂
Hello!
When I execute the command line, after entering my password I get this message:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)
Do you know what happens?
I run under mac os 10.7.3
Or do you know any other mysql CLI for Mac?
Thank you for your kind reply!
Ok forget all about my message, I have the answer.
Have a nice day!
Thank you. I was veering back towards phpMyAdmin until I read this and now have new hope for learning mysql the proper way.
Ashley,
Thanks for this tip.
Needed to restart the terminal session and worked like a dream.
Peace,
Simon
You can also go to:
Applications/MAMP/Library/Bin/
and then double click on ‘mysql’. It brings it right up. However, as always, TMTOWTDI.
Thanks! Usefull!
Thanks man! That answer was more helpful than what I was finding on Stack Overflow.
Спасибо,очень помогла статья!)
thanks a lot..!!
worked excellently.. 🙂
Great tutorial. Thanks
One thing missing on setting up ~/.bash_profile
After editing the file, you need to source it (reload .bash_profile) before you can use the mysql command or you need to log out and back in.
To source it, from the command line, enter source ~/.bash_profile