I use the mysql command-line client a lot. It acts like a mini-shell that makes it easy to send commands or queries to a MySQL server and view the results. However, its navigational facilities aren't terribly shell-like.

To change to a particular database, you must use the "USE" command:

mysql> use test
Database changed

To get a list of databases, you must use the "SHOW DATABASES" command:

mysql> show databases;
+------------+
| Database   |
+------------+
| Tribute911 |
| mysql      |
| test       |
+------------+
3 rows in set (0.07 sec)

To get a list of tables within a database, you'd typically use the "SHOW TABLES" command after having USEd that database:

mysql> use test
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| BODY_SIDE      |
| Filer          |
| aatest         |
| blah           |
| http_auth      |
| passwd         |
| t1             |
| t2             |
| test           |
+----------------+
9 rows in set (0.07 sec)

Often times I'll forget that I'm at a "mysql>" command prompt, type "ls" or "cd", and just expect it to know what I want.

Today I fixed that bug. As of now, my copy of the client understands the "cd" and "ls" commands. The patch is available (against the latest 4.0 BitKeeper tree) and has been sent to the developers for possible inclusion.

Here it is in action:

mysql> ls
+------------+
| Database   |
+------------+
| Tribute911 |
| mysql      |
| test       |
+------------+
3 rows in set (0.07 sec)

mysql> cd mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ls
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.07 sec)

I like it. :-)

Update: In the comments, Russell asks about setting the prompt to contain the current database name. Of course you can do that!

mysql> prompt mysql (\d)>
PROMPT set to 'mysql (\d)> '
mysql ((none))> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql (mysql)>

That was added quite a while ago.

Posted by jzawodn at July 15, 2004 01:11 PM

Reader Comments
# Russ said:


Nice! Now if you could just get the prompt to tell you which DB you're in (like a Unix command prompt), that would *rock*.

-Russ

on July 15, 2004 01:30 PM
# Seth Finkelstein said:

Cool!

But don't stop there.

mkdir == create database

rmdir == drop database

ls -l table == describe table

And so on. Sort of the reverse of the shell which aped the game of Adventure.

on July 15, 2004 01:35 PM
# Ivan Tumanov said:

I've actually given some thought to incorporating mysql commands into a regular shell.. extend the filesystem to treat mysql as a set of directories and files, etc..

The things that complicate the situation are: 1) authentication to get into mysql databases - since mysql doesn't use /etc/passwd users. 2) issuing mysql statements would be a pain because you'd have to escape sertain characters on the command line, even if you had SELECT, DELETE, UPDATE shell binaries that just passed stuff to the mysql client.

Your mods are definitely a step in the right direction. I find that the mysql command-line client is by far the most convenient interface to a database server I've used, and there's no harm in making it even more convenient. In fact, there's room to maybe add a .mysqlrc file to user directories and read a bunch of alias commands from there. :)

on July 15, 2004 01:41 PM
# Doug Schasteen said:

Ha. You aren't the only one who repeatedly types ls accidentally while using mysql. I like the addition, though I don't think I agree with making other shell commands do things. I'd rather see rmdir give an error than drop a database. But maybe thats just me. ls, on the other hand, is harmlessly helpful.

on July 15, 2004 01:42 PM
# kaishaku said:

I don't know how many times I've done the same thing trying to CD to a database or LS the tables in a database. I sure hope they accept this for inclusion.
Another nice feature for the MySQL Client would be to provide additional info for the SHOW DATABASES (such as number of tables in each database) and SHOW TABLES (such as number of columns and rows for each) commands. But maybe that's asking too much.
Otherwise, I really don't have any gripes with the client's performance.

on July 15, 2004 01:52 PM
# Russ said:


"Of course you can do that! ... That was added quite a while ago."

Nice again! That should really be the default.

on July 15, 2004 01:52 PM
# Mike Kruckenberg said:

Had to laugh because of course I've done that. Even worse, typed 'exit' and/or 'ls -l' etc into IM windows not realizing the terminal wasn't in focus.

Was thinking it would be cool to have something like 'alias' inside the MySQL shell to create shortcuts for common commands like "show status like 'Qcache%'". Maybe that's overkill, but it did prompt me to make a shell alais: alias qq="mysql -e \"show status like 'Qcache%'\""

on July 15, 2004 02:12 PM
# Ryan said:

That is just cool!

on July 15, 2004 02:18 PM
# Scott Johnson said:

I'm partial to the "alias" syntax that Mike Kruckenberg mentioned above. It would be nice to be able to setup a .mysqlrc file with aliases for commonly used commands.

on July 15, 2004 02:40 PM
# d2s said:

Nice to notice that people are trying to make MySQL easier to use. Indeed some kind of support for aliases could be nice thing also so that would allow do more things in less time. Still that if there are some errors in aliases that could propably destroy much of the content of the database if not used properly (but that would be possible anyway if user don't know what he/she is doing).

on July 15, 2004 02:40 PM
# mikedaddy said:

Another thing that would be nice is wildcards. I would love to be able to enter:

use bigdatabase;
show tables;
describe %; or describe 'user%';

Anything to be able to describe all my tables in a database.

on July 15, 2004 03:02 PM
# Peter van Dijk said:

Seth: The funny thing is that 'rmdir' and 'mkdir' are actually the only bloody thing that mySQL -does- on a DROP DATABASE/CREATE DATABASE ;)

Ivan: if you ever get around to your plans, you may want to look at my dteq tool (http://www.dataloss.nl/software/dteq/), it allows for easy formatting of mysql query results. It's mostly used for generating configuration files from mySQL but it can be useful in a plain shell environment too. I've been trying to come up with a couple of aliases (like SELECT, UPDATE) but it's not trivial - and I've been getting on without aliases for years now :)

on July 15, 2004 03:17 PM
# said:

Please don't have them add hardcoded commands! create and Alias keyword. So that everyone can create their own little hacks.

It's a neat idea.. none the less. As I'm sure many people type ls all over the place.. like IRC :)

on July 15, 2004 03:27 PM
# Philip Tellis said:

Why not make a mysql file system module for linux/BSD. That way one could just mount a database and cd to it.

on July 16, 2004 01:35 AM
# Dan said:

Philip, you mean like the project at http://no.spam.ee/~tonu/modules.php?name=News&new_topic=2

?

on July 16, 2004 05:04 AM
# duncan said:

That's great - the best ideas are always the simplest.

However I wouldn't take it any further and go down the mkdir/rmdir road as otherwise you'll end up rm'ing the wrong things and ls'ing the wrong things - which will just give you the same problem in reverse ;-)

on July 16, 2004 07:39 AM
# duncan said:

That's great - the best ideas are always the simplest.

However I wouldn't take it any further and go down the mkdir/rmdir road as otherwise you'll end up rm'ing the wrong things and ls'ing the wrong things - which will just give you the same problem in reverse ;-)

on July 16, 2004 07:40 AM
# Basil Hussain said:

Cool. I also like the idea of the filesystem access to MySQL. I sometimes try to do a "select * from foo..." at the command prompt.

Also, my excitment about customising the MySQL client's prompt was promptly dashed (pun intended), as that was only added in version 4.0.2. I really must get around to upgrading from 3.23...

on July 16, 2004 07:46 AM
# gabriel ricard said:

I imagine that most of us have been doing this secretly and each time yelling something along the lines of "Newman!" at MySQL when it comes back with a parse error.

Bravo, Jeremy!

on July 16, 2004 08:02 AM
# Eric said:

Actually, what I really want is:
show processlist order by State, Info; or
select * from 'show processlist' where info is not NULL.
Of course, neither of these things make it more unix like, just would make my life easier at times.

on July 16, 2004 09:01 AM
# dennis said:

This is a tremendous bit of business; nicely done. I suspect it'll be difficult to roll between installations that have/don't have these capabilities. :)

on July 17, 2004 06:40 AM
# Michael said:

Good!

on January 7, 2005 12:01 AM
# arben said:

hey i have problem with mysql databasse

radiomar 17057 0.0 0.9 10408 4096 ? S 18:15 0:00 /usr/bin/php radio_stats.php
radiomar 17068 0.0 0.9 10412 4100 ? S 18:16 0:00 /usr/bin/php radio_stats.php
radiomar 17089 0.0 0.9 10412 4096 ? S 18:16 0:00 /usr/bin/php radio_stats.php
radiomar 17092 0.0 0.9 10412 4100 ? S 18:16 0:00 /usr/bin/php radio_stats.php
now shows to me this and ussign my server load slow and i cant open my website how i can stop this

on June 10, 2005 09:19 AM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.