MySQL command line essentials for OS X

I use phpMyAdmin for most of work with MySQL database tables. It's a fantastic GUI that helps me better conceptualize my databases's architectures which speeds my development.

However, I've found it's still important to understand how to work with MySQL in the OS X Terminal environment. For example, consider some of these useful gems the next time you find yourself in command-line mode:

You need to type /usr/local/mysql/bin in front of every mysql-related command in the Terminal. To avoid this, add /usr/local/mysql/bin into your shell's PATH environment: echo 'setenv PATH /usr/local/mysql/bin:$PATH' >> ~/.tcshrc (for the tchs shell). You can also add the line manually to your `$HOME/.tcshrc file. If no `.tcshrc' file exists, create one with pico or vi.

For the bash shell, the command is echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile. This will create a file in your home directory named .bash_profile (to see it, you can issue ls -a in your home directory to see all hidden files). IMPORTANT: After issuing the command you'll need to quit the Terminal and re-launch to see it work. Remember, if you would like to change shells in Panther and above (OX 10.3+), type chsh -s /bin/tcsh (-s for superuser?) or set your default shell in OS X Terminal's preferences (Terminal >Preferences).

UPDATE 3/30/07
Forget about the Terminal > Preferences option for Jaguar 10.2.x. Use the Netinfo command line option:

niutil -createprop . /users/admin shell /bin/tcsh

Why?
"When you set the shell preference in the Terminal, you are only setting the shell that the Terminal program runs for you when it is invoked. Any other use of the shell by the system or through other means (such as, when you ssh into your box remotely) uses the shell specified in NetInfo. For instance, say you want to use the bash shell when you su to root. Setting the Terminal variable won't do that for you; when you run su, you'll find yourself as root but running the default shell of tcsh. Now, if you change the shell assigned to the root account in NetInfo, then when you su you get the shell you wanted. Same goes for other applications that can invoke shells like vi or whatnot."
- acet (Matt) macosxhints.com article

I've read that you can also add aliases to your shell's resource files:
alias mysql /usr/local/mysql/bin/mysql (/usr/bin/mysql on OS 10.2 Server!!!).
alias mysqladmin /usr/local/mysql/bin/mysqladmin.
For the bash shell use:
alias mysql=/usr/local/mysql/bin/mysql

Added 1/2/07!! Consider this article for configuring the OS X UNIX/shell environment.

Good instructions for MySQL setup under OS X including the OS X MySQL installation .pkg can be found on Marc Liyanage's www.entropy.ch site here! There are also instructions at entropy explaining how to re-initialize a database in case something gets screwed up (not that this has ever happened to me! :-)

To start MySQL under OS X, type cd /usr/local/mysql. Then type sudo chown -R mysql data/ (enter password) to start the server, issue sudo echo first, then type sudo ./bin/mysqld_safe &.

To stop your instance of mysql:
[norman:/usr/local/mysql]root# /usr/local/mysql/bin/mysqladmin -h norman.local -u root -p shutdown.

The very first thing to do (after a clean installation) is to secure your open master password: /usr/local/mysql/bin/mysqladmin -u root password new_password.
/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password "newpasswd"
(the backtick character will replace `hostname` with the output of your system's hostname).

To log in to your box remotely, type ssh -l username my.hostname.com. To get to the mysql prompt (mysql>) type mysql (remember the setenv PATH stuff above, and don't forget that different paths to mysql exist between OS X and OS X Server! [heavy sigh]). To access the MySQL server, type mysql -u root -p. To access a specific database, type mysql databasename -u USER -pPASSWORD.

Other common commands:
create database databasename;
show databases;
use databasename;
show tables;
GRANT ALL ON databasename.* TO name@localhost IDENTIFIED BY "password"
Typing quit or exit will end your mysql session.

To import an sql dump, type the following from the shell (NOT the mysql prompt!):
mysql -u root -p password databasename < /Users/Desktop/name_of_file.sql

To dump a database, type:
mysqldump -u USER -pPASSWORD DATABASE >name_of_database.sql

I might add that a data *.sql dump using phpMyAdmin doesn't always work or behave the way you might expect when it's time to import it (perhaps on to another test or production server). First, make sure you are logged into the target MySQL server as root to ensure you have enough privileges to perform the import operation. Then, if you still experience problems, try opening the *.sql dump file in a text editor like BBEdit and then re-save it with the following options:
File Creator: (generic text file)
Save State: None
Line Breaks: Unix
Many times this will fix whatever was wrong with the file (an unrecognized character or line return?) and your import will work like it should (this applies to .csv files, too)!