How to use the MAMP Mysql command line client in a terminal

Posted by Ashley on Mar 17, 2009 • (18)

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.

18 Comments

Leo says:
March 23rd, 2009 at 10:15 pm

Wow, short and sweet and very helpful. Nice run-through! Thanks!!
Kiran says:
April 10th, 2009 at 6:00 am

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
manual labour says:
April 14th, 2009 at 12:14 pm

Outstanding tutorial, saved me loads of time and effort after many, many dead ends. thanks!
Dawood Abbas says:
May 6th, 2009 at 8:38 pm

I was getting nowhere with various overpriced books until I followed this tutorial. God bless!
Scott says:
July 24th, 2009 at 3:31 am

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
stefan says:
August 29th, 2009 at 12:12 am

very helpful, thanks a lot!
Geoff Taylor says:
October 26th, 2009 at 1:14 am

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.
Michael Peters says:
January 7th, 2010 at 3:51 am

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.
victor says:
January 21st, 2010 at 10:54 pm

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
lichi says:
February 18th, 2010 at 8:34 am

Sweet!!… thanks..
And also thanks to Scott for the tip for the short command.
Sarah says:
March 2nd, 2010 at 9:56 am

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.
Nicholas Blexrud says:
March 2nd, 2010 at 3:06 pm

Awesome. Just what I needed. Was using myPHPadmin and trying to import a database that was larger than 32mb. Much thanks!
ling says:
April 22nd, 2010 at 10:37 pm

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…
Chris says:
May 15th, 2010 at 6:17 pm

Great post. Very useful.

Thanks.
daffy says:
July 2nd, 2010 at 7:01 am

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
Ashley says:
July 2nd, 2010 at 5:50 pm

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.
Ivan says:
August 25th, 2010 at 11:12 pm

Thanks! Very useful!
Adam says:
August 31st, 2010 at 4:06 am

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!