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
-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
-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:
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.
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
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.
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:
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.
1) Check your PATH contains your local bin:
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:
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.