Using the MySQL command line tool

MySQL has a useful command line tool which can be used to query and manage databases and users. This command is simply "mysql" and will usually be in the command path on Linux and BSD distributions, although to use it on Windows you would normally first need to change to the directory/folder that the MySQL binary applications are before running "mysql". This post gives a brief overview of logging in and a couple of commands for listing and changing databases, and running queries.

Logging in

The basic usage of the "mysql" command is this:

mysql -u [username] -p

The -u flag allows you to specify the username to log in as, replacing [username] in the example above with your username, and the -p flag that you want to enter a password. You can omit these but after starting up the command prompt you won't be able to do much. With the -p flag specified, you'll be prompted for a password before continuing. You can also tell the MySQL command line client to automatically select a particular database by specifying it at the end, e.g.:

mysql -u [username] -p [database name]

After entering your password (successfully), the mysql command prompt will start and you'll see something like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can quit from the MySQL command line client at any time by entering \q and then <enter>

Running queries

To run a query, enter your query, follow it with ; and then hit the enter key. For example:

select * from my_example_table_name; 

Selecting/changing a database

You can change to a different database at any stage that you have the rights to access. If you passed one in as a command line argument then it will have been selected automatically, but you can change it at any stage too. To select a specific database, do this:

use [database name];

This will change to the specified database, if it exists. It it doesn't, you'll get an error like this:

ERROR 1049 (42000): Unknown database 'testing123'

Listing available databases

You can list the available databases by doing this:

show databases;

This will show a list of databases which you can then change to using the "use" command detailed above. For example:

+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| admin                 |
| electrictoolbox       |
| mysql                 |
| test                  |
+-----------------------+
5 rows in set (0.00 sec)